My current MySQL backup scenario is to replicate our db to a second server and run mysqldump on that server to remove any downtime from table or row locking. This is working well but costs $150 per month for the second server (Australian hosting is a lot more expensive than US.)
I read a lot of questions on here about this, most people need help with the scheduled backups and whatnot which is not what I need. I need to mysqldump (preferably every 4hrs) with no downtime. The db is ~7GB uncompressed, so the mysqldump can take some time depending on the server.
I have considered replicating to the same machine, but I didnt want the slave to eat into much needed memory. I'm not sure I can constrain memory usage on a per db basis? Either way, this will put load on the server while its dumping the db.
I just read this http://www.zmanda.com/quick-mysql-backup.html and it looks good, $300 per year is ok, that saves me a lot.
Unfortunately I can't replicate to Amazon's RDS but I could replicate to a micro RC2 instance but the replication would take place over-net and the ping is ~220ms.
I saw a few people on here talking about LVM snapshots which might be a good option. I dont know a great deal about this option tho.
Opinions would be greatly appreciated.
Best Answer
If you use innodb tables, you can use
http://www.percona.com/docs/wiki/percona-xtrabackup:start
That will take a dump of your database that can be imported by their tools also without locking. I believe if you have myisam tables it locks those.