MySQL – How to Backup Database on Windows Without Using mysqldump

The question essentially equals this one: "what is a fast backup + recovery method for MySQL". This is because setting up MySQL replication is essentially taking a backup of some sorts, and opening it on another machine (and then syncing the slave with the master, which is a simple operation).

So it boils down to the fact mysqldump is too slow for you (makes sense). I've just answered the same here, but here are the highlights:

• Percona Xtrabackup is a common and tested (and free) backup tool. It is of interest to you if you're using InnoDB since it makes a hot backup of the InnoDB tablespace, with small impact on the running server. The resulting backup reflects the time at which the backup process ended, which means shorter initial lag for your slave.

• File system snapshots, via filesystems such as ZFS, or via LVM, or otherwise disk based snapshots, are all good solutions. Some (LVM in particular) making for more load than others.

What's common to the above solutions is that they make for a binary backup, not a logical one.

For your next question - yes. You can use MyDumper, which makes for a multithreaded backup (complemented by the multithreaded MyLoader tool). This backup is logical, and uses mysqldump internally, concurrently. It is typically a few times faster than standard mysqldump.

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.