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

Principle of mysqldump backup

The basic process of backup is as follows:

FLUSH TABLES

Function: close all open entries on the instance: prepare for the second step, in order to prevent long transaction operations from causing FLUSH TABLES WITH READ LOCK operations to be locked, but at the same time blocking other client operations

FLUSH TABLES WITH READ LOCK

Function: add global read lock purpose: to obtain DB consistency status

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Function: set the transaction isolation level of the current session to RR,RR to avoid unrepeatable reads and phantom readings purpose: to ensure that the data is the same at all times in the backup transaction

START TRANSACTION WITH CONSISTENT SNAPSHOT

Function: take a snapshot of the current database, which is determined by single-transaction in mysqldump: in short, it starts a transaction and performs a SELECT operation on all tables, which ensures that the data obtained by executing select * from table at any point in time during backup is consistent with that obtained when START TRANSACTION WITH CONSISTENT SNAPSHOT is executed.

Obtain Log position

Function: get relevant information about binlog, which is determined by-- master-data: record the status information of binlog at the beginning of backup, including MASTER_LOG_FILE and MASTER_LOG_POS backup non-innodb table data (.frm, .myi, .myd, etc.)

Unlock tables (non-innodb table backup completed)

Backing up innodb data

Backup completed

Principle of xtrabackup backup

The essence of innobackupex: innobackupex scripts are used to back up non-InnoDB tables, while calling the xtrabackup command to back up InnoDB tables

The basic process of backup is as follows:

After innobackupex starts, it will first fork a process, start the xtrabackup process, and then wait for xtrabackup to back up the ibd data files.

When xtrabackup backs up InnoDB-related data, there are two threads, one is the redo copy thread, which is responsible for copying redo files, and the other is the ibd copy thread, which is responsible for copying ibd files. There is only one redo copy thread, which starts before the ibd copy thread and ends after the ibd thread ends. After the xtrabackup process starts execution, start the redo copy thread to copy the redo log sequentially from the latest checkpoint point, and then start the ibd data copy thread. During the xtrabackup copy ibd process, the innobackupex process has been waiting (waiting for the file to be created)

After the xtrabackup copy completes the idb, notify the innobackupex (by creating the file) while entering and waiting (the redo thread still continues to copy).

When innobackupex receives the xtrabackup notification, it executes FLUSH TABLES WITH READ LOCK (FTWRL), obtains the consistency point, and then starts backing up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par, and so on). In the process of copying non-InnoDB files, because the database is in a global read-only state, you should be especially careful if you back up the main database of the business. If there are more non-InnoDB tables (mainly MyISAM), the read-only time of the whole database will be longer. This impact must be evaluated.

When innobackupex has copied all the non-InnoDB table files, notify xtrabackup (by deleting the file) and enter and wait (wait for another file to be created)

When xtrabackup receives a non-InnoDB notification from innobackupex backup, it stops the redo copy thread and then notifies innobackupex redo log that the copy is complete (by creating a file)

After receiving the notification of the completion of the redo backup, innobackupex begins to unlock and execute UNLOCK TABLES

Finally, the innobackupex and xtrabackup processes finish the finishing work respectively, such as releasing resources, writing backup metadata information, etc. Innobackupex waits for the xtrabackup child process to finish and then exits.

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.