Install MariaDB 10.1 on Ubuntu16.04

I have Ubuntu16.04 servers running and I want to run database engines like MariaDB on it. Normally I would just type sudo apt install mariadb-server and the database system would be up and running in under two minutes. Ubuntu16.04 will install version 10.0 by default, but I want to install a different version. At the very least 10.1. Why 10.1 you might ask? Well because 10.1 was the first release that had Galera included by default in the package. Galera is a gift from the gods for all system administrators, developers, backup operators and other people who work with databases a lot. With three nodes, the minimum, it will offer you fast and reliable database synchronization and redundancy. Galera is a solid foundation for an easy to build load balancing environment. But first of we will install the MariaDB 10.1 instance on a clean Ubuntu16.04 server. If you want to be absolutely certain that the instructions below will work flawlessly for you, I would advise to use a clean fresh installed server also. Please remember that upgrading (or downgrading) from a different release version is NOT a good idea. When I was learning this the hard way I did a lot of reading on the subject and I can safely say that you should not follow these instructions on a system that runs, or used to run a MariaDB or MySQL instance. Even if you have removed it first apt remove you are still at risk of running into problems. When I was having problems with this I tried many things that included killing the process: kill -9 $(pgrep mysql)  and then purging the removed packages apt purge and also apt autoremove and I even had to delete some left-over files manually. Don’t even try to install it on a system that ran a higher version > 10.1 because that will be guaranteed imminent failure. You’ve been warned! Let’s get started and install some required software and updates.

sudo apt update && sudo apt upgrade
sudo apt-get install nano ssh openssh-server software-properties-common rsync

To see the rest of this article please click on the read more link.

We need to set a static IP address if we want the server to become part of the Galera cluster later on.

This can be accomplished by setting a static IP using the DHCP server options in your modem or router. Or you can configure a static IP on the server itself by typing this command:

sudo nano /etc/network/interfaces

Don’t just blindly copy the lines from the example shown in the image. This setup works for me, but since your network is probably different you will need to edit these lines so they fit your network environment. Use your brains. If you have no idea what an IP address is or how to properly set it up for your local network, you might as well stop reading now. Why are you even reading this?

I don’t like having to enter my password after entering a sudo command. I will enable the root account for the time being to make life easier. sudo su and then type passwd root and remember what password you used. It’s best to disable the account again as soon as you are done setting up the server. But for now .we want to allow the root account to log in from a remote computer.

Open the OpenSSH-Server configuration file by entering this command:

sudo nano /etc/ssh/sshd_config

Search for a line that starts with: PermitRootLogin. Remove the prohibit-password and replace it with yes instead. If the line starts with a # comment then remove the # symbol. Restart the SSH service sudo service ssh restart. You can now finish setting up your server from your Windows desktop through SSH using Putty.

The best and most reliable way to find the correct repositories is to use the tool on the website of the good people from MariaDB HQ. Start by choosing your distro and the correct release version. Make a choice on what MariaDB release you want to have, and pick a mirror that is nearest to you. Click this link that will open in a new tab: https://downloads.mariadb.org/mariadb/repositories

The lines that appear can be copy/pasted from your browser window directly in to Putty. After doing a sudo apt update we’re ready to install MariaDB 10.1. In my case I needed to enter the lines below.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.i3d.net/pub/mariadb/repo/10.1/ubuntu xenial main'

sudo apt update
sudo apt install mariadb-server

When it’s finished installing enter the following command and use the password that you choose in the previous step. You should see the same thing as shown below:

mysql -u root -p

Server version: 10.1.33-MariaDB-1~xenial mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

Enter each of the following lines at the MariaDB [mysql]> prompt but make sure you change ‘password’ in the first line to your own password.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
use mysql;
update user set plugin='' where User='root';
FLUSH PRIVILEGES;
quit;

In the future we’re going to set up a Galera cluster and that means the nodes will have to be able to communicate with each other so we’re going to edit the MariaDB configuration file to allow this. Use a # symbol at the start of the line to comment out the part that says: bind-address = 127.0.0.1. Also we will add two extra options as shown below:

sudo nano /etc/mysql/my.cnf

Search for the part that says [mysqld] and make it look somewhat like this:

[MYSQLD]
# bind-address = 127.0.0.1
plugin-load-add = auth_socket.so
sql-mode=”NO_ENGINE_SUBSTITUTION”

Before we continue with the next step it might be a good idea to restart the database service:

sudo systemctl restart mariadb.service

The next step is to perform the secure installation. As shown in the example image make sure you answer NO when asked: Disallow root login remotely? [Y/n] N   <– Answer N for NO

mysql_secure_installation

Congratulations! You have successfully installed MariaDB 10.1 on a Ubuntu16.04 system. Your installation is now ready to be configured as a node for a Galera cluster. I will create a new post very soon on how to setup such a cluster, so make sure to come back and check that out very soon!