mysql5.7 闪回数据（update delete insert）
本次测试用Myflash闪回dml操作，有个前提条件是log_bin开启并且log模式是row；
mysql> show global variables like "binlog%"; +--------------------------------------------+--------------+ | Variable_name | Value | +--------------------------------------------+--------------+ | binlog_cache_size | 32768 | | binlog_checksum | NONE | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | +--------------------------------------------+--------------+ 15 rows in set (0.00 sec) mysql> show variables like 'log_%'; +----------------------------------------+-----------------------------+ | Variable_name | Value | +----------------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /data/mysql/mysql-bin | | log_bin_index | /data/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_builtin_as_identified_by_password | OFF | | log_error | /data/log/mysql-error.log | | log_error_verbosity | 3 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | ON | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_statements_unsafe_for_binlog | ON | | log_syslog | OFF | | log_syslog_facility | daemon | | log_syslog_include_pid | ON | | log_syslog_tag | | | log_throttle_queries_not_using_indexes | 0 | | log_timestamps | UTC | | log_warnings | 2 | +----------------------------------------+-----------------------------+ 21 rows in set (0.00 sec)
安装myflash
git clone https://github.com/Meituan-Dianping/MyFlash.git myflash依赖glibc查询
yum install -y gcc pkg-config glib2 libgnomeui-devel [root@redis01 MyFlash]# rpm -qa|grep glibc glibc-headers-2.12-1.209.0.1.el6.x86_64 glibc-2.12-1.209.0.1.el6.x86_64 glibc-common-2.12-1.209.0.1.el6.x86_64 glibc-devel-2.12-1.209.0.1.el6.x86_64
[root@mysql01 MyFlash]# rpm -qa|grep glib2
glib2-2.56.1-9.el7_9.x86_64
glib2-devel-2.56.1-9.el7_9.x86_64
动态编译 gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback [root@redis01 binary]# ./flashback -h Usage: flashback [OPTION...] Help Options: -h, --help Show help options Application Options: --databaseNames databaseName to apply. if multiple, seperate by comma(,) --tableNames tableName to apply. if multiple, seperate by comma(,) --start-position start position --stop-position stop position --start-datetime start time (format %Y-%m-%d %H:%M:%S) --stop-datetime stop time (format %Y-%m-%d %H:%M:%S) --sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,) --maxSplitSize max file size after split, the uint is M --binlogFileNames binlog files to process. if multiple, seperate by comma(,) --outBinlogFileNameBase output binlog file name base --logLevel log level, available option is debug,warning,error --include-gtids gtids to process --exclude-gtids gtids to skip
生成100万行数据
[root@redis01 sysbench]# sysbench oltp_common --tables=1 --table-size=1000000 --db-driver=mysql --mysql-db=ht --mysql-user=root --mysql-password=ocm123 prepare sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 1000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'...
查看binglog的pos
mysql> reset master; Query OK, 0 rows affected (0.17 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 150 | +------------------+-----------+ 1 row in set (0.00 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 190803685 | +------------------+-----------+ 1 row in set (0.00 sec)
查看表中数据
mysql> select * from ht.sbtest1 where id < 10; +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 499284 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 | | 2 | 501969 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 | 23183251411-36241541236-31706421314-92007079971-60663066966 | | 3 | 504261 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 | 38615512647-91458489257-90681424432-95014675832-60408598704 | | 4 | 502014 | 37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498 | 63947013338-98809887124-59806726763-79831528812-45582457048 | | 5 | 499813 | 44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783 | 34551750492-67990399350-81179284955-79299808058-21257255869 | | 6 | 500585 | 37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105 | 05161542529-00085727016-35134775864-52531204064-98744439797 | | 7 | 501371 | 33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045 | 91798303270-64988107984-08161247972-12116454627-22996445111 | | 8 | 499938 | 73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059 | 76460662325-41613089656-42706083314-81833284991-17063140920 | | 9 | 527268 | 26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653 | 30508501104-50823269125-88107014550-70202920684-95842308929 | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 9 rows in set (0.00 sec)
更新行，并且查看binglog
mysql> update ht.sbtest1 set pad='qdds'; Query OK, 1000000 rows affected (33.62 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0 mysql> select * from ht.sbtest1 where id < 10; +----+--------+-------------------------------------------------------------------------------------------------------------------------+------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+------+ | 1 | 499284 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | qdds | | 2 | 501969 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 | qdds | | 3 | 504261 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 | qdds | | 4 | 502014 | 37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498 | qdds | | 5 | 499813 | 44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783 | qdds | | 6 | 500585 | 37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105 | qdds | | 7 | 501371 | 33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045 | qdds | | 8 | 499938 | 73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059 | qdds | | 9 | 527268 | 26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653 | qdds | +----+--------+-------------------------------------------------------------------------------------------------------------------------+------+ 9 rows in set (0.43 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 517083888 | +------------------+-----------+ 1 row in set (0.00 sec)
准备脚本闪回
[root@redis01 binary]# cat 1.sh
echo `date`
export MYSQL_PWD=ocm123
./flashback --databaseNames=ht --tableNames=sbtest1 --sqlTypes=update --binlogFileNames=/data/mysql/mysql-bin.000001 --outBinlogFileNameBase=./org --start-position=190803685
./flashback --binlogFileNames=org.flashback --maxSplitSize=30
arr=`find ${file_path} -name "binlog_output_base.0*"|sort -n`
for i in ${arr}
do
mysqlbinlog --skip-gtids ${i} | mysql ht
done
echo `date`
[root@redis01 binary]# ./1.sh
Thu Jul 12 18:36:46 CST 2018
Thu Jul 12 18:42:29 CST 2018
闪回100万行数据只用6分钟
闪回完毕查看数据
mysql> select * from ht.sbtest1 where id < 10; +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 499284 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 | | 2 | 501969 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 | 23183251411-36241541236-31706421314-92007079971-60663066966 | | 3 | 504261 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 | 38615512647-91458489257-90681424432-95014675832-60408598704 | | 4 | 502014 | 37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498 | 63947013338-98809887124-59806726763-79831528812-45582457048 | | 5 | 499813 | 44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783 | 34551750492-67990399350-81179284955-79299808058-21257255869 | | 6 | 500585 | 37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105 | 05161542529-00085727016-35134775864-52531204064-98744439797 | | 7 | 501371 | 33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045 | 91798303270-64988107984-08161247972-12116454627-22996445111 | | 8 | 499938 | 73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059 | 76460662325-41613089656-42706083314-81833284991-17063140920 | | 9 | 527268 | 26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653 | 30508501104-50823269125-88107014550-70202920684-95842308929 | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 9 rows in set (0.00 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 851563888 | +------------------+-----------+ 1 row in set (0.00 sec)
测试过程当中出现的问题
ERROR 1782 (HY000) at line 18: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
解决：mysqlbinlog 加--skip-gtids
[root@redis01 binary]# mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql ht
ERROR 2006 (HY000) at line 8017: MySQL server has gone away
解决：加大max_allowed_packet
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)
set global max_allowed_packet=67108864;
还有用下面脚本执行完毕不回滚数据
./flashback --databaseNames=ht --tableNames=sbtest1 --sqlTypes=update --binlogFileNames=/data/mysql/mysql-bin.000001 --maxSplitSize=30 --start-position=190803685
mysqlbinlog --skip-gtids ${i} | mysql ht
疑惑的是直接切割生成的日志文件不能回滚数据；得用下面的脚本在单独其切割成小文件才能回滚数据；后面有空得看看源码尝试自己修改下
./flashback --databaseNames=ht --tableNames=sbtest1 --sqlTypes=update --binlogFileNames=/data/mysql/mysql-bin.000001 --outBinlogFileNameBase=./org --start-position=190803685
./flashback --binlogFileNames=org.flashback --maxSplitSize=30
---mysql 8.0测试
[mysql@mysql01 ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| recovery |
| sys |
| test |
+--------------------+
7 rows in set (0.01 sec)
mysql> show master status;
+-------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+------------------------------------------+
| prod.000011 | 47603 | | | 60d209ad-47c5-11ec-86dd-08002727eab9:1-8 |
+-------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from base_goods1;
Query OK, 100 rows affected (0.02 sec)
mysql> show master status;
+-------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+------------------------------------------+
| prod.000011 | 94828 | | | 60d209ad-47c5-11ec-86dd-08002727eab9:1-9 |
+-------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+-------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------+-----------+-----------+
| prod.000001 | 82457735 | No |
| prod.000002 | 231 | No |
| prod.000003 | 231 | No |
| prod.000004 | 88327931 | No |
| prod.000005 | 231 | No |
| prod.000006 | 231 | No |
| prod.000007 | 82457467 | No |
| prod.000008 | 212 | No |
| prod.000009 | 231 | No |
| prod.000010 | 212 | No |
| prod.000011 | 94828 | No |
+-------------+-----------+-----------+
11 rows in set (0.00 sec)
./flashback --databaseNames=test --tableNames=base_goods1 --start-position=47603 --stop-position=94828 --binlogFileNames=/data/binlogs/prod.000011 --outBinlogFileNameBase=test1back
[mysql@mysql01 binary]$ mysqlbinlog --skip-gtids test1back.flashback | mysql -uroot -pocm123
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select count(1) from base_goods1;
+----------+
| count(1) |
+----------+
| 100 |
+----------+
1 row in set (0.05 sec)
浙公网安备 33010602011771号