MySQL 逻辑备份mysqldump&mysqlpump&mydumper原理解析

目录

---

想弄清除逻辑备份的原理，最好的办法是开启general_log，一探究竟

准备

创建用户

create user if not exists 'test1'@'%'  identified with mysql_native_password by 'test1';
grant create,index,reload,insert,select,update,delete,alter,drop on *.* to 'test1'@'%';

grant reload,select,replication client,view,event  on *.* to 'test1'@'%';

生成10000000条测试数据

sysbench  /usr/local/share/sysbench/oltp_read_write.lua  --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --mysql-user=test1 --mysql-password=test1 --table_size=10000000 --tables=1 --threads=10 --time=120 --report-interval=1 prepare

开启general_log

ip=`/sbin/ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tr -d "addr:" | tail -n 1`;
mysql_port=3306;

mysql -uadmin -padmin -n -b -h${ip} -p$mysql_port -e"set global general_log=on;show variables like 'general_log%'"

mysqldump备份

--single-transaction 启用一致性备份

user=test1
passwd=test1
time mysqldump -u$user -p$passwd -h127.0.0.1 -p3306 --single-transaction --set-gtid-purged=off test sbtest1>/tmp/backup_mydump.sql

备份执行语句

2019-03-25t21:07:46.187423+08:00          427 connect   test1@127.0.0.1 on  using tcp/ip
2019-03-25t21:07:46.187786+08:00          427 query     /*!40100 set @@sql_mode='' */
2019-03-25t21:07:46.187956+08:00          427 query     /*!40103 set time_zone='+00:00' */
2019-03-25t21:07:46.188102+08:00          427 query     set session transaction isolation level repeatable read
2019-03-25t21:07:46.188179+08:00          427 query     start transaction /*!40100 with consistent snapshot */
2019-03-25t21:07:46.188257+08:00          427 query     unlock tables
2019-03-25t21:07:46.188470+08:00          427 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='test' and table_name in ('sbtest1'))) group by logfile_group_name, file_name, engine, total_extents, initial_size order by logfile_group_name
2019-03-25t21:07:46.523648+08:00          427 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='test' and table_name in ('sbtest1')) order by tablespace_name, logfile_group_name
2019-03-25t21:07:46.700383+08:00          427 query     show variables like 'ndbinfo\_version'
2019-03-25t21:07:46.701942+08:00          427 init db   test
2019-03-25t21:07:46.702050+08:00          427 query     show tables like 'sbtest1'
2019-03-25t21:07:46.702255+08:00          427 query     savepoint sp
2019-03-25t21:07:46.702342+08:00          427 query     show table status like 'sbtest1'
2019-03-25t21:07:46.702575+08:00          427 query     set sql_quote_show_create=1
2019-03-25t21:07:46.702678+08:00          427 query     set session character_set_results = 'binary'
2019-03-25t21:07:46.702748+08:00          427 query     show create table `sbtest1`
2019-03-25t21:07:46.702843+08:00          427 query     set session character_set_results = 'utf8'
2019-03-25t21:07:46.702936+08:00          427 query     show fields from `sbtest1`
2019-03-25t21:07:46.703220+08:00          427 query     show fields from `sbtest1`
2019-03-25t21:07:46.703466+08:00          427 query     select /*!40001 sql_no_cache */ * from `sbtest1`
2019-03-25t21:07:46.927679+08:00          427 query     set session character_set_results = 'binary'
2019-03-25t21:07:46.927844+08:00          427 query     use `test`
2019-03-25t21:07:46.927948+08:00          427 query     select @@collation_database
2019-03-25t21:07:46.928062+08:00          427 query     show triggers like 'sbtest1'
2019-03-25t21:07:46.928452+08:00          427 query     set session character_set_results = 'utf8'
2019-03-25t21:07:46.928541+08:00          427 query     rollback to savepoint sp
2019-03-25t21:07:46.928607+08:00          427 query     release savepoint sp
2019-03-25t21:07:46.935931+08:00          427 quit

- 设置会话的隔离级别为rr
- 开启一致性快照事务
- 获取备份表信息
- select from tabledump出数据
- mysqldump中savepoint 用处是什么了？

