LinuxHostingSupport

By default, Mysql doesn’t allow remote connections for security reasons. However, sometimes you or your client may want to acess the database remotely i.e. from a different server Or from home Or a developer who is working on the website.

To grant remote access to a Mysql databas, the first thing is to make sure ‘skip-networking’ is not enabled in Mysql configuration i.e. in the my.cnf file.

On a CentOS/RHEL server, the file is located at /etc/my.cnf
On a Debian/Ubuntu server, the file is located at /etc/mysql/my.cnf

1) SSH to the server and comment/remove the line from my.cnf that says:

skip-networking

where,
        skip-networking means don’t listen on TCP/IP connections but only from Unix sockets. This is recommended on systems where only local connections are allowed. However, as you need to allow remote connections, skip-networking must be removed/commented in my.cnf as stated above.

2) Save the file and restart the mysql service

# /etc/init.d/mysqld restart

3) Connect to the Mysql server to grant access to a remote IP address.

# mysql -uroot -p

4) Now, for example, if you want to allow access to database ‘db_name’ for user ‘db_user’ and the remote IP is 1.1.1.1, the mysql command should be as follows:

mysql> use mysql
mysql> GRANT ALL ON db_name.* TO 'db_user'@'1.1.1.1' IDENTIFIED\
BY 'PASSWORD';

If your IP is NOT static and you want to grant access to the database from any IP, use % instead of IP, i.e.

mysql> GRANT ALL ON db_name.* TO 'db_user'@'%' IDENTIFIED\
BY 'PASSWORD';

5) Logout of Mysql and make sure port 3306 is open in your server firewall. Test the remote mysql connection from your local computer Or a different server:

# mysql -u db_user -h serverIP -D db_name -p

where,
        -u db-user: db_user is the database username.
        -h serverIP: ServerIP is the IP of your mysql server.
        -D db_name: db_name is the database name
        -p: password of the database user