Select an appropriate backup method for MySQL _ MySQL

Database Backup is extremely important. If there is no backup, you will be crazy in the following situations: UPDATEorDELETEwhitoutwhere & hellip; tablewasDROPPedaccidentally & hellip; INNODBwascorrupt & hellip; entiredatacenterlosespower & helli database backup is extremely important. If there is no backup, the system will go crazy in the following situations:

UPDATE or DELETE whitout where... Table was DROPPed accidentally... INNODB was Upload UPT... Entire datacenter loses power...

From the perspective of data security, server disks are all raid, MySQL also has a master-slave, drbd, and other disaster recovery mechanisms, but they cannot completely replace backup. Disaster Tolerance and high availability can help us effectively cope with physical, hardware, and mechanical faults, but we can't do anything about the logical errors we make. The probability of each logical error is extremely low, but when multiple possibilities are combined, a small probability event poses a large security risk. at this time, the necessity of backup is highlighted. Which of the many MySQL backup methods is suitable for us?

Common backup methods

MySQL itself provides us with mysqldump and mysqlbinlog remote backup tools, percona also provides us with powerful Xtrabackup, coupled with open-source mydumper, there are also methods such as delayed backup based on master-slave synchronization, cold backup from the database, and backup based on file system snapshots. In fact, there are many dazzling choices. The backup itself is for recovery, so the backup method that enables us to quickly and accurately recover after a fault is the most suitable for us. of course, it can save money and save time, that's perfect. Below I will compare several backup tools I understand and discuss their respective application scenarios.

1. mysqldump & mydumper

Mysqldump is the simplest logical backup method. When backing up a myisam table, if you want to obtain consistent data, you need to lock the table, which is simple and crude. When backing up the innodb table, add the-master-data = 1-single-transaction option to record the binlog pos point at the beginning of the transaction, then mvcc is used to obtain consistent data. because it is a long transaction, a lot of undo will be generated in the database with a large number of writes and updates, which significantly affects the performance. Therefore, use it with caution.

• Advantage: it is simple and can be used for single-table backup, especially when the table structure is fully exported.
• Disadvantages: simple and crude, single-thread, slow backup and slow recovery, cross-IDC may encounter time zone problems.
  Mydumper is an enhanced version of mysqldump. Compared with mysqldump:
• Built-in compression is supported, which saves 2-4 times the storage space.
• Supports parallel backup and recovery, so the speed is much faster than mysqldump, but it is still not very fast because it is a logical backup.

2. file system-based snapshots

A file system-based snapshot is a type of physical backup. Before backup, you need to perform some complex settings. at the beginning of the backup, you can obtain the snapshot and record the binlog pos point. then, you can use a copy-on-write method to dump the snapshot. Dumping snapshots consumes a certain amount of I/O resources, and the impression that I/O is consumed before saving the changed data blocks on instances with high write pressure, the final result is a decline in overall performance. The server also reserves more disk space for the copy-on-write snapshot, which is a waste of resources. Therefore, we do not use this backup method much.

3. Xtrabackup

This is perhaps the most extensive backup method. Percona is widely known, and Xtrabackup should be indispensable. It is actually a combination of physical backup and logical backup. When backing up the innodb table, it copies the ibd file and keeps monitoring changes to the redo log for a moment, and appends it to its own transaction log file. During the process of copying ibd files, ibd files may be written as "flowers", which is not a problem because the first prepare phase after the copy is completed, xtrabackup adopts a method similar to innodb crash recovery to restore the data file to the same state as the log file and roll back the uncommitted transactions. If you need to back up files such as myisam table and innodb table structure at the same time, you need to use flush tables with lock to obtain the global lock, start copying these files that are no longer changing, and get the binlog location, release the lock after the copy ends, and stop monitoring the redo log.
It works as follows:

Because mysql inevitably contains myisam tables and innobackup does not back up table structures and other files, to back up the mysql instance completely, you must execute flush tables with read lock, this statement is blocked by any query (including select). during the blocking process, it in turn blocks any query (including select ). If a persistent query on the backup instance is executed before flush tables with read lock, the database will hang. When flush tables with read lock acquires the global lock, the update will still be blocked even though the query can be executed. Therefore, we hope that flush tables with read lock will end from the beginning to the end, the shorter the duration, the better.

To solve this problem, there are two effective methods:

1. try not to use the myisam table. 2. added the-rsync option for Xtrabackup to reduce the time for holding the global lock through two rsyncs.

The optimized backup process is as follows:

• Advantages: online hot backup, full backup + incremental backup + stream backup, supports speed limit, compression, and encryption.
• Disadvantage: you need to obtain the global lock. if you encounter a long query, the wait time will be uncontrollable. Therefore, you need to monitor the lock and if necessary, kill the long query or commit suicide. if you encounter a large instance, the backup process is long, and too many redo logs will affect the recovery speed. in this case, it is best to use delayed backup.

4. MySQL binlog 5.6

All the above backup methods can only restore the database to a certain backup time point: mysqldump and mydumper, and snapshot are the start time of the backup; Xtrabackup is the end time of the backup. To restore point in time, binlog must be backed up. At the same time, binlog is a valuable resource for incremental backup.

Fortunately, mysql 5.6 provides us with the option to remotely back up binlog:

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

It will pretend to be a mysql slave database, get the binlog remotely, and then perform a dump. This is very useful for scenarios where the online master database capacity is insufficient to store more binlogs. However, unlike the real mysql slave database instance, status monitoring and synchronization must be deployed independently. Therefore, I personally think that using blackhole to back up full binlogs is a better choice. I have implemented a tool to automatically build the blackhole Slave Database. with a slight modification, we can build a perfect blackhole slave database. Once it is synchronized, there are few problems once and for all. you can switch between the master and slave nodes.

Tip:

• Do not underestimate the binlog backup. When 5.6 of multi-threaded replication is used in a large scale, the time consumed to catch up with the master database command points from the database will be greatly shortened, in this way, we change the daily full backup to the full backup once every three days or even once a week, and the continuous binlog incremental backup. When data needs to be restored due to a fault, the three or five-day binlog replays extremely fast. The most direct advantage of reducing the backup frequency is to save money and save time.
• Blackhole is excellent for backing up binlog. On the one hand, binlog can be backed up for a long time to restore the database, and on the other hand, semi-synchronous replication can be configured on it to effectively prevent the loss of the binlog of the master database.

Summary

Backup methods have their own advantages. for us, it is the king to choose a proper backup tool for thousands of instances for unified configuration and planning, and to build a smart scheduling backup cloud platform. After all, O & M costs of coexistence of multiple backup methods cannot be ignored.

From the usage experience, it is a good choice to use Xtrabackup for full backup data, add binlog with blackhole, and regularly verify the validity of backup data.