MySQL is the second most used database management system and is used by the most famous Content management systems like WordPress. It’s kind of a big deal, and indeed it is kind of a big deal considering it is a free relational database management system.
Basically, when you configure your server using ServerAvatar or any other server management systems, whole LAMP server is installed on the server. LAMP stands for Linux, Apache, MySQL or MariaDB and PHP or Perl or Python.
Considering you already have a web server ready to query MySQL server, In this article, we are going to set up an individual MySQL database server using ServerAvatar.com. It means that, while setting up the server, you would not have to type a single command in the console or use external software.
In this in-depth guide, we will cover 5 steps that are very important to setup MySQL server. They are:
- Selecting the right location for MySQL server
- Connecting a Server with ServerAvatar
- Disabling unwanted services
- Creating Databases and MySQL users
- Configuring Firewall to filter our Un-authorized IP addresses
We will go through each and every step and get as much fundamental knowledge about MySQL server as we can. So, without wasting any more time, Let’s get started with the first and most crucial step. If you already have a server and you cannot change the location, you can skip the first step.
Selecting the right location for MySQL server
In the previous post about remote MySQL connection, we mentioned the importance of the location of the database server. If you haven’t read that article, don’t worry because I will cover the same in this article too.
The reason behind selecting the right location?
First of all, let’s assume that we have a basic WordPress website that will connect to our MySQL Database server to fire queries. Also, let’s assume that our WordPress website needs 10 queries to be executed when a page is loaded.
We are assuming some values because it will be easier to understand why selecting the right location for MySQL database server is very much important.
When a database server(MySQL) and a web server(Apache) is installed and configured on the same machine, no overhead is created because the queries are executed on the same machine. But, when the MySQL server installed and configured on the different machine, Overhead is produced and that overhead is called Latency.
Latency is the delay that happens before data is transferred between two servers. Latency is measured in milliseconds. Let’s assume that our latency is 100ms. It means that to execute a single query, 100+100+EXECUTION_TIME milliseconds will be required. Let’s assume that EXECUTION_TIME is about 60ms.
It means that to execute a single query on a remote server, 360 milliseconds will be needed. Considering our WordPress website which executes 10 queries, Every single page request will take about 3600milliseconds or 3.6 seconds to fire queries and get results form our database server.
Also, We are not counting the time taken by PHP processor to process the code and render the output. So, If our latency is higher, It’s not good for our visitors and us. So, How to decrease latency?
Practically, Latency depends on the distance between two servers. It means that If you keep your database server close to your web server (Maybe, In the same data center), latency will be less. Let me show you the best method to select the right location for MySQL database server.
How to select the right location?
TIP: In this guide, the term Web server or client-server is used to point a server from which we want to create a connection with the database server we are going to create.
Nowadays, Almost every company allows their users to select the location of their new server. For example, If you are using Digitalocean, you can choose from 8 locations. Your server will be created based on the location you select. Same goes for Vultr, Linode, etc.
The first step is to know the location of our web servers that will connect to our MySQL database server to fire queries. Let’s assume that one of our web servers is located in Miami and another one is located in Los Angeles. Based on these locations, we will find a best possible location for our database server.
Click here to open the tool that we are going to use to analyze the latency between regions. Enter Miami and Los Angeles as Source cities. Sure, you can remove unwanted source cities from the list.
Now, Enter the available locations as Destination cities. For the sake of example, I am going to enter random datacenter locations available in Vultr. You can enter the locations that are available for you to create a server. Here are the results that I got.
According to the values, Dallas is the best location because it has very low values for both the locations. It’s best if you can create a server in any one of the datacenters having any one of your web servers. In that case, One server will work faster, and another one will perform slow.
Now, as we know the right location of our database server, we can create a server in Dallas and connect it with our ServerAvatar account. It will take less than 15 minutes in total to build a complete MySQL database server using ServerAvatar.
Connecting a server with ServerAvatar
NOTE: Please make sure your server is running on Ubuntu 16.04 operating system. If you haven’t created an account in ServerAvatar.com, Click here to create one and get a surprise in your E-mail.
I created a server on Vultr, and now I can connect this server to my ServerAvatar account. Just click on the Add in the top navbar and click on the Server option. Just like this:
Now, You will see a form that will contain three fields. You just have to provide necessary information about your server. In the first field, Enter the name of a server; Here, a name is just to identify a server, You can enter nickname on the server. In the second field, Enter the IP address of a server and in the third field, Enter the Root password of a server.
You must have password logins enabled on your server, and it must not contain PHP, MySQL or Apache. Once you click on the Add Server, It will take 2-3 minutes to configure your server. Once done, you will be able to open Server Panel from your ServerAvatar dashboard. It will look like the following image.
On the Server Panel, you will see basic information about your server. You will see current memory usage, Disk usage, and Server Load. Server Monitor will monitor all three metrics.
It’s always good to turn the firewall on when you first open the server panel. Just turn on the firewall, and you are good to go! Your server is successfully connected with ServerAvatar, and we are ready to make a complete database server out of it.
Disabling unwanted services
When you connect a server with ServerAvatar, Whole LAMP stack is installed and configured. We want to make a database server which means we don’t want Apache and PHP to consume memory that can be used to process queries. It’s simple to shut them down to just keep MySQL running.
To turn off services, Click on the Services option from the sidebar on the server panel just like the following image.
In the Services section, you will find a bunch of services that are currently configured and active on the server. We have to turn off three services. They are Apache2, php7.0-fpm, and postfix.
The reason behind turning off these services is we don’t want them to consume memory that can be used by MySQL. Click on the Stop button to turn off the specific service. Here is how updated services page will look like.
We have to keep SSH on because we want to connect to the server to update stuff. If ssh is closed, we will be locked out of our server. We also have to keep ufw on because we will use it to filter out unauthorized IP addresses.
So, Once the unwanted services are turned off, we can go ahead and create database and database user with remote connectivity enabled.
Creating Database and Database users
Currently, we have a web server with only MySQL, Firewall (ufw) and ssh active. Next thing we have to do is to create a database that we can manage with a remote connection from our web server.
To create a database, click on the Databases section on the left sidebar. In the database section, you will get a list of databases that exist on your server. However, It will not show databases like performance_schema or MySQL on purpose. If you don’t have databases on the server, You will see something like the following image.
Now, Click on the Create database button to create a database. You will see a dialog box with one required field named Database name just like the following image.
Enter the database name and then click on the Remote access checkbox. Checking the remote access checkbox will reveal a new field to enter the IP address of the server from which we want to create a remote connection.
In the IP address field, Enter the IP address of the web server or the server from which you want to create connections with the database server. This is how the dialog box will look like after entering the IP address and the database name.
Tip: If you want to create user and define password on your own, check the first checkbox with label I want to define database credentials manually. You will be able to enter custom username and password of your choice. However, It is not recommended because auto-generated values are random and secure.
Now, Click on the Create database button, and your database will be ready in seconds. You will get E-mail containing database credentials. You can also get credentials by clicking on the Users button corresponding to the database.
Once you know the database credentials, you can verify that you can make remote MySQL connections with our database server using the following command. Just open up a terminal on your web server or client-server and execute the following command.
sudouser@web-server:~$mysql -uDB_USERNAME -p -hDB_SERVER_IP
You will be asked to enter the password. Just enter the password and hit enter. You are now connected to your MySQL database server! Now you can use this database information in your web application so that your application can work with the remote database.
You can also create more users from Users section of the database. You can allow users to connect from different IP addresses so that you can have one database server for multiple web servers.
Now comes the security, if you haven’t turned on firewall yet, Your MySQL port is open for everyone which means anyone can perform attacks like brute-force to exploit your database server. Let’s get help from a firewall and close MySQL port for everyone except our web servers.
Configuring Firewall to filter our un-authorized IP addresses
If you have a good habit of turning on the firewall first, you won’t have to update firewall rules because ServerAvatar automatically creates firewall rule while creating a database. Still, check Firewall section and confirm the rules.
If you haven’t turned on the firewall yet, Open up the server panel, and in the bottom-left corner of the panel, you will find one article labeled Firewall. Just click on the switch and turn on the firewall. Settings will be applied immediately. then, click on the edit icon given just after the switch. You can also click on the Firewall section given in the sidebar.
Both the links will open the same page. On that page, you will be able to manage Firewall rules. By default, there will be two firewall rules and the firewall rules page will look like the following image.
As you can see, Ports 80 and 443 are open. We don’t want to keep those ports open because we are not serving content on those ports. Just remove by clicking on the Trash button given on the right side.
Now, It’s time to add a rule to allow our web server to connect to MySQL server. Click on the Add rule, and you will see a form just like the following image.
Here, you have to enter 3306 in the Port field (Because MySQL uses 3306 port), Keep Protocols as it is, Keep connections to Allow and an IP address field, Enter the IP address of your web server. Once You have entered all the information accurately, Click on the Add rule button and rules on the firewall will be updated immediately.
Updated Firewall section will look like the following image.
Now, our firewall will block all the connect requests except the requests created by our web server from which we want to make connections with our database server. Make sure you can still login into MySQL console directly from a terminal of the web server.
Conclusion: So, this is how you can set up your database server using ServerAvatar. Your disk usage, RAM usage, and server load will be monitored by ServerAvatar and you will be able to see it in beautiful charts on the server panel. In case if your server experiences unusual load, you will get notified by E-mail within 15 minutes.
If you are facing issue setting up a database server using this method or you are unable to connect, you can command down in the comment section and we will help as soon as possible.