根据日志解析mysqldump原理

来源：互联网发布：js的string是不可变编辑：程序博客网时间：2024/06/06 07:01

1.基本信息

(product)root@localhost [(none)]> select version();
+------------+
| version() |
+------------+
| 5.6.29-log |
+------------+
1 row in set (0.00 sec

2.打开general_log

(product)root@localhost [(none)]> set global general_log =on;
Query OK, 0 rows affected (0.00 sec)

(product)root@localhost [(none)]> show global variables like 'general_log%';
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log | ON |
| general_log_file | /data/mysql/mysql3376/data/mvxl0782.log |
+------------------+-----------------------------------------+
2 rows in set (0.01 sec)

3.mysqldump所有库
mysqldump -u root -psafe_2016 -S /tmp/mysql3376.sock --master-data=2 --single-transaction -A > A0516.sql

60516 17:07:38    17 Connect   root@localhost on
                   17 Query     /*!40100 SET @@SQL_MODE='' */
                   17 Query     /*!40103 SET TIME_ZONE='+00:00' */
                   17 Query     FLUSH /*!40101 LOCAL */ TABLES
160516 17:07:39    17 Query     FLUSH TABLES WITH READ LOCK
                   17 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                   17 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                   17 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                   17 Query     SHOW MASTER STATUS
                   17 Query     UNLOCK TABLES
                   17 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SC
HEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GR
OUP_NAME
                   17 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FR
OM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                   17 Query     SHOW DATABASES
                   17 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                   17 Init DB   lots
                   17 Query     SHOW CREATE DATABASE IF NOT EXISTS `lots`
                   17 Query     SAVEPOINT sp
                   17 Query     show tables
                   17 Query     show table status like 't1'
                   17 Query     SET SQL_QUOTE_SHOW_CREATE=1
                   17 Query     SET SESSION character_set_results = 'binary'
                   17 Query     show create table `t1`
                   17 Query     SET SESSION character_set_results = 'utf8'
                   17 Query     show fields from `t1`
                   17 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                   17 Query     SET SESSION character_set_results = 'binary'
                   17 Query     use `lots`
                   17 Query     select @@collation_database
                   17 Query     SHOW TRIGGERS LIKE 't1'
                   17 Query     SET SESSION character_set_results = 'utf8'
                   17 Query     ROLLBACK TO SAVEPOINT sp
                   17 Query     show table status like 't\_order'
                   17 Query     SET SQL_QUOTE_SHOW_CREATE=1
                   17 Query     SET SESSION character_set_results = 'binary'

4.根据日志解析mysqldump原理

上面mysqldump执行后，可看到先是发出一条flush tables来关闭实例上所有打开的表，执行成功后再执行FLUSH TABLES WITH READ LOCK加全局读锁，阻止commit，获得db一致性状态，接着执行：SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，以确保备份事务中任何时刻的数据都相同，再开始执行：START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */，创建一个RR级别的事务一致性快照(--single-transaction参数的作用)，然后根据参数--master-data=2 打印出change master(SHOW MASTER STATUS)信息。备份完非innodb的数据后，再将锁释放UNLOCK TABLES，后面开始备份innodb的数据，利用保存点机制，每备份完一个表就将一个表上的MDL锁释放，避免对一张表锁更长的时间。为啥备份innodb表之前，就已经将锁释放掉了，这实际上是利用了innodb引擎的MVCC机制，开启快照读后，就能获取那个时间的一致的数据，无论需要备份多长时间，直到整个事务结束(commit)为止。
所以备份的数据时间点为17:07:39。

5.确认mysqldump最后完成时间和完成时的binlog position

查看导出文件 A0516.sql，可看到如下信息：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000069', MASTER_LOG_POS=1369; ---dump时的binlog对应位置
...............................
-- Dump completed on 2016-05-16 17:08:15 ----dump完成时间

我们然后根据binlog去查时间点：2016-05-16 17:08:15 所对应的position：mysql-bin.000069', MASTER_LOG_POS=1369;
mysqlbinlog -vv mysql-bin.000069 --stop-datetime='2016-05-16 17:08:15' --base64-output=DECODE-ROWS

#160515 21:50:02 server id 1073376 end_log_pos 1369 CRC32 0xce7870ca Xid = 37
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

0 0