How to Enable Remote Access to Mysql database server?

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 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, the mysql command should be as follows:

mysql> use mysql
mysql> GRANT ALL ON db_name.* TO 'db_user'@'' IDENTIFIED\

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\

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

        -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

This entry was posted on Tuesday, March 13th, 2012 and is filed under Mysql & PostGres SQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.