How to configure a remote MySQL connection

0
On low traffic websites rare times you will need to run you own MySQL dedicated vps or server, however on high traffic websites it’s a very common practice to split the database queries to a dedicated server or VPS that will run only MySQL server. This server scheme has two machines, one for php with your webserver, and the second for MySQL.

mysql

On small traffic sites, you will always serve the webserver requests and mysql locally, so all the connections are made to the same host. But on this case, we must call a remote MySQL connection from our PHP files in order to execute the SQL queries successfully.

Today I’m gonna show you how to configure a remote MySQL connection, let’s begin.

Login to both servers via ssh.
On the MySQL server/vps, connect to the MySQL console with this command:

mysql -u root -p

Now we will run this queries to alter your current MySQL connection configuration:

mysql> update db set Host='xxx.xxx.xxx.xxx' where db='database'; mysql> update user set Host='xxx.xxx.xxx.xxx' where user='user';

The first line is used to set the remote IP that will access to the MySQL server, it must be always your webserver/php public server IP. The second line configures the IP and the username. Of course, replace “xxx.xxx.xxx.xxx” with the real IP of your webserver, “database” and “username” with your real names.

Another way to do it (my favourite) is just to grant all the remote privileges to the new host, you can do it using this query:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'password'; flush privileges;

Replace “database”, “user”, “xxx.xxx.xxx.xxx” and “password” with the real data you want to use.

This configuration will allow you to access one specific database from the remote host, however if you need to allow complete access to all the databases you can use this query:

GRANT ALL PRIVILEGES ON *.* to 'user'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'password'; flush privileges;

Replace “user”, “xxx.xxx.xxx.xxx” and “password” with the real data of your MySQL connection.

Very important, always after doing this changes, remember to run flush privielges command as you see below:

mysql> flush privileges;

Otherwise the changes will not be applied to the MySQL server.

Once done, you can exit the MySQL console:

mysql> exit;

Configuring remote MySQL firewall access

If you have your own iptables firewall you can run this iptables rule to allow access from your webserver IP, example:

iptables -I INPUT -s xxx.xxx.xxx.xxx -j ACCEPT service iptables save

If you use CSF or APF firewall, you can allow the webserver IP with this commands:

csf -a xxx.xxx.xxx.xxx apf -a xxx.xxx.xxx.xxx

On this three examples, on the MYSQL server replace xxx.xxx.xxx.xxx with your webserver IP. Now, my advise is to allow all traffic from and to both machines (webserver and MySQL server) to avoid any network blocking.

That is all, at this point you should be able to stablish a remote MySQL connection.