Adarsh SojitraScaling is always done in a hurry! Like dividing a web application into two different servers to manage more traffic and distribute the load over multiple servers. Or having multiple web servers accessing data from a single database server. Remote MySQL database connection is the solution here!
In this guide, I will show you how you can set up secure remote MySQL connection so that you can query MySQL server remotely. To understand the whole guide, you just have to know what is MySQL and you should have sudo access to the server that will act as MySQL database server.
Without wasting any more time, let me show you the steps we are going to cover in this guide. First one is apparently the location of your MySQL server. If you already have a server and you can’t change the place, you can skip the first step.
- Selecting the right location for MySQL database server
- Allowing remote MySQL connections
- Creating/Altering user to enable remote MySQL connections
- Securing the remote MySQL connections
We will go through each step in depth. It means when we will complete the first step, you will know exactly where you need your MySQL database server in this world and why the location you will select will be the best in terms of performance. So, let’s dive into the first exciting step.
If you already have MySQL database server and you can’t change the location, You can skip the first step.
Selecting the right location for MySQL database server
Companies like Digitalocean, Vultr, Linode, Google and many more allow users select the location of the new server. They have data centers in tens of cities around the world. That’s not to show off the investments that companies raise, There is a reason behind allowing users to select the data center location of their choice.
Let me tell you what do we mean by the right location.
What do we mean by the right location?
Let’s consider we have one server that will act as a web server. The same server will create remote MySQL connections with MySQL server that we will create.
For instance, when a typical visitor will visit our WordPress website, our WordPress website will query our remote MySQL server multiple times to get information like Latest posts, List of popular posts (For sidebar), Number of subscribers (so that we can show off), etc from the database.
Let us assume that our WordPress website executes 8 MySQL queries to get the information it wants from the database. It means, WordPress will create 8 connections with our MySQL server and MySQL server will process those queries and will return the query results.
This process will be repeated every time a visitors visits our WordPress website. If the Web server and MySQL server are installed on the same machine, This is not an issue, but if our Web server and MySQL server are installed on the different machines, There is an overhead called Latency.
Latency or Network Latency is the delay that is caused to connect to the remote server before sending the request and receiving the response. Let’s learn more about latency!
Understanding latency
If you are a system administrator, Your definition of Latency would be An overhead that is created to connect to the remote server and practically, it is true. From our previous example, Let’s assume that our WordPress wants to execute 8 queries on remote MySQL server and the latency is 150ms (It is considered high).
Executing single query will take about 400ms considering our query will take 0.1 seconds to execute, How 400ms for a single query? 400ms = 150ms latency to connect to MySQL server + 100ms to execute a query and generate results + 150ms to receive results on our web server.
If you are not using cache, Every pageview will take 3.2 seconds (8 queries, each taking 400ms) just to get required data from the database server. Sure, you can keep MySQL connection live to avoid latency, but it’s expensive! A better way to decrease this time would be to select a right location!
Let ‘s see how we can do that with Pinpoint accuracy.
Selecting the right location
Let’s consider we have our web server in New York (US). And we want to select the right location for our MySQL server based on the location of our web server. It will be a dumb decision to create MySQL server in datacenters located in Singapore, Australia or India. It is because more distance means more latency.
You can get the latency between any two cities using this tool. You can select Source cities(In our case, New York) and destination cities (Available locations for our MySQL server). Then, we can compare ping(latency) values. LOWER IS BETTER. Here are the results I got by entering New York as Source city and Bunch of other cities available to create MySQL server as Destination cities.
Here you can see that Bangalore and Sydney are awful locations for our MySQL server. It is because, If our WordPress wants to execute 8 queries when the page is loaded, It will take ~4.6 seconds to process if a database server is in Bangalore and ~4.3 seconds if the database server is in Sydney.
Considering our web server is in New York, and we have above given 5 locations to select from, Chicago is the best choice if we only have one web server and it is located in New York.
For instance, we have two web servers located in New York and Seattle. You can add one more Source city and re-evaluate values. Here are the results I got.
Again, Chicago is the best choice because we get good Latency from New York and Seattle Both! So, based on the current location of your web server(s), this is how you can select the best location for your remote MySQL server.
Considering we are going to handle thousands of visitors on a daily basis, each millisecond matters! Assuming that you have already installed MySQL server on your new server, Let’s Allow remote MySQL connections and make stuff practical!
If you haven’t installed MySQL server yet, Just execute the following commands on your server to install MySQL.
sudouser@mysql-server:~$ sudo apt-get install mysql-server -y
Once it’s installed, you are ready to update the configuration to support remote connections.
Allowing remote MySQL connections
If you are using ServerAvatar to manage your servers, You can skip this step.
By Default, MySQL only allows connection from localhost or 127.0.0.1 which means the same server. To allow remote connections, we have to edit one configuration file. In the command line, execute the following command to open the file for editing, it may ask for your password to use sudo privilege.
sudouser@mysql-server:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Now, find a line containing bind-address = 127.0.0.1 and add # before bind-address to comment out the line. It means we are allowing connections from everywhere. Still, a user has to pass MySQL password authentication to access our MySQL server.
Now, to reflect the changes, we have to restart our MySQL server by executing the following command.
sudouser@mysql-server:~$ sudo service mysql restart
Currently, we don’t have a MySQL user to access our database server. We first have to create a user that can access the database server to add/update/delete data. Let’s create a user.
Creating/Altering MySQL to enable remote MySQL connections
We can identify MySQL user by the username and the host. Every time we try to connect to MySQL server, MySQL will first try to match the username and the host.
For instance, If we try to connect to our MySQL server from the same server and our username is root, MySQL will try to find a user with username root and the host localhost. If username, host and password matches, it will allow a user to access MySQL console.
You can get a list of the users and hosts by executing the following command in your MySQL console as a root user.
mysql> select User,Host from mysql.user;
Now, We have to create or alter users to set our web server’s IP as host so that our Web server can access our MySQL server. Without wasting any more time, let’s do it.
Creating MySQL user with remote MySQL connectivity support
If you are using ServerAvatar to manage your server, You don’t have to create a user manually, It’s effortless with ServerAvatar. Just open up the Server Panel, From the left sidebar, Click on the Databases and then click on the create database button. You will see a dialog box just like the following image.
In the Database name, Enter the name of the database you want to create on your MySQL server, Enter Username and password and finally enter the IP address of your web server and click on the Create Database button. Your database with remote connectivity will be ready in few seconds. If you already have a database, You can just add a user by clicking on Users and then Add user.
If you are not using ServerAvatar to manage your server, Just follow instructions given below to create a user with remote MySQL connectivity.
The key value here is the Host. Here, we will indicate IP address of the web server as WEB_SERVER_IP. So, you have to replace WEB_SERVER_IP with the IP address of your web server while creating a real user for real MySQL server.
To create a new user, Execute the following command in the MySQL console as a root user.
mysql> CREATE USER 'user1'@'WEB_SERVER_IP' IDENTIFIED BY 'PASSWORD_HERE';
Now, It’s time to grant privileges to user1 on a specific database. Execute the following command to grant all the privileges to the specific user.
mysql> GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'user1'@'WEB_SERVER_IP';
Don’t forget to replace DATABASE_NAME with the actual database name in this case. Once the user is created, A user can access the MySQL server from our web server using the following command.
sudouser@web-server:~$mysql -uuser1 -p -hDATABSE_SERVER_IP
It will ask for the password to authenticate the user. If you are trying to access MySQL server from your web server and the password is correct, you will be able to access MySQL console. If you try to connect from some other IP address, Host will not match, and hence, Authentication will fail.
Altering current user to support remote MySQL connectivity
If you already have a MySQL user and you just want to update the host without altering the privileges, just execute the following command to update host for the specific user without altering the privileges.
mysql> RENAME USER 'username'@'localhost' TO 'username'@'WEB_SERVER_IP';
Renaming user won’t mess up with the privileges granted to a specific user. So, you don’t have to worry about updating privileges if you are using this method.
Securing remote MySQL connections
The current setup is not so secure because we are allowing traffic to the MySQL port which is vulnerable to attacks like Bruteforce (Because we are not filtering out un-authorized IP addresses).
If you are using ServerAvatar, You don’t have to worry about Filtering out un-authorized IP addresses because your MySQL port is already closed. When you create a user with remote host, ServerAvatar will automatically configure the firewall to allow connections from that IP address only.
Filtering out IP addresses using Firewall
ufw is the firewall that comes with Ubuntu 16.04. We are going to use ufw to allow connection to MySQL port from specific IP address only (The IP address of our web server). Login to your database server as a sudo user and execute the following commands.
sudouser@mysql-server:~$ufw allow 22
sudouser@mysql-server:~$ufw allow from WEB_SERVER_IP to any port 3306 proto tcp
sudouser@mysql-server:~$ufw enable
Now, your firewall will be active and will only allow connections from the following ports.
- Port 22: Never close this port because once this port is closed, you are locked out of your own server, and there is no way to disable firewall without accessing the server. So, we’ve kept this port open.
- Port 3306 from WEB_SERVER_IP: MySQL uses the port 3306 for MySQL operations. In this case, we are allowing connections on port 3306 from our web server’s IP address only.
Now, It’s time to restart the server using sudo reboot command and once the server is up again, our database server is ready to handle remote MySQL connections from our web server. This is how you can set up an independent database server that only deals with MySQL queries.
Conclusion: So, this is how you can set up a MySQL database server that allows remote MySQL connections. More than that, we have also encrypted our connections using SSL, and we have also filtered out IP addresses using Firewall. It is recommended to use SSL to support encrypted remote MySQL connections because one can prevent Man in the middle attack using Encrypted MySQL connections.
If you need help or having some questions in your mind, you can comment down in the comments section, and we will get back to you as soon as possible!
