Shulou(Shulou.com)06/01 Report--

The following content mainly brings you several backup methods of MySQL. The knowledge mentioned is slightly different from books, which are summed up by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing. I hope to bring help to the majority of readers.

Backup is the last line of defense of data security, for any data loss scenario, backup can not necessarily recover 100% of the data (depending on the backup cycle), but at least minimize the loss. There are two important metrics to measure backup recovery: recovery point objective (RPO) and recovery time objective (RTO). The former focuses on the extent to which recovery can be restored, while the latter focuses on how long it will take to restore. This article mainly discusses the backup scheme of MySQL, focusing on the principles of several backup methods, including file system snapshot (LVM), logical backup tool Mysqldump,Mydumper, and physical backup tool Xtrabackup. At the same time, it will explain in detail the advantages and disadvantages of several schemes, as well as the download address of the problems that may be encountered.

Cold backup

The simplest backup method is to shut down the MySQL CVM, and then copy and save all the files under the data directory. When you need to restore, you can copy the directory to the machine that needs to be restored. This method is indeed convenient, but it has little effect in the production environment. Because all machines provide services, and even Slave sometimes needs to provide read-only services, it is not realistic to turn off MySQL outage backup. A concept corresponding to cold backup is hot backup. The so-called hot backup is to backup without affecting the external service of MySQL. Hot backup is the focus of this article.

Snapshot backup

The first hot backup to be introduced is snapshot backup, which refers to backing up the database through the snapshot feature supported by the file system. The principle of backup is to put all the database files in the same partition, and then perform snapshot work on that partition. For Linux, it needs to be implemented through LVM (Logical Volumn Manager). LVM uses replication-on-write (copy-on-write) technology to create snapshots, for example, a logical copy of an entire volume that is similar to the MVCC of the innodb storage engine in the database, except that the snapshot of LVM is at the file system level, while MVCC is at the database level and only supports the innodb storage engine. LVM has a snapshot reserved area, and if the original volume data changes, LVM guarantees that the affected block will be copied to the snapshot reserved area before any changes are written. Simply put, all the consistent old data at the beginning of the snapshot point is retained in the snapshot area. For databases with few updates, snapshots can also be very small. For MySQL, in order to use snapshot backup, you need to put the data files and log files in a logical volume, and then take a snapshot backup of that volume. Because snapshots can only be backed up locally, if the local disk is corrupted, the snapshot is corrupted. Snapshot backup is more inclined to prevent misoperation, which can quickly restore the database to the point in time generated by the snapshot, and then combined with binary logs can be restored to the specified point in time.

Logical backup

Cold backup and snapshot backup are seldom used in production environment because of their disadvantages, and more logical backup and physical backup tools are used in MySQL. This section focuses on logical backup. MySQL officially provides Mysqldump logical backup tool, which is good enough, but it has the problem of slow single-thread backup. A better logical backup tool mydumper is provided in the community, and its advantage is mainly reflected in multi-thread backup and faster backup speed.

Mysqldump

When Mysqldump is used for backup, you have to mention two key parameters:

-- single-transaction: before starting the backup, execute the start transaction command to obtain a consistent backup, which is valid only for the innodb storage engine.

-- master-data=2: the point that is mainly used to record consistent backups.

To understand how Mysqldump works, be sure to distinguish between transactional tables (innodb) and non-transactional tables (such as myisam), because the backup process is closely related to it. And, so far, we can't avoid the myisam table, even if all our business tables are innodb, because the system tables in the mysql library still use the myisam table. The basic process of backup is as follows:

1. Call FTWRL (flush tables with read lock) to disable reading and writing globally

two。 Turn on snapshot reading and take the snapshot at this time (only for innodb tables)

3. Back up non-innodb table data (* .frm, * .myi,*.myd, etc.)

4. Release the FTWRL lock after the non-innodb table is backed up

5. Back up the innodb data one by one

6. Backup complete.

