Shulou(Shulou.com)05/31 Report--

This article introduces you what is the principle of MySQL backup, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Cold backup

The easiest way to back up is to shut down the MySQL server, then copy and save all the files under the data directory, and when you need to restore, 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. The basic principles are as follows:

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 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

The principle of Mydumper is similar to that of Mysqldump. The difference is that multi-thread backup is introduced. Each backup thread backs up 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 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 official backup tool (5.7.11 mysqlpump) support multithreaded backup, so the speed difference may be further narrowed, 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 checkpoint of *.

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 copy of the redo log is completed, the data of both the innodb table and the non-innoDB table are *.

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 "LOCKTABLES 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.

UNLOCKTABLES

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.

UNLOCKBINLOG

What is the principle of MySQL backup is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.