Installing MariaDB on Ubuntu 20.04

Blog tags

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.

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA
Are you the real deal?
8 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.