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

What this article shares with you is about how to analyze the principle of mysqldump backup. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

By enabling general log to track the backup process of mysqldump, you can dynamically adjust the parameter settings of general log.

First, do not add-single-transaction backup

Mysqldump-- default-character-set=utf8mb4-uroot-- triggers-R-E-A-- master-data=2 | gzip > dbbackup.sql.gz

1. Connect to the database

2. Adjust sql_mode and time_zone

3. Refresh tables (close all open tables and empty cached data)

4. FLUSH TABLES WITH READ LOCK (execute global read lock)

5. Record relevant variables, parameters, binlog location, etc.

6. Backup data files, tablespaces, etc.

7. Backup business table structure, table data, trigger

8. Backup system table structure, table data, trigger

9. Backup functions, stored procedures

10. When the backup ends and exits, the global lock is automatically released.

2017-06-24T00:48:48.622170Z 56 Connect root@localhost on using Socket

2017-06-24T00:48:48.622372Z 56 Query / *! 40100 SET @ @ SQL_MODE='' * /

2017-06-24T00:48:48.622500Z 56 Query / *! 40103 SET TIME_ZONE='+00:00' * /

2017-06-24T00:48:48.622654Z 56 Query FLUSH / *! 40101 LOCAL * / TABLES

2017-06-24T00:48:48.623539Z 56 Query FLUSH TABLES WITH READ LOCK

2017-06-24T00:48:48.623669Z 56 Query SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =

'performance_schema' AND table_name = 'session_variables'

2017-06-24T00:48:48.624191Z 56 Query SELECT COUNT (*) FROM performance_schema.session_variables WHERE VARI

ABLE_NAME LIKE 'rocksdb\ _ skip\ _ fill\ _ cache'

2017-06-24T00:48:48.625417Z 56 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

2017-06-24T00:48:48.627542Z 56 Query SHOW MASTER STATUS

2017-06-24T00:48:48.627743Z 56 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E

NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_

NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_

NAME

2017-06-24T00:48:48.628321Z 56 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE

NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L

OGFILE_GROUP_NAME

2017-06-24T00:48:48.628778Z 56 Query SHOW DATABASES

2017-06-24T00:48:48.629166Z 56 Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'

2017-06-24T00:48:48.631175Z 56 Init DB dbtest

2017-06-24T00:48:48.631282Z 56 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`

2017-06-24T00:48:48.631415Z 56 Query show tables

2017-06-24T00:48:48.631704Z 56 Query show table status like 't'

2017-06-24T00:48:48.632269Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:48:48.632372Z 56 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.632462Z 56 Query show create table `t`

2017-06-24T00:48:48.632642Z 56 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.632810Z 56 Query show fields from `t`

2017-06-24T00:48:48.633252Z 56 Query show fields from `t`

2017-06-24T00:48:48.633664Z 56 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t`

2017-06-24T00:48:48.633894Z 56 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.634000Z 56 Query use `dbtest`

2017-06-24T00:48:48.634136Z 56 Query select @ @ collation_database

2017-06-24T00:48:48.634281Z 56 Query SHOW TRIGGERS LIKE 't'

2017-06-24T00:48:48.634750Z 56 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.634887Z 56 Query show table status like 't1'

2017-06-24T00:48:48.635388Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:48:48.635483Z 56 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.635575Z 56 Query show create table `t1`

2017-06-24T00:48:48.635719Z 56 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.635852Z 56 Query show fields from `t1`

2017-06-24T00:48:48.636268Z 56 Query show fields from `t1`

