How To Install MySQL on Ubuntu 18.04

Introduction

MySQL is a free and open-source database management system that is typically included in the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. To manage its data, it employs a relational database and SQL (Structured Query Language).

The installation is straightforward: update your package index, install the mysql-server package, and then execute the supplied security script.

Prerequisites

A root or non-root user with Sudo privileges

# sudo apt update

# sudo apt install mysql-server

# sudo mysql_secure_installation

This guide will show you how to install MySQL 5.7 on an Ubuntu 18.04 server.

Step 1 : Installing MySQL

Only the most recent version of MySQL is provided by default in the APT package repository on Ubuntu 18.04. That is MySQL 5.7 at the time of writing.

To install it, use apt to update your server’s package index:

# sudo apt update

Install the default package next:

# sudo apt install mysql-server

Using the systemctl start command, ensure that the server is up and running:

# sudo systemctl start mysql.service

These instructions will install and run MySQL without prompting you to set a password or make other configuration adjustments. We will handle this next because it makes your MySQL installation insecure.

Step 2 : MySQL Configuration

You should run the provided security script on new instals. This modifies some of the less secure default settings for remote root logins and sample users. Previously, you had to explicitly initialise the data directory in older versions of MySQL, but this is now done automatically.

Execute the security script:

# sudo mysql_secure_installation

It will take you through a series of prompts where you can adjust the security options for your MySQL installation. The first step will ask you if you want to install the Validate Password Plugin, which will allow you to test the strength of your MySQL password. The following step will be to set a password for the MySQL root user, regardless of your choice. Enter and confirm your preferred safe password.

From there, you can accept the settings for all further questions by pressing Y and then ENTER. This will remove some anonymous users and the test database, deactivate remote root logins, and load these new rules so that MySQL recognises the changes you’ve made right away.

To set up the MySQL data directory, use mysql install db for versions prior to 5.7.6, and mysqld —initialize for versions 5.7.6 and later. However, if you installed MySQL from the Debian package, as mentioned in Step 1, the data directory was automatically initialised; you don’t need to do anything. If you attempt to run the command nevertheless, you will receive the following error:

Output :
mysqld: Can’t create directory ‘/var/lib/mysql/’ (Errcode: 17 – File exists)
. . .
2018-04-23T13:48:00.572066Z 0 [ERROR] Aborting

Even if you’ve assigned a password to the root MySQL user, this user is not configured to utilise a password while connecting to the MySQL shell. If you like, you can change this setting by going to Step 3.

Step : 3 Updating User Authentication and Privileges (Optional)

The root MySQL user in Ubuntu systems running MySQL 5.7 (and later versions) is configured by default to authenticate using the auth socket plugin rather than a password. In many circumstances, this improves security and usability, but it can also complicate things when an external software (such as phpMyAdmin) needs to access the user.

To connect to MySQL as root using a password, change the authentication method from auth_socket to mysql _native_password. To begin, launch the MySQL prompt from your terminal:

# sudo mysql

Next, use the following command to determine which authentication mechanism each of your MySQL user accounts uses:

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

The root user does, in fact, authenticate using the auth socket plugin in this case. Run the ALTER USER command to configure the root account to require password authentication. Change the password to a strong password of your choice, and keep in mind that this operation will change the root password you specified in Step 2:

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

Then, execute FLUSH PRIVILEGES, which instructs the server to reload the grant tables and apply your modified changes:

Mysql> FLUSH PRIVILEGES;

Check the authentication methods used by each of your users once more to ensure that root is no longer authenticating via the auth_socket plugin:

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

In this example output, you can see that the root MySQL user now authenticates with a password. You can quit the MySQL shell once you have confirmed this on your own server:

Mysql> exit

Alternatively, some people may find that connecting to MySQL with a dedicated user is more convenient for their workflow. Open the MySQL shell once more to create such a user:

# sudo mysql

Note: If you have activated password authentication for root, as indicated in the preceding paragraphs, you will need to use a separate command to access the MySQL shell. The following will execute your MySQL client with regular user credentials, and you will only get administrator capabilities within the database by authenticating:

# mysql -u root –p

Create a new user and assign it a secure password:

Mysql> CREATE USER 'administrator'@'localhost' IDENTIFIED BY 'password';

Then, give your new user the necessary permissions. For example, you could use the following command to provide the user privileges to all tables in the database, as well as the ability to add, alter, and delete user privileges:

mysql > GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' WITH GRANT OPTION;

You do not need to use the FLUSH PRIVILEGES command again at this point. This command is only required when you use INSERT, UPDATE, or DELETE statements to edit the grant tables. FLUSH PRIVILEGES is unneeded here because you created a new user rather than changing an existing one.

After that, leave the MySQL shell:

mysql >exit

Finally, let’s put MySQL to the test.

Step 4: Put MySQL to the test

MySQL should have started immediately, regardless of how you installed it. Check its status to see if it works.

# systemctl status mysql.service

You should see something like this:

Output :
● mysql.service – MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
Active: active (running) since Wed 2018-04-23 21:21:25 UTC; 30min ago
Main PID: 3754 (mysqld)
Tasks: 28
Memory: 142.3M
CPU: 1.994s
CGroup: /system.slice/mysql.service
└─3754 /usr/sbin/mysqld

If MySQL is not already operating, start it using sudo systemctl start mysql.

You can also connect to the database using the mysqladmin tool, which is a client that allows you to conduct administrative commands. For instance, this command instructs MySQL to login as root (-u root), prompt for a password (-p), and return the version.

# sudo mysqladmin -p -u root version

You should see something like this:

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

Server version 5.7.21-1ubuntu1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 30 min 54 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 115 Flush tables: 1 Open tables: 34 Queries per second avg: 0.006

This indicates that MySQL is operational.

Conclusion

On your server, you now have a simple MySQL configuration.

Related Articles

Leave A Comment Cancel reply

How to Config Initial Server Setup With Ubuntu 18.04

Support

Manual Head Office