想弄清除逻辑备份的原理,最好的办法是开启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%'"
--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 用处是什么了?
添加了--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
--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可指定备份文件的字符集
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
..........
使用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 QuerySTART 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 QuerySHOW 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.925394+08:00 452 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.926476+08:00 453 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.940559+08:00 454 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.941990+08:00 455 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.943200+08:00 456 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.944215+08:00 457 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.945203+08:00 458 QuerySTART 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 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2019-03-25T21:43:03.946243+08:00 459 QuerySTART 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 DATABASEtest
2019-03-25T21:43:03.955361+08:00 451 Query SHOW INDEX FROMtest
.sbtest1
2019-03-25T21:43:03.955579+08:00 451 Query SELECT /!40001 SQL_NO_CACHE / MIN(id
),MAX(id
) FROMtest
.sbtest1
2019-03-25T21:43:03.955963+08:00 451 Query EXPLAIN SELECTid
FROMtest
.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 / * FROMtest
.sbtest1
WHERE (id
>= 11113 ANDid
< 22225) 2019-03-25T21:43:03.976857+08:00 451 QueryUNLOCK 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 / * FROMtest
.sbtest1
WHERE (id
>= 33337 ANDid
< 44449) 2019-03-25T21:43:03.977274+08:00 455 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHERE (id
>= 44449 ANDid
< 55561) 2019-03-25T21:43:03.977299+08:00 454 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHEREid
IS NULL OR (id
>= 1 ANDid
< 11113) 2019-03-25T21:43:03.976916+08:00 453 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHERE (id
>= 22225 ANDid
< 33337) 2019-03-25T21:43:03.977619+08:00 458 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHERE (id
>= 66673 ANDid
< 77785) 2019-03-25T21:43:03.977663+08:00 452 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHERE (id
>= 55561 ANDid
< 66673) 2019-03-25T21:43:03.977670+08:00 459 Query SELECT /!40001 SQL_NO_CACHE / * FROMtest
.sbtest1
WHERE (id
>= 77785 ANDid
< 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 TABLEtest
.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 / * FROMtest
.sbtest1
WHERE (id
>= 88897 ANDid
< 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