Installing MariaDB on Ubuntu 20.04

At the OER Foundation, we have the convention of using Docker to deploy most components of the web services we offer. One notable exception to that is the way we deploy our most widely used database. Rather than deploying a separate MySQL/MariaDB container for every service on a given host, we instead deploy a single instance running on the host. We do this for a couple reasons:

1. MySQL/MariaDB are mature and are generally backwards compatible, and (importantly),
2. it's easier to back up a single instance using automysqlbackup or our hourly backup script for some hosts.

So why am I constantly talking about 'MySQL/MariaDB'? MariaDB is effectively a drop-in alternative to MySQL and we prefer it because it's not controlled by Oracle and has a more active developer community. On Ubuntu, MariaDB pretends to be MySQL for compatibility purposes, so don't be weirded out by the interchangeable names below.

You'll replace the similarly [named] variables in the configuration files and command lines below. These are the values you need to find or create.

• MariaDB/MySQL database details
  • [database root password] - the administrative user (root) password for this server - use a random password
  • [your database password] - if you, optionally, want to set up an admin user for yourself on this server. Paired with [your username] on the server.

Install the latest versions of the server and client like this.

sudo apt-get install mariadb-server mariadb-client

You need to set a root (admin) user password - you might want to create a /root/.my.cnf file containing the following (replacing [mysql root password]) to let you access MariaDB without a password from the commandline:

sudo nano /root/.my.cnf

and put the following info into it

[client]
user=root
password=[database root password]

You should now be able to type mysql at the command prompt (note, the name mysql is used for backward compatibility with many implementations where MariaDB is being used to replace MySQL).

Optional MySQL non-root user

If you're happy to use your root user to access MySQL, e.g. sudo mysql (which uses 'sudo' to access it via the root user), then you can safely ignore the rest of this section.

If you're accessing the server via a non-root user (which is a good idea, and is the reason we use sudo in this howto), you might want to create a similar ~/.my.cnf file in your directory , with your username in place of root, and a different password. That will allow you to work with the MariaDB client without needing to enter the root credentials each time.

To make it work, you'll need to run the following as the MySQL admin user - this should be the default on this new install - remember to replace your [tokens]!). This creates two users with the same credentials that will allow you to log in either from the same server (i.e. 'localhost') or from any of your Docker containers (often useful for debugging!), namely the wildcard '%'. Remember: if you change the user's details, you'll have to do it for both the localhost and '%' users.

CREATE USER "[your username]"@"localhost" IDENTIFIED BY "[your database password]";
CREATE USER "[your username]"@"%" IDENTIFIED BY "[your database password]";
GRANT ALL ON *.* to "[your username]"@"localhost" WITH GRANT OPTION;
GRANT ALL ON *.* to "[your username]"@"%" WITH GRANT OPTION;
FLUSH PRIVILEGES;

Don't be alarmed if MySQL tells you "0 rows affected" when you create a user - unless you see a specific 'error', it's still creating them.

End optional MySQL non-root user section

Tweak the configuration so that it's listening on the right internal network device.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

and copy the bind-address line and adjust so it looks like this - we want MariaDB to be listening on all interfaces, not just localhost (127.0.0.1)...

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0

Then restart MariaDB:

sudo service mysql restart

It should now be listening on MySQL/MariaDB's default port 3306 on all interfaces, i.e. 0.0.0.0. For safety's sake, external access to the MariaDB server is blocked by your UFW firewall.

Now you should be able to log into the MariaDB client on the host (if you've created a .my.cnf file in your home directory as describe above, you won't need to enter your username and password):

mysql -u root -p

You should now be able to create new databases as in our other tutorials... And in case you're stuck, to exit the SQL client, just type \q and ENTER.

Regular automatic database backups

Finally, it's a good idea (but optional - if you're in a hurry, you can do this later) to make sure that your server is maintaining backups of your database - in this case, we'll use the automysqlbackup script to automatically maintain a set of dated daily database backups. It's easy to install, and the database backups will be in /var/lib/automysqlbackup in dated folders and files. If you haven't set up Postfix in the previous step, just beware you will be asked to set it up when installing automysqlbackup.

sudo apt-get install automysqlbackup

That's all there is to it. It should run automatically every night and store a set of historical SQL snapshots that may well save your bacon sometime down the track!