MySQL that stands for My (co-founder Michael Widenius’s daughter name) Structured Query Language is an open source relational database management system. This RDBMS is backed by Oracle and runs on almost all platforms such as Linux, UNIX and MS Windows. MYSQL is mostly the first choice for online publishing and web-based applications. Facebook, YouTube, and Twitter are all using MySQL as their RDBMS. MySQL is part of the very popular LAMP stack: Linux, Apache, MySQL and Python/PHP/Perl.
In this article, we will describe a step-by-step procedure on how to:
- Install the latest version of MySQL available in the apt package repository
- Run MySQL security script
- Configure Root user to access MySQL shell
- Finally, test if MySQL is up and running
We have run the commands and procedures mentioned in this article on a Ubuntu 18.04 LTS system
MySQL installation and security configuration
In this section, we will describe how to install MySQL through the Ubuntu command line and configure security on it.
You can open the Ubuntu command line, the Terminal, either through the application launcher search bar or the Ctrl+Alt+T shortcut.
Step 1: Update repository index
In order to install the latest available version of a software from the Internet repositories, your local repository index needs to be in line with them. Run the following command as sudo in order to update your local repository index:
$ sudo apt-get update
Step 2: Install MySQL Server with apt
Please run the following command as sudo in order to install MySQL from the APT repositories.
$ sudo apt-get install mysql-server
Please note that only an authorized user can add, remove and configure software on Ubuntu.
The system might ask you the password for sudo and also provide you with a Y/n option to continue the installation. Enter Y and then hit enter; MySQL will then be installed on your system. The process may, however, take some time depending on your Internet speed.
Step 3: Verify 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 included security script
Whenever you install a fresh copy of MySQL, there are some default settings that you should change in order to enhance the security of your MySQL installation. This includes the removal of test users, test databases and permission for remote login by a root user.
Run the following command as sudo in order to run the security script:
$ sudo mysql_secure_installation
As you run this command, the first thing you will be asked to do is to setup the Validate Password plugin. This lets you set a secure password for root depending on the strength of the password you want to choose. Enter Y in order to run the Validate Password Plugin and you will get the following prompt:
Enter your number of choice for password strength and hit Enter. The system will then ask you for the new password of root. Enter and then re-enter the password on 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.
Enter Y for Yes and hit Enter.
The system will now present you with a series of questions, one by one, 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 hit Enter.
The second question will ask 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 the localhost.
Thus, we recommend entering y.
The 3rd question will ask you if you want to remove the default MySQL database named “test” from your system and also remove the access to it.
Enter y to remove this test database.
In order for all your above-configured changes to take effect, the system needs to reload the privilege tables. Enter y and all your security changes will be committed.
Configuring Root to use MySQL shell
While running the security script, you provided a password for root. This user, however, is not allowed to connect to the MySQL shell using the same password. You can change configure root to use the MySQL shell by changing its authentication method from the default “auth_socket” to “mysql_native_password”.
Here is how you can 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 that you can work on the MySQL prompt.
Step 2: Check authentication method for MySQL users
In the MySQL prompt, enter the following command that lets you check the authentication method/plugin that all 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 is using the auth-socket plugin for authentication by default.
Step 3: Change the authentication method for root
Our purpose here is that the root user should authenticate with a password on MySQL. To do this, run the following command that lets the root be identified by a mysql_native_password. Please remember that this password has to be very strong.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
From now on your root will not have the password you specified while running the included security script, but this strong password you specified in the above-mentioned command.
Step 4: Reload grant tables
Now is the time to tell the server to use the new privilege settings from now on. Run the following command in the MySQL prompt to reload the grant tables and register your changes:
mysql> FLUSH PRIVILEGES;
Step 5: Recheck authentication method for MySQL users
Now, when you recheck the authentication method for your MySQL user accounts through 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 setup to connect to the MySQL shell through a secure password, you can exit the shell using the exit command as follows:
Test if MySQL is Up and Running
In order to check if 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 have installed MySQL on your system, the mysql.service should most probably be automatically running. The output of the following command should verify the active status of the service:
$ systemctl status mysql.service
If for any reason, the service is not running you can use the following command as sudo to start the service:
$ sudo systemctl start mysql
The following command will help you in stopping the service whenever you have to:
$ sudo systemctl stop mysql
Method 2: By connecting to MySQL Admin as root and running any administrative command
MySQL Admin is a client that lets you perform administrative operations on MySQL. Let us run one of the administrative commands through it as an example to check if the system is running properly and our root is configured to use it.
$ sudo mysqladmin -p -u root version
This command is meant to connect to MySQL as root, prompt for root password and then return the version number of MySQL admin.
If the command does what it is required to and produces output similar to the above one, you can be sure that your MySQL is up and running.
The process of installing and setting up MySQL on Ubuntu might seem cumbersome to some, especially using the command line. If you, however, use the above mentioned steps carefully one by one, you will have no problem in having a reliable, secure and stable installation of MySQL running on your Ubuntu.