MySQL is an open-source RDBMS (Relational Database Management System) developed by “Oracle Corporation” that used to store and retrieve data for a variety of applications. It’s one of the most prominent RDBMS platforms globally, which provides extensive community supports and widely used in modern web-application technology.
What’s New on MySQL 8.0
- New Database Role
- Improvement of JSON and Documents supports
- Ability to store Documents
- Default Charset to utf8mb4
- Support Window Functions
- Persistence Configuration through MySQL Shell
- Improvement of InnoDB supports, GIS, MySQL Shell, MySQL Router, etc
What will you learn?
- How to Install MySQL Server 8.0 on Ubuntu 20.04
- Secure MySQL Server installation using the “mysql_secure_installation” tool
- Checking MySQL list users and MySQL version
What do you need?
- Ubuntu 20.04 Server
- Root privileges
Before going any further, log in to your server and run the “sudo” command below to get the full root privileges on your system.
Next, install the MySQL 8.0 on the Ubuntu system using the following steps.
Step 1 – Add Official MySQL Repository
Firstly, you need to add the official MySQL to the Ubuntu 20.04 Server.
Go to the MySQL Apt repository download page https://dev.mysql.com/downloads/repo/apt/, download the release package for Ubuntu/Debian system using the ‘wget’ command below.
wget -q https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
After that, verify the package using the md5sum command below.
Now make sure the MD5 signature of your file match with the MD5 signature on the download page.
Next, install the official MySQL repository using the following command.
dpkg -i mysql-apt-config_0.8.15-1_all.deb
During the installation, select the MySQL product that you want to install, and by default, the “MySQL 8.0″ is the default candidate for the installation.
Choose ‘Ok‘ to confirm the product that you want to install, then select ‘Ok‘ to install the repository.
After that, update all available repositories on the Ubuntu system.
As a result, you’ve added the official MySQL repository to the Ubuntu 20.04 system. In effect, you’re ready to install the MySQL 8.0.
Step 2 – Install MySQL 8.0 Server on Ubuntu 20.04
In this step, you will install the MySQL 8.0, start the MySQL service and add it to run automatically on system boot.
To install the MySQL 8.0, run the apt command below.
apt install mysql-server
Now type your strong password for the MySQL root user and select ‘Ok.’
Re-type your password again and select ‘Ok‘ to continue.
After that, you must configure the Authentication plugin for your MySQL installation.
The new version of MySQL 8.0 comes with the new authentication plugins ‘caching_sha2_password‘. It’s an improvement for the MySQL security, which is using the sha256-based password method.
More information about MySQL Authentication plugin “caching_sha2_password“.
Select the “Use Strong Password Encryption” if you want to use the ‘caching_sha2_password‘ authentication plugin, which is good for your MySQL security.
However, you can use the old authentication plugin ‘mysql_native_plugin‘ for the MySQL 5.x compatibility.
After that, select ‘Ok‘ again to continue.
As a result, the MySQL 8.0 installation is completed.
Next, start the MySQL service and add it to the system boot using the systemctl command below.
systemctl start mysql systemctl enable mysql
After that, check the MySQL service using the following command.
systemctl status mysql
Below is the MySQL service status you will get.
As can be seen, the MySQL service is up and running on Ubuntu 20.04 Server.
Step 3 – Secure MySQL Installation on Ubuntu 20.04
In this step, you will learn how to secure MySQL using the ‘mysql_secure_installation’ shell script, which is installed by default within MySQL packages.
Below are some tasks which the ‘mysql_secure_installation’ command can do for your:
- Setup and validate root password
- Disable root login from the outside localhost
- Remove defualt Anonymous account
- Delete default “test” database, which is accessible for all users including Anonymous user
- Also, remove all privileges that allow everyone to access databases with names that start with “test_”
Next, secure your MySQL installation using the ‘mysql_secure_installation’ command below.
Enter your MySQL root password to continue.
Enter the password for user root: TYPE YOUR PASSWORD.
– Choose the Password Policy
Next, type “Y” to configure the “VALIDATE PASSWORD COMPONENT” for MySQL.
VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of the password and allows the users to set only those passwords which are secure enough. Would you like to set up the VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: Y
After that, type 1 or 2 for your password validation policy, which is between “MEDIUM” and “STRONG” policies. Also, for a production environment, do not choose the “LOW” 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
– Change the Password with the New Password Policy
Next, the ‘VALIDATE PASSWORD COMPONENT’ will check your current password for the MySQL root user and give you an estimated score. Also, if your score is not strong enough, you need to change the current password.
Type ‘Y‘ to change your current password.
Estimated strength of the password: 50 Change the password for root? ((Press y|Y for Yes, any other key for No): Y
Type your new password and repeat it.
New password: Re-enter new password:
After that, the MySQL will recheck your new password, make sure you get the password score ‘100’ and type ‘Y‘ to continue.
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
– Remove Anonymous User
Type ‘Y‘ to continue and remove the default anonymous user.
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.
– Disable Root Login remotely
After that, disable the root login from outside the localhost, type ‘Y‘ to continue.
Usually, root should only be allowed to connect from the '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.
– Remove the Database Test
The database ‘test’ is always there on the MySQL server. Type ‘Y‘ to remove the ‘test’ database.
By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing and remove 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 privileges
After removing the anonymous user and ‘test’ database, type ‘Y‘ again to reload the MySQL privilege and apply the new configuration.
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.
Next, at the end of your terminal, you will get the ‘All done!‘ response.
As a result, you’ve successfully secured the MySQL installation using the ‘mysql_secure_installation’ script.
Step 4 – Verify MySQL 8.0 Installation on Ubuntu 20.04
In this step, you will verify the MySQL installation using the MySQL shell and the “mysqladmin” command.
First, log in to the MySQL shell using your root user and password as below.
mysql -u root -p TYPE YOUR PASSWORD
After that, check all available users on the MySQL server and make sure all users have a ‘caching_sha2_password’ authentication plugin.
Check using the following query.
SELECT user,host,plugin,show_db_priv FROM mysql.user;
Below is the result you will get.
As can be seen, all users on the MySQL Server has an authentication plugin “caching_sha2_password”.
Next, log out from the MySQL by typing “exit” or using the “Ctrl+d” button.
After that, check your MySQL server using the “mysqladmin” command below.
mysqladmin -u root -p version
Type your MySQL root password, and you will get the following result.
As can be seen, you’ve successfully installed the MySQL 8.0.20 on the Ubuntu 20.04 Server.