Mysqldump --single-transaction 选项解析

查看general_log：

      206 Query    SETSESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

     ##更改隔离级别（采取快照，保证了事务中一致性读）

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

         ##事务开始

      206 Query    UNLOCKTABLES

      206 Init DB  sanguo_single

      206 Query    SAVEPOINTsp

          ##设置保存点

      206 Query    showtables

      206 Query    showtable status like ‘achievements‘4

      206 Query    SETSQL_QUOTE_SHOW_CREATE=15

      206 Query    SETSESSION character_set_results = ‘binary‘5

      206 Query    showcreate table `achievements`

          ##获取表结构

      206 Query    SETSESSION character_set_results = ‘utf8‘

            ##设置备份参数

      206 Query    showfields from `achievements`

      206 Query    SELECT/*!40001 SQL_NO_CACHE */ * FROM `achievements`6

     ##获取表的数据

      206 Query    SETSESSION character_set_results = ‘binary‘

      206 Query    use`sanguo_single`

      206 Query    select@@collation_database

      206 Query    SHOWTRIGGERS LIKE ‘achievements‘

      206 Query    SETSESSION character_set_results = ‘utf8‘

      206  Query     ROLLBACK TOSAVEPOINT sp

           ##回滚保存点（撤销select对于achievements表的影响）

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

## achievements表备份结束，其他表以此类推。可以看出--single-transaction是一张一张表进行备份然后一一进行撤销select

二、实验

1. --single-transaction 选项备份时执行dml语句

------------

TRANSACTIONS

------------

Trx idcounter 12887

Purgedone for trx‘s n:o < 12794 undo n:o < 0 state: running but idle

Historylist length 639

LIST OFTRANSACTIONS FOR EACH SESSION:

---TRANSACTION0, not started

MySQLthread id 178, OS thread handle 0x7f61312f1700, query id 48564 localhost rootinit

showengine innodb status

---TRANSACTION12793, not started

MySQLthread id 208, OS thread handle 0x7f608bfff700, query id 39534 localhost rootcleaning up

---TRANSACTION12886, ACTIVE 4 sec starting index read

mysqltables in use 1, locked 1

20188lock struct(s), heap size 1799720, 64197 row lock(s)

MySQL thread id 210, OS thread handle 0x7f613122e700, queryid 48563 localhost root updating   ###Delete没有被锁住，然后他对player_pve_battles加了个锁。

deletefrom player_pve_battles

---TRANSACTION12792, ACTIVE 625 sec

MySQLthread id 207, OS thread handle 0x7f61312b0700, query id 39535 localhost rootcleaning up

Trx read view will not see trx with id >= 12793, sees< 12793

2. --single-transaction 选项备份时执行ddl语句

结论：因为-single-transaction选项备份时涉及到的是select语句，所以dml语句是被允许的，ddl不被允许直到回滚保存点撤销select 语句

本文出自 “业精于勤而荒于嬉” 博客，转载请与作者联系！

Mysqldump --single-transaction 选项解析

标签：mysql   mysqldump   innodb