MySQL is among the popular relational database systems used as data storage of websites and web applications. This tutorial provides all the steps required to install MySQL 8 on Ubuntu 20.04 LTS. The steps should be the same on other versions of Ubuntu and Linux systems. You can also follow How To Install MySQL 8 on Ubuntu 18.04 LTS. You may also be interested in other MySQL specific tutorials including How To Install MySQL 8 on Windows and Learn Basic SQL Queries Using MySQL.
Prerequisites
This tutorial assumes that you have already installed Ubuntu 20.04 LTS desktop or server version either for local or production usage. You can follow Install Ubuntu 20.04 LTS Desktop, Install Ubuntu 20.04 LTS On Windows Using VMware, and Spin Up Ubuntu 20.04 LTS Server On Amazon EC2 to install Ubuntu 20.04 LTS. It also assumes that you have either root privileges or a regular user with sudo privileges.
Install MySQL
This section provides the commands required to install MySQL Database Server version 8 on Ubuntu 20.04 LTS.
# Install MySQL Server 8 sudo apt install mysql-server
# Output Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libfcgi-perl libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0 --- --- Setting up mysql-server-8.0 (8.0.20-0ubuntu0.20.04.1) ... update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode Renaming removed key_buffer and myisam-recover options (if present) mysqld will log errors to /var/log/mysql/error.log mysqld is running as pid 63195 Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service. Setting up mysql-server (8.0.20-0ubuntu0.20.04.1) ... Processing triggers for systemd (245.4-4ubuntu3) ... Processing triggers for man-db (2.9.1-1) ... Processing triggers for libc-bin (2.31-0ubuntu9) ...
I have highlighted the MySQL packages that get installed after executing the installation command. It installs MySQL core, server, and client packages. Also, the MySQL server version 8.0.20 was installed while writing this tutorial.
Configure and Secure MySQL Installation
We must also configure and secure the installation using the command mysql_secure_installation as shown below. It will ask to set the root password and a few security questions.
Step 1 - Secure Installation - Execute the command to start the MySQL configuration.
# Secure MySQL sudo mysql_secure_installation
Step 2 - Password Validator - It asks to confirm the usage of the password validator to validate the password. It will also show the password strength while providing the password.
# Password Validator Component Press y|Y for Yes, any other key for No: y
If we select No, it won't check the password strength of the MySQL root and other users while adding them. We should use a strong password for the MySQL users, hence it's recommended to use the Password Validator Component.
Step 3 - Password Validation Level - The secure installation command asks the password validation level and provides options to choose among Low(0), Medium(1), and Strong(2). It's recommended to use at least Medium Level to have a strong password of all the MySQL users. The validation rules of all the levels are as listed below.
Low - It expects a password having at least 8 characters without any restriction on the characters.
Medium - The Medium Level expects a password that has at least 8 characters and allows numeric, uppercase, lowercase, and special characters.
Strong - The Strong Level expects a password that has at least 8 characters and allows numeric, uppercase, lowercase, and special characters. It also allows the dictionary file.
# Password Validation Level Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Step 4 - Root Password - The default authentication plugin used by MySQL for the root user is auth_socket.
# Password Prompt New password:<password> Re-enter new password:<repeat password>
In case you have selected to use Password Validator, it will also show the password strength of the root password and confirm to use the given password as shown below.
# Confirm Password Estimated strength of the password: 80 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
If we opt for No, it will again prompt for the password.
Step 5 - Remove Anonymous Users - After providing the password, the secure installation process asks to remove the anonymous users. MySQL adds an anonymous user while installing it for testing purposes and allows anyone to log in without a password. It's recommended to remove the anonymous user.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Step 6 - Disallow Remote Login - The secure installation process also asks to confirm whether remote login is allowed for the root user. We should choose option y to restrict the root user to the localhost. We can always add additional users to allow remote login when required.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Step 7 - Remove Test Database - MySQL creates the test database while installing it. You may keep the test database for analysis purposes and later drop it.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
Step 8 - Reload Privilege Tables - At last, the secure installation asks to reload the privileges tables to immediately apply the changes.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
The complete steps followed by me on a fresh installation of MySQL are as shown below.
# Secure MySQL sudo mysql_secure_installation
# Configuration 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: 1
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 50 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y 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.
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!
The above steps remove the test database and anonymous users. It also disallows remote login to ensure that the server is accessible locally either using 127.0.0.1 or localhost.
Verify Installation
We can verify the installation of MySQL server using the command as shown below to check whether MySQL server is running.
# Check MySQL Status systemctl status mysql
# Output● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Fri 2020-06-05 19:42:35 IST; 9min ago Main PID: 63433 (mysqld) Status: "Server is operational" Tasks: 40 (limit: 4624) Memory: 319.2M CGroup: /system.slice/mysql.service └─63433 /usr/sbin/mysqld Jun 05 19:42:35 bravo systemd[1]: Starting MySQL Community Server... Jun 05 19:42:35 bravo systemd[1]: Started MySQL Community Server.
Also, check the version of the server installed by us and make sure that the server is accessible using the root password configured by us.
# Check version sudo mysql --version
# Output mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
# Login sudo mysql # OR sudo mysql -u root
# Quit Database exit
We can simply log in to the MySQL as shown above, even we have provided the password of the root user while securing the MySQL server. MySQL directly allows the root user since it uses the auth_socket plugin for the root user which does not need any password.
Root User Password Plugin
We can change the password plugin of the root user to caching_sha2_password (preferred) or mysql_native_password to allow the other applications including phpMyAdmin to login to MySQL server using the root user. It can be done using the commands as shown below.
# Login to MySQL sudo mysql
# Check password scheme of root user SELECT user,authentication_string,plugin,host FROM mysql.user;
# Note the password plugin of root user +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+------------------------------------------------------------------------+-----------------------+-----------+ | debian-sys-maint | $A$005$[DA NP9|K1zAmHe`LVwrhII7zBo5b5xUoPnvOLuCa9CSJVqCn7W1rzOCCyZD | caching_sha2_password | localhost | | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost | | root | | auth_socket | localhost | +------------------+------------------------------------------------------------------------+-----------------------+-----------+
# Change to new and recommended password plugin - caching_sha2_password ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<pw>';
# Apply changes flush privileges;
# Check password scheme of root user SELECT user,authentication_string,plugin,host FROM mysql.user;
# Note the password plugin of root user
+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | $A$005$ZtYD-ppbn>iO�"MHhl/0TXh9Qo3xYdWK3ThKPmDB6r.QhVlZY1dcT1LWH0A | caching_sha2_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+
# Change password plugin of root user to mysql_native_password - not recommended ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<pw>';
# Apply changes
flush privileges;
# Check password scheme of root user SELECT user,authentication_string,plugin,host FROM mysql.user;
# Note the password plugin of root user +------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | *E5C4F73D963032BEF9BB4CA799A848C08BADC343 | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+
# Quit Database
exit
Additional Users
We can add additional users using the CREATE USER command as shown below.
# Login to MySQL - auth_socket sudo mysql
# OR - Login to MySQL - caching_sha2_password sudo mysql -u root -p
# Add User CREATE USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '<pw>';
# Apply changes flush privileges;
# Quit Database exit
You can also follow Learn Basic SQL Queries Using MySQL to learn basic SQL queries.
Important Commands
This section shows some of the important commands to start, stop, and restart the server.
# Check server status sudo service mysql status
# Stop server sudo service mysql stop
# Start server sudo service mysql start
# Restart server sudo service mysql restart
Summary
This tutorial provided all the steps required to install the most recent version of MySQL server i.e MySQL 8 on Ubuntu 20.04 LTS. It also provided the configuration steps to further secure the MySQL server installation.
After completing the installation you may also follow Learn Basic SQL Queries Using MySQL, Guide To Design Database For Blog Management In MySQL, and Guide To Design Database For Online Shopping Cart In MySQL.
