Low Orbit Flux Logo 2 F

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.

We’re basing these instructions on Ubuntu 20.04. Originally we had built this document around Ubuntu 16.04.

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 -y mysql-server

OR alternatively pull down and install MariaDB Server: 


sudo apt-get install -y mariadb-server

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

You can check the version of the package like this:


mysqld -V   # server version
mysql -V    # client version

Run the secure install script:


sudo mysql_secure_installation

Options for MySQL:

Options for Maria:

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

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

NOTE - You may end up being prompted for two passwords, one after the other. First you will need your password for sudo and then you will need the DB password. Pay attention to which you are entering. If you’ve recently entred command using sudo you may not need to type your password for this again.

Show the version:


sudo mysqladmin -p -u root version

Test out the client:


sudo mysql -uroot -p

Authentication Issue

Both of the above two commands need to either be run with sudo or as root. If you don’t run them with sudo or as root they will give you an authentication error even with the correct password. 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.

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;

update user set authentication_string = \
CONCAT('*', UPPER(SHA1(UNHEX(SHA1('SecretPassword5!'))))) Where user = 'root'\
and host = 'localhost';

update user set plugin='mysql_native_password' where User='root';

flush privileges;

Once that is done you should be able to login using the password from any OS user account without using sudo.


mysql -uroot -p

NOTE - The syntax to generate a password hash inside a SQL command has changed in newer MySQL/MariaDB versions.

Old syntax:


PASSWORD('SecretPassword5!')

New syntax:


CONCAT('*', UPPER(SHA1(UNHEX(SHA1('SecretPassword5!')))))

Check Users and Passwords

It is a good idea to manually check the users and passwords that have been setup. You will see differences between MySQL and MariaDB here.

Note that the ‘Password’ field has been replaced by the ‘authentication_string’ field in recent versions of MySQL and MariaDB ( for a while now ).

Login:


mysql -u root -p

Then check:


use 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.


delete from user where authentication_string="";         # delete extra users
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).

Login:


mysql -u root -p

Update the password and flush privileges:


use mysql
alter user 'root'@'localhost' IDENTIFIED BY 'r00t';
FLUSH PRIVILEGES;

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 CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘SecretPassword5!’))))) where SecretPassword5! is the password.

Login:


mysql -u root -p

Check users / passwords:


use mysql;
select Host,User,authentication_string from user;

Use either of these commands to set the password for any instances where the user ‘root’ is not set.


update user set authentication_string="*E84DCE3A4AECBB932DE021FE9ABFF4C4529B190C" where User="root" and authentication_string="";   
update user set authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('r00t'))))) where User="root" and authentication_string="";

Flush the privileges so that the permissions will be effective.


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 | debian-sys-maint | $A$005$qtHf.%K
DCCj;m3dYcCFqTH0ftcurobgUN/bKyVNs6.SkWNtmg1wdBBq2 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | *E84DCE3A4AECBB932DE021FE9ABFF4C4529B190C                              |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Example from MariaDB:


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

MariaDB [mysql]>

Video Guide