MySQL/MariaDB data directory backup

Question

I am using a solution of backblaze B2 / duplicity / duply to create backups of my databases. I upload full backups and incremental backups to B2 using duplicity via duply. The incrementals are done similar to rsync.

My server is 100GB and my databases are currently 70GB, but I don't suspect them to grow much. I dont have a master/slave setup. I want a backup solution that allows me to backup the 70GB of databases but not require very much space during the backup process.

I decided that I could just backup the whole /var/lib/mysql directory instead of using mysqldump or other backup methods which requires a full backup persist on disk.

I read that in order to do this method I would have to completely turn off the mysql server, do the backup, and turn it back on. I am OK with that because duplicity does incremental backups which should only take a few minutes.

I have ran SET GLOBAL innodb_max_dirty_pages_pct = 0; in order to make shutdown/starup as fast as can be and verified via show global status like '%dirty%'; that Innodb_buffer_pool_bytes_dirty hovers around 0.

I also understand the caveat for InnoDB tables is that they require the exact same mysql version in order to properly restore. Is that still the case?

Is there anything I am missing and will I have a near 100% confidence that if I use the exact mysql version (mariadb in my case) that at least one of my hundreds of incremental backups will restore.

Answer 1

How does duplicity do the "incremental" backups? By doing something like rsync? Or using MySQL's binlogs?

One of the least invasive backups involves LVM.

• About 1 minute of downtime, regardless of dataset size.
• Only a few percent of disk space needed, regardless of dataset size. (10% might be more than generous.)
• And, yet, you always get a full backup.

How, you should ask? Obviously some kind of sleight of hand. Hint: a COW is involved.

But, alas, you have to plan ahead and set up a "Logical Volume" for this to work with. And the OS has to provide such. Here's how it works:

1. Set up the LV (one-time task)
2. when you want to take a dump, do the following steps:
3. stop mysqld
4. take a "snapshot" of the entire disk (perhaps 1 minute)
5. start mysqld
6. now you get the slow tedious task of copying the entire snapshot to somewhere else.

More info on the LV and the snapshot in the LV:

• The snapshot is a "logical" copy of all the disk blocks.
• It feels like a separate drive.
• It takes very little extra space -- to start with
• When either drive writes anything, the COW mooves into help. COW = Copy On Write. That is, the block that is being modified now needs to be come two blocks (and takes more real disk space). One drive keeps the old copy; one gets the new.
• During your copy to elsewhere, the backup LV drive still sees only "old" blocks.
• During your copy, mysqld continues to modify blocks, oblivious to the COW grazing in the background.
• Well, there will be a lot of I/O due to the copy.

Other notes...

• Yes, it would be better to have exactly the same version.
• You could install a newer version with the backup, but be sure to do mysql_upgrade. You could even test an upgrade right there in the snapshot -- without copying to elsewhere. (Note: the LV would need to be big enough for whatever changes in it.)
• There is no concept of "incremental" (unless your copy is something like rsync).
• Because I suggested actually stopping mysqld, there is no hard need for playing with %dirty%, etc. After all, InnoDB can recover from a power failure; this is more graceful than that.

Answer 2

The only way to have 100% confidence that the backups are working is to test them regularly.

How busy is this server? Doing the backups from the secondary server, is generally a good way to keep the primary performant. Maybe you don't have that concern though.