Roughly one year ago I started working on a set of backup scripts for NetEye with the following requirements:
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:
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
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_drbd
A 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.pid
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:
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.