1 Login Using SSH (if server is outside your data center)
Login over ssh to remote MySQL server. You may need to login to your MySQL server as the root user:
ssh user@server1.cyberciti.biz
### login as the root using su or sudo ##
su
# or use sudo ##
sudo -i
OR directly login as root user if allowed:
ssh root@server1.cyberciti.biz
2 Edit my.cnf
Use a text editor eg nano, vi:
In Debian/Ubuntu Linux, file is located at /etc/mysql/my.cnf location.
In Red Hat Linux/Fedora/Centos Linux, file is located at /etc/my.cnf location.
In FreeBSD, create /var/db/mysql/my.cnf
Edit /etc/my.cnf:
# vi /etc/my.cnf
3 Under [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 65.55.55.2 then entire block should be 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 = 65.55.55.2
# skip-networking
....
..
....
Where,
bind-address: IP address to bind to.
skip-networking : Do not 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 put it in comment state.
Save and Close the file
4 Restart the server
In Debian / Ubuntu Linux, issue ...
# /etc/init.d/mysql restart
OR
# systemctl restart mysql
In RHEL / CentOS / Fedora / Scientific Linux, issue ...
# /etc/init.d/mysqld restart
OR
# systemctl restart mysqld
In FreeBSD ...
# /usr/local/etc/rc.d/mysql-server restart
OR
# service mysql-server restart
5 Grant access to remote IP address
Connect to mysql server:
$ mysql -u root -p mysql
To add a new database `foo` for user `bar` and remote IP 202.54.10.20, issue ...
mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
If you always connect from remote IP 202.54.10.20 to database `webdb` as user `webadmin`, grant that user/host access with ...
mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';
6 Exit mysql command program
mysql> exit
7 Open port 3306 using iptables or BSD pf firewall.
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
OR 
Allow only remote connection from your web server located at 10.5.1.3:
/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -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
Save all rules 
In RHEL / CentOS ...
# service iptables save
In FREEBSD / OPENBSD / NETBSD PF FIREWALL RULE ( /ETC/PF.CONF)
pass in on $ext_if proto tcp from any to any port 3306
OR 
allow only access from your web server located at 10.5.1.3:
pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306 flags S/SA synproxy state
8 Test from your remote system or desktop ...
$ mysql -u webadmin -h 65.55.55.2 -p
Where,
-u webadmin: webadmin is MySQL username
-h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-p : Prompt for password
OR
Use the telnet or nc command to connect to port 3306 for testing purpose:
$ echo X | telnet -e X 65.55.55.2 3306
OR
$ nc -z -w1 65.55.55.2 3306
Sample outputs ...
Connection to 65.55.55.2 3306 port [tcp/mysql] succeeded!

Last updated 31 Aug 2024