Providing remote access to MySQL server in Linux / Ubuntu

You have installed all the basic requirements for your web server like apache, php, mysql, phpmyadmin and git. Then you test out the server connection and hit the server’s IP address. A apache welcome page opens up. Hurray!!! Your apache web server is working perfectly. Then you try to establish a mysql database connection. Then Oh Snap! MySQL denied your service.

It means that your web server’s mysql still needs to have remote access configured.

To configure remote access to the MySQL server follow the below steps.

Log into mysql with

$ mysql -u root -p
Enter Password :

Enter the password and you are logged in into the mysql.

Then use mysql database.

mysql> use mysql;
mysql> SELECT host,user from user.

This will give you the list of hosts and the users.

mysql> GRANT ALL PRIVILEGES ON *.* TO username@"%" INDENTIFIED BY "password"
mysql>FLUSH PRIVILEGES

Where username is the database user and the password is the corresponding users password. Basically username will be root and the password will also be root.

Exit mysql. Now to edit the configuration files for mysql.

For Ubuntu 14.04 got to file /etc/mysql/my.cnf incase of Ubuntu 16.04 /etc/mysql/mysql.conf.d/mysqld.cnf

In the file comment out the line that says

bind-address = 127.0.0.1

into

# bind-address = 127.0.0.1

The line means that bind the connection of the mysql only to 127.0.0.1 i.e. localhost. So any requests outside localhost will not be served by the mysql server. We don’t want that. So we comment out this line and allow any IP to access our mysql server.

Be aware that the remote access should be enabled only in mysql servers working on a local network and not in any publicly accessible servers.

So done that been there.

Lets move on.

Finally we restart the apache server.

$ sudo service apache2 restart

In some situations the above procedures are not enough. Firewall may also be blocked for connection to the mysql server.

So if the connection to the mysql server is still denied. You need to open port 3600 for the mysql connection.