MYSQL 备份工具

 backup of a database is a very important thing. If no backup, meet the following situation goes crazy:

　　UPDATE or DELETE whitout where…

　　table was DROPPed accidentally…

　　INNODB was corrupt…

　　entire datacenter loses power…

　　Data from the safety point of view, the server disk will do raid, MySQL itself also has a master-slave, DRBD disaster recovery mechanism, but they are not completely replace the backup. Fault tolerance and high availability can help us deal with physical, hardware, machinery, and logic errors on our committed but incapable of action. The probability of each logical errors are very low, but when the various possibilities of superposition, the small probability event is enlarged into a great safety hidden trouble, this time need backup highlights. So in many ways the MySQL backup, which is suitable for us?

　Backup common

　　MySQL itself provides us with mysqldump, mysqlbinlog remote backup tool, Percona also provides powerful Xtrabackup for us, With the open source mydumper, And based on the master-slave synchronization delay from the library backup, cold standby mode, And based on the file system snapshot backup, In fact, the choice has much to see things in a blur. While the backup is in order to recover, so that we can backup mode after a failure occurs rapidly, accurately and recovery, is the most suitable for us, of course, also can save money, save trouble, it is perfect. Several backup tools here I understand some comparison, discuss their applicable scene.

　　1. mysqldump & mydumper

　　Mysqldump is a logical backup method is the most simple. When the backup MyISAM table, if you want to obtain consistent data, you need to lock table, simple and rude. And when the backup InnoDB table, With – master-data=1 – the single-transaction option, Beginning in the transaction, Record binlog POS, Then use mvcc to obtain consistent data, Because it is a long transaction, In the write and update of large quantity of database, Will have a lot of undo, Significant performance impact, It should be used with caution.

 

• Advantages: simple, for single table backup, especially useful when the total amount of export structure.
• Disadvantages: simple and crude, single threaded, backup and recovery slow slow, span of IDC are likely to encounter the time zone. 
  Mydumper is an enhanced version of the mysqldump. Compared with mysqldump:
• Built-in support for compression, can save storage space of 2-4 times.
• Parallel backup and recovery, so a lot faster than mysqldump, but because it is a logical backup, still not very fast.

　　2 the snapshot file system based on

　　The snapshot based on file system, is a physical backup. In the backup before the need for some complex settings, in the backup time get the snapshot and record binlog POS, then the copy-on-write like mode, the snapshot dump. Dump the snapshot itself will consume some IO resources, but also in the writing case pressure, save the changes to the data block before the impression will consume IO, finally showed a decline in the overall performance of the. And the server and copy-on-write snapshots allow more disk space, which in itself is a waste of resources. This backup methods we use no more.

 

　　3. Xtrabackup

　　This is probably the most widely backup mode. Percona is make known to every family, Xtrabackup should not. It is actually the combination of physical backup, logical backup. When the backup InnoDB table, it copies the IBD file, and change constantly monitors the redo log, append to their transaction log files. In the process of copying IBD files, IBD files may be written "flower", this is not a problem, because the first prepare stage in the copy after the completion of the Xtrabackup, by a similar method to the InnoDB crash recovery, to restore data files and log files consistent state, and the transaction is rolled back the uncommitted. If at the same time the need to backup MyISAM and file InnoDB table structure, then need to use the flush tables with lock to obtain the global lock, began to copy these files no longer changes, at the same time to obtain binlog location, copy after release the lock, and stop the redo log monitor. 
It works as follows:

 

　　Because MySQL is inevitably contains the MyISAM table, At the same time, innobackup is not a backup file table structure, So you want to backup the MySQL instance, Will not have to execute flush tables with read lock, This statement will be any query (including select) obstruction, During a jam, It in turn blocking any query (including select). If you happen to back up the instance is executed before flush tables with read lock query, database hang. When flush tables with read lock to obtain the global lock, although the query can be executed, but still blocking update, so, we hope that flush tables with read lock from the beginning to the end, the duration of the shorter the better.

　　In order to solve this problem, two methods are effective:

　　1 as far as possible without MyISAM table.

　　2 Xtrabackup increase – Rsync options, to reduce holdings of global locking time by two Rsync.

　　The backup process optimized as follows:

 

• Advantages: online hot backup, all increased by + + flow speed, support, support compression, encryption is supported.
• Disadvantages: the need to obtain a global lock, if meet the query, the waiting time is not controllable, so to do a good job monitoring, kill the long queries or Dutch act when necessary; encountered large examples, the backup process is longer, redo log is the effect of the recovery rate, in this case the best delay backup.

　　4. mysqlbinlog 5.6

　　All the above backup, only to recover the database to a point in time the backup: mysqldump and mydumper, and snapshot is a backup starting time; Xtrabackup is the backup end point in time. In order to realize the point in time recovery, must also backup binlog. At the same time, binlog is also the precious resources increased by.

　　Fortunately, MySQL 5.6 offers binlog remote backup options:

　　mysqlbinlog --raw --read-from-remote-server --stop-never

　　It will be disguised as a MySQL from the database, obtaining binlog from a remote and then dump. This is not on the line capacity cannot save more binlog scene is very useful. However, it is after all not a true MySQL from the database instance, state monitoring and synchronization requires a separate deployment. So personally think that using Blackhole to backup the full amount of binlog is the better choice. The author has implemented an automated build Blackhole from the database tools, slightly modified, can perfect build Blackhole from the library. Once the basic synchronous up, put things right once and for all, little problems, when the switch with cut on the line.

　　Prompt:

• Don't underestimate binlog backup. When 5.6 of the multithreading replication large-scale use, from the library after the main library command point of time will be greatly reduced, so we put every day a full backup to once every 3 days, and weekly full backup, incremental backup and persistent binlog. When a failure to recover the data, reproducing 3, 5 days binlog is extremely fast. Reduce the benefits of backup frequency is the most direct, save money, save trouble.
• Blackhole is excellent for backup binlog. On the one hand can be used to recover the database backup binlog for a long time, on the other hand, in the semi synchronous replication, which can effectively prevent the main library binlog is missing.

　Summary

　　Each has its own merits. backup mode, and to us, in the face of thousands of examples, select the appropriate backup tool to achieve unified configuration, unified planning, construction of backup cloud platform of intelligent scheduling is the kingly way. After all, cost of operation and maintenance of coexistence of multiple backup modes can not be ignored.

　　From experience, with Xtrabackup full data, preparation of binlog by Blackhole enrichment, and regularly on the effectiveness of the backup data to verify, is the good choice.