Introduction
MySQL is an open-source relational database that is free and widely used apart of Popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl)stack.This RDBMS is backed by Oracle and runs on almost all platforms such as Linux, UNIX and MS Windows.In this article will learn how to install MySQL from default repositories on ubuntu 16.04.
Also we will describe a step-by-step procedure on how to:
- Configure Root user to access MySQL shell
- Finally, test if MySQL is up and running
This tutorial will explain how to install MySQL version 5.7 on an Ubuntu 18.04 server.The same will work for 16.04 also.
Before We Begin:
To follow this tutorial, you will need:
- Ubuntu 18.04 server /desktop with a non-root user with sudo privileges
Step 1 — Installing MySQL
At the time of writing this article, the default latest version of MySQL available from the official Ubuntu repositories is 5.7.So we will get MySQL version 5.7.
To install it, update the package index on your server with apt:
sudo apt update
Then install the MySQL package with the following command:
sudo apt install mysql-server
The installation process will prompt for enter ‘Y’ to complete the installation.
Secure MySQL Installation
Now run the mysql_secure_installation command.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.
sudo mysql_secure_installation
follow the onscreen instructions .Here is what we are getting
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?
Note:For production environment you can enable this plugin for more security.You can select a password policy from the options and set a root password accordingly.For this tutorial we are ignoring this option and putting a simple password for root.
Press y|Y for Yes, any other key for No: n
Please set the password for root here.
New password:
Re-enter new password:
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.
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!
Test if MySQL is Up and Running
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:
sudo systemctl status mysql
OR
sudo service mysql status
will get output like :
● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2020-07-12 19:39:47 IST; 44min ago Main PID: 24129 (mysqld) Tasks: 28 (limit: 4666) CGroup: /system.slice/mysql.service └─24129 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid Jul 12 19:39:46 docker systemd[1]: Starting MySQL Community Server... Jul 12 19:39:47 docker systemd[1]: Started MySQL Community Server.
If MySQL isn’t running, you can start it with sudo systemctl start mysql.service (can use command sudo systemctl start mysql )
In order to stop MySQL service type :
sudo systemctl stop mysql
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.
If you try to login as
mysql -u root -p
will get error like
You can fix this 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.
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.
3: Change the authentication method for root
Our goal 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.
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;
Now you can exit the shell using the exit command as follows:
mysql> exit
Test mysql root login :
Now login into mysql shell as root user using the new password
mysql -u root -p
enter the password when prompt
You can check mysql version using :
mysql --version
Conclusion
You now have a basic MySQL setup installed on your server with root login.