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

Today, I will talk to you about the internal analysis of the implementation process of mysqldump single-table backup plus-single-transaction-master-data=2 parameters, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Enable general_log:

Mysql (mdba@localhost: (none) 01:08:55) > set global general_log=on

Query OK, 0 rows affected (0.02 sec)

Mysql (mdba@localhost: (none) 01:09:07) > show variables like'% general%'

| | Variable_name | Value |

| | general_log | ON |

| | general_log_file | / usr/local/mysql/data/localhost.log |

2 rows in set (0.00 sec)

Mysql (mdba@localhost: (none) 01:31:43) > show databases

| | Database |

| | information_schema |

| | bbb |

| | credit |

| | e_contract |

| | ixinnuo_sjcj |

| | ixinnuo_sjcj-_T |

| | mysql |

| | percona |

| | performance_schema |

| | sys |

| | test |

| | whpmap |

12 rows in set (0.05sec)

Mysql (mdba@localhost: (none) 01:31:46) > use credit

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql (mdba@localhost:credit 01:32:02) > show tables

| | Tables_in_credit |

| | f_audit |

| | f_audit_bak_20170504 |

2 rows in set (0.00 sec)

For example, back up the f_audit table under the credit library, using the-- single-transaction-- master-data=2 parameter:

[root@localhost tmp] # mysqldump-umdba-pdsf0723-single-transaction-- master-data=2 credit f_audit > / tmp/f_audit.sql

View general_log:

[root@localhost tmp] # cat / usr/local/mysql/data/localhost.log

/ opt/app/mysql/5.7.18/bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). Started with:

Tcp port: 3306 Unix socket: / tmp/mysql.sock

Time Id Command Argument

2017-08-31T05:09:09.189375Z 19513 Query show variables like'% gen%'

2017-08-31T05:09:24.603322Z 19513 Query SELECT DATABASE ()

2017-08-31T05:09:24.604041Z 19513 Init DB credit

2017-08-31T05:09:24.605173Z 19513 Query show databases

2017-08-31T05:09:24.605838Z 19513 Query show tables

2017-08-31T05:09:24.605976Z 19513 Field List f_audit

2017-08-31T05:09:24.606416Z 19513 Field List f_audit_bak_20170504

2017-08-31T05:09:26.575331Z 19513 Query show tables

2017-08-31T05:11:18.126829Z 19514 Connect mdba@localhost on using Socket

2017-08-31T05:11:18.126927Z 19514 Query / *! 40100 SET @ @ SQL_MODE='' * /

2017-08-31T05:11:18.130352Z 19514 Query / *! 40103 SET TIME_ZONE='+00:00' * /

2017-08-31T05:11:18.130450Z 19514 Query FLUSH / *! 40101 LOCAL * / TABLES

2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK

2017-08-31T05:11:18.140318Z 19514 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2017-08-31T05:11:18.140355Z 19514 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

2017-08-31T05:11:18.140417Z 19514 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

2017-08-31T05:11:18.150840Z 19514 Query SHOW MASTER STATUS

2017-08-31T05:11:18.168898Z 19514 Query UNLOCK TABLES

2017-08-31T05:11:18.181199Z 19514 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =' DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('fanciaudit') GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

