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

Subscribe
Share

@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-transaction or skip-lock-tables flag in mysqldump to prevent the tables being locked?


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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?