X-Files Wiki

Please note: This guide shows how to install MySQL and NOT MariaDB just to be clear.

apt update

apt install -y mysql-server mysql-client
systemctl start mysql
systemctl enable mysql
mysql_secure_installation

Apply the following when running mysql_secure_installation

"Would you like to setup validate password component?" press enter
"New password:"                                        enter new root password
"Re-enter new password:"                               enter new root password again
"Remove anonymous users?"                              press y
"Disallow root login remotely?"                        press y
"Remove test database and access to it?"               press y
"Reload privilege tables now?"                 press y

Adjust User Authentication

In Ubuntu MySQL the user root is set to authenticate using the auth_socket plugin by default rather than with a password. This can cause some interesting complications when trying things like PhpMyAdmin. Below we change the authentication method. The best way is to create a privileged user and not use the root user at all. Unfortunately this is not always possible.

First let's se the current schema.

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

You will get a result similar then one below.

+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | $A$005$M9U\_UIiw12DIuB/xKgH9/9q1M.zSvkO65DvfdO0/CiJ9kj33 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             |                                                                        | auth_socket           | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)

As you can see we need to change the authentication method for the root account. We can do it like this.

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

Let's check if thing look correct. We should see the root account now using “mysql_native_password”

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

+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user             | authentication_string                                                  | plugin                | host      |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | $A$005$M9U\_UIiw12DIuB/xKgH9/9q1M.zSvkO65DvfdO0/CiJ9kj33 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root             | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19                              | mysql_native_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)