What's the most appropriate way to backup Mysql databases? I would like to have the backups in at least 1 hour intervals. I though mysqldump was the only option but It turns out there are other options too, like binary logs.
I would like to know how "big guys do it"
My tables are innodb
would like to have backups as often as possible (at least every hour)
I gues even 1 hour intervals is not a perfect solution, since I would still use the changes after last backup, up to 1 hour.
Best Answer
I believe mysqldump is best used on servers where you're not going to hold up any traffic. On myisam tables, this creates a read lock. I know you said you're using innodb though, so there are a few solutions out there.
I would start by checking this tool out. http://www.percona.com/software/percona-xtrabackup/ Percona guys are considered some of the experts on mysql and this is one of their tools for doing a 'hot backup' of innodb tables. This means you can run it even while your database is in use. I don't personally have any experience with it yet.
The site I'm currently working on has hundreds of concurrent users and in order to pull off reliable backups, we first have to set up replication to a slave. Then we run mysqldump on the slave, which does not disturb performance on the master server. Read more about replication here: http://dev.mysql.com/doc/refman/5.5/en/replication.html
I believe the sense is that you want to run backups about once a day using a very reliable method like mysqldump. In between you have a master and slave replicating and producing binary logs. The binary logs can be used to do minor catchup. In my experience hot backup tools tend to block one operation or another, leading to some poor user having a bad experience.