• 提取释放表上的mdl读锁，每备份完一个表，就释放该表上的mdl读锁（dml加mdl读锁，ddl加mdl写锁，mdl读锁和mdl写锁互斥）
• 若没有savepoint，会等待需要备份的所有表完成后才释放mdl读锁，阻塞ddl的机会大
• mysqlpump和mydumper逻辑备份工具不会加savepoint，大概是这两种工具有多线程并行备份的功能，mysqldump只有单线程备份t_t

添加了--master-data
flush /!40101 local / tables
flush tables with read lock
set session transaction isolation level repeatable read
start transaction /!40100 with consistent snapshot /
show master status
unlock tables

- 先执行flush tables关闭所有打开的表
- 上全局读锁ftwrl
- 设置会话的隔离级别为rr
- 开启一致性快照事务
- 获取master status 已经执行事务信息
- 获取备份表信息
- select from table dump出数据

flush tables

force it to flush any table modifications that are still buffered in memory closes all open tables;
forces all tables in use to be closed, and flushes the prepared statement cache

mysqlpump备份

--single-transaction 开启一致性备份
--default-parallelism 并行备份线程数
--parallel-schemas=[n:]db_list 可对不同的库指定不同的并行备份线程数

user=test1
passwd=test1
time mysqlpump -u$user -p$passwd -h127.0.0.1 -p3306 --default-parallelism=8 --default-character-set=utf8 --set-gtid-purged=off --single-transaction test sbtest1>/tmp/backup_mysqlpump.sql

mysqlpump默认备份会设置备份文件的字符集为utf8mb4（set names utf8mb4），设置--default-character-set可指定备份文件的字符集

• mysqlpump的并行备份是基于不同的表的（即时指定了并行备份，对同一张表也只会有一个线程备份）
• 有多少个并发线程备份，就会建立多少个子线程开启rr隔离级别一致性快照
• 设置了并行备份时，第一个线程会执行ftwrl，再开启rr隔离级别一致性快照；等其它线程都开启了一致性快照后再 unlock tables 取消全局读锁（确保并行备份每个线程得到一致的数据）
• 已经指定了对指定的表进行备份，还会show 其它表的status和其它对象（trigger,event的status），show create table 其它表，和备份整个实例一样
• 表数量多时，会花费更多的时间；在备份文件中却只有指定的表信息
• 备份文件中最后才添加二级索引 alter table test.sbtest1 add key k_1 (k); 加快了导入的速度（减少导入时了维护二级索引的开销）

