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.
Edit the MySQL configuration file.
Allow connections from all your network interfaces by commenting out the following line:
That’s it. Now restart MySQL
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.