Upgrade from MariaDB 10.1 to 10.2 on Ubuntu / Debian


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.


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


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


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


Upgrading to PHP7.1 and away from DotDeb


Having used dotdeb.org packages for years (a huge thanks is appropriate here), package maintainer Guillaume Plessis recently announced he would stop providing PHP packages, instead suggesting to switch to the PPA packages provided by Ondrey Sury.

As I was running DotDeb PHP7.0 packages, I first looked at switching to the Sury packages on Debian 8.

apt-get install apt-transport-https lsb-release ca-certificates
wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg
echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/php.list
apt-get update

This adds the repository to your Debian installation, allowing the use of apt to install the packages it provides.
At the time of writing, this includes separately installable packages for php7.0 and php7.1.

Next, simply perform an update with apt

sudo apt update

Apt overwrites the dotdeb packages with those from the Sury repository rapidly, and perfectly. As I chose to keep my existing php.ini file, things worked out of the box without other changes.

Next up, you can safely remove dotdeb repository (at least if you’re not using it for anything else anymore). Just comment out or remove the lines from /etc/apt/sources.list that point to dotdeb.

Install PHP 7.1

Now that the repository is added and tested via PHP7.0, installing PHP7.1 is a piece of cake:

sudo apt install php7.1 php7.1-fpm php7.1-mysql php7.1-mbstring php7.1-curl

You may need to install more packages (you can list all of them via ‘apt-cache search php7.1’), but this is basically my starter package.

After installation, PHP gets installed under /etc/php/7.1 . You can test via the command line if things are installed properly via:

# php7.1 -v
PHP 7.1.1-1+0~20170120094658.14+jessie~1.gbp69d416 (cli) (built: Jan 20 2017 11:53:59) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.1.1-1+0~20170120094658.14+jessie~1.gbp69d416, Copyright (c) 1999-2017, by Zend Technologies

While you can continue to change handlers in nginx, apache or whatever webserver you’re going to use, you could also add this handler to Plesk, if you’re running that as well.

Thanks to their useful documentation, this turns out to be easy as well.

plesk bin php_handler --add -displayname "7.1 by deb.sury.org" -path /usr/bin/php -clipath /usr/bin/php7.1 -phpini /etc/php/7.1/fpm/php.ini -type fpm -service php-fpm7.1 -poold /etc/php/7.1/fpm/pool.d -id 71fpm

Note that in the above command, I’m providing a custom Id for the handler, avoiding Plesk to generate a long hash, making it difficult to modify your handler via its Id if you want to later on.

I was left to simply change the PHP handler for my site via the Plesk control panel, and things were working as smooth as ever!

Monitoring mysql / mariadb with telegraf


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:

  ## 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:

   servers = ["debian-sys-maint:xxxxxxxxxx@tcp("]

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.


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.

Monitoring with Collectd, InfluxDB and Grafana


For a long while, I’ve used munin along with a few custom made munin plugins to monitor a server of mine. Recently however, there’s been a few interesting new offerings on the market that aim to make monitoring more flexible, so I decided to give it a go.

I quickly ended up with collectd as the monitoring agent, given it’s extremely lightweight, around for more than 10 years. It also supports sending its collected data in numerous different formats, making my choice on the server side more or less independent.

For starters, I tried the ELK stack. However, given that I’m particularly conscious of resource usage, I had to eliminate this early on. In my setup, with which I’m using a sole and small monitoring server, it was impossible to get ElasticSearch to run for more than a couple of days before running out of heap space. The week that I’ve been running and testing it though made me realise Kibana is just too complex to configure, given that all I need is something similar to my good old and simple munin.

Having done more experiments with Graphite (a bit complex to setup), and collectd with GraphZ (more or less similar output as with munin), I ended up installing InfluxDB and Grafana.

Installing influxDB

Since I’m on Debian, I’m doing the following, in line with the downloads page:

$ sudo dpkg -i influxdb_1.1.0_amd64.deb

This installs the service influxdb, with the CLI executable available at /usr/bin/influx. The configuration file can be found at /etc/influxdb/influxdb.conf.

The latter must be modified first, to enable gather metrics coming from collectd agents.

    enabled = true
    bind-address = ""
    database = "collectd"
    typesdb = "/usr/share/collectd/"

Note that you need to change the template value of the enabled property to true (apart from uncommenting). I have also had to modify the typesdb property to a directory, telling InfluxDB to load all files as types.db definitions. This is necessary if you have custom types (which you'd define in collecd.conf with separate typedb properties).

Next, we start the service, enabling influxdb to start gathering metrics on collectd’s default port:

sudo service influxdb start

You can subsequently check if metrics are succesfully retrieved by InfluxDB as follows:

$ sudo influx -precision rfc3339
Visit https://enterprise.influxdata.com to register for updates, InfluxDB server management, and monitoring.
Connected to http://localhost:8086 version 1.1.0 InfluxDB shell version: 1.1.0 
> use collectd 
Using database collectd 
> show measurements 
name: measurements 

(Note how I added the rfc3339 precision, to make sure that timestamps in the measurements are readable)

If you have no measurements listed, you’ll have to work out where things are going wrong. Chances are you need to open up the firewall to accept incoming traffic on the collectd port.

So far so good. Metrics are being gathered, but we obviously need to seem the as well. This is where the Grafana dashboard comes in. I’ll use this, rather than the recently open-sourced Chronograph, as it is still more feature rich. There’s still room to switch over later on while retaining InfluxDB as the data container.

Installing Grafana

Just like with Influx, the documentation is clear. I decided to use the APT repository. For Debian, I have to add the grafana repository to my /etc/apt/sources.list , but on Ubuntu 16.04, Grafana can be installed from the available packages:

$ sudo apt-get update
$ sudo apt-get install grafana

Start up the service:

$ sudo service grafana-server start

By default, this starts the grafana web interface on port 3000, so you could go and check grafana at http://localhost:3000.

I found this a bit impractical, and therefore decided to proxy it through nginx. For this, I needed to adapt both the configuration of grafana, as well as of nginx.

I added the following to my /etc/nginx/sites-enabled/nginx.conf file:

        location /grafana/ {
                proxy_pass    ;

And in /etc/grafana/grafana.conf, modified the root_url property:

root_url = %(protocol)s://%(domain)s:%(http_port)s/grafana

Restart both to apply the changes, after which I could navigate to localhost/grafana to see the grafana login screen.

$ sudo service grafana-server start
$ sudo service nginx restart

The default login for grafana is admin / admin, but you can change this in the grafana configuration file. Once logged in, you’ll need to add your data source. For me, the settings look as follows:

InfluxDB datasource configuration

InfluxDB datasource configuration

This brings us to a point where we can start setting up some graphs in Grafana. I’ll follow this up with some example graphs in a new post.