2019-03-25t21:11:15.524978+08:00 429 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.549169+08:00 429 query flush tables with read lock
2019-03-25t21:11:15.549937+08:00 429 query show warnings
2019-03-25t21:11:15.551858+08:00 429 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.551958+08:00 429 query show warnings
2019-03-25t21:11:15.552031+08:00 429 query start transaction with consistent snapshot
2019-03-25t21:11:15.552106+08:00 429 query show warnings
2019-03-25t21:11:15.552337+08:00 430 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.552453+08:00 430 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.552532+08:00 430 query show warnings
2019-03-25t21:11:15.552597+08:00 430 query start transaction with consistent snapshot
2019-03-25t21:11:15.552663+08:00 430 query show warnings
2019-03-25t21:11:15.552869+08:00 431 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.553070+08:00 431 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.553215+08:00 431 query show warnings
2019-03-25t21:11:15.553330+08:00 431 query start transaction with consistent snapshot
2019-03-25t21:11:15.553432+08:00 431 query show warnings
2019-03-25t21:11:15.553714+08:00 432 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.553833+08:00 432 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.553905+08:00 432 query show warnings
2019-03-25t21:11:15.553986+08:00 432 query start transaction with consistent snapshot
2019-03-25t21:11:15.554048+08:00 432 query show warnings
2019-03-25t21:11:15.554349+08:00 433 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.554485+08:00 433 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.554563+08:00 433 query show warnings
2019-03-25t21:11:15.554635+08:00 433 query start transaction with consistent snapshot
2019-03-25t21:11:15.554698+08:00 433 query show warnings
2019-03-25t21:11:15.554933+08:00 434 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.555058+08:00 434 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.555175+08:00 434 query show warnings
2019-03-25t21:11:15.555243+08:00 434 query start transaction with consistent snapshot
2019-03-25t21:11:15.555301+08:00 434 query show warnings
2019-03-25t21:11:15.555510+08:00 435 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.555702+08:00 435 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.555835+08:00 435 query show warnings
2019-03-25t21:11:15.555963+08:00 435 query start transaction with consistent snapshot
2019-03-25t21:11:15.556083+08:00 435 query show warnings
2019-03-25t21:11:15.556360+08:00 436 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.556486+08:00 436 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.556563+08:00 436 query show warnings
2019-03-25t21:11:15.556628+08:00 436 query start transaction with consistent snapshot
2019-03-25t21:11:15.556689+08:00 436 query show warnings
2019-03-25t21:11:15.558260+08:00 437 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:11:15.558487+08:00 437 query set session transaction isolation level repeatable read
2019-03-25t21:11:15.558647+08:00 437 query show warnings
2019-03-25t21:11:15.558768+08:00 437 query start transaction with consistent snapshot
2019-03-25t21:11:15.558878+08:00 437 query show warnings
2019-03-25t21:11:15.559015+08:00 429 query unlock tables
2019-03-25t21:11:15.559141+08:00 429 query show warnings
2019-03-25t21:11:15.559244+08:00 437 query set sql_quote_show_create= 1
2019-03-25t21:11:15.559370+08:00 437 query show warnings
2019-03-25t21:11:15.559474+08:00 437 query set time_zone='+00:00'
2019-03-25t21:11:15.559578+08:00 437 query show warnings
2019-03-25t21:11:15.559682+08:00 437 query select @@global.gtid_mode
2019-03-25t21:11:15.559794+08:00 437 query show warnings
2019-03-25t21:11:15.559871+08:00 437 query select @@global.gtid_executed
2019-03-25t21:11:15.559993+08:00 437 query show warnings
2019-03-25t21:11:15.560311+08:00 437 query show databases
2019-03-25t21:11:15.645400+08:00 436 query set sql_quote_show_create= 1
2019-03-25t21:11:15.645632+08:00 436 query show warnings
2019-03-25t21:11:15.645744+08:00 436 query set time_zone='+00:00'
2019-03-25t21:11:15.645819+08:00 436 query show warnings
2019-03-25t21:11:16.473856+08:00 437 query show warnings
2019-03-25t21:11:16.474118+08:00 437 query show create database if not exists information_schema
2019-03-25t21:11:16.474206+08:00 437 query show warnings
2019-03-25t21:11:16.474315+08:00 437 query show table status from information_schema
2019-03-25t21:11:16.501656+08:00 437 query show warnings
2019-03-25t21:11:16.501933+08:00 437 query show columns in character_sets from information_schema
2019-03-25t21:11:16.502417+08:00 437 query show warnings
2019-03-25t21:11:16.502531+08:00 437 query show create table information_schema.character_sets
..........

mydumper备份

• mydumper默认会开启一致性快照备份
• 有多少个并发线程备份，就会建立多少个子线程+1（第一个线程加ftwrl和开启一致性快照）
• 设置了多线程并行备份时，第一个线程会执行ftwrl，再开启一致性快照；等其它线程都开启rr隔离级别和一致性快照后并备份完非事务表后（若有）再 unlock tables 取消全局读锁（确保并行备份每个线程得到一致的数据）
• --trx-consistency-only 选项开启后，创建完子线程就就释放ftwrl
• 第一个连接的线程加了全局读锁ftwrl后， 执行show master status, show slave status，获取当前备份 mysql已经执行了的事务gtid快照信息
• 支持多个线程以chunk的方式备份同一个表或多个表
• 添加了-r 参数后，会根据show table status 和select max(id),min(id) from table_name 的结果分成多个chunck，多个线程去dump

使用mydumper8线程并行备份

