MySQL Backup principle detailed

Backups are the last line of defense for data security, and for any scenario where data is lost, backups may not necessarily restore a single amount of 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), which focus on what level of recovery is, while the latter focuses on how long it takes to recover. This article focuses on MySQL backup scenarios, focusing on the principles of several backup methods, including file system Snapshot (LVM), logical Backup tool mysqldump,mydumper, and physical Backup tool Xtrabackup, which will explain in detail the pros and cons of several scenarios, and the problems you may encounter.

Cold backup
      The simplest way to backup is to shut down the MySQL server and then copy and save all the files under the Data directory, and then copy the directory to the machine that needs to be restored when it needs to be restored. This is a convenient way to do it, but it's basically useless in a production environment. Since all machines are to be serviced, even slave sometimes need to provide read-only services, so it is unrealistic to turn off MySQL for backup. A concept that corresponds to a cold backup is a hot backup, and a hot backup is the focus of this article as it is backed up without affecting MySQL external services.

snapshot backup
      The first hot backup to describe is a snapshot backup, which refers to a backup of the database through the file system-supported snapshot feature. The rationale behind the backup is to put all the database files in the same partition and then perform the snapshot work on the partition, which, for Linux, needs to be done through LVM (Logical volumn Manager). LVM uses write-time replication (copy-on-write) technology to create snapshots, such as a logical copy of an instantaneous for an entire volume, similar to the MVCC of the InnoDB storage engine in the database, except that the LVM snapshot is at the file system level, and MVCC at the database level, And only the INNODB storage engine is supported. LVM has a snapshot reservation area, and if the original volume data changes, LVM guarantees that the affected block will be copied to the snapshot reserve area before any changes are written. Simply put, all old data consistent at the beginning of the snapshot point is retained in the snapshot area. For databases with few updates, snapshots are also very small. For MySQL, in order to use a snapshot backup, you need to put the data files, log files in a logical volume, and then back up the volume snapshot. Because the snapshot backup is local only, the snapshot is corrupted if the local disk is damaged. Snapshot backups are more biased against misoperation, which can quickly restore a database to the point at which the snapshot was generated, and then combine the binary logs to restore to a specified point in time. Basic principles such as:

Logical backup
Cold and snapshot backup due to its drawbacks in the production environment is rarely used, using more MySQL comes with logical backup and physical backup tools, this section mainly on logical backup, MySQL official provides mysqldump logical backup tool, although good enough, but there is a single-threaded backup slow problem. In the community provides a better logical backup tool Mydumper, its advantages are mainly reflected in multi-threaded backup, backup faster.

