mysql导出数据lock tables_mysqldump --single-transaction 和--lock-tables参数详解

mysqldump的备份原理

mysqldump在备份过程中，是采用查询备份相关表的数据，然后导出，拼接成insert语句的形式进行备份。

关于--single-transaction 和--lock-tables

--single-transaction选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交

相关探究实验

1.开启mysql general log

2.对数据库进行备份

/usr/local/mysql/bin/mysqldump -uroot -p -h 127.0.0.1 --hex-blob -A --events --ignore-table=mysql.events --single-transaction --master-data=2 >/tmp/20160304alls.sql

在不同参数的配合下，备份方式有所不同，因为mysqldump是查询数据库的备份方式，所以我们开启general-log来查看mysqldump究竟如何完成备份过程。在备份过程中，获取对应的部分备份的执行的语句列表进行分析

--master-data=2

备份头部：

9:51:09    25646 Connect    root@127.0.0.1 on

25646 Query    /*!40100 SET @@SQL_MODE='' */

25646 Query    /*!40103 SET TIME_ZONE='+00:00' */

25646 Query    FLUSH /*!40101 LOCAL */ TABLES

25646 Query    FLUSH TABLES WITH READ LOCK

25646 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

25646 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

25646 Query    SHOW VARIABLES LIKE 'gtid\_mode'

25646 Query    SHOW MASTER STATUS

25646 Query    UNLOCK TABLES

--master-data=2的作用：

这段代码，通过FLUSH TABLES WITH READ LOCK ,然后SHOW MASTER STATUS，再然后UNLOCK TABLES，在这个过程中获取到了master在lock时刻的binlog，获取binlog位置的过程一定是在加锁后，这样才可以准确获取响应的binlog

位置，而这个位置，会在备份文件的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=655717;来体现，非常适合用于点对点的备份恢复 (--master-data=2)

--single-transaction

5646 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

25646 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

25646 Query    SHOW VARIABLES LIKE 'gtid\_mode'

25646 Query    SHOW MASTER STATUS

25646 Query    UNLOCK TABLES

25646 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 GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

25646 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

25646 Query    SHOW DATABASES

25646 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'

25646 Init DB    hive

25646 Query    SHOW CREATE DATABASE IF NOT EXISTS `hive`

25646 Query    SAVEPOINT sp

25646 Query    show tables

25646 Query    show table status like 'bucketing\_cols'

25646 Query    SET SQL_QUOTE_SHOW_CREATE=1

25646 Query    SET SESSION character_set_results = 'binary'

25646 Query    show create table `bucketing_cols`

25646 Query    SET SESSION character_set_results = 'utf8'

25646 Query    show fields from `bucketing_cols`

25646 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `bucketing_cols`

25646 Query    SET SESSION character_set_results = 'binary'

25646 Query    use `hive`

25646 Query    select @@collation_database

25646 Query    SHOW TRIGGERS LIKE 'bucketing\_cols'

25646 Query    SET SESSION character_set_results = 'utf8'

25646 Query    ROLLBACK TO SAVEPOINT sp

在搭配--single-transaction 参数后，会在FLUSH TABLES WITH READ LOCK 后添加START TRANSACTION 语句，用来开启单一事务 ，这个时候的加锁，仅仅是为了确定master-data中的binlog的具体位置和开启事务，开启事务后，就已经把读锁释放了，而且在由日志可以看出，在日志的回滚过程中，是回滚到单一的TRANSACTION，也是sp点，每次进行对表和参数的改动后，都会对事务进行回滚。 通过对备份log的分析，可以发现，所有的的备份阶段完成后，都是rollback到sp点的。也就是返回到SAVEPOINT sp时间点，也就是备份完成后，实际上备份库仍然是在sp点，而这个所谓的sp回滚，其实是调用的undo中的数据快照来实现的。

使用--single-transaction 可以保证在备份过程中，整个备份集的数据一致性。

--lock-tables

14707 Query    SET autocommit=0