user=test1
passwd=test1
time mydumper -u $user -p $passwd -h 127.0.0.1 -p 3306 -b test -t sbtest1 -o /tmp -t 8 -r 800000


real    0m12.386s
user    0m5.720s
sys     0m1.729s

du -sh /tmp/test.sbtest1*
161m /tmp/test.sbtest1.00000.sql
162m /tmp/test.sbtest1.00001.sql
162m /tmp/test.sbtest1.00002.sql
162m /tmp/test.sbtest1.00003.sql
162m /tmp/test.sbtest1.00004.sql
162m /tmp/test.sbtest1.00005.sql
162m /tmp/test.sbtest1.00006.sql
162m /tmp/test.sbtest1.00007.sql
162m /tmp/test.sbtest1.00008.sql
162m /tmp/test.sbtest1.00009.sql
162m /tmp/test.sbtest1.00010.sql
162m /tmp/test.sbtest1.00011.sql
4.0k /tmp/test.sbtest1-schema.sql
19m /tmp/test.sbtest1.sql
dump出了多个sql文件

使用mydumper32线程并行备份

user=test1
passwd=test1
time mydumper -u $user -p $passwd -h 127.0.0.1 -p 3306 -b test -t sbtest1 -o /tmp -t 32 -r 800000

real    0m10.531s
user    0m5.684s
sys     0m1.751s

dump出的文件和8线程并行备份一样的

mydumper备份执行语句

user=test1
passwd=test1
time mydumper -u $user -p $passwd -h 127.0.0.1 -p 3306 -b test -t sbtest1 -o /tmp -t 8

2019-03-25t21:37:55.267321+08:00 442 connect test1@127.0.0.1 on test using tcp/ip
2019-03-25t21:37:55.282783+08:00 442 query set session wait_timeout = 2147483
2019-03-25t21:37:55.292308+08:00 442 query set session net_write_timeout = 2147483
2019-03-25t21:37:55.294956+08:00 442 query show processlist
2019-03-25t21:37:55.307138+08:00 442 query flush tables with read lock
2019-03-25t21:37:55.321024+08:00 442 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.347388+08:00 442 query /!40101 set names binary/
2019-03-25t21:37:55.347552+08:00 442 query show master status
2019-03-25t21:37:55.347730+08:00 442 query show slave status
2019-03-25t21:37:55.348652+08:00 443 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.348941+08:00 443 query set session wait_timeout = 2147483
2019-03-25t21:37:55.349067+08:00 443 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.349156+08:00 443 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.349235+08:00 443 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.349299+08:00 443 query /!40101 set names binary/
2019-03-25t21:37:55.349853+08:00 444 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.350084+08:00 444 query set session wait_timeout = 2147483
2019-03-25t21:37:55.350203+08:00 444 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.350298+08:00 444 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.350396+08:00 444 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.350486+08:00 444 query /!40101 set names binary/
2019-03-25t21:37:55.351008+08:00 445 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.351181+08:00 445 query set session wait_timeout = 2147483
2019-03-25t21:37:55.351268+08:00 445 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.351334+08:00 445 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.351404+08:00 445 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.351469+08:00 445 query /!40101 set names binary/
2019-03-25t21:37:55.351891+08:00 446 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.352086+08:00 446 query set session wait_timeout = 2147483
2019-03-25t21:37:55.352223+08:00 446 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.352339+08:00 446 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.352448+08:00 446 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.352549+08:00 446 query /!40101 set names binary/
2019-03-25t21:37:55.353028+08:00 447 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.353172+08:00 447 query set session wait_timeout = 2147483
2019-03-25t21:37:55.353244+08:00 447 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.353301+08:00 447 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.353361+08:00 447 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.353418+08:00 447 query /!40101 set names binary/
2019-03-25t21:37:55.353874+08:00 448 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.354083+08:00 448 query set session wait_timeout = 2147483
2019-03-25t21:37:55.354196+08:00 448 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.354288+08:00 448 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.354380+08:00 448 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.354468+08:00 448 query /!40101 set names binary/
2019-03-25t21:37:55.354912+08:00 449 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.355066+08:00 449 query set session wait_timeout = 2147483
2019-03-25t21:37:55.355151+08:00 449 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.355216+08:00 449 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.355283+08:00 449 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.355346+08:00 449 query /!40101 set names binary/
2019-03-25t21:37:55.355783+08:00 450 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:37:55.355930+08:00 450 query set session wait_timeout = 2147483
2019-03-25t21:37:55.356028+08:00 450 query set session transaction isolation level repeatable read 2019-03-25t21:37:55.356090+08:00 450 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:37:55.356152+08:00 450 query /!40103 set time_zone='+00:00' /
2019-03-25t21:37:55.356211+08:00 450 query /!40101 set names binary/
2019-03-25t21:37:55.356337+08:00 442 init db test
2019-03-25t21:37:55.356459+08:00 442 query show table status
2019-03-25t21:37:55.562238+08:00 442 query show create database test
2019-03-25t21:37:55.562630+08:00 442 query unlock tables /* ftwrl */
2019-03-25t21:37:55.562751+08:00 443 query show create table test.sbtest1
2019-03-25t21:37:55.562864+08:00 449 quit
2019-03-25t21:37:55.562941+08:00 448 quit
2019-03-25t21:37:55.562995+08:00 446 quit
2019-03-25t21:37:55.563010+08:00 445 quit
2019-03-25t21:37:55.562942+08:00 447 quit
2019-03-25t21:37:55.563100+08:00 442 quit
2019-03-25t21:37:55.563207+08:00 450 quit
2019-03-25t21:37:55.563207+08:00 443 quit
2019-03-25t21:37:55.570808+08:00 444 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:37:55.578158+08:00 444 query select /!40001 sql_no_cache / * from test.sbtest1
2019-03-25t21:37:55.711160+08:00 444 quit