2017-06-24T00:48:48.636699Z 56 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t1`

2017-06-24T00:48:48.636911Z 56 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:48:48.637030Z 56 Query use `dbtest`

2017-06-24T00:48:48.637132Z 56 Query select @ @ collation_database

2017-06-24T00:48:48.637264Z 56 Query SHOW TRIGGERS LIKE 't1'

2017-06-24T00:48:48.637719Z 56 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:48:48.637856Z 56 Query show table status like 't2'

2017-06-24T00:48:48.638347Z 56 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:48:48.638441Z 56 Query SET SESSION character_set_results = 'binary'

II. Add-single-transaction backup

Mysqldump-- default-character-set=utf8mb4-uroot-- triggers-R-E-A-master-data=2-- single-transaction | gzip > dbbackup.sql.gz

1. Connect to the database

2. Adjust sql_mode,time_zone

3. Adjust the transaction isolation subsector to RR (SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ)

4. Open transaction consistency snapshot (START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /)

5. Record gtid_mode, binlog location and other information

6. Release the global lock

7. Backup data files and tablespace data

8. Backup business database table structure

9. Set savepoint sp (SAVEPOINT sp)

10. Backup data table structure, table data, triggers, events (each time a table is backed up, do a rollback to savepoint sp, release the DDL lock)

11. Backup system database, table structure, trigger

12. Backup stored procedures and functions

13. Release savepoint

14. Backup event

15. Backup ends session exits

2017-06-24T00:58:05.650060Z 58 Connect root@localhost on using Socket

2017-06-24T00:58:05.650336Z 58 Query / *! 40100 SET @ @ SQL_MODE='' * /

2017-06-24T00:58:05.650491Z 58 Query / *! 40103 SET TIME_ZONE='+00:00' * /

2017-06-24T00:58:05.650703Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'

2017-06-24T00:58:05.660129Z 58 Query SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =

'performance_schema' AND table_name = 'session_variables'

2017-06-24T00:58:05.660477Z 58 Query SELECT COUNT (*) FROM performance_schema.session_variables WHERE VARI

ABLE_NAME LIKE 'rocksdb\ _ skip\ _ fill\ _ cache'

2017-06-24T00:58:05.662214Z 58 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2017-06-24T00:58:05.662314Z 58 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

2017-06-24T00:58:05.663369Z 58 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

2017-06-24T00:58:05.667436Z 58 Query SHOW STATUS LIKE 'binlog_snapshot_%'

2017-06-24T00:58:05.670160Z 58 Query UNLOCK TABLES

2017-06-24T00:58:05.670343Z 58 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, E

NGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_

NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_

NAME

2017-06-24T00:58:05.671210Z 58 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTE

NT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, L

OGFILE_GROUP_NAME

2017-06-24T00:58:05.671971Z 58 Query SHOW DATABASES

2017-06-24T00:58:05.672341Z 58 Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'

2017-06-24T00:58:05.675964Z 58 Init DB dbtest

2017-06-24T00:58:05.676080Z 58 Query SHOW CREATE DATABASE IF NOT EXISTS `dbtest`

2017-06-24T00:58:05.676223Z 58 Query SAVEPOINT sp

2017-06-24T00:58:05.676343Z 58 Query show tables

2017-06-24T00:58:05.676641Z 58 Query show table status like 't'

2017-06-24T00:58:05.677297Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:58:05.677404Z 58 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:58:05.677525Z 58 Query show create table `t`

2017-06-24T00:58:05.677659Z 58 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:58:05.677821Z 58 Query show fields from `t`

2017-06-24T00:58:05.678319Z 58 Query show fields from `t`

2017-06-24T00:58:05.678746Z 58 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t`

2017-06-24T00:58:05.678967Z 58 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:58:05.679059Z 58 Query use `dbtest`

2017-06-24T00:58:05.679211Z 58 Query select @ @ collation_database

2017-06-24T00:58:05.679357Z 58 Query SHOW TRIGGERS LIKE 't'

2017-06-24T00:58:05.679851Z 58 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:58:05.679958Z 58 Query ROLLBACK TO SAVEPOINT sp

2017-06-24T00:58:05.680059Z 58 Query show table status like 't1'

2017-06-24T00:58:05.680589Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:58:05.680702Z 58 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:58:05.680807Z 58 Query show create table `t1`

2017-06-24T00:58:05.680945Z 58 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:58:05.681071Z 58 Query show fields from `t1`

2017-06-24T00:58:05.681579Z 58 Query show fields from `t1`

2017-06-24T00:58:05.682021Z 58 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `t1`

2017-06-24T00:58:05.682244Z 58 Query SET SESSION character_set_results = 'binary'

2017-06-24T00:58:05.682369Z 58 Query use `dbtest`

2017-06-24T00:58:05.682477Z 58 Query select @ @ collation_database

2017-06-24T00:58:05.682609Z 58 Query SHOW TRIGGERS LIKE 't1'

2017-06-24T00:58:05.683147Z 58 Query SET SESSION character_set_results = 'utf8mb4'

2017-06-24T00:58:05.683268Z 58 Query ROLLBACK TO SAVEPOINT sp

2017-06-24T00:58:05.683366Z 58 Query show table status like 't2'

2017-06-24T00:58:05.683899Z 58 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-06-24T00:58:05.684005Z 58 Query SET SESSION character_set_results = 'binary'

III. Database recovery

Gunzip

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.