14707 Query    SELECT @@session.tx_isolation

14707 Query    rollback

14707 Query    SET autocommit=1

14707 Query    set session transaction read write

14711 Query    select @@session.tx_read_only

14711 Query    SET autocommit=0

14711 Query    SELECT @@session.tx_isolation

14711 Query    SELECT 'org.apache.hadoop.hive.metastore.model.MDatabase' AS `NUCLEUS_TYPE`,`A0`.`DESC`,`A0`.`DB_LOCATION_URI`,`A0`.`NAME`,`A0`.`OWNER_NAME`,`A0`.`OWNER_TYPE`,`A0`.`DB_ID` FROM `DBS` `A0` WHERE `A0`.`NAME` = ''

14711 Query    SELECT 'org.apache.hadoop.hive.metastore.model.MTableColumnStatistics' AS `NUCLEUS_TYPE`,`A0`.`AVG_COL_LEN`,`A0`.`COLUMN_NAME`,`A0`.`COLUMN_TYPE`,`A0`.`DB_NAME`,`A0`.`BIG_DECIMAL_HIGH_VALUE`,`A0`.`BIG_DECIMAL_LOW_VALUE`,`A0`.`DOUBLE_HIGH_VALUE`,`A0`.`DOUBLE_LOW_VALUE`,`A0`.`LAST_ANALYZED`,`A0`.`LONG_HIGH_VALUE`,`A0`.`LONG_LOW_VALUE`,`A0`.`MAX_COL_LEN`,`A0`.`NUM_DISTINCTS`,`A0`.`NUM_FALSES`,`A0`.`NUM_NULLS`,`A0`.`NUM_TRUES`,`A0`.`TABLE_NAME`,`A0`.`CS_ID` FROM `TAB_COL_STATS` `A0` WHERE `A0`.`DB_NAME` = ''

14711 Query    SELECT 'org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics' AS `NUCLEUS_TYPE`,`A0`.`AVG_COL_LEN`,`A0`.`COLUMN_NAME`,`A0`.`COLUMN_TYPE`,`A0`.`DB_NAME`,`A0`.`BIG_DECIMAL_HIGH_VALUE`,`A0`.`BIG_DECIMAL_LOW_VALUE`,`A0`.`DOUBLE_HIGH_VALUE`,`A0`.`DOUBLE_LOW_VALUE`,`A0`.`LAST_ANALYZED`,`A0`.`LONG_HIGH_VALUE`,`A0`.`LONG_LOW_VALUE`,`A0`.`MAX_COL_LEN`,`A0`.`NUM_DISTINCTS`,`A0`.`NUM_FALSES`,`A0`.`NUM_NULLS`,`A0`.`NUM_TRUES`,`A0`.`PARTITION_NAME`,`A0`.`TABLE_NAME`,`A0`.`CS_ID` FROM `PART_COL_STATS` `A0` WHERE `A0`.`DB_NAME` = ''

14711 Query    commit

14711 Query    rollback

14711 Query    SET autocommit=1

14711 Query    set session transaction read write

--lock-tables

从日志可以看出，在采用该参数后，在每次的不同备份id的备份过程中，都会先开启一个事务，然后再提交，这样在整个备份过程中，会有多次的事务开启与提交过程，在备份过程中的大量事务的开启与关闭，这样势必会和带--single-transaction出现差距 。

我们可以得到如下结论：

1. 带--lock-tables参数的备份过程在时间上会比较长，而且这种时间的差距，在大表的时候会表现的更加明显

2. 带--lock-tables参数后，只会影响对应备份过程中的表，而不会对备份过后的表有所影响，但是在--single-transaction过程中，因为单一事务的检查点已经在刚开始时候就已经被确定为sp，所有的备份过程的回滚过程都是sp点，所以，即使备份完成后的表，也会受到事务内相关锁的影响。因此可见在--single-transaction备份过程中，虽然备份速度有所加快，但是对于数据库的影响时间也是比较长的。

