The Advantages Of Setting Up Remote MySQL Server
With servers hosting large websites, resource usage is a big issue and in most cases MySQL is consuming the majority of the resources. To tackle this, setting up a separate stand alone MySQL server and loading the websites from there is a very good option. With this kind of setup, the server load of both the web server and MySQL server can be maintained well.
Scenario: There are hundreds of high-traffic Ecommerce websites hosted in a server (say a cPanel server) and in the peak time, there could be thousands of connections to the server. Let’s not get caught up in what kind of connections they are. This might be connections to web server (Apache, Nginx etc) or MySQL server. Consider a case where there is 1000 connections each to web server and to MySQL server. The amount of work that the server has to do to handle these connections and acknowledge the requests in these connections would be huge and this may end up in server load shooting up and server hanging up eventually.
Work Around: One way to handle this situation is to separate the web server and MySQL server from the same server i.e, MySQL server will be installed in a remote machine/server and websites loading from the local web server are tied to the databases in the remote MySQL server.
In this tutorial, we will go through how this setup can be achieved.
Setting up the remote MySQL server
Step 1) First we need to install MySQL in a remote server.
This can be done easily with:
yum install mysql
Step 2) Make the MySQL server listen to your public IP, so that external servers (server where the websites are hosted) will be able to reach the MySQL server.
For this we need to add the below given directive in the MySQL configuration file (/etc/my.cnf)
Below given is a screenshot of it.
Step 3) We need to migrate the databases from the local web hosting server to the MySQL server. For this you can use the ‘mysqldump‘ utility.
Step 4) Create the MySQL database and user and grant privileges to access this from the webhosting server.
Go to MySQL prompt
mysql -u root -p
In the MySQL prompt, create a MySQL database for Joomla website present in the webhosting server.
CREATE DATABASE joomla;
Create MySQL user who can access this database from the web hosting server and assign a password for it.
CREATE USER 'joomlauser'@'Web_Server_IP' IDENTIFIED BY 'password';
Grant all privileges to the MySQL user to access this database.
GRANT ALL PRIVILEGES ON joomla.* TO 'joomlauser'@'Web_Server_IP';
Flush MySQL Privileges and exit MySQL prompt.
You can refer the screenshot for more info.
Step 5) Restart MySQL service after this.
service mysql restart
Step 6) Allow the IP of the web hosting server in the firewall or atleast allow the IP of it just for port 3306 (MySQL port).
Things to do in the local web hosting server
After restoring the database in the remote server, you can remove the database in the current server to save space. It is advised to keep a backup of it always.
Step 1) Allow the remote server IP in the firewall.
Step 2) Test MySQL connection to the remote MySQL server.
You can do this via command;
mysql -u joomlauser -h MySQL_server_IP -p
Enter the MySQL user password and if you are able to go to the MySQL prompt, the connection to remote MySQL server is established.
In the above screenshot you can see the remote MySQL connection established.
Step 3) Edit the database configuration file of the CMS or custom coded website with the DB hostname as that of the remote MySQL server IP. For joomla, it is configuration.php file present in the Document Root of the website.
Refer the screenshot for a deeper look.
After this try loading your website in the browser and you can see your website loading as it was earlier and there won’t be any sign of database loading from elsewhere.
And that’s all folks. So remember, if you have a really busy eCommerce site or your dedicated server is having performance related issues then separating the MySQL server could really improve performance.