Upgrade from MariaDB 10.1 to 10.2 on Ubuntu / Debian

Standard

The recently released MariaDB 10.2 includes a lot in interesting new features, making it extremely worthwhile to attempt an upgrade sooner rather than later.

The knowledge base of MariaDB does provide useful general information of what to do, but I decided to share the commands involved to upgrade the database on Debian / Ubuntu , with the repos from MariaDB itself.

Backup

As a golden rule for sys admins, never forget to make a backup whenever you’re fiddling with database settings or versions. So, first things first.

Backup all database on your server:

mysqldump --all-databases -p > \tmp\db-backup.sql

Before you go on, make sure to check the file, and see if it actually contains from SQL statements. You could do so with the tail command.

When all is fine, continue.

Shutdown 10.1

Before shutting down, set ‘innodb_fast_shutdown‘ to 0. You can do so in the query console.

First, I checked the previous value:

MariaDB [(none)]> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+

Update the value (this method does not persist the value after restart):

MariaDB [(none)]> SET GLOBAL innodb_fast_shutdown= 0;

Checking its new value with the previous command should show it with value=0, rather than 1.

Next, shutdown your database:

$ sudo service mysql stop

Backup

Here is actually the ideal time to backup your database, so if you didn’t already, do it here!

Upgrade

MariaDB have excellent repositories and an online tool to create the commands needed to setup your apt repositories to add mariadb to your system.

You have two options here. Either you update your /etc/apt/sources.list file yourself to use the new repository for 10.2, or you remove the old entry and use MariaDB’s repository page to generate the commands to execute for you.

I’ll do the first one. To know the new location, I used the MariaDB download tool to locate the repository URL. For Ubuntu Xenial, this turned out to be:

http://mariadb.cu.be/repo/10.2/ubuntu xenial main

Hence, I just updated my sources.list , and changed the 10.1 url to 10.2.

Next, update apt ant upgrade mariaDB.

$ sudo apt update
$ sudo apt list --upgradable | grep mariadb

libmariadbclient18/unknown 10.2.7+maria~xenial amd64 [upgradable from: 10.1.24+maria-1~xenial]
mariadb-client/unknown,unknown,unknown 10.2.7+maria~xenial all [upgradable from: 10.1.24+maria-1~xenial]
mariadb-common/unknown,unknown,unknown 10.2.7+maria~xenial all [upgradable from: 10.1.24+maria-1~xenial]
mariadb-server/unknown,unknown,unknown 10.2.7+maria~xenial all [upgradable from: 10.1.24+maria-1~xenial]

$ sudo apt install mariadb-server

Now that we’ve installed the newer version, its also worth mentioning that you might want to edit your my.cnf file to make use of the new features via new configuration variables, or change some settings due to the default values having been modified between 10.1 and 10.2.

Again, more information about these at MariaDB’s upgrade page. Personally, I haven’t had to change anything, and my upgrade was painless, ie. everything worked as needed after starting up the server again via:

$ sudo service mysql start

 

Monitoring mysql / mariadb with telegraf

Standard

I’m currently monitoring most of the processes that I need info about with collectd, but it seems that the default mysql plugin does not support metrics related to innodb. I’m aware that is a python plugin that could add support for this, but I’ve decided to give telegraf a try.

Telegraf is a metrics gathering tool written in Go, hence distributed as a single executable. It was designed to be easily extendable, and has a minimal memory footprint.

Installing telegraf

Because I’m on debian, I’ve opted to add the apt reporsitory from InfluxData to my system, so that updating in the future is a breeze.

Everything is well documented on InfluxData’s help pages, but here is what I’ve gone through:

curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/os-release
test $VERSION_ID = "7" && echo "deb https://repos.influxdata.com/debian wheezy stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
test $VERSION_ID = "8" && echo "deb https://repos.influxdata.com/debian jessie stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

Next, the usual update and install

sudo apt-get update
sudo apt-get install telegraf

This immediately started the telegraf monitor with the default settings, so on to /etc/telegraf/telegraf.conf for some modifications…

The default configuration had InfluxDB output enabled, but on localhost. I modified it to have this:

[[outputs.influxdb]]
  ## The full HTTP or UDP endpoint URL for your InfluxDB instance.
  ## Multiple urls can be specified as part of the same cluster,
  ## this means that only ONE of the urls will be written to each interval.
  urls = ["http://monitor.xxxxxx.xxx:8086"] # required
  ## The target database for metrics (telegraf will create it if not exists).
  database = "telegraf"

You can optionally also configure additional security through the use of username/password or via an SSL certificate. Also don’t forget to setup your influxdb machine to only accept metrics data from your known hosts via firewall settings.

Setting up monitoring for mysql or mariadb was equally straightforward:

 [[inputs.mysql]]
   servers = ["debian-sys-maint:xxxxxxxxxx@tcp(127.0.0.1:3306)/"]

As you can see, I’m using the debiam sys-maintenance user. You can alternatively create a specific database user for this.

After restarting telegraf, metrics started flowing in on InfluxDB.

Charting data

The data gathered by the mysql plugin was easy to use, and hence I ended up with, among others, this chart panel in Grafana.

grafana-mysql-qcache

All in all, this was an immediate upside of telegraf for me, which triggered me to explore more of its plugins, and perhaps use some of them instead of their collectd counterparts.