Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
possible duplicate of Best way to backup MySQL data on Linux server –  Caleb Jun 10 '11 at 12:58

4 Answers 4

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.

share|improve this answer

To a degree it depends on what restore options you want. Some methods work (mostly) but greatly limit your restore capabilities. e.g. A file system level backup only allows you to restore to the same machine or a clone. Using binary logs for backup purposes is fraught with risk, as the binary log doesn't tell the whole story.

Using mysqldump on a replicant is the best option in the vast majority of cases. It not only allows you to backup without disturbing the master but if you configure master/master instead of master/slave you also add redundancy. If you need to take the master off-line for any reason just point your application to the second system first.

share|improve this answer

In conjonction with a mysqldump backup, you may replicate data to a MySQL Slave where the datas are stored on a filesystem with a snapshot feature such as ZFS. In that case, you stop the slave, take a snapshot and restart the slave.

share|improve this answer
    
+1 reliability. Stoping the slave and making a snapshot of the database files will provide a more reliable (and easier to restore) backup of mysql. –  racyclist Jun 10 '11 at 21:22

The basic database backup scheme would include the following components:

  1. database data and transaction logs on different physical disks
  2. frequent restorable backups

The idea behind this is that in case of a disk (or disk array) failure of the database disk, you will be able to restore your data up to the last transaction with your previously taken backup and a replay of the latest transaction logs which were left unaffected by the failure.

On how to do that, a similar question has been asked before and got a reference to the MySQL documentation which describes some options.

I would certainly strongly recommend against replication as a backup "strategy" - it is not designed as a backup. Some people use the replica to reduce the load and locking concurrency on the primary database server by using a slave's database as the backup source. Yet, you are still left with the problem of consistency-checking of the slave's data set - MySQL does not do it for you and it is easy to screw up MySQL replication by misconfiguration. There are tools for consistency checking like mk-table-checksum from the maatkit, but then again little is won - consistency checking would put load and locks on your master's tables.

share|improve this answer
    
While initially your proposal of the transaction logs being on a separate drive seems sound it overlooks the possibility that it's that drive that fails, which would produce completely unpredictable results. As for using replication as part of the backup system, that's what the MySQL themselves recommend. –  John Gardeniers Jun 10 '11 at 7:35
    
The DBMS should be able to ensure that a transaction log drive failure does not lead to unpredictable results. I do not know how MySQL implements it, but usually it would be something like "rollback all uncommitted transactions, write a checkpoint and stop the database". This is possible as long as information for committed transactions not yet written to disk is kept in the hosts's memory (which is the case anyway as part of a performance optimization). The only scenario where this would fail is a coherent failure of the transaction log disk and the host's memory. –  the-wabbit Jun 10 '11 at 7:58
    
Regardless of what the MySQL team might or might not recommend (they certainly do not recommend using replication as the sole backup solution but only to aid load alleviation), with the integrity problem of the slave's data being inherent to MySQL replication design, it remains an inferior choice. –  the-wabbit Jun 10 '11 at 8:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.