mysql en dump_MySQL dump备份时MySQL到底做了什么？

一、先决条件

备份时使用的mysqldump命令

mysqldump -uroot -p -S /data/mysql/data/3308/mysqld.sock -B testdb -R --triggers --routines --master-data=2 --single-transaction --set-gtid-purged=OFF > /root/testdb.sql

开启general_log命令

mysql> set global general_log=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%g%log%';

+----------------------------------+------------------------------------+

| Variable_name | Value |

+----------------------------------+------------------------------------+

| binlog_rows_query_log_events | ON |

| general_log | ON |

| general_log_file | /data/mysql/data/3308/dmp-udp3.log |

| log_statements_unsafe_for_binlog | ON |

| log_syslog | OFF |

| log_syslog_facility | daemon |

| log_syslog_include_pid | ON |

| log_syslog_tag | |

+----------------------------------+------------------------------------+

8 rows in set (0.00 sec)

查询 testdb 库一共有多少张表

mysql> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| sbtest1 |

| sbtest2 |

+------------------+

2 rows in set (0.00 sec)

二、分析general_log日志过程

开启general_log日志后执行备份命令

[root@dmp-udp3 ~]# /data/mysql/base/5.7.25/bin/mysqldump -uroot -p -S /data/mysql/data/3308/mysqld.sock -B testdb -R --triggers --routines \

--master-data=2 --single-transaction --set-gtid-purged=OFF > /root/testdb.sql

[root@dmp-udp3 ~]# cat /data/mysql/data/3308/dmp-udp3.log

2020-06-24T07:38:58.288670-00:00 391 Connect root@localhost on using Socket

2020-06-24T07:38:58.289269-00:00 391 Query /*!40100 SET @@SQL_MODE='' */

2020-06-24T07:38:58.289550-00:00 391 Query /*!40103 SET TIME_ZONE='+00:00' */

2020-06-24T07:38:58.289764-00:00 391 Query FLUSH /*!40101 LOCAL */ TABLES

2020-06-24T07:38:58.290656-00:00 391 Query FLUSH TABLES WITH READ LOCK

2020-06-24T07:38:58.291805-00:00 391 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2020-06-24T07:38:58.292738-00:00 391 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

2020-06-24T07:38:58.293075-00:00 391 Query SHOW MASTER STATUS

2020-06-24T07:38:58.293330-00:00 391 Query UNLOCK TABLES

2020-06-24T07:38:58.293657-00:00 391 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 IN ('testdb'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

2020-06-24T07:38:58.296022-00:00 391 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 IN ('testdb')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

2020-06-24T07:38:58.297643-00:00 391 Query SHOW VARIABLES LIKE 'ndbinfo\_version'

2020-06-24T07:38:58.301038-00:00 391 Init DB testdb

2020-06-24T07:38:58.301171-00:00 391 Query SHOW CREATE DATABASE IF NOT EXISTS `testdb`

2020-06-24T07:38:58.301309-00:00 391 Query SAVEPOINT sp

2020-06-24T07:38:58.301429-00:00 391 Query show tables

2020-06-24T07:38:58.301671-00:00 391 Query show table status like 'sbtest1'

2020-06-24T07:38:58.301999-00:00 391 Query SET SQL_QUOTE_SHOW_CREATE=1

2020-06-24T07:38:58.302177-00:00 391 Query SET SESSION character_set_results = 'binary'

2020-06-24T07:38:58.302310-00:00 391 Query show create table `sbtest1`

2020-06-24T07:38:58.302490-00:00 391 Query SET SESSION character_set_results = 'utf8'

2020-06-24T07:38:58.302618-00:00 391 Query show fields from `sbtest1`

2020-06-24T07:38:58.303065-00:00 391 Query show fields from `sbtest1`

2020-06-24T07:38:58.303513-00:00 391 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`

2020-06-24T07:38:58.971979-00:00 391 Query SET SESSION character_set_results = 'binary'

2020-06-24T07:38:58.972338-00:00 391 Query use `testdb`

2020-06-24T07:38:58.972539-00:00 391 Query select @@collation_database

2020-06-24T07:38:58.972787-00:00 391 Query SHOW TRIGGERS LIKE 'sbtest1'

2020-06-24T07:38:58.973504-00:00 391 Query SET SESSION character_set_results = 'utf8'

2020-06-24T07:38:58.973643-00:00 391 Query ROLLBACK TO SAVEPOINT sp

2020-06-24T07:38:58.973820-00:00 391 Query show table status like 'sbtest2'

2020-06-24T07:38:58.974130-00:00 391 Query SET SQL_QUOTE_SHOW_CREATE=1

2020-06-24T07:38:58.974256-00:00 391 Query SET SESSION character_set_results = 'binary'

2020-06-24T07:38:58.974371-00:00 391 Query show create table `sbtest2`

2020-06-24T07:38:58.974551-00:00 391 Query SET SESSION character_set_results = 'utf8'

2020-06-24T07:38:58.974697-00:00 391 Query show fields from `sbtest2`

2020-06-24T07:38:58.975132-00:00 391 Query show fields from `sbtest2`

2020-06-24T07:38:58.975489-00:00 391 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest2`

2020-06-24T07:38:59.642175-00:00 391 Query SET SESSION character_set_results = 'binary'

2020-06-24T07:38:59.642386-00:00 391 Query use `testdb`

2020-06-24T07:38:59.642531-00:00 391 Query select @@collation_database

2020-06-24T07:38:59.642737-00:00 391 Query SHOW TRIGGERS LIKE 'sbtest2'

2020-06-24T07:38:59.643297-00:00 391 Query SET SESSION character_set_results = 'utf8'

2020-06-24T07:38:59.643458-00:00 391 Query ROLLBACK TO SAVEPOINT sp

2020-06-24T07:38:59.643585-00:00 391 Query RELEASE SAVEPOINT sp

2020-06-24T07:38:59.643730-00:00 391 Query use `testdb`

2020-06-24T07:38:59.643892-00:00 391 Query select @@collation_database

2020-06-24T07:38:59.644069-00:00 391 Query SET SESSION character_set_results = 'binary'

2020-06-24T07:38:59.644244-00:00 391 Query SHOW FUNCTION STATUS WHERE Db = 'testdb'

2020-06-24T07:38:59.649984-00:00 391 Query SHOW PROCEDURE STATUS WHERE Db = 'testdb'

2020-06-24T07:38:59.651970-00:00 391 Query SET SESSION character_set_results = 'utf8'

2020-06-24T07:38:59.658759-00:00 391 Quit