MySQL database backup: performance issues

Question

Folks,

I'm trying to set up a regular backup of a rather large production database (half a gig) that has both InnoDB and MyISAM tables. I've been using mysqldump so far, but I find that it's taking increasingly longer periods of time, and the server is completely unresponsive while mysqldump is running.

I wanted to ask for your advice: how do I either

1. Make mysqldump backup non-blocking - assign low priority to the process or something like that, OR

2. Find another backup mechanism that will be better/faster/non-blocking.

I know of the existence of MySQL Enterprise Backup product (http://www.mysql.com/products/enterprise/backup.html) - it's expensive and this is not an option for this project.

I've read about setting up a second server as a "replication slave", but that's not an option for me either (this requires hardware, which costs $$).

Thank you!

UPDATE: more info on my environment: Ubuntu, latest LAMPP, Amazon EC2.

Answer 1

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.