Accessing MySQL database from outside localhost

When I installed or use certain application that needed a MySQL database, mostly it will be accessing from a localhost. For example like the Apache Server and etc. It is much more convenient and easy to configure.

However, as my project require to have a dedicated centralize database server, and the application is reside in other server, so I have to configure the application and MySQL to allow connection from outside localhost. As we all know, by default, MySQL only allow connection or access from within the localhost.

So here’s the solution for this case,

edit your /etc/mysql/my.cnf and change the bind address:

$ sudo vim /etc/mysql/my.cnf

find the word ‘bind-address’ and change 127.0.0.1 to your IP.

Then login to the mysql console:

$ mysql -uroot -pYOURROOTPASSWORD
mysql> GRANT ALL PRIVILEGES ON *.* TO USERNAME@OUTSIDE-IP IDENTIFIED BY “PASSWORD”;
mysql> FLUSH PRIVILEGES;
mysql> exit

Now you can access your database from outside localhost:

$ mysql -uUSERNAME -pPASSWORD -h MYSQL_SERVER_IP