3.带--lock-tables参数的情况下，并不能保证全库备份情况下的数据一致性。即所有的数据，不一定是在一个时间点的数据。

--lock-all-tables

14707 Query     SET autocommit=0

14707 Query     SELECT @@session.tx_isolation

14707 Query     rollback

14707 Query     SET autocommit=1

14707 Query     set session transaction read write

14710 Query     select @@session.tx_read_only

14710 Query     SET autocommit=0

14710 Query     SELECT @@session.tx_isolation

14710 Query     SELECT 'org.apache.hadoop.hive.metastore.model.MDatabase' AS `NUCLEUS_TYPE`,`A0`.`DESC`,`A0`.`DB_LOCATION_URI`,`A0`.`NAME`,`A0`.`OWNER_NAME`,`A0`.`OWNER_TYPE`,`A0`.`DB_ID` FROM `DBS` `A0` WHERE `A0`.`NAME` = ''

14710 Query     SELECT 'org.apache.hadoop.hive.metastore.model.MTableColumnStatistics' AS `NUCLEUS_TYPE`,`A0`.`AVG_COL_LEN`,`A0`.`COLUMN_NAME`,`A0`.`COLUMN_TYPE`,`A0`.`DB_NAME`,`A0`.`BIG_DECIMAL_HIGH_VALUE`,`A0`.`BIG_DECIMAL_LOW_VALUE`,`A0`.`DOUBLE_HIGH_VALUE`,`A0`.`DOUBLE_LOW_VALUE`,`A0`.`LAST_ANALYZED`,`A0`.`LONG_HIGH_VALUE`,`A0`.`LONG_LOW_VALUE`,`A0`.`MAX_COL_LEN`,`A0`.`NUM_DISTINCTS`,`A0`.`NUM_FALSES`,`A0`.`NUM_NULLS`,`A0`.`NUM_TRUES`,`A0`.`TABLE_NAME`,`A0`.`CS_ID` FROM `TAB_COL_STATS` `A0` WHERE `A0`.`DB_NAME` = ''

14710 Query     SELECT 'org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics' AS `NUCLEUS_TYPE`,`A0`.`AVG_COL_LEN`,`A0`.`COLUMN_NAME`,`A0`.`COLUMN_TYPE`,`A0`.`DB_NAME`,`A0`.`BIG_DECIMAL_HIGH_VALUE`,`A0`.`BIG_DECIMAL_LOW_VALUE`,`A0`.`DOUBLE_HIGH_VALUE`,`A0`.`DOUBLE_LOW_VALUE`,`A0`.`LAST_ANALYZED`,`A0`.`LONG_HIGH_VALUE`,`A0`.`LONG_LOW_VALUE`,`A0`.`MAX_COL_LEN`,`A0`.`NUM_DISTINCTS`,`A0`.`NUM_FALSES`,`A0`.`NUM_NULLS`,`A0`.`NUM_TRUES`,`A0`.`PARTITION_NAME`,`A0`.`TABLE_NAME`,`A0`.`CS_ID` FROM `PART_COL_STATS` `A0` WHERE `A0`.`DB_NAME` = ''

14710 Query     commit

14710 Query     rollback

14710 Query     SET autocommit=1

14710 Query     set session transaction read write

虽然官方说，--lock-all-tables对所有的表都进行了加锁，但是我发现，其加锁过程和--lock-tables几乎是一致的。

如果你感觉上面讲的很乱，看结论就行了：

总结：

1.要实现点对点的备份恢复，或者对于某点的数据完整备份，必须使用--single-transaction 和--master-data=2两个参数，比如主从搭建时候的，因为在--single-transaction参数中，没可以保证数据备份的备份时间点的事务一致性

2.--single-transaction备份对于数据库的影响时间比较长，影响范围比较大 ，但是加入该参数后，对于多张大表的备份速度肯定会加快。

3.--lock-all-tables并不能保持所有数据对于某个时间点的一致性。