Control remote access in MySQL

Last Updated on: October 11, 2022

On a standard MySQL installation, remote access is disabled by default. This is great if you are sitting next to your server but generally inconvenient if you are not.

With the advent of modern support teams spanning the globe, it is entirely possible to administer a server that is in a different country or continent and in that case, you need to enable remote access.

The first thing you need to do is enable remote access and then restrict it to prevent unauthorized access. Start with an ssh connection to the remote MySQL database server.

ssh user@mysql.mydomain.com

After login, edit the my.cnf configuration file with a text editor, locate the [mysqld] section and make the following changes:

[mysqld]
#skip-networking Can delete this line or comment it
bind-address = Enter your server ip address

Save the file and restart the MySQL server.

$ /etc/init.d/mysql restart

Now you need to grant remote access to the databases for each remote computer.

$ mysql –u root –p password
mysql> CREATE DATABASE mydb;
mysql> GRANT ALL ON mydb.* to user@ipaddress IDENTIFIED BY Password;
mysql> UPDATE DB SET Host=ipaddress where DB=’mydb’;
mysql> UPDATE USER SET HOST=ipaddress WHERE USER=username;
mysql> exit

The first two MySQL statements create a new database called mydb, and grant remote access to the user at ipaddress who supplies the correct password. The second set of two MySQL statements do the same action but for an existing database.

The next step is to open TCP port 3306 using iptables.

/sbin/iptables –A INPUT –i eth0 –p tcp –destination-port 3306 –j ACCEPT
$ service iptables save

You can use the –s switch to specify connections from an IP address or a subnet.

Once your configuration is complete, the last stage is to test it from your remote workstations. Once it is working, you have a secure method for remote access to your MySQL servers, no matter how far away they are physically located from you.

---

---