Wan A10: how does mysqldump achieve consistent backup
Experimental scene
MySQL 8.0.25
InnoDB
Experimental steps:
Start general first_ Log observe the changes in the export execution process
set global general_log=ON;
mysqldump key parameter description:
--Single transaction parameter description:
Set the isolation level to REPEATABLE READ and start a read-only transaction in which consistent snapshot reads will be performed. This option is valid for the data table of InnoDB, but it can not guarantee the data consistency of MyISAM table and MEMORY table. During dump operation, all open tables will be closed first, FTWRL lock will be generated, and then FTWRL lock will be released quickly.
general_ The log is displayed as follows:
FLUSH /*!40101 LOCAL */ TABLES FLUSH TABLES WITH READ LOCK SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
If the current database already has a table lock at this time, the dump operation will be blocked until the previous table lock is released; Or the lock time is exceeded_ wait_ Exit after timeout.
The following is a simulation of LOCK TABLES t_user READ; After the operation, the dump operation is blocked.
(Sun Nov 21 06:42:07 2021)[root@GreatSQL][(none)]>show processlist; +----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+----------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined | +----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+----------+-----------+---------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 77523 | Waiting on empty queue | NULL | 77523235 | 0 | 0 | | 11 | root | localhost | test | Sleep | 266 | | NULL | 266664 | 0 | 0 | | 14 | root | localhost | NULL | Query | 263 | Waiting for table flush | FLUSH /*!40101 LOCAL */ TABLES | 263751 | 0 | 0 | | 17 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 | +----+-----------------+-----------+------+---------+-------+-------------------------+--------------------------------+----------+-----------+---------------+ 4 rows in set (0.00 sec)
--master_data=1|2 Parameter Description:
Execute show master status\G; Get the binlog file and position value. The difference is that the parameter is set to 2 and the exported content is annotated.
general_ The log is displayed as follows:
Diagram of normal backup process
- Diagram of normal backup process
No -- single transaction and -- master are added_ Data parameter execution process
- Added -- single transaction and -- master_data parameter execution process
Recommended extended reading:
Enjoy GreatSQL :)
Article recommendation:
Technology sharing | MGR best practice
https://mp.weixin.qq.com/s/66...
Technology sharing | the way to repair MGR Bug in Wanli database
https://mp.weixin.qq.com/s/Ia...
Macos system compiler percona and the difference of some functions on Macos system
https://mp.weixin.qq.com/s/jA...
Technology sharing | using systemd to manage single machine and multiple instances of MySQL
https://mp.weixin.qq.com/s/iJ...
Product | GreatSQL to create a better MGR ecology
https://mp.weixin.qq.com/s/By...
Product | GreatSQL MGR optimization reference
https://mp.weixin.qq.com/s/5m...
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Wechat & QQ group:
You can scan code to add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR exchange wechat group, or directly scan code to join GreatSQL/MGR exchange QQ group.
This article is composed of blog one article multi posting platform OpenWrite release!