Mysqldump
mysqldump is used for backup and has to mention two key parameters:
--single-transaction: Before starting the backup, execute the start transaction command to obtain a consistent backup that is only valid for the InnoDB storage engine.
--master-data=2: Used primarily to record the bit points of a consistent backup.
to understand how mysqldump works, be sure to treat the transaction table (INNODB) and non-transactional tables (such as MyISAM) differently, because the process of backing up is closely related. And, so far, we have been unable to circumvent the MyISAM table even though all our business tables are INNODB because the system tables in the MySQL library still use the MyISAM table. The basic process for backup is as follows:
1. Call FTWRL (flush tables with read lock), global prohibit read/write
2. Turn on snapshot read to get a snapshot at this time (only works on InnoDB table)
3. Backing up non-InnoDB table data (*.FRM,*.MYI,*.MYD, etc.)
4. After the non-InnoDB table backup is complete, release the FTWRL lock
5. Backup InnoDB table data individually
6. The backup is complete.
The whole process, you can refer to a picture of my colleague, but his diagram only consider the backup of InnoDB table, in fact, before unlock tables execution, the non-InnoDB table has been backed up, the t1,t2 and T3 in the InnoDB is essentially a table, and 5.6 of mysqldump use the savepoint mechanism to release the MDL lock on a table every time a table is backed up, avoiding a longer lock on a table. Here you can refer to my previous Blog:flush TABLE with READ LOCK
you may have a question about why the lock was released before you backed up the InnoDB table, which is actually taking advantage of the MVCC mechanism of the InnoDB engine, which allows you to get consistent data for that time, regardless of how long it takes to backup, until the end of the entire transaction (commit Far

Mydumper
Mydumper principle and the principle of mysqldump similar, the biggest difference is the introduction of multi-threaded backup, each backup thread backup part of the table, of course, the concurrency granularity can be to the row level, to achieve multi-threaded backup purposes. Here to solve the biggest problem is how to ensure the consistency of the backup, in fact, the key is FTWRL. For non-InnoDB tables, you need to complete the table backup before releasing the lock. For the InnoDB table, it is necessary to ensure that multiple threads have access to the consistency bit, which is also done during the holding of the global lock, because the database is not read-write, and the bit is guaranteed to be consistent. So the basic flow is as follows:

physical Backup (xtrabackup)
with a query that extracts all the records in the data relative to a logical backup, the physical backup is more straightforward, copying the database files and logs to complete the backup, and therefore faster. Of course, both the open source Mydumper and the official latest Backup tool (5.7.11 's Mysqlpump) support multi-threaded backup, so the speed difference may be further reduced, at least from the current production environment, physical backup use or more. Because Xtrabackup supports backing up the InnoDB table, the tool we use in the actual production environment is Innobackupex, which is a layer of xtrabackup encapsulation. The Innobackupex script is used to back up non-InnoDB tables, and the Xtrabackup command is called to back up the InnoDB table, and the basic flow of Innobackupex is as follows:
1. Turn on the Redo log copy thread and copy the redo log from the latest checkpoint .
2. Open the IDB file copy thread and copy the data from the InnoDB table.
3.idb file copy end, notify call FTWRL, get consistency bit point
4. Back up non-InnoDB tables (System tables) and frm files
5. Wait for redo log copy to complete since no new transaction is committed at this time
6. After the latest redo log copy is complete, the InnoDB table and non-InnoDB table data are up-to-date at this time
7. Get the Binlog site, at which point the state of the database is consistent.
8. Release the lock and end the backup.

 

xtrabackup improvements
      from the logical and physical backups described earlier, regardless of the backup tool, it is strongly dependent on FTWRL in order to obtain consistency sites. This lock is very lethal, because the whole database is essentially unable to provide write service to the time the lock is held. In addition, because FTWRL needs to close the table, if there is a large query, it causes FTWRL to wait, which in turn causes the DML to become clogged longer. Even if it is a standby, there are SQL threads that replicate updates from the main library, which causes the master repository to be delayed when the global lock is on. From the previous analysis, FTWRL this lock holding time is mainly related to the data volume of non-InnoDB table, if the non-InnoDB table data volume is very large, backup is slow, then the time to hold the lock is very long. Even if all the InnoDB tables are present, the MySQL library system table exists, which can cause a certain amount of time to be locked. To solve this problem, Percona has made improvements to the MySQL server layer by introducing backup lock, which specifically backs up non-InnoDB table data through the "Lock TABLES for Backup" command, through the "lock BINLOG For backup to get the consistency bit, minimizing the service damage caused by database backups. Let's look at the difference between using these two locks with FTWRL:

LOCK TABLES for BACKUP
role: Backing up data
1. Prohibit non-InnoDB table update
2. Disable DDL for all tables
Optimization points:
1. Will not be blocked by large queries (close the table)
2. It is important that the InnoDB table is not blocked from reading and updating, and that the DML is completely intact during the backup process for all INNODB of the business table
UNLOCK TABLES

LOCK BINLOG for BACKUP
Role: Gets the consistency site.
1. Disable the operation of the site update
Optimization points:
1. Allow DDL and updates until Binlog is written.
UNLOCK BINLOG

MySQL Backup principle detailed