2

I'm looking for some insight whether or not rsyncing a copy of the data folder from MariaDB well running in docker will provide a usable backup. I'm deploying several containers with mapped folders in a production environment using docker.

I'm thinking of using rsnapshot for nightly backups as it uses hardlinks incrementally and I can specify the number of weekly / daily / monthly copies to keep.For the code and actual files I suspect this will work wonderfully.

For MariaDB I could run mysqldump every night but this would essentially use a new copy of the database each time instead of an incremental one. If I could rsync the data folder and be 100% sure the backup would be fully intact it would be advantageous I presume. Is there any chance this backup method would fail if data was written during the rsync? Would all the files inside of MariaDB change with daily usage (it wouldn't be advantageous if so)?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
John Everden
  • 176
  • 8

1 Answers1

1

This is probably a frequent question, but I can't find a really exact match right now.

The answer is NO — you can't use filesystem-level copy tools to back up a MySQL database unless the mysqld process is stopped. In a Docker environment, I would expect the container to stop if the mysqld process stops.

Even if there are no queries running, the InnoDB engine is probably doing writes in the background to flush pages from memory into the tablespace, clean up rolled-back transactions, or finish some deferred index merges.

If you try to use rsync or cp or any other filesystem-level tools to copy InnoDB files, you will only get corrupted files that can't be restored.

Some people use LVM snapshots to get an atomic snapshot of the whole filesystem as of a single instant, and this can be used to get quick backups.

Another useful tool is Percona XtraBackup, which copies the InnoDB tablespace files while it is also copying the InnoDB transaction log continually. Only with both of these in sync can the backup be restored. Read the documentation here: https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

At my current job, we use Percona XtraBackup to make nightly backups for thousands of MySQL instances. We run Percona Server (not MariaDB) in Docker pods, and Percona XtraBackup runs as another container in the pod. It works very well, and it's free, open-source software.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I agree, should always use appropriate tools to backup your data. pg_dump for [PostgreSQL](https://stackoverflow.com/a/57969034/1423507). [Recovery Manager](https://www.oracle.com/database/technologies/high-availability/rman.html) for Oracle Database. [sqlcmd](https://www.oracle.com/database/technologies/high-availability/rman.html) for MS SQL Server. For MySQL Server, mysqldump —single-transaction creates consistent backups while the disks are hot. – masseyb Oct 01 '19 at 07:19
  • But the OP said they didn't want to use mysqldump because restoring it technically creates a different database containing the same data. I gave two options for consistent _physical_ backups. – Bill Karwin Oct 01 '19 at 14:07