MySQL, which stands for My (co-founder Michael Widenius’ daughter name) Structured Query Language, is an open source relational database management system. This RDBMS is supported by Oracle and runs on almost all platforms such as Linux, UNIX and MS Windows. MYSQL is usually the first choice for online publishing and web-based applications. Facebook, YouTube and Twitter all use MySQL as their RDBMS. MySQL is part of the very popular LAMP stack: Linux, Apache, MySQL and Python/PHP/Perl.
In this article we describe step by step how to proceed:
- Install the latest version of MySQL available in the apt package repository
- Run the MySQL security script
- Configure the root user to access the MySQL shell
- Finally test that MySQL is running
We have run the commands and procedures mentioned in this article on an Ubuntu 20.04 LTS system
MySQL installation and security configuration
In this section, we describe how to install MySQL from the Ubuntu command line and configure security.
You can open the Ubuntu command line, the terminal, either from the application launcher search bar or by pressing Ctrl+Alt+T.
Step 1: Update repository index
To install the latest available version of a software from the Internet repositories, your local repository index must match it. Run the following command as sudo to update your local repository index:
$ sudo apt-get update
Step 2: Install MySQL server with apt
Please run the following command as sudo to install MySQL from APT repositories.
$ sudo apt-get install mysql-server
Please note that only an authorized user on Ubuntu can add, remove and configure software.
The system may ask you for the password for sudo and also offer you a Y/n option to continue the installation. Type Y and then press Enter; MySQL will then be installed on your system. However, the process may take some time depending on your internet speed.
Step 3: Verify the installation (optional)
You can verify your MySQL installation and also check the version number by running the following command in your terminal:
$ mysql --version
Step 4: Make security configurations by running the provided security script
Whenever you install a new copy of MySQL, there are some default settings that you should change to increase the security of your MySQL installation. These include removing test users, test databases, and allowing remote login by a root user.
Run the following command as sudo to start the security script:
$ sudo mysql_secure_installation
When you run this command, the first thing you will be prompted to do is set up the Validate Password plugin. This allows you to set a secure password for root, depending on the strength of the password you want to choose. Type Y to start the Validate Password plugin and you will get the following prompt:
Enter the number of your choice for the password strength and press Enter. The system will then ask you for the new password of root. Type the password and retype it at the following prompts.
The system will then display the strength of the password you provided and also ask you if you want to continue with the password.
Type Y for Yes and press Enter.
The system will now ask you a series of questions, one at a time, and you can set the security of your system depending on your answers to these questions.
Series of questions:
The first question will ask you if you want to remove the anonymous test users.
Press y and the Enter key.
The second question will ask you if you want to disallow root login from a remote system. This should normally be your choice, because for a secure system, root should only be allowed to connect from localhost.
We, therefore, recommend that you enter y.
The third question asks if you want to remove the default MySQL database named “test” from your system and also delete access to it.
Type y to remove this test database.
In order for all of your changes configured above to take effect, the system must reload the permissions tables. Enter y and all your security changes will be applied.
Configuring root to use the MySQL shell
When running the security script, you specified a password for root. However, this user is not allowed to connect to MySQL Shell with the same password. You can configure root to use MySQL Shell by changing its authentication method from the default “auth_socket” to “mysql_native_password”.
Here’s how to do it:
Step 1: Start MySQL Shell
First, start the MySQL shell by running the following command as sudo:
$ sudo mysql
This will start the MySQL shell so you can work on the MySQL prompt.
Step 2: Check authentication method for MySQL users
At the MySQL prompt, enter the following command, which will allow you to check the authentication method/plugin that all of your MySQL accounts are currently using:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
In the above output, you can see that root uses the auth-socket plugin for authentication by default.
Step 3: Change the authentication method for root
Our goal is to have the root user authenticate to MySQL with a password. To do this, run the following command, which will have the root user identified by a mysql_native_password. Please remember that this password must be very strong.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
From now on, your root user will no longer have the password you specified when running the provided security script, but the strong password you specified in the above command.
Step 4: Reload grant tables
Now it’s time to tell the server to use the new permission settings from now on. Run the following command at the MySQL command prompt to reload the grant tables and register your changes:
mysql> FLUSH PRIVILEGES;
Step 5: Recheck authentication method for MySQL users
Now, if you recheck the authentication method for your MySQL user accounts using the following command, you will see that your root user is now using the mysql_native_password plugin for authentication:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Now that your root user is set up to connect to the MySQL shell with a secure password, you can exit the shell with the exit command as follows:
Testing if MySQL is running
To test whether MySQL is running on your system or not, you can use one of the following methods:
Method 1: Check the status of mysql.service
After you install MySQL on your system, mysql.service should most likely be running automatically. The output of the following command should verify the active status of the service:
$ systemctl status mysql.service
If for some reason the service is not running, you can use the following command as sudo to start the service:
$ sudo systemctl start mysql
You can use the following command to stop the service again if needed:
$ sudo systemctl stop mysql
Method 2: By connecting to MySQL Admin as root and running any administrative command
MySQL Admin is a client that allows you to perform administrative operations on MySQL. As an example, let’s run one of the administrative commands over it to check if the system is running properly and our root is configured to do so.
$ sudo mysqladmin -p -u root version
This command is to connect to MySQL as root, get the root password, and then return the MySQL admin version number.
If the command does what it is supposed to do and produces output similar to the above, you can be sure that your MySQL is up and running.
The process of installing and setting up MySQL on Ubuntu may seem cumbersome to some, especially when using the command line. However, if you carefully follow the above steps one by one, you will have no problems having a reliable, secure and stable installation of MySQL running on your Ubuntu.