本文详细介绍了如何在MySQL 5.7环境中进行数据闪回操作，通过使用Myflash工具，展示了从开启binlog到闪回DML（UPDATE、DELETE、INSERT）的完整流程。在实验中，首先创建并填充了100万行数据，然后执行更新操作，接着利用binlog文件和Myflash工具成功将数据回滚到更新前的状态，整个过程仅耗时6分钟。文章还提到了在闪回过程中遇到的问题及解决方案，如GTID模式下的错误和数据包大小限制。
本次测试用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
最低0.47元/天 解锁文章
扫一扫
1621
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
