In this tutorial, we will show you how to install and improve the security of MySQL on Ubuntu 18.04. MySQL is one of the most popular and widely-used open-source relational database management systems. It is used for building web applications such as WordPress, Drupal, Joomla, and Magento, and is an important part of the LAMP/LEMP open-source web application software stacks. At the moment of writing this tutorial, the latest supported version from the official Ubuntu package repository is MySQL 5.7. Let’s get started.
Requirements:
- For the purposes of this tutorial, we will be using an Ubuntu server.
- Full SSH root access or a user with sudo privileges.
Step 1: Connect to Your Server
Before we begin, you need to connect to your server via SSH. To do connect to your server as the root user, use the following command:
ssh [email protected]_ADDRESS -p PORT_NUMBER
Make sure to replace IP_ADDRESS and PORT_NUMBER with your actual server IP address and SSH port number.
Once logged in, make sure that your server is up-to-date by running the following commands:
sudo apt update sudo apt upgrade
Step 2: Install MySQL
The following command will install the latest MySQL 5.7 server from the official Ubuntu repositories:
sudo apt install mysql-server
The MySQL web server will be started automatically as soon as the installation is completed.
To verify if the installation has been successful and if MySQL is running on your server, you can execute the following command:
sudo systemctl status mysql
You should see the following output on your screen:
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2019-02-08 02:05:27 CST; 2min 4s ago
Main PID: 13853 (mysqld)
Tasks: 27 (limit: 2321)
CGroup: /system.slice/mysql.service
└─13853 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
You can also enable the MySQL service to automatically start up upon server reboot with the following command:
sudo systemctl enable mysql
You can also check the MySQL version with:
mysql -V
Output:
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
Step 3: Secure MySQL
To improve the security of our MySQL installation and set up a password for our MySQL root user, we need to run the mysql_secure_installation script, which is included with the MySQL package.
To run the script, execute the following command:
sudo mysql_secure_installation
Output:
Securing the MySQL server deployment. Connecting to MySQL using a blank password.
First, you will be asked about setting up the validate password plugin.
VALIDATE PASSWORD PLUGIN 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 plugin? Press y|Y for Yes, any other key for No:
This plugin checks the strength of a password and allows the users to set only passwords which are secure enough.
Whether to enable this or not is entirely up to you, but using strong passwords is always highly recommended.
If you choose ‘Yes’, you will be asked to choose the level of the password validation policy:
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:
Next, you will be asked to set the password for the root user. Enter and confirm the password in order to continue to the next step:
Please set the password for root here. New password: Re-enter new password: Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
After the password has been set, you will be asked a few more questions about removing anonymous users, disallowing remote root login, and removing test databases. You should answer “Yes” to all of them:
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. 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. 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.
Reload the privilege tables so that all changes take effect:
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!
Step 4: Log in to MySQL
To log in to your MySQL web server as the root user, you can run the following command:
sudo mysql -u root -p
and enter the MySQL root password you have set up during the mysql_secure_installation script.
Once successfully logged in, you should get the following output:
Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
Step 5: Configuring the MySQL Database Server
If we want to make any additional changes, such as changing settings to help optimize the performance of our database server, you can edit the MySQL default configuration file located at /etc/mysql/my.cnf.
sudo nano /etc/mysql/my.cnf
After making any changes, you need to restart the database server:
sudo systemctl restart mysql
Conclusion
That’s it – MySQL 5.7 has been successfully installed on your Ubuntu 18.04 VPS. Make sure to check our blog for more MySQL related tutorials, including:
Basic MySQL database administration on a Linux VPS Part 1
Basic MySQL database administration on a Linux VPS Part 2
How to Create a New MySQL User and Grant Permissions
How to backup and restore MySQL database
and many more…