ZFS Backup using ZFS on database

[fred974]

Hi Guys,

I run a backup script to run daily snapshot for differential backup and send a copy to external backup server..

It work well so far and I used it to retore lost data in the past..

I only just realise that I am not sure if it actually work for my MariaDB database?
As the MariaDB jail is not stopped at the time of the snapshot does the snapshot that is taken worth anything for that jail?

Should I be running mysqldump on a cron so that dump get saved in the snapshot?

Thank you in advance
Fred

 

[ekingston]

I only just realise that I am not sure if it actually work for my MariaDB database?
As the MariaDB jail is not stopped at the time of the snapshot does the snapshot that is taken worth anything for that jail?

Your database data files may be in an unstable state at the time you take the backup. As a result data may be unrecoverable. If you have journaling enabled, the database server may be able to recover with minimal data loss. This would be similar to the issues experienced as the result of an unexpected system crash or power failure (without a UPS).

Should I be running mysqldump on a cron so that dump get saved in the snapshot?

In my opinion, yes, you should run mysqldump.

You could also shutdown the database server, take a zfs snapshot, then restart the database server. You would then back-up the snapshot. This has the advantage of leaving your database data files in a known stable state for backing them up while minimizing down time (as the shutdown/snapshot/start is pretty quick). Once you have backed-up the snapshot you can remove it.

You could even start a second instance of MariaDB that points to the snapshot to generate the mysqldump output. This would let you offload the processing of the dump file to a different server (if you copy the snapshot to the other server first). This process might provide better performance than backing up directly from the database server.

Your specific needs will determine the route you take.

Note: The mysqldump output will most probably be a lot smaller and more compressible than taking a backup of the actual database data files. The trade-off it is may take longer to restore the database.

 

[Eric A. Borisch]

sysutils/zfstools has a zfs-snapshot-mysql flavor... this (with proper configuration) with flush tables and lock the database during the (brief) time it takes to create the snapshot.

Here's the relevant mysql commands in the script:

FLUSH LOGS;
FLUSH TABLES WITH READ LOCK;
SYSTEM #{cmd};
UNLOCK TABLES;


where the cmd is the zfs snapshot.

From https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-with-read-lock :

[FLUSH TABLES WITH READ LOCK] is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time.​

As long as you replicate these snapshots, you’ll have known-good-state backups.

Edit: If we're talking mission critical data here, be sure to dig into this beyond the comments above for your particular configuration and use case; I use it for backing up my all-important mythtv and zabbix databases.