Ubuntu Server 18.04 – Installing MariaDB

How to install Ubuntu Server 19.10

Now we’ve come to the fun part, installing MariaDB. To get the ball rolling, we’ll install the mariadb-server package:

sudo apt install mariadb-server 

If your organization prefers to stick with MySQL, the package to install is
mysql-server instead:

sudo apt install mysql-server 
Although it might be tempting to try out both MySQL and MariaDB to compare and contrast their differences, I don’t recommend switching from MariaDB to MySQL (or vice versa) on the same server. I’ve seen some very strange configuration issues occur on servers that had one installed and then were switched to the other (even after wiping the configuration). For the most part, it’s best to pick one solution per server and stick with it. As a general rule, MySQL should only be used if you have legacy databases to support. For brand new installations, go with MariaDB.

Going forward, I’ll assume that you’ve installed MariaDB, though the instructions here shouldn’t differ much between them. For the name of the service, you’ll want to substitute mysql for mariadb whenever I mention it if you are using MySQL instead. Previous versions of Ubuntu Server used mysql for the service name, even when installing MariaDB (Ubuntu 16.04 is an example of this). This is just something to keep in mind if you’re supporting a legacy edition of Ubuntu.

After you install the mariadb-server package, check to make sure the service started and is enabled. By default, it should already be running:

systemctl status mariadb 

Next, we’ll want to add some security to our MariaDB installation (even though we’re using MariaDB, the following command still references mysql in the name):

sudo mysql_secure_installation 

At this point, we haven’t set a root password yet, so go ahead and just press Enter when the script asks for it. This script will ask you additional questions. Next, it will ask you if you want to set a root password. The root user for MariaDB is not the same as the root user on your system, and you definitely should create a password for it. So when this comes up, press y to tell it you want to create a root password, then enter that password twice.

After setting the root password, the script will ask you whether you’d like to remove anonymous users, and also disallow remote access to the database server. You should answer yes to both. The latter is especially important, as there’s almost never a situation in which allowing external access to MySQL/MariaDB is a good idea. Even if you’re hosting a website for external users, those users only need access to the website, not the database server. The website itself will interface with the database locally as needed, an external connection wouldn’t be necessary. Basically, just answer yes to everything the script asks you.

The entire process after executing mysql_secure_installation looks like the following:

Enter current password for root (enter for none): 
Set root password? [Y/n] 
Remove anonymous users? [Y/n] 
Disallow root login remotely? [Y/n] 
Remove test database and access to it? [Y/n] 
Reload privilege tables now? [Y/n] 

At this point, we officially have a fully functional database server. The previous command allowed us to apply some basic security, and our database server is now available to us. To connect to it and manage it, we’ll use the mariadb command to access the MariaDB shell, where we’ll enter commands to manage our database(s). There are actually two methods to connect, to this shell. The first method is by simply using the mariadb command with sudo:

sudo mariadb 

This particular command works because if you use the mariadb command as root (we used sudo in this example) the password is bypassed. In fact, we didn’t even enter the username either, root is assumed if you are attempting to access MariaDB with sudo. This is by far the simplest way to connect. However, some of you may be accustomed to a different method of authentication if you’ve used other Linux distributions: entering the username and password. In that case, the command will look like this (it won’t work by default though):

mariadb -u root -p

When that command works correctly, it will ask you for your root password and then let you into the shell. However, by default the root user is set up to use a completely different mode of authentication altogether (UNIX sockets) and this will fail with the following error (even if you enter the correct password):

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

You have two options for dealing with this. First, you can simply use sudo mariadb to access the MariaDB shell and not use this method. Doing so is perfectly valid, and there are no downsides as it gives you the same level of access. If you prefer to access the root account via the traditional means (by providing the username and password for the root user) you’ll need to change the root user to use the native password authentication method instead. To do so, first access the MariaDB shell and then enter these two commands:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE USER='root';
Now, you'll be able to access the MariaDB shell as root with native authentication:
mysql -u root -p
It’s recommended to create a different user in order to manage your MariaDB installation, as logging in to root is not recommended in most cases. We’ll be creating additional users later on in this chapter, but for now, the root account is the only one we have available at the moment. It’s common practice to use the root account to do the initial setup, and then create a different user for administrative purposes going forward. However, the root account is still often used for server maintenance, so use your best judgment.

So now that we have access to the MariaDB shell, what can we do with it? The commands we’ll execute on this shell allow us to do things such as create and delete databases and users, add tables, and so on. The mariadb command comes from the mariadb-client package which was installed as a dependency when we installed mariadb-server. Entering the mariadb command by itself with no options connects us to the database server on our local machine. This utility also lets us connect to external database servers to manage them remotely, which we’ll discuss later.

The MariaDB shell prompt will look like this:

MariaDB [(none)]> 

We’ll get into MariaDB commands and user management later. For now, you can exit the shell. To exit, you can type exit and press Enter or press Ctrl + D on your keyboard.

Now, our MariaDB server is ready to go. While you can now move on to the next section, you might want to consider setting up another MariaDB server by following these steps on another machine. If you have room for another virtual machine, it might be a good idea for you to get this out of the way now, since we’ll be setting up a slave database server later.

Comments are closed.