Installing MySQL on Ubuntu Server

MySQL is a database management system. It provides access to databases where your site can store information.

Use apt-get to acquire and install this software:

sudo apt-get update
sudo apt-get install mysql-server

Once the installation is completed, run a simple security script. This will remove some dangerous defaults and lock down access to your database system.

Start the script by running:

sudo mysql_secure_installation

This will ask if you want to configure the VALIDATE PASSWORD PLUGIN
Answer Y for yes, or anything else to continue without enabling.

If you answer “yes”, you’ll be asked to select a level of password validation.

Then select Password strength level

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1

Regardless of whether you chose to set up the VALIDATE PASSWORD PLUGIN, your server will next ask you to select and confirm a password for the MySQL root user. This is an administrative account in MySQL that has increased privileges. Think of it as being similar to the root account for the server itself (although the one you are configuring now is a MySQL-specific account). Make sure this is a strong, unique password, and do not leave it blank.

If you enabled password validation, you’ll be shown the password strength for the root password you just entered and your server will ask if you want to change that password. If you are happy with your current password, enter N for “no” at the prompt:

MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password.

Eventually you are going to change this method to the authentication by password, in order to create password, follow the steps below.

sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

You will see list of users, root must be using plugin “auth_socket”, this need to be replace by “mysql_native_password”

Update root user to use password by running following SQL:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

After changing password, you need to refresh user privileges.

FLUSH PRIVILEGES;
MYSQL> exit;

That’s it, now you should be able to connect to MySQL using root password you just created using following command.

sudo mysql -u root -p

About the author

Danyal Ali Butt

I'm working as programmer since 2002. Born, grew up, studied and worked in Pakistan now Im located in Denmark since 2006.

View all posts