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.
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.