MySQL is a popular open-source relational database management system (RDBMS) that is used by many of the world's largest companies like Facebook, Google, and Twitter. It is easy-to-use, fast, highly scalable, and is commonly installed as part of the famous LAMP (Linux, Apache, MySQL & PHP) stack.

In this article, you'll learn how to install, secure, and manage MySQL on a Ubuntu 18.04 machine. The same instructions are more or less applicable to Ubuntu 16.04 and other Ubuntu-based distributions like Kubuntu, Linux Mint and Elementary OS.

Prerequisites

To complete this tutorial successfully, you will need:

Installing MySQL on Ubuntu

Ubuntu provides excellent support for MySQL. By default, the latest version of MySQL is included in Ubuntu 18.04 repositories. At the time of writing this article, the current MySQL version available from the official Ubuntu repositories is 5.7.

Follow the below step-by-step instructions to install MySQL 5.7 on your Ubuntu 18.04 machine.

Step 1 — Update Packages List

Before installing new packages on your Ubuntu system, run the following commands to update the apt package index:

# update package index
$ sudo apt update

# upgrade packages
$ sudo apt -y upgrade

Step 2 — Install MySQL Packages

Now type the following command to install the default MySQL package:

$ sudo apt install mysql-server

The above command will install the MySQL server and all required dependencies. Once the installation is completed, the MySQL service will start automatically. To verify whether the MySQL service is running, type the following:

$ sudo service mysql status

The above command will output the following:

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2020-01-16 00:45:10 UTC; 5min ago
 Main PID: 22899 (mysqld)
    Tasks: 27 (limit: 1152)
   CGroup: /system.slice/mysql.service
           └─22899 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Step 3 — Secure MySQL Installation

During the installation, the installer will not prompt you to choose a password for the root user or confirm any other configuration changes. Since this leaves the MySQL installation insecure, you need to manually run the included security script to set a password and change other security defaults.

Just type the following in your terminal to run the security script:

$ mysql_secure_installation

The security script will prompt you several times to make changes to MySQL security options. The first prompt will ask you to configure the password validation plugin which is used to measure the strength of your MySQL users' passwords and allow the users to set only those passwords which are secure enough. Press ENTER if you don't want to set up the password validation plugin.

The next prompt is about setting up a new password for the MySQL root user. Just enter and then confirm a secure password of your choice.

Once you choose a password, the script will ask you to confirm the removal of the test database and anonymous user, restrict root user access to local machine only, and then load these changes so that MySQL immediately honors the changes you have made. You should press Y and ENTER to confirm all these changes.

Although we have set a password for the root MySQL user, it is not authenticated with a password when you connect to the MySQL shell. In the next section, you can adjust this setting to use a password for authentication instead.

Step 4 — Adjust User Authentication and Privileges (Optional)

By default, on a Ubuntu 18.04 system running MySQL 5.7 (and later), the root MySQL user is set to authenticate using the auth_socket plugin rather than with a password. The auth_socket plugin authenticates MySQL users that connect from the localhost through the Unix socket file.

Nevertheless, it provides more security and flexibility in many cases, but it also complicates thins when you want to connect to MySQL through 3rd-party software like phpMyAdmin. To use a password to connect to MySQL as root, you need to change its authentication method from auth_socket to mysql_native_password.

Type the following command in your terminal to connect to MySQL as a root user:

$ sudo mysql

Next, execute the following command to find out what authentication method each MySQL user is using:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

You should see something like below printed on the console:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *957563DE03A7F63A027EBE7FC33CD7C8BD2090BE | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

As you can see above, the root user is set to authenticate using the auth_socket plugin. To configure the root user to use a password for authentication instead, run the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

Don't forget to replace the your_password string in the above command with a strong password of your choice. This command will also change the root you set in Step 3.

Next, execute the following command to apply the changes you just made above:

mysql> FLUSH PRIVILEGES;

To confirm that the root user no longer uses the auth_socket plugin for authentication, run the above command again:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Here is how the output looks like this time:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *957563DE03A7F63A027EBE7FC33CD7C8BD2090BE | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

As you can see in the above example output, the root user authentication method is changed to mysql_native_password. You can now exit the MySQL by typing:

mysql> exit;

Managing MySQL Service

MySQL is installed as a systemd service. It means that you can manage it using standard systemd commands like systemctl or service on Ubuntu.

To start the MySQL service, run the following command:

$ sudo service mysql start

To check the current status of the MySQL service, issue the following command:

$ sudo service mysql status

You can stop the service anytime by typing:

$ sudo service mysql stop

If you want to restart the service, use the following command:

$ sudo service mysql restart

To reload the MySQL service, type the following:

$ sudo service mysql reload

By default, MySQL is configured to start automatically when the server starts. You can disable the automatic startup by typing:

$ sudo systemctl disable mysql

If you want to enable the automatic startup again, just type:

$ sudo systemctl enable mysql

Uninstalling MySQL

If you decide to uninstall MySQL from your Ubuntu machine for any reason, you can remove it just like any other package installed with the apt package manager.

Run the following commands to completely remove MySQL from Ubuntu 18.04:

$ sudo apt remove --purge mysql*
$ sudo apt autoremove
$ sudo apt autoclean

Next, remove the MySQL folders by typing:

sudo rm -rf /etc/mysql /var/lib/mysql

Finally, type the following to delete all MySQL files on your server:

$ sudo find / -iname 'mysql*' -exec rm -rf {} \;

That's it. Your Ubuntu 18.04 system should no longer contain default MySQL related files.

Conclusion

That's all folks for how to install MySQL on a Ubuntu 18.04 machine. In this article, you have learned how to install, manage, and uninstall MySQL service on your Ubuntu server.

You can use the same instructions for Ubuntu 16.04 and other Ubuntu-based distributions like Kubuntu, Linux Mint and Elementary OS.

✌️ Like this article? Follow me on Twitter and LinkedIn. You can also subscribe to RSS Feed.