XtraBackup Production Example

jesdynf · December 30, 2020, 4:25pm

Thought you might like to see this – I included XtraBackup in the deployment packages for OpenEMR, an open-source electronic medical records platform.

https://github.com/openemr/openemr-devops/tree/master/packages/lightsail is the generalized deployment package, which relies on a docker container with XtraBackup (and some support scripts) https://github.com/openemr/openemr-devops/tree/master/docker/mysql-xtrabackup , and is summarized with AWS CloudFormation stack https://github.com/openemr/openemr-devops/tree/master/packages/express_plus .

The result is automated daily incremental hot backups, copied to cloud storage via Duplicity, with a single command-line for retrieval and application. The script https://github.com/openemr/openemr-devops/blob/master/docker/mysql-xtrabackup/xrecovery.sh may be of special interest, because although I found a script to run and rotate backups, I honestly could not find a way to simply tell XtraBackup to do the right thing if it was confronted with a set of incrementals it needed to restore.

lorraine.pocklington · December 30, 2020, 4:35pm

Thanks for highlighting this project, jesdynf, looks like you’ve been doing some interesting work.

Would you be interested in writing a post about your project for our community blog? https://www.percona.com/community-blog/ I’d be happy to have it.

jesdynf · December 30, 2020, 4:35pm

I would, thanks! But part of why I brought it up was to attract community interest and review, so that might be after I have a chance to fix any glaring mistakes I made.

lorraine.pocklington · December 30, 2020, 4:35pm

Gotcha. tbh I’d taken it that you were more ‘proud and sharing’ LOL, but now I know I’ll bring the project to the attention of the Percona XtraBackup team too!

jesdynf · December 30, 2020, 4:35pm

Oh, I totally /am/, but that doesn’t mean it shouldn’t get a look. Much of what I’m doing in there I sorta wish I hadn’t had to; there should be better management than what I cobbled together, so anything the XtraBackup team wants to lift is fine with me.

jesdynf · December 30, 2020, 4:35pm

Quick notes while I’m on lunch –

mysql-xtrabackup is just a MySQL container built from the Percona Debian packages. It includes two scripts of note, ‘xbackup.sh’ (largely another’s work) that will create a DB table on init, and then take the responsibility for creating, compressing, and rotating full and incremental backups on demand.

‘xrestore.sh’ is the paired part that’s able to read xbackup’s work (parsing log files and crawling backwards to create a chain of processed backup sets, if we’re working from incrementals) and signaling for a container restart. If the parsing were any harder I’d’ve grit my teeth and switched to Python, but I avoided that and got away with shell alone.

The wrapper scripts are mostly just cooperating with the host, moving the backups where Duplicity can find them or further arranging useful things based on context. When the host runs a backup, the database backup runs and they’ve moved to somewhere Duplicity will find them, and then they’re backed up to AWS S3 (using managed key encryption and IAM instance-role support) along with patient records and documents. When the host triggers a restore, everything is pulled back down from the cloud, the patient records are unpacked, the database is built anew, and then it gets moved into the data directory while MySQL’s down.

The whole thing is aimed at providing low-resource cloud users with a HIPAA-eligible EMR, and I didn’t want to let one out in the wild with a backup solution that was ‘remember to run your backup’ or ‘nobody can use it while it’s backing up’ – if I wanted hands-off automation without consequences, I needed a MySQL hot backup solution.