注意：由于开启了多线程dump，但没有指定-r参数，dump数据是在一个线程里完成的，dump数据在 unlock tables后进行

user=test1
passwd=test1
mydumper -u $user -p $passwd -h 127.0.0.1 -p 3306 -b test -t sbtest1 -o /tmp -t 8 -r 10000

添加了-r 10000后，会根据show table status 和select max(id),min(id) from table_name 的结果分成多个chunck，多个线程去dump

2019-03-25t21:43:03.921777+08:00 451 connect test1@127.0.0.1 on test using tcp/ip
2019-03-25t21:43:03.922070+08:00 451 query set session wait_timeout = 2147483
2019-03-25t21:43:03.922210+08:00 451 query set session net_write_timeout = 2147483
2019-03-25t21:43:03.922356+08:00 451 query show processlist
2019-03-25t21:43:03.922503+08:00 451 query flush tables with read lock
2019-03-25t21:43:03.923579+08:00 451 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.924068+08:00 451 query /!40101 set names binary/
2019-03-25t21:43:03.924171+08:00 451 query show master status
2019-03-25t21:43:03.924283+08:00 451 query show slave status
2019-03-25t21:43:03.924998+08:00 452 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.925181+08:00 452 query set session wait_timeout = 2147483
2019-03-25t21:43:03.925291+08:00 452 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.925394+08:00 452 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.925490+08:00 452 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.925577+08:00 452 query /!40101 set names binary/
2019-03-25t21:43:03.926146+08:00 453 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.926293+08:00 453 query set session wait_timeout = 2147483
2019-03-25t21:43:03.926392+08:00 453 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.926476+08:00 453 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.926568+08:00 453 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.926652+08:00 453 query /!40101 set names binary/
2019-03-25t21:43:03.930841+08:00 454 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.940335+08:00 454 query set session wait_timeout = 2147483
2019-03-25t21:43:03.940486+08:00 454 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.940559+08:00 454 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.940642+08:00 454 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.940730+08:00 454 query /!40101 set names binary/
2019-03-25t21:43:03.941518+08:00 455 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.941734+08:00 455 query set session wait_timeout = 2147483
2019-03-25t21:43:03.941876+08:00 455 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.941990+08:00 455 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.942145+08:00 455 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.942245+08:00 455 query /!40101 set names binary/
2019-03-25t21:43:03.942805+08:00 456 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.942954+08:00 456 query set session wait_timeout = 2147483
2019-03-25t21:43:03.943121+08:00 456 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.943200+08:00 456 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.943278+08:00 456 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.943351+08:00 456 query /!40101 set names binary/
2019-03-25t21:43:03.943881+08:00 457 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.944053+08:00 457 query set session wait_timeout = 2147483
2019-03-25t21:43:03.944154+08:00 457 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.944215+08:00 457 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.944275+08:00 457 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.944333+08:00 457 query /!40101 set names binary/
2019-03-25t21:43:03.944825+08:00 458 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.944986+08:00 458 query set session wait_timeout = 2147483
2019-03-25t21:43:03.945127+08:00 458 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.945203+08:00 458 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.945272+08:00 458 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.945332+08:00 458 query /!40101 set names binary/
2019-03-25t21:43:03.945950+08:00 459 connect test1@127.0.0.1 on using tcp/ip
2019-03-25t21:43:03.946096+08:00 459 query set session wait_timeout = 2147483
2019-03-25t21:43:03.946183+08:00 459 query set session transaction isolation level repeatable read
2019-03-25t21:43:03.946243+08:00 459 query start transaction /*!40108 with consistent snapshot */
2019-03-25t21:43:03.946302+08:00 459 query /!40103 set time_zone='+00:00' /
2019-03-25t21:43:03.946364+08:00 459 query /!40101 set names binary/
2019-03-25t21:43:03.946520+08:00 451 init db test
2019-03-25t21:43:03.946640+08:00 451 query show table status
2019-03-25t21:43:03.955079+08:00 451 query show create database test
2019-03-25t21:43:03.955361+08:00 451 query show index from test.sbtest1
2019-03-25t21:43:03.955579+08:00 451 query select /!40001 sql_no_cache / min(id),max(id) from test.sbtest1
2019-03-25t21:43:03.955963+08:00 451 query explain select id from test.sbtest1
2019-03-25t21:43:03.976352+08:00 457 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976389+08:00 454 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976402+08:00 453 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976424+08:00 456 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976520+08:00 455 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976779+08:00 457 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 11113 and id < 22225)
2019-03-25t21:43:03.976857+08:00 451 query unlock tables /* ftwrl */
2019-03-25t21:43:03.976875+08:00 452 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.976912+08:00 458 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.977184+08:00 459 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:03.977209+08:00 456 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 33337 and id < 44449)
2019-03-25t21:43:03.977274+08:00 455 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 44449 and id < 55561)
2019-03-25t21:43:03.977299+08:00 454 query select /!40001 sql_no_cache / * from test.sbtest1 where id is null or (id >= 1 and id < 11113)
2019-03-25t21:43:03.976916+08:00 453 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 22225 and id < 33337)
2019-03-25t21:43:03.977619+08:00 458 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 66673 and id < 77785)
2019-03-25t21:43:03.977663+08:00 452 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 55561 and id < 66673)
2019-03-25t21:43:03.977670+08:00 459 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 77785 and id < 88897)
2019-03-25t21:43:03.987751+08:00 457 query select column_name from information_schema.columns where table_schema='test' and table_name='sbtest1' and extra like '%generated%'
2019-03-25t21:43:04.003411+08:00 455 query show create table test.sbtest1
2019-03-25t21:43:04.009033+08:00 451 quit
2019-03-25t21:43:04.009160+08:00 454 quit
2019-03-25t21:43:04.012462+08:00 455 quit
2019-03-25t21:43:04.012600+08:00 457 query select /!40001 sql_no_cache / * from test.sbtest1 where (id >= 88897 and id < 100009)
2019-03-25t21:43:04.017057+08:00 456 quit
2019-03-25t21:43:04.017188+08:00 452 quit
2019-03-25t21:43:04.018227+08:00 458 quit
2019-03-25t21:43:04.019988+08:00 459 quit
2019-03-25t21:43:04.020469+08:00 453 quit
2019-03-25t21:43:04.025336+08:00 457 quit

参考