Install MySQL Server (Ubuntu 18.04)

Introduction

This is a quick step-by-step tutorial on how to install MySQL server on Ubuntu 18.04. The MySQL is an open-source database management system that uses/relies on relational database and SQL (Structured Query Language).

Step 1: Install MySQL Server

First of all, update package index and install default package (~90 MB ):

$ sudo apt update
$ sudo apt install mysql-server

By default, server is exposed (open to access/exploit) and installed without any passwords. So, it’s relevant to go through short steps of securing it (especially if you’re going to access it remotely). We’ll use mysql_secure_installation:

$ 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: 1
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!

Step 2: Configure

Finally, to use server remotely, edit the /etc/mysql/mysql.conf.d/mysqld.cnf configuration file:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and change the bind-address:

FROM:
bind-address            = 127.0.0.1
TO:
bind-address            = 0.0.0.0

also reboot the server:

$ sudo service mysql restart

Related to ufw firewall you will also have to adjust some network/system rules. To allow TCP incoming traffic from any source to your server’s port 3306:

$ sudo ufw allow from any to any port 3306 proto tcp

or

$ sudo ufw allow mysql

MySQL server usually listens on port 3306. It’s maybe a good idea to confirm that with either netstat -tulpn or ss -ltn

Active Internet connections (only servers) 
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 14429/mysqld

To connect remotely to the server use:

$ mysql -u USERNAME -p PASSWORD -h MYSQL_SERVER_IP

If you are missing the mysql command you can install it by executing:

$ sudo apt install mysql-client

Check version, with query:

SHOW VARIABLES LIKE "%version%";

or you can also use:

$ sudo mysqladmin -p -u root version

Enter password: 
mysqladmin Ver 8.42 Distrib 5.7.22, for Linux on x86_64
Copyright (c) 2000, 2018, 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.

Server version 5.7.22-0ubuntu18.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 32 min 34 sec

Additional Info

You can encounter this problem:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The reason is usage of default UNIX auth_socket plugin, which is authorizing users with system user credentias. You can see which user uses what by entering the following command:

$ sudo mysql -u root

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin

There are few ways to solve this:

Option 1: Set the root user to use the mysql_native_password plugin

$ sudo mysql -u root # I had to use “sudo” since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Option 2: Create a new db_user with you system_user (RECOMMENDED)

$ sudo mysql -u root

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Remember that if you use option #2 you’ll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)

Note: On some systems (e.g., Debian stretch) auth_socket plugin is called unix_socket, so the corresponding SQL command should be:

UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Move MySQL database to another path

Before continuing, it might be a good idea to make a backup. In short:

• Stop MySQL service
• Move all files from current MySQL DB location to a new location
• Find MySQL config file (my.cnf or mysqld.cnf) and change “datadir” parameter to point to a new path/location
• Edit the /etc/apparmor.d/usr.sbin.mysqld file. Remove /var/lib/mysql and add the new data dir location. In my case it is

   # Allow data dir access
   /data/mysql/ r,
   /data/mysql/** rwk,
  

• Restart apparmor service systemctl restart apparmor or service apparmor restart
• Start MySQL service

Note: Don’t forget to adjust permissions (mysql:mysql)

Conclusion

MySQL server is finally ready. The tutorial is very basic/rough  and from experience, nothing is straightforward in Linux. Each user or environment is different, and because of that problems may occur (unexpected errors, missing libraries or repositories). Just be persistent!

Another thing we could add is phpMyAdmin, for easy access/control.