Most developers and webmasters don’t need remote MySQL connections. Their small websites use MySQL, running on the same server as their PHP applications. That’s why ‘localhost’ (127.0.0.1) is specified as the MySQL server/host on most CMS and apps installations.
Even if you have a cache system configured for MySQL, when you have big traffic applications with a huge resource impact on the server performance, you will probably need to set up a second dedicated or cloud server to serve your MySQL operations.
Today, you will learn how to configure a remote MySQL server to decrease the web server load average. At the same time, you will gain security and better performance.
How to set up a Remote MySQL Database on Ubuntu Linux
Assuming that you have two Cloud VPS or dedicated servers, look at the following scenario:
192.168.1.101 - the Web/PHP server 192.168.1.102 - the MySQL server
First, you will have to set up the MySQL remote privileges for the web server IP. Look at a practical example by connecting to your MySQL console. Type the following:
mysql -u root -p
It will ask for your MySQL root password.
Here, you can grant the remote privileges for your new IP:
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'192.168.1.102' IDENTIFIED BY 'password'; flush privileges;
Notice that the first lines contain a few important details:
db_name: the database name of your application. db_user: the database user name used to connect. 192.168.1.102: your web server IP address, replace it with your public or private IP address. It's best to connect via private network so your MySQL information isn't directly exposed to the internet. password: your MySQL database password
That configuration will allow your web server to connect with the remote MySQL database named “db_name.” But, what if you need to connect to ALL of the databases on the remote MySQL server instead of only one? You will have to alter some values on the grant command:
GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'192.168.1.102' IDENTIFIED BY 'password'; flush privileges;
Did you notice what changed? You replace “db_name.*” with an asterisk “*” to allow remote access to all of the databases. While this can be a security hole, many developers still use it when the same MySQL user needs to access many different databases.
Another thing you may have noticed is that the “flush privileges” command was used. The “flush privileges” command is used to update and apply all of the privileges set in the previous grant command.
When finished, you can exit the MySQL shell by typing ‘exit’:
Iptables Firewall configuration
If you are using iptables to filter your traffic, you will have to run commands to allow communication between your app and database servers. To allow outgoing connections from the web server to the MySQL server on port 3306, run this on the web server shell:
iptables -A OUTPUT -p tcp -d 192.168.1.102 --dport 3306 -j ACCEPT
To allow incoming connections from the web server to MySQL server on port 3306, run this command on the database server:
iptables -A INPUT -p tcp -s 192.168.1.101 --dport 3306 -j ACCEPT
Apply and save your configuration:
service iptables save
APF/CSF Firewall configurations
On both machines for APF:
apf -a 192.168.1.101 apf -a 192.168.1.102
csf -a 192.168.1.101 csf -a 192.168.1.102
Remember that you will have to replace “192.168.1.101” and “192.168.1.102” with your real private network IPs. At this point, you should be ready to test the remote MySQL connection from the web server:
[[email protected]:~]mysql -u db_user -h 192.168.1.102 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1640498 Server version: 5.5.43 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
That’s it! Your MySQL server is now running from a remote location.