mysqldump原理及实践

系统环境

# cat /etc/redhat-release
CentOS release 6.5 (Final)
# uname -a
Linux miles 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

软件版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.27-log |
+------------+

打开general_log

mysql> set global general_log=1;
Query OK, 0 rows affected (0.04 sec)

mysql> show global variables like '%gen%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /data/mysql/miles.log |
+------------------+-----------------------+
2 rows in set (0.01 sec)

创建测试数据

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test
Database changed

mysql> create table t1 (
    -> id int primary key auto_increment,
    -> name varchar(10) not null,
    -> age int not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> create table t2(id int not null primary key auto_increment,name varchar(10) not null,age int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2(name,age) values('name1',10),('name2',10),('name3',10),('name4',10),('name5',10),('name6',10),('name7',10),('name8',10),('name9',10);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

不开启–single-transaction执行备份：

# mysqldump -uroot -p --socket=/data/mysql.sock -B test

查看对应的general_log

160728 21:28:00     5 Connect   root@localhost on 
                    5 Query     /*!40100 SET @@SQL_MODE='' */
                    5 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    5 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    5 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 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    5 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 ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    5 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    5 Init DB   test
                    5 Query     SHOW CREATE DATABASE IF NOT EXISTS `test`
                    5 Query     show tables
                    5 Query     LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */
                    5 Query     show table status like 't1'
                    5 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     show create table `t1`
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show fields from `t1`
                    5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     use `test`
                    5 Query     select @@collation_database
                    5 Query     SHOW TRIGGERS LIKE 't1'
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show table status like 't2'
                    5 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     show create table `t2`
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     show fields from `t2`
                    5 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
                    5 Query     SET SESSION character_set_results = 'binary'
                    5 Query     use `test`
                    5 Query     select @@collation_database
                    5 Query     SHOW TRIGGERS LIKE 't2'
                    5 Query     SET SESSION character_set_results = 'utf8'
                    5 Query     UNLOCK TABLES

原理：

加全局读锁(防止备份数据不一致)
LOCK TABLES `t1` READ /*!32311 LOCAL */,`t2` READ /*!32311 LOCAL */
获取建表信息生成建表语句
show create table `t1`
当前字符集会做一些处理，防止乱码
SET SESSION character_set_results = 'utf8'
获取行记录从而转换成insert插入语句
show fields from `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
重复表t1操作
释放读锁
UNLOCK TABLES

开启–single-transaction备份：

# mysqldump -uroot -p --socket=/data/mysql.sock -B test --single-transaction

查看对应的general_log

160728 21:28:41     6 Connect   root@localhost on 
                    6 Query     /*!40100 SET @@SQL_MODE='' */
                    6 Query     /*!40103 SET TIME_ZONE='+00:00' */
                    6 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                    6 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                    6 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                    6 Query     UNLOCK TABLES
                    6 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 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 ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                    6 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 ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                    6 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                    6 Init DB   test
                    6 Query     SHOW CREATE DATABASE IF NOT EXISTS `test`
                    6 Query     SAVEPOINT sp
                    6 Query     show tables
                    6 Query     show table status like 't1'
                    6 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     show create table `t1`
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     show fields from `t1`
                    6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     use `test`
                    6 Query     select @@collation_database
                    6 Query     SHOW TRIGGERS LIKE 't1'
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     ROLLBACK TO SAVEPOINT sp
                    6 Query     show table status like 't2'
                    6 Query     SET SQL_QUOTE_SHOW_CREATE=1
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     show create table `t2`
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     show fields from `t2`
                    6 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
                    6 Query     SET SESSION character_set_results = 'binary'
                    6 Query     use `test`
                    6 Query     select @@collation_database
                    6 Query     SHOW TRIGGERS LIKE 't2'
                    6 Query     SET SESSION character_set_results = 'utf8'
                    6 Query     ROLLBACK TO SAVEPOINT sp
                    6 Query     RELEASE SAVEPOINT sp

原理：

更改当前会话隔离级别为rr
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
开始事务（并生成一致性快照）
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
释放表锁
UNLOCK TABLES
设置一个savepoint
SAVEPOINT sp
查看数据库下的表
show tables
对t1表进行操作，获得表信息、建表语句及数据
show create table `t1`
SET SESSION character_set_results = 'utf8'
show fields from `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
回滚到savepoint sp
ROLLBACK TO SAVEPOINT sp
对其他表重复该过程
最后释放savepoint
RELEASE SAVEPOINT sp

整个备份过程，没有任何锁。RR隔离级别保证在事务中只读取本事务之前的一致性的数据 。 
rollback to savepoint sp，保证了对数据库中的数据没有影响。