How to connect to MySQL from a remote server

In few words, you have to configure MySQL to allow remote connections, create a user for connecting and setup your firewall to allow it. You don’t have to do this if you are only connecting from a local application like WordPress.

You need root permissions to perform the following commands.

 

Configuring MySQL

Edit the MySQL configuration file.

nano /etc/my.cnf

Allow connections from all your network interfaces by commenting out the following line:

# bind-address=127.0.0.1

That’s it. Now restart MySQL

/etc/init.d/mysqld restart

 

Creating a user for remote connections

Create a MySQL user and a strong password. Connect to the MySQL console:

mysql -u root -p
<type your MySQL root password here>

And create the user:

create user 'myUser'@'%' identified by 'myStrongPassword';
grant all privileges on myDatabase.* to 'myUser'@'%';
flush privileges;
exit;

Do you wonder what ‘%’ means? This is just a wildcard that means ‘any computer’. So what ‘myUser’@’%’ really means is “a user named ‘myUser’ who can connect from ‘any computer'”. To make this even safer, you can substitute the ‘%’ by your IP address, like in ‘192.168.1.2’.

 

Configuring your firewall

Finally, you have to add a rule to your firewall so it allows connections from your remote IP address to your server on port 3306 (that’s the MySQL port):

iptables -A INPUT -p tcp -s 192.168.1.2 --dport 3306 -j ACCEPT

The previous line will instruct iptables to accept connections from IP 192.168.1.2 on port 3306. You should change the IP shown here to the one you are going to use.

 

That’s it! You should now be able to connect from your remote host using the information you previously provided.

Leave a Reply