Question
Looking for backup strategy for write-intensive mysql database
Right now the setup consists of $10 droplet with an attached volume with MySQL datadir on the volume (following this guide). The reason for this is to be able to scale storage and cpu/ram separately with little downtime (using the flexible resizing for droplet). All tables are using InnoDB.
That said I’m looking for how to backup this properly. I have set up mysqldump and automysqlbackup (which uses mysqldump, if I remember correctly) before but (if I remember correctly) both of them lock the database while doing the backup which isn’t acceptable for write-intensive scenario (monitoring type of thing).
After some googling I found percona hot backup but I didn’t really understand how to automate it (looking to save backups to S3). Also I wasn’t sure if it’s what I’m looking for.
Googling “write-intensive mysql backups” unfortunately doesn’t really give anything. Looking for any ideas on how to do this so I can rely on it if/when something happens
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
Hi @xymanek
Just so everyone else might learn a little - here’s how to setup hot backup: https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-ubuntu-14-04 In case you’re using MariaDB 10.1+: https://mariadb.com/kb/en/mariadb-backup/
So you’re looking for a S3 backup tool? There are several tools out here, but have a look at these: https://github.com/bloomreach/s4cmd http://s3tools.org/s3cmd
You would then create a little script, where you would run the backup and then the S3 backup.
Out of interest, when you say write-intensive: How many queries per second? How many rows added per day? How much storage are you currently using?
@DigitalCarrot tables are InnoDB. I didn’t know about the skip table lock option, I will definitely check it out. Thanks!
Are your tables MyISAM or InnoDB?
If InnoDB can you use the
single-transactionorskip-lock-tablesflag inmysqldumpto prevent the tables being locked?