The whole process, you can refer to a picture of my colleague, but his picture only considers the backup of the innodb table. In fact, before the completion of the unlock tables execution, the non-innodb table has been backed up, and the following T1 Magi T2 and T3 are essentially innodb tables. And mysqldump of 5.6 uses the SavePoint mechanism to release the MDL lock on a table every time a table is prepared to avoid a longer lock on a table. You can refer to my previous blog:FLUSH TABLE WITH READ LOCK here.

You may have a question as to why the lock was released before backing up the innodb table. This actually makes use of the MVCC mechanism of the innodb engine. After snapshot reading is enabled, you can get consistent data at that time, no matter how long it takes to back up until the end of the whole transaction (commit).

Mydumper

Mydumper principle is similar to Mysqldump principle, the biggest difference is the introduction of multi-thread backup, each backup thread backup part of the table, of course, the concurrent granularity can reach the row level, to achieve the purpose of multi-thread backup. One of the biggest problems to be solved here is how to ensure the consistency of backups. In fact, the key lies in FTWRL. For non-innodb tables, the table needs to be backed up before the lock is released. For the innodb table, you need to ensure that multiple threads can get the consistency point, which is also done during the holding of the global lock, because there is no read or write to the database at this time, which ensures that the site is consistent. So the basic process is as follows:

Physical backup (Xtrabackup)

Compared to logical backup, physical backup is more direct and copies database files and logs to complete backup, so it is faster to extract all records from data by query. Of course, both the open source Mydumper and the latest official backup tool (5.7.11 mysqlpump) support multithreaded backup, so the speed difference may further narrow, at least from the current production environment, physical backup is still used more. Because Xtrabackup supports backing up innodb tables, the tool we use in the actual production environment is innobackupex, which is a layer of encapsulation of xtrabackup. The innobackupex script is used to back up non-InnoDB tables, and the xtrabackup command is called to back up InnoDB tables. The basic process of innobackupex is as follows:

1. Start the redo log copy thread and copy the redo log sequentially from the latest checkpoint

two。 Start the idb file copy thread to copy the data of the innodb table

When the copy of 3.idb file ends, call FTWRL to get the consistency site.

4. Back up non-innodb tables (system tables) and frm files

5. Since no new transaction is committed at this time, wait for the redo log copy to complete

6. After the latest redo log copy is completed, both the innodb table and non-innoDB table data are up-to-date.

7. Get the binlog locus, and the state of the database is consistent.

8. Release the lock and the backup ends.

Improvement of Xtrabackup

Judging from the logical and physical backups described earlier, no matter which backup tool it is, it is strongly dependent on FTWRL in order to obtain the consistency point. This lock is very lethal, because during the period of holding the lock, the whole database is essentially unable to provide write services. In addition, because the FTWRL needs to close the table, if there is a large query, it will cause the FTWRL to wait, which in turn causes the DML to be blocked for longer. Even if it is a standby library, SQL threads will cause delays in the primary and secondary libraries when they copy updates from the primary library and put on a global lock. From the previous analysis, the holding time of the FTWRL lock is mainly related to the amount of data of the non-innodb table. If the non-innodb table has a large amount of data and the backup is slow, then the holding time of the lock will be very long. Even if they are all innodb tables, they will be locked for a certain amount of time because of the existence of mysql library system tables. In order to solve this problem, Percona made improvements to the Server layer of Mysql, introducing BACKUP LOCK, specifically, backing up non-innodb table data through the "LOCK TABLES FOR BACKUP" command, and obtaining consistency sites through "LOCK BINLOG FOR BACKUP" to minimize service damage caused by database backup. Let's look at the difference between using these two locks and FTWRL:

LOCK TABLES FOR BACKUP

Function: backing up data

1. Prohibit non-innodb table updates

two。 Disable ddl for all tables

Optimization points:

1. Will not be blocked by large queries (close tables)

two。 It is very important that the reading and updating of the innodb table will not be blocked. If the business tables are all innodb, the DML will not be damaged at all during the backup.

UNLOCK TABLES

LOCK BINLOG FOR BACKUP

Function: to obtain the consistency site.

1. Prohibit the operation of site update

Optimization points:

1. Allow DDl and updates until binlog is written.

UNLOCK BINLOG

For the above backup methods of MySQL, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.