On: AWS Data Pipeline

[mseebach]

It might not be appropriate for you, but a good way to handle MySQL backups is to maintain a mirror. This has the added benefit of being available as a fail-over and as a secondary instance where you can run reports or test long-running queries on current data without the risk of taking prod down.

[jacques_chester → mseebach]

> It might not be appropriate for you, but a good way to handle MySQL backups is to maintain a mirror.

    DELETE * FROM business_critical_data; WHERE obsolete = true;

You were saying? :D

[iamjustlooking → jacques_chester]

You can run your daily/hourly backups on the mirror and not impact performance on your main database.

[jacques_chester → iamjustlooking]

Right. But the grandparent comment was suggestive of the possibility that he or she wanted the mirror to fulfil multiple roles, including being the backup.

[Negitivefrags → jacques_chester]

The mirror is to run the backups on, not the backup itself.

[dsl → jacques_chester]

CHANGE MASTER TO MASTER_DELAY = 3600;

Your slave will always be 5 minutes behind the master. You were saying?

[jacques_chester → dsl]

If this is your tactic, I'd think the binary log would be the winning way to rollback a bad delete.

[mseebach → jacques_chester]

Well, with a functional mirror to run manual queries against, you wouldn't be running the risk of running such a query on your prod DB anyway. But yeah, you still need a dump.

[jacques_chester → mseebach]

There's lots of disaster reports that start with "shouldn't" and "normally we wouldn't" :)

http://www.taobackup.com/

[mcos → mseebach]

Thanks, I've already tried that. My main issue is EBS performance when writing the dump file to disk. The backups themselves don't impact on database performace much, but writing up to 20 Gigs of a dump file to an EBS disk on a nightly basis is extremely slow. Maybe this Data Pipeline service will help bypass that.

[1SaltwaterC → mcos]

Have you tried piping the dump directly to a compression tool? We use pbzip2 for our dumps which works great if you have some CPU power to spare. The largest was around 8 Gigs uncompressed, but the total size of the plain text dumps is over 20 Gigs. Hardly an issue for EBS that way. Did kill the DB for a few minutes several times before using cstream to cap the dump bw.

[jeffbarr → mcos]

Have you set up Provisioned IOPS for the volume?