How to setup MySQL on a Linux VPS

So you've made an awesome backend or full-stack application, powered by a MySQL database. You are pretty happy with your work and are ready to take it to production. You are deploying it to a Linux Virtual Private Server (VPS) and you have no idea how to set up a MySQL DB. You probably don't have a feature-rich admin panel like CPanel at your disposal to simplify the process. Fret not, this article is for you.

Getting started

It goes without saying that you first need to connect to the VPS. I prefer an SSH connection rather than the browser-based No-VNC viewer. So fire up your favourite terminal and let's do this.

ssh <username>@<hostname_or_ip>

In most cases, you will be logging in as root. You'll be prompted to enter your password. The password won't be shown on the screen as you type it. Don't worry, this is a security feature, not a bug. If SSH won't get to the password stage, you might want to check whether your server is up and running by pinging it. You could also contact your hosting provider.

Installing MySQL

Before installing MySQL you should update your OS to make sure you have all the necessary updates. This might take some time, depending on how much of a fossil your OS currently is.

PS: Please don't omit sudo in the following commands. It will save you a world of hurt.

sudo apt update

Now you can install MySQL.

sudo apt install mysql-server

Yay, you have MySQL installed!

Configuring MySQL

Security should be our first concern. You will probably be storing sensitive information in your database, and you would not want hackers all up in your business. To secure your MySQL installation, the mysql_secure_installation command is used. So how exactly does this secure your MySQL installation?

1. Changing root password - the command will prompt you to change the password for user root. By default the password for root is blank, which is essentially no password. Yap, that's an unguarded wide-open door to your MySQL installation! You should set a strong password. Make sure you'll remember it, or you risk getting locked out of MySQL.

2. Removing anonymous users - MySQL, by default, has an anonymous user, which allows anyone to log in without credentials. This was meant to make testing easy and is bad for production.

3. Disabling remote root login - the root user has a lot of privileges, and you would not want to expose that power to the public. By pressing yes, you limit root to localhost only, which means you can only access MySQL as root from within the server (using SSH or terminal for example) but not externally. You could, and in fact should, create non-root users with limited privileges for your web apps to use. More on that later.

4. Removing test databases - Out of the box, MySQL comes with a database test that anyone can access. Again, this is intended for testing and is bad for production.

5. Reloading privileges - this implements the changes you have made.

All you have to do is to enter a new password, confirm it, and press y (for yes) on all the prompts.

sudo mysql_secure_installation

Creating a non-root user for your web app

As you might have known, using root credentials in your web app is a massive security risk. Employing the concept of "least privilege", it is prudent to create a MySQL user for each web app you have. Each user should only have access to the database(s) it is using. This minimizes the potential damage to your databases should a user's credentials leak.

You'll need to run MySQL, still within the terminal, by running:

mysql

You should see mysql > . Here, you will be typing SQL statements, not ordinary shell commands. You should now create the database your MySQL user will use. Don't worry, you can always create the tables and more later.

CREATE DATABASE my_db;

There are multiple approaches to creating a MySQL user, depending on the scenario.

1. Creating a local MySQL user

This is useful for creating users that will be used by web apps within the VPS. Using these user credentials remotely will not work.

CREATE USER 'my_awesome_user'@'localhost' IDENTIFIED BY 'my_super_secret_password';
GRANT ALL PRIVILEGES ON my_db.* TO 'my_awesome_user'@'localhost';
FLUSH PRIVILEGES;

2. Creating a remote MySQL user - accessible from any IP

This is useful for creating users that will be used remotely by web apps on other servers or by SQL tools such as PHPMyAdmin on your local computer. These users cannot be accessed from localhost (within the VPS) like local users.

CREATE USER 'my_awesome_user'@'%' IDENTIFIED BY 'my_super_secret_password';
GRANT ALL PRIVILEGES ON my_db.* TO 'my_awesome_user'@'%';
FLUSH PRIVILEGES;

Voilà, you have set up remote MySQL. Your work is done, champ!

Well, not quite. While this approach may be convenient, anyone on the Internet can now access MySQL through these users, or at least they can try. To mitigate this risk, you should set up a firewall. More on that later on.

3. Creating a remote MySQL user - accessible from only a specific IP (Not recommended)

This is useful for creating users that will be used remotely by web apps on an external server with a fixed IP. However, these users cannot be accessed from any other IPs which is inconvenient for devices with dynamic IPs like a local development computer. This approach is too rigid and it may give you headaches later on. You might even need to create other users. I don't recommend it, but for completeness, I have included it.

CREATE USER 'my_awesome_user'@'some_ip' IDENTIFIED BY 'my_super_secret_password';
GRANT ALL PRIVILEGES ON my_db.* TO 'my_awesome_user'@'some_ip';
FLUSH PRIVILEGES;

You can now quit MySQL.

exit;

Enabling Remote MySQL: Binding Address

By default, MySQL only accepts connections from the local machine because it is bound to the loopback address (127.0.0.1 or localhost). Let's change that. Open the Nano in-terminal editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then scroll down the file using the keyboard directional keys and look for the line bind-address. Replace the default value bind-address = 127.0.0.1 with bind-address = 0.0.0.0. Press CTRL + X to exit Nano, press y to save the file, and then press Enter to confirm the file path.

Setting up a firewall

There is no better way to protect your server than to put it behind a big ol' wall of fire. This article uses the Uncomplicated Firewall (ufw) by Canonical, the company behind Ubuntu. Why? Because it's free and, as the name suggests, uncomplicated. It is available on Ubuntu out of the box, but it can be installed in seconds.

sudo apt install ufw

You need to enable only authorized IPs to access MySQL. If you do not wish to set up remote MySQL, you can skip the first command.

sudo ufw allow from <my_ip> to any port 3306
sudo ufw enable

You can check your firewall status and rules by running:

sudo ufw status

It's crucial to ensure that only the necessary ports are open to the public. Usually, these would be 4.

• 22 - SSH

• 80 - HTTP

• 443 - HTTPS

• 3306 - Remote MySQL

Restarting MySQL

You need to restart MySQL for the changes to take effect.

sudo service mysql restart

If that does not work you could try:

sudo systemctl restart mysql

That's it, seriously this time! You deserve a medal!

Troubleshooting

I hope you did not run into any issues but if you did, I will list them here with possible solutions. If the one you're experiencing is not listed here, kindly write it in the comments.

1. SQL GUI Application on my PC is refusing to authenticate with remote MySQL on VPS, with an error mentioning "sha2" or "sha256".

Your SQL GUI app is most likely old and does not support SHA256. Change the type of password your MySQL user uses. Make sure you use the same existing user's username, hostname (localhost/%/IP), and password.

ALTER USER 'my_awesome_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'my_super_secret_password';

Then restart MySQL, and you're good to go.

---

Now you can deploy that project that will take the world by surprise!

If this article has helped you, if you found an error or misinformation or if you have a security tip, please let me know in the comments.