I am running a mariadb 10.1 instance with all innodb tables and a replication slave in a k8s cluster on Google Cloud's GKE. I have a scheduled mysqldump that runs every night. But for additional security, I am working on a mariabackup implementation that will run on the slave pod. Ideally this would create an incremental backup on an hourly basis. The database is not that large, but growing, around 20GB.
But as I work out the logistics of exporting the backup, whether to external object store or just a separate disk, I am wondering if GCP's own snapshot feature would not be an easier and cheaper solution than using mariabackup at all.
My understanding of mariabackup is that it makes an un-modified copy of the underlying database files. The tables are not necessarily consistent, so upon restore, you must –prepare the files to make them consistent. You can then copy them over to the live folder and restart the instance. – https://mariadb.com/kb/en/library/how-mariabackup-works/
This leaves me with base copies and separate folders for incremental copies that I supposed I could send directly to object storage. Then of course write a restore script that can pull in and prepare the base backup and each incremental backup individually up to the point I need to restore.
The other option is simply to schedule an hourly snapshot in GCP. It takes an incremental snapshot and can be restored to a disk at any given point.
Am I missing anything? GCP seems to indicate that their snapshots are close to consistent, but in a write heavy environment, they won't be perfect. Would this be likely to cause problems? Can innodb recover from small inconsistencies? And am I missing out on anything else by using snapshots instead of something like mariabackup?
Best Answer
To answer my own question, GCP (and AWS) snapshots are mostly consistent, but in a very write heavy database, you would want to lock the tables for the duration of the snapshot backup. I believe there is actually an system level option to lock the drive itself for the snapshot, but that is not necessary for my use-case.
I ended up using both methods to backup. I scheduled an incremental snapshot to happen every 2 hours. Then I also do a mysql dump every night. For the mysqldump, it is done with the --single-transaction flag which will give you a consistent copy of the database. There is a momentary lock on all tables while a binary log point is found. But this is usually very short and not noticeable.
Binary logs are turned on and also included in the mysql dump. So this allows both methods to be used to do a point in time recovery. It is definitely possible that the snapshot image could create inconsistent copies, but it seems to not be an issue and innodb is smart enough to recover from most minor inconsistency issues.
The snapshots take very little storage space because they are only storing the delta with each snap. The mysqldump files do not, but if they become a problem, there are other backup options to store only a delta between each mysql dump.