mysql 闪回删除记录_mysql5.7 闪回数据（update delete insert）

最新推荐文章于 2023-07-19 07:50:14 发布

徐贵枝

最新推荐文章于 2023-07-19 07:50:14 发布

阅读量186

点赞数

文章标签： mysql 闪回删除记录

本文链接：https://blog.csdn.net/weixin_34900470/article/details/113686431

版权

本次测试用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.00sec)

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)

View Code

安装myflash

git clone https://github.com/Meituan-Dianping/MyFlash.git

myflash依赖glibc查询

[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

动态编译

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}

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分钟

闪回完毕查看数据