想弄清除逻辑备份的原理，最好的办法是开启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