How to enable remote access to MySQL database?

Usually remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to MySQL database server from home or a web server. You can follow these steps to enable it.

Step 1 : Log in using SSH (if server is outside your environment or intranet)

First, login over ssh to remote MySQL database server from windows using PuTTy or from Linux using SSH

Step 2 : Edit the my.cnf file

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi:

On Debian Linux file is located at /etc/mysql/my.cnf location.
On Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnflocation.

To edit /etc/my.cnf, run:

# vi /etc/my.cnf

On Windows, my.ini file is located at C:\Program Files\MySQL\MySQL Server X.Y (X.Y is version number of MySQL server)

For windows, open my.ini file in notepad

Step 3: Once file is opened, locate line that reads as follows

[mysqld]

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 173.234.21.12 then entire block will look like as follows:

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 173.234.21.12
# skip-networking
....

Where,

bind-address: IP address to bind to.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or commented.

Step 4: Save and Close the file

On Debian / Ubuntu Linux, type the following command to restart the mysql server:

# /etc/init.d/mysql restart

On RHEL / CentOS / Fedora / Scientific Linux, type the following command to restart the mysql server:

# /etc/init.d/mysqld restart

On Windows, open command prompt as Administrator and type

net stop MySQL
net start MySQL

Step 5: Grant access to remote IP address

Grant access to a new database

If you want to add a new database called foo for user bar and remote IP 162.72.20.23 then you need to type the following commands at mysql prompt:

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'162.72.20.23' IDENTIFIED BY 'PASSWORD';

Grant Access To An Existing Database

Let us assume that you are always making connection from remote IP called 162.72.20.23 to database called webdb for user webadmin. To grant access to this IP address for existing database type the following command at mysql prompt:

mysql> update db set Host='162.72.20.23' where Db='webdb';
mysql> update user set Host='162.72.20.23' where user='webadmin';

Step 6: Logout of MySQL

Type exit command to logout of mysql:

mysql> exit

Step 7: Open port 3306

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 162.72.20.23:

/sbin/iptables -A INPUT -i eth0 -s 162.72.20.23 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

Finally save all rules (RHEL / CentOS specific command):
# service iptables save

On windows, go to Control Panel > Windows Firewall > Add inbound rule for port 3306

Step 8: Test remote access to MySQL database

From your remote system or your desktop type the following command:

$ mysql -u webadmin –h 173.234.21.12 –p

On windows, run the MySQL command-line program, and log in as an administrator by typing the following command from the MySQL root folder (e.g Program Files\MySQL\MySQL Server 5.5\bin):

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u webadmin –h 173.234.21.12 –p