MySQL database is open-source software which can be used for many purposes. In this tutorial I will show you How to install MySQL database on Ubuntu 18.04? WordPress 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). (How to install MySQL database on Ubuntu 18.04?)
How to install MySQL database on Ubuntu 18.04?
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. (How to install MySQL database on Ubuntu 18.04?)
# 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. (How to install MySQL database on Ubuntu 18.04?)
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: (How to install MySQL database on Ubuntu 18.04?)
- Login to MySQL database.
- Check current authentication methods for each user.
- Set new passwordfor root.
- Flush privileges.
- 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. (How to install MySQL database on Ubuntu 18.04?)
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. (How to install MySQL database on Ubuntu 18.04?)
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. (How to install MySQL database on Ubuntu 18.04?)
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. (How to install MySQL database on Ubuntu 18.04?)
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
(How to install MySQL database on Ubuntu 18.04?)
If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!
