Mysql – Backing up large MySQL DB for DR purposes

Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application.

I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a virtual machine on your local mac machines that run the same OS your production servers run. A tool that makes managing those machines easy is Vagrant.

Mimicking the production environment has the added benefit of allowing you to test any restore procedure with a fairly high degree of certainty that it would work on the production servers should you need to.

Promoting a slave would probably be my preferred route. As you pointing out any selects on MyISAM tables would require table level locks. There is one tool that might be able to help, pt-table-sync. It's primary purpose is to find gaps and differences in existing master slave relationships.

A nice thing about it is it does this in nice "chunks". Think of it kind of like antilock breaks. The chunk size is configurable but you could, for example go through doing 1000 rows at a time, minimizing lock times and letting things flow through. I haven't used it to fully repopulate a slave from scratch though although I'd give that a look. Once you have a full copy, do another run to catch new stuff, updates that have come in. Do a flush table with read locks, do a final table sync. run show master status to get the binary log position to start slaving from, unlock tables.

Oh, if you don't have binary logging you'll at least need a master bounce and cnf change unfortunately.

Another approach that might be much simpler depending your write downtime tolerance. You're all myisam, you gave the size in rows by what's the disk footprint in MB or GB? Figure out how long it would take to transfer that size between your machines (hopefully there both in the same local network). You could do flush table with readlock, again still show master status, then just rsync the .MY* files over to your new DB's datadir.

One final alternative, depending on how you're setup: Can you do an LVM or other kind of filesystem snapshot? This would be the best way to minimize downtime. You flush tables with readlock, show master status, start snapshot, then unlock the tables to allow full read write activity to flow through. The difference here is you'll copy the snapshot you started. You'll just need to feel confident the write activity won't exceed the snapshot size you allocate before the copy finishes.

What ever method, before promotion I would verify the character set conversions went as desired. They can be a real pain to reverse.

I would also recommend upgrading to innodb if possible to make it possible to use xtrabackup in a non blocking fashion in the future.