Install and Secure MYSQL Server on Ubuntu 20.04 and Debian 11
Introduction
MySQL is a free and open-sourced relational database management system.
It is a fast, scalable and secure database system, and it is an integral part of the LAMP stack that runs much of the internet.
This article will show how to install and secure a production-ready MySQL server across Ubuntu, Debian and CentOS Linux distros.
Prerequisites
Deploy either an Ubuntu 20.04, Debian 11 or CentOS 8 cloud server at Vultr with at least 2 GB of RAM
Log in to your server as the non-root user.
Install MySQL on Ubuntu 20.04 & Debian 11
On ubuntu and other debian derivatives, you can install the MySQL server using the apt package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.28. Follow the simple steps below to install MySQL on ubuntu or any other debian derivatives:
1. Add the MySQL Software Repository
MySQL provides a .deb package that handles configuring and installing the official MySQL software repositories. To use it, the repository needs to be enabled to use Debian’s standard apt command to install the software.
However, GnuPG is required. The GnuPG ensures the MySQL package remains intact and tamper-proof. Let's begin:
Update the local package index.
sudo apt update
Install GnuPG.
sudo apt install gnupg
Next, proceed to download and install MySQL .deb package using wget and installed using the dpkg command:
cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
The file should now be downloaded in our current directory. List the files to make sure:
ls
Next, Install using dpkg:
sudo dpkg -i mysql-apt-config*
Use the arrow-keys to navigate to the OK menu option and hit ENTER once the configuration screen pops up.
Refresh the apt package to make the new software repository available:
sudo apt update
2. Install MySQL
After successfuly updating the apt repository, Install MySQL using:
sudo apt install mysql-server
A prompt asks whether to continue with the installation, answer with Y and hit ENTER.
Next, select a default authentication plugin. Read the display to understand the choices. If unsure, choosing **Use Strong Password Encryption** is safer.
Confirm if MySQL is installed and running using:
mysql --version
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
and:
sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2022-02-24 09:55:18 UTC; 2h 34min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 36286 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 2274)
Memory: 358.1M
CGroup: /system.slice/mysql.service
└─36286 /usr/sbin/mysqld
Feb 24 09:55:17 bolton systemd[1]: Starting MySQL Community Server…
Feb 24 09:55:18 bolton systemd[1]: Started MySQL Community Server...
Configuring & Securing MySQL
The MySQL instance on your machine is insecure immediately after installation. For fresh installations, you’ll want to run the DBMS’s included security script. This script changes the less secure default options for things like remote root logins and sample users.
1. Run the security script with sudo
sudo mysql_secure_installation
Follow the onscreen instructions carefully, for reference see the output of the above command below:
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: Y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
NOTE : In setting root passwords, it is important to note that the VALIDATE PASSWORD component requires passwords to be strings of words containing one uppercase, one lowercase, one special character and one number.
2. Delete anonymous users
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.
3. Disable root login over the internet
This is an excellent security feature, it ensures no one can guess the root password of the MySQL server from the public internet.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.
4. Delete the default test database and reload privileges
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!
Create a dedicated non-root user and grant privileges
The MySQL server creates a root user upon installation. This user has complete control over every database, table, user, and so on. Because of this, it is best to avoid using this account outside administrative functions. Below, outlines steps to create a non-root user and grant it privileges:
1. Log in to the MySQL shell
sudo mysql -u root -p
Enter the password created when configuring and securing the mysql installation.
2. Create a user
mysql> CREATE USER "username" @ "host" IDENTIFIED WITH caching_sha2_password BY "password";
Where username represents the name of your username, host is represented by localhost since it is on your server you are performing the creation and the password is any password of your choice and caching_sha2_password is the default authentication plugin.
The password has to conform to the VALIDATE PASSWORD COMPONENT requirements enabled previously.
NOTE: The
caching_sha2_passwordandsha256_passwordauthentication plugins provide more secure password encryption than the previousmysql_native_passwordplugin, andcaching_sha2_passwordprovides better performance thansha256_password.
3. Grant privileges to the newly created user
Run this GRANT statement to grant these privileges to the new user:
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO "username"@"localhost" WITH GRANT OPTION;
4 Flush Privileges
This frees up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
mysql> FLUSH PRIVILEGES;
Exit the shell
mysql > exit
5. Log in using the new user credentials
mysql -u charles -p
Enter the password and hit enter
mysql>
This output shows you have logged in successfully.
Next Steps
A production-ready MySQL has been installed on your server. However, It is best practise to create a database on the server to work with in your application.
Conclusion
For more information about the basics of MySQL, the following documentations can help extensively: