MySQL/MariaDB data directory backup

Posted on

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 :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *