sybench压测下模拟误truncate数据恢复

基本环境：官方社区版MySQL 5.7.21 Row+Gtid
开启sysbench压测，使用mysqldump备份数据库，执行truncate操作，恢复数据到truncate前的时间点
1、切换日志，记录当前位置

# 3306切换日志
mydba@192.168.85.132,3306 [sbtest]> flush binary logs;
Query OK, 0 rows affected (0.07 sec)
# 3306查看当前位置
mydba@192.168.85.132,3306 [sbtest]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000013 |      194 |              |                  | 60863f8d-01af-11e8-bfdf-000c29c1025c:1-185088 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
row in set (0.00 sec)

2、开启sysbench压测

[root@ZST1 ~]# sysbench --version
sysbench 1.0.10
[root@ZST1 ~]# sysbench --threads=4 --events=0 --time=100 --rate=0 --report-interval=1 \
--db-driver=mysql --db-ps-mode=disable \
--mysql-host=192.168.85.132 --mysql-port=3306 --mysql-user=mydba --mysql-password=123456 \
--mysql-db=sbtest --tables=5 --table-size=10000 \
oltp_read_write run


================================ 后期补充 ================================
#下载安装
wget --content-disposition https://packagecloud.io/akopytov/sysbench/packages/el/7/sysbench-1.0.10-1.el7.x86_64.rpm/download.rpm
yum localinstall sysbench-1.0.10-1.el7.x86_64.rpm

# sysbench命令语法
sysbench [options]... [testname] [command]
● testname：oltp_*.lua、fileio、cpu、memory、threads、mutex
● command：prepare、run、cleanup、help

/usr/share/sysbench/  sysbench 1.0.x 建议使用这个目录下的lua脚本
/usr/share/sysbench/tests/include/oltp_legacy/  保留兼容旧版本写法的lua脚本

$ cat sysbench.sh 
#!/bin/bash
## sh sysbench.sh port command

port=$1
command=$2

#适合于sysbench 1.0.x
sysbench --threads=16 --events=0 --time=0 --rate=0 --report-interval=1 \
--db-driver=mysql --db-ps-mode=disable \
--mysql-host=127.0.0.1 --mysql-port=${port} --mysql-user=mydba --mysql-password=123456 \
--mysql-db=sbtest --tables=10 --table-size=100000 \
oltp_read_write ${command}

##适用于old
#sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
#--mysql-host=127.0.0.1 --mysql-port=${port} --mysql-user=mydba --mysql-password=123456 \
#--db-driver=mysql --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=100000 \
#--oltp-read-only=off --oltp-test-mode=complex --rand-type=uniform --rand-init=on \
#--threads=16 --rate=0 --time=0 --events=0 --report-interval=1 --percentile=99 ${command}
================================ 后期补充 ================================

先prepare再run，窗口有输出后，使用mysqldump进行备份。sysbench详细使用参考https://github.com/akopytov/sysbench
3、使用mysqldump备份数据库

# 3306备份数据
[root@ZST1 ~]# mysqldump --login-path=1323306 --single-transaction --master-data=2 -A >/data/backup/all_dump_1323306_`date +%Y%m%d`.sql

sysbench创建的表为innodb引擎，在本例中使用mysqldump能确保备份数据的一致性。详细说明参考：MySQL备份可能遇到的坑
4、删除数据

# 3306删除数据
mydba@192.168.85.132,3306 [sbtest]> truncate table sbtest1;

执行truncate操作后恍然发现数据删除错误，现在需要将数据恢复到truncate前的时间点(⊙_⊙)
首先我们使用第3步的备份恢复到较近的时间点，结合备份之后产生的binary log恢复到truncate时刻。这里为了方便，仅恢复sbtest数据库到3308实例
5、单库恢复

# 单库恢复(手动将gtid_purged变量注释)
[root@ZST1 backup]# mysql --login-path=1323308 sbtest -o </data/backup/all_dump_1323306_`date +%Y%m%d`.sql
ERROR 1049 (42000) at line 936: Unknown database 'replcrash'

为什么会报这个错？
-o, --one-database：Ignore statements except those that occur while the default database is the one named at the command line.
指定-o参数，仅执行默认数据库(use dbname)与命令行指定dbname相同的脚本
查看备份出来脚本

# vim打开备份脚本搜索关键字replcrash
[root@ZST1 backup]# vim /data/backup/all_dump_1323306_`date +%Y%m%d`.sql
..
 931 -- Current Database: `replcrash`
 932 --
 933 
 934 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `replcrash` /*!40100 DEFAULT CHARACTER SET utf8 */;
 935 
 936 USE `replcrash`;
 937 
 938 --
 939 -- Table structure for table `py_user`
 940 --
 941 
 942 DROP TABLE IF EXISTS `py_user`;
...

虽然有create database replcrash语句，但是它没在use sbtest下面，那么它的默认数据库与命令行中的不同，因此create database replcrash不会执行。紧接着后面一句use replcrash就会找不到对应db，在3308实例创建备份文件中包含的用户数据库

# 创建用户数据库
mydba@192.168.85.132,3308 [sbtest]> create database replcrash;
mydba@192.168.85.132,3308 [sbtest]> create database sbtest;
# 重新恢复
[root@ZST1 backup]# mysql --login-path=1323308 sbtest -o </data/backup/all_dump_1323306_`date +%Y%m%d`.sql

等待片刻，上述命令仅恢复use sbtest里面的脚本
将恢复出来的sbtest.sbtest1拷贝一份数据到sbtest_bak.sbtest1

# 拷贝一份全备点的数据
mydba@192.168.85.132,3308 [sbtest]> create table sbtest_bak.sbtest1 select * from sbtest1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
mydba@192.168.85.132,3308 [sbtest]> create table sbtest_bak.sbtest1 like sbtest1;
Query OK, 0 rows affected (0.02 sec)
mydba@192.168.85.132,3308 [sbtest]> insert into sbtest_bak.sbtest1 select * from sbtest1;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

# 后续可以检测数据的一致性 
[root@ZST1 ~]# mysqldbcompare --server1=mydba:mysql5721@192.168.85.132:3308 --server2=mydba:mysql5721@192.168.85.132:3308 --changes-for=server2 --difftype=sql sbtest:sbtest_bak --run-all-tests

6、找到truncate的位置

# 3306解析truncate位置
[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000013
...
COMMIT/*!*/;
# at 14270546
#180202 15:05:12 server id 1323306  end_log_pos 14270611 CRC32 0x582ae6bb       GTID    last_committed=8454     sequence_number=8455    rbr_only=no
SET @@SESSION.GTID_NEXT= '60863f8d-01af-11e8-bfdf-000c29c1025c:193543'/*!*/;
# at 14270611
#180202 15:05:12 server id 1323306  end_log_pos 14270702 CRC32 0xe3231860       Query   thread_id=7     exec_time=0     error_code=0
use `sbtest`/*!*/;
SET TIMESTAMP=1517555112/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
truncate table sbtest1
/*!*/;
# at 14270702
#180202 15:05:12 server id 1323306  end_log_pos 14270767 CRC32 0xaa411397       GTID    last_committed=8455     sequence_number=8456    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '60863f8d-01af-11e8-bfdf-000c29c1025c:193544'/*!*/;
# at 14270767

# 3306查看备份位置
[root@ZST1 backup]# more /data/backup/all_dump_1323306_`date +%Y%m%d`.sql
...
-- GTID state at the beginning of the backup 
-- SET @@GLOBAL.GTID_PURGED='60863f8d-01af-11e8-bfdf-000c29c1025c:1-188148';

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=5165474;

因此备份点到truncate之间需补充的数据为60863f8d-01af-11e8-bfdf-000c29c1025c:188149-193542
mysql-bin.000013 --start-position=5165474 --stop-position=14270546
7、恢复数据到truncate前的位置

# 恢复数据到truncate前的位置
[root@ZST1 logs]# mysqlbinlog mysql-bin.000013 --start-position=5165474 --stop-position=14270546 |mysql --login-path=1323308

此时3308实例已恢复到truncate操作点的一致性状态，那是不是就到此结束呢？
如果我们只需要还原数据到truncate时间点，那差不多完成~
如果我们需要跳过误删除的命令，其他操作继续，那就得考虑是否存在关联误删除的操作，以及这些操作对线上数据的影响是否能接受~
跳过误删除操作，应用后续日志

# 误删除对应的事务
60863f8d-01af-11e8-bfdf-000c29c1025c:193543
mysql-bin.000013 --start-position=14270546 --stop-position=14270702

# 跳过误删除操作，应用后续日志
[root@ZST1 logs]# mysqlbinlog mysql-bin.000013 --start-position=14270702 |mysql --login-path=1323308
ERROR 1062 (23000) at line 34: Duplicate entry '21' for key 'PRIMARY'
[root@ZST1 logs]#

为什么会有重复条目？sysbench逻辑如下：
sysbench prepare：按照指定table-size往表sbtest*写入数据
sysbench run：根据key值(key受限于table-size)执行一系列的update、delete、insert操作，并且每一个delete..where key=keydel后面紧接着insert..values(keydel)，每一次删除一条记录，马上写入相同键值的记录
sysbench cleanup：删除sbtest*表
由于binlog_format='row'，在truncate后执行的delete没有找到对应记录，就不会记录binlog，但紧接着的insert却记录到binlog。在我们还原数据到truncate时间点前，应用truncate后的binlog时，在执行insert前没有delete操作时就会遇到Duplicat entry的现象。
如果binlog_format='statement'就不会出现这种情况(上述操作都能进行)，但这并不影响我们推荐使用row格式~