MySQL database backup: performance issues

If replication to a slave isn't an option, you could leverage the filesystem, depending on the OS you're using,

• Consistent backup with Linux Logical Volume Manager (LVM) snapshots.
• MySQL backups using ZFS snapshots.
• The joys of backing up MySQL with ZFS...

I've used ZFS snapshots on a quite large MySQL database (30GB+) as a backup method and it completes very quickly (never more than a few minutes) and doesn't block. You can then mount the snapshot somewhere else and back it up to tape, etc.

Edit: (previous answer was suggestion a slave db to back up from, then I noticed Alex ruled that out in his question.)

There's no reason your replication slave can't run on the same hardware, assuming the hardware can keep up. Grab a source tarball, ./configure --prefix=/dbslave; make; make install; and you'll have a second mysql server living completely under /dbslave.

EDIT2: Replication has a bunch of other benefits, as well. For instance, with replication running, you'll may be able to recover the binlog and replay it on top your last backup to recover the extra data after certain kinds of catastrophes.

EDIT3: You mention you're running on EC2. Another, somewhat contrived idea to keep costs down is to try setting up another instance with an EBS volume. Then use the AWS api to spin this instance up long enough for it to catch up with writes from the binary log, dump/compress/send the snapshot, and then spin it down. Not free, and labor-intensive to set up, but considerably cheaper than running the instance 24x7.

Try mk-parallel-dump utility from maatkit (http://www.maatkit.org/)

regards,