How to install MySQL database on Ubuntu 18.04?

How to install MySQL database on Ubuntu 18.04?

MySQL database is open-source software which can be used for many purposes. Worpress as default use MySQL under the hood for storing all blog information for example. MySQL is a component of the LAMP web application software stack (Linux, Apache, MySQL, Perl/PHP/Python).

Installation

First of all we have to update packages. As the next step we will install mysql-server package and finally configure our MySQL database instance. In this tutorial we will install MySQL database in version 5.7.

# 1. Update packages
sudo apt update
sudo apt upgrade
# 2. Install MySQL Server
sudo apt install mysql-server
# 3. Configure MySQL database instance
sudo mysql_secure_installation

Configuration

When you run the MySQL secure-installation you will follow step by step to configure security of our MySQL database instance. One of the most important step (in my opinion) is to disallow root user to login remotely. If you will disallow it, you will be able to login to MySQL only from local machine when the database was installed. It will give you two levels of authentication. Firstly, you must be logged in remote machine and next you can login to MySQL database.

pawel@test:~$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN 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 plugin?
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: 0
Please set the password for root here.
New password:
Re-enter new password:
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
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!

Change authentication method

The next important step is to change the authentication method. As default is “auth_socket”. Our target is to change it to: “mysql_native_password”. To change it please:

  1. Login to MySQL database.
  2. Check current authentication methods for each user.
  3. Set new passwordfor root.
  4. Flush privileges.
  5. Verify new status of authentication methods.

Step #1

# Login into MySQL database
sudo mysql

Step #2

-- Check current authentication methods for each user.
mysql> SELECT user, plugin FROM mysql.user;

+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.session    | mysql_native_password |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
4 rows in set (0.01 sec)

 Step #3

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

Step #4

Flush privileges command will reload all grants. MySQL server will refresh all entries which are exposed in grant tables.

mysql> FLUSH PRIVILEGES;

Step #5

Now you are ready to verify whether new authentication method was applied successfully to root user. Let’s run the SQL query one again to check it.

mysql> SELECT user, plugin FROM mysql.user;
+------------------+-----------------------+
| user             | plugin                |
+------------------+-----------------------+
| root             | mysql_native_password |
| mysql.session    | mysql_native_password |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
4 rows in set (0.01 sec)

Let’s logout from MySQL:

mysql> exit

Login using password

To test our new password, we have to login to MySQL using the syntax like below. After -u we put the username. The -p parameter gives us prompt for password.

pawel@test:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 MySQL server

In case when you will be not able to login to MySQL database, let’s check if service is up and running. To do this you can use the command below.

systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2019-01-26 16:45:43 UTC; 14min ago
  Process: 4202 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 4179 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 4204 (mysqld)
    Tasks: 28 (limit: 4708)
   CGroup: /system.slice/mysql.service
           └─4204 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Jan 26 16:45:42 test systemd[1]: Starting MySQL Community Server...
Jan 26 16:45:43 test systemd[1]: Started MySQL Community Server.

Start service

In case when service is not running please run it using this command:

sudo systemctl start mysql

If you enjoyed this post please leave the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

Please follow and like us:

Leave a Reply

Close Menu
Social media & sharing icons powered by UltimatelySocial

Enjoy this blog? Please spread the word :)