2017-08-31T05:11:18.210240Z 19514 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN (' fanciaudit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

2017-08-31T05:11:18.220125Z 19514 Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'

2017-08-31T05:11:18.221667Z 19514 Init DB credit

2017-08-31T05:11:18.221712Z 19514 Query SHOW TABLES LIKE'f\ _ audit'

2017-08-31T05:11:18.221834Z 19514 Query SAVEPOINT sp

2017-08-31T05:11:18.221887Z 19514 Query show table status like'f\ _ audit'

2017-08-31T05:11:18.222054Z 19514 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-08-31T05:11:18.222100Z 19514 Query SET SESSION character_set_results = 'binary'

2017-08-31T05:11:18.222142Z 19514 Query show create table `f_ audit`

2017-08-31T05:11:18.222231Z 19514 Query SET SESSION character_set_results = 'utf8'

2017-08-31T05:11:18.222287Z 19514 Query show fields from `f_ audit`

2017-08-31T05:11:18.222614Z 19514 Query show fields from `f_ audit`

2017-08-31T05:11:18.222940Z 19514 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `f_ audit`

2017-08-31T05:11:18.223074Z 19514 Query SET SESSION character_set_results = 'binary'

2017-08-31T05:11:18.223116Z 19514 Query use `credit`

2017-08-31T05:11:18.223165Z 19514 Query select @ @ collation_database

2017-08-31T05:11:18.223224Z 19514 Query SHOW TRIGGERS LIKE'f\ _ audit'

2017-08-31T05:11:18.223465Z 19514 Query SET SESSION character_set_results = 'utf8'

2017-08-31T05:11:18.223509Z 19514 Query ROLLBACK TO SAVEPOINT sp

2017-08-31T05:11:18.223547Z 19514 Query RELEASE SAVEPOINT sp

2017-08-31T05:11:18.281441Z 19514 Quit

It was found to have executed the FLUSH TABLES WITH READ LOCK:

2017-08-31T05:11:18.130450Z 19514 Query FLUSH / *! 40101 LOCAL * / TABLES

2017-08-31T05:11:18.140266Z 19514 Query FLUSH TABLES WITH READ LOCK

Through further testing, it is found that mysqldump backups only use-- single-transaction does not use-- the master-data=2 parameter will not lock the table.

Do not add-- master-data=2 parameter:

[root@localhost tmp] # mysqldump-umdba-pdsf0723-- single-transaction credit f_audit > f_audit.sql

View general_log:

2017-08-31T05:24:05.890881Z 19516 Connect mdba@localhost on using Socket

2017-08-31T05:24:05.890984Z 19516 Query / *! 40100 SET @ @ SQL_MODE='' * /

2017-08-31T05:24:05.891037Z 19516 Query / *! 40103 SET TIME_ZONE='+00:00' * /

2017-08-31T05:24:05.891478Z 19516 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2017-08-31T05:24:05.891520Z 19516 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

2017-08-31T05:24:05.891589Z 19516 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

2017-08-31T05:24:05.893983Z 19516 Query UNLOCK TABLES

2017-08-31T05:24:05.894099Z 19516 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =' DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN ('fanciaudit') GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

2017-08-31T05:24:05.911788Z 19516 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='credit' AND TABLE_NAME IN (' fanciaudit')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

2017-08-31T05:24:05.916870Z 19516 Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'

2017-08-31T05:24:05.918150Z 19516 Init DB credit

2017-08-31T05:24:05.918208Z 19516 Query SHOW TABLES LIKE'f\ _ audit'

2017-08-31T05:24:05.918336Z 19516 Query SAVEPOINT sp

2017-08-31T05:24:05.918390Z 19516 Query show table status like'f\ _ audit'

2017-08-31T05:24:05.918558Z 19516 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-08-31T05:24:05.918603Z 19516 Query SET SESSION character_set_results = 'binary'

2017-08-31T05:24:05.918648Z 19516 Query show create table `f_ audit`

2017-08-31T05:24:05.918730Z 19516 Query SET SESSION character_set_results = 'utf8'

2017-08-31T05:24:05.918786Z 19516 Query show fields from `f_ audit`

2017-08-31T05:24:05.919114Z 19516 Query show fields from `f_ audit`

2017-08-31T05:24:05.919417Z 19516 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `f_ audit`

2017-08-31T05:24:05.919547Z 19516 Query SET SESSION character_set_results = 'binary'

2017-08-31T05:24:05.919592Z 19516 Query use `credit`

2017-08-31T05:24:05.919639Z 19516 Query select @ @ collation_database

2017-08-31T05:24:05.919698Z 19516 Query SHOW TRIGGERS LIKE'f\ _ audit'

2017-08-31T05:24:05.919999Z 19516 Query SET SESSION character_set_results = 'utf8'

2017-08-31T05:24:05.920064Z 19516 Query ROLLBACK TO SAVEPOINT sp

2017-08-31T05:24:05.920107Z 19516 Query RELEASE SAVEPOINT sp

2017-08-31T05:24:05.971293Z 19516 Quit

It is found that FLUSH TABLES WITH READ LOCK.

Only use the-- master-data=2 parameter to obtain the show master status, the global lock of FLUSH TABLES WITH READ LOCK will be executed. During the business trough, it is very fast for mysql to acquire the global lock, but it is not recommended to perform the global lock when there are many database tables in the business peak.

Therefore, mysqldump backup should be done during the business trough, or the master-data=2 parameter should be removed directly when backing up.

After reading the above, do you have any further understanding of how to perform the internal analysis of mysqldump single-table backup plus-- single-transaction-- master-data=2 parameters? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.