MySQL/MariaDB data directory backup

backupinnodbmariadbMySQL

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.

Best 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.
Related Question