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
Where,
- -u webadmin: webadmin is MySQL username
- -h IP or hostname: 173.234.21.12 is MySQL server IP address or hostname (FQDN)
- -p : Prompt for password
