Low Orbit Flux Logo 2 D

MySQL Install and Setup

We're going to install, setup, and configure MySQL on Ubuntu Linux. We're going to cover both MariaDB and MySQL both of which are in the Ubuntu 16.04 repositories by default.

MariaDB is a drop in replacement for MySQL that is created and maintained by the original developers of MySQL. Most of the commands and script names are the same. Some of the branding and bits and pieces are different. They are compatible and what works in one should generally work in the other. We're going to try to show how things are done in both and note where things are different. We view them as being interchangable but prefer MariaDB.

MySQL / MariaDB Install

First, make sure your list of what is available from the repositories is up to date.

sudo apt-get update

Next, pull down and install MySQL Server:

sudo apt-get install mysql-server

OR alternatively pull down and install MariaDB Server:

sudo apt-get install mariadb-server

This will give you either one of these depending on which you choose:

mysql-server 5.7.21
mariadb-server 10.0.34

The MySQL package prompts for a root password. The MariaDB package does not.

Run the secure install script:

sudo mysql_secure_installation

DB Initialization

Normally you would need to initialize the database. You don’t need to do that here because the current Ubuntu/Debian packages initialize the DB automatically. Before version 5.7.6 you would have needed to run ‘mysql_install_db’. For 5.7.6 and above you would have needed to run ‘mysqld –initialize’.

Note - Both MariaDB and MySQL packages create a new service account user called ‘mysql’.

Post Install and Testing

Check that the system is running:

sudo systemctl status mysql.service

This command works for both MariaDB and MySQL. The services are managed by systemd but are setup a tiny bit differently under the hood. The command is exactly the same for both databases but will show slightly different output for each.

You can stop and start the service with the following:

sudo systemctl stop mysql
sudo systemctl start mysql

The CLI Client

Show the version:

mysqladmin -p -u root version

Test out the client:

mysql -uroot -p

Issue - Authentication Error:

Both of the previous two commands should work on MySQL but MariaDB will give you an authentication error even with the correct password unless you run the comand using the OS root user. This is due to the following feature being enabled which connects using the identity of the currently logged in OS user when connecting over a unix socket: Authentication Plugin Unix Socket.

To login with the client for our MariaDB instance we can run the client as root or with sudo. So long as we have root privileges the password will not be needed at this point.

Fixed login:

sudo mysql -uroot

If you like this feature, great. If not, then you can disable it with the following once you are logged into the database. Make sure that the password is actually set so that you don't lock yourself out. It wasn't set when we first tried this.

mysql> use mysql;
mysql> update user set authentication_string = \
    -> PASSWORD('SecretPassword5!') Where user = 'root'\
    -> and host = 'localhost';
mysql> update user set plugin='mysql_native_password' where User='root';
mysql> flush privileges;

Once that is done you should be able to login using the password from any OS user account just like we did with MySQL.

Check Users and Passwords

It is a good idea to manually check the users and passwords that have been setup. There are two select statements in the example below. The first uses the 'Password' field and the second uses the 'authentication_string' field which has replaced the Password field in some versions. The MySQL instance we setup uses the new field while the MariaDB instance we setup uses the old 'Password' field.

mysql -u root -p
mysql> use mysql;
mysql> select Host,User,Password from user;             
mysql> select Host,User,authentication_string from user;

Once you know you have login access setup with a working password, you can go ahead and remove any extra users with blank passwords. You shouldn't really need to do this if you ran the secure install script but it is good to be aware of. You could also remove users for remote hosts if you are only logging in locally.

mysql -u root -p
mysql> delete from user where Password="";         # delete extra users
mysql> flush privileges;

Remember, if the password field doesn't exist it may have been renamed to authentication_string. This is the case for some versions.

Update Password

You can update the password using the mysqladmin tool like this. I personally don’t like doing this without making sure that it is consistent in the actuall DB table.

mysqladmin -u root -p password 'NEWPASSWORD'

You can also use the following to update the password from within the database directly. You will just want to be careful to make sure that you change this for all enteries (for each host in the table).

mysql -u root -p
mysql> use mysql
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('r00t');

You can also, set the password by writing directly to the user table. I personally like this method the most. You can verify each user for each host is set. You can just swap xxxxxxx for the password hash if you are copying from an existing entry. Otherwise, if you need to pass the actual password, you will want to specify it with something like PASSWORD(‘xxxxx’) where xxxxx is the password. Notice that the section of code below has four update lines. You will only want to use one of these lines.

mysql -u root -p
mysql> use mysql;
mysql> select Host,User,Password from user;                                 
mysql> select Host,User,authentication_string from user;                 
mysql> update user set Password="xxxxxxx" where User="root" and Password="";   
mysql> update user set authentication_string="xxxxxxx" where User="root" and Password="";   

mysql> update user set Password=PASSWORD('xxxxxxx') where User="root" and Password="";   
mysql> update user set authentication_string=PASSWORD('xxxxxxx') where User="root" and Password="";   
mysql> flush privileges;

Example Output of User Table

These look kind of ugly with text wrapping but it should give you an idea of what to expect.

Example from MySQL:

mysql> select Host,User,authentication_string from user;
| Host      | User             | authentication_string                     |
| localhost | root             | *E84DCE3A4AECBB932DE021FE9ABFF4C4529B190C |
| localhost | debian-sys-maint | *9886189D40345E99A2216B8205842287ED79F0E4 |
4 rows in set (0.00 sec)


Example from MariaDB:

MariaDB [mysql]> select Host,User,Password from user;
| Host      | User | Password                                  |
| localhost | root | *E84DCE3A4AECBB932DE021FE9ABFF4C4529B190C |
1 row in set (0.00 sec)

MariaDB [mysql]>