Roughly one year ago I started working on a set of backup scripts for NetEye with the following requirements:
- Cluster support without standby
- Configuration backups
- InfluxDB backup
- MariaDB backup
In this post I’ll focus on the journey through my MariaDB backup implementation.
Backing up MariaDB is usually a simple task: you run the mysqldump command and that’s it. In NetEye, however, we have lots of writes to MariaDB and the dump instruction is write-blocking, so it’s not suitable for our use case.
Luckily, MariaDB suggests a script intended exactly for this purpose: mylvmbackup. As the name suggests, this tool relies on LVM to create a snapshot of MariaDB, greatly reducing the read-only phase. Then directories are moved to an archive and can be restored just by copying them back. Mylvmbackup worked pretty well, but it has two drawbacks:
- You have to restore the data to another NetEye installation in order to use backup out-of-the box, since configurations and paths are strictly related to NetEye
- The last update dates back to November of 2014
A couple of months ago several customers reported issues when running NetEye backup, probably as a consequence of LVM updates, and there was no chance to get a fix for mylvmbackup. So, with the help of our Service&Support team, we investigated possible alternatives and finally decided to go for our own script.
The procedure is pretty simple after all, and is an improvement of the idea behind mylvmbackup.
The script creates and mounts an LVM backup of the MariaDB LVM. Please note that the specified size (i.e. 5G) is the maximum size of differences written from the moment of the snapshot to the current instant, not the actual size of the MariaDB data folder. Once this size has been reached, the snapshot is dropped.
usr/sbin/lvcreate -L 5GB --snapshot --name lvmariadb_snap /dev/vg00/lvmariadb_drbdA new instance of MariaDB is then launched pointing to the snapshot mount-point, the original executable file and a custom pid file:
/usr/bin/nohup /usr/bin/sudo -u mysql /opt/rh/rh-mariadb103/root/usr/libexec/mysqld --no-defaults --datadir=/mnt/lvmariadb_snap/shared/mysql/data --socket=/tmp/mysql_backup/mysql.sock --port=$PORT --pid-file=/tmp/mysql_snap.pidThen mysqldump is executed on this instance since, at this point, we don’t care about the write lock: nobody will be writing to this instance.
Finally, MariaDB is killed (using the value stored inside the pid file), the snapshot is dropped, and temporary directories are removed.
In the case of a cluster, all the operations above are executed via ssh by our backup orchestrator.
The advantages of this approach are:
- Independence from external tools: as long as MariaDB and LVM work on NetEye, the backup script will also work
- A dump is much easier to handle than a lot of raw files, and furthermore we can improve our tools to provide separate dumps for each database
This solution is currently in the testing phase for our customers who reported issues with mylvmbackup, but our hope is to make this solution the standard for all our customers in the future.
Alessandro Valentini
Author
