基于binlog的恢复测试

测试数据库MySQL 5.6.35
mysqlbinlog_flashback目前只支持MySQL5.5和5.6，binlog格式为ROW格式，详细建议查看https://github.com/58daojia-dba/mysqlbinlog_flashback的说明文档
1. 安装pymysql并下载mysqlbinlog_flashback
[root@testdb_172_16_123_101 /root]# yum -y install git python-pip
[root@testdb_172_16_123_101 /root]# pip install --upgrade pip
[root@testdb_172_16_123_101 /root]# pip install pymysql
[root@testdb_172_16_123_101 /root]# git clone https://github.com/58daojia-dba/mysqlbinlog_flashback.git

root@localhost [test]>create table t1(id int not null auto_increment, name varchar(30), primary key(id));
Query OK, 0 rows affected (0.28 sec)

root@localhost [test]>insert into t1(name) values('aa'), ('bb'), ('cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@localhost [test]>update t1 set name='mysql';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

3. 创建用于flashback的用户
root@localhost [(none)]>create user flback@'172.16.123.%' identified by 'flback';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>grant super,replication client,replication slave on *.* to flback@'172.16.123.%';
Query OK, 0 rows affected (0.00 sec)

[root@testdb_172_16_123_101 /root/mysqlbinlog_flashback]# python mysqlbinlog_back.py --host='172.16.123.101' --port=3307 --username='flback' --password='flback' --schema='test' --table='t1' -S mysql-bin.000003
===log will also write to .//mysqlbinlog_flashback.log===
parameter={'start_binlog_file': 'mysql-bin.000003', 'stream': None, 'keep_data': True, 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 'keep_current_data': False, 'start_to_timestamp': None, 'mysql_setting': {'passwd': 'flback', 'host': '172.16.123.101', 'charset': 'utf8', 'port': 3307, 'user': 'flback'}, 'table_name': 't1', 'skip_delete': False, 'schema': 'test', 'stat': {'flash_sql': {}}, 'table_name_array': ['t1'], 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': False, 'dump_event': False, 'end_to_timestamp': 1490422192.0, 'skip_insert': False, 'schema_array': ['test']}
===statistics===
scan 50 events
{'flash_sql': {u'test': {u't1': {'insert': 0, 'update': 3, 'delete': 3}}}}

默认在log目录生成相关的文件
[root@testdb_172_16_123_101 /root/mysqlbinlog_flashback]# ll log/
total 12
-rw-r--r-- 1 root root 372 Mar 25 14:11 flashback_test_20170325_141107.sql
-rw-r--r-- 1 root root 179 Mar 25 14:11 save_data_create_table_test_20170325_141107.sql #save_data_create_table_开头的文件用于生成建表语句, 注意字符集改成了utf8mb4
-rw-r--r-- 1 root root 921 Mar 25 14:11 save_data_dml_test_20170325_141107.sql #用于插入到新的表中

当binlog日志比较大的时候，通过show binlog events in 'mysql-bin.000003';找到事务的开始位置和结束位置，也可以通过mysqlbinlog命令解析binlog日志查找
[root@testdb_172_16_123_101 /root/mysqlbinlog_flashback]# python mysqlbinlog_back.py --host='172.16.123.101' --port=3307 --username='flback' --password='flback' --schema='test' --table='t1' -S mysql-bin.000003 -L 1029
===log will also write to .//mysqlbinlog_flashback.log===
parameter={'start_binlog_file': 'mysql-bin.000003', 'stream': None, 'keep_data': True, 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 'keep_current_data': False, 'start_to_timestamp': None, 'mysql_setting': {'passwd': 'flback', 'host': '172.16.123.101', 'charset': 'utf8', 'port': 3307, 'user': 'flback'}, 'table_name': 't1', 'skip_delete': False, 'schema': 'test', 'stat': {'flash_sql': {}}, 'table_name_array': ['t1'], 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 1029, 'skip_update': False, 'dump_event': False, 'end_to_timestamp': 1490425272.0, 'skip_insert': False, 'schema_array': ['test']}
===statistics===
scan 37 events
{'flash_sql': {u'test': {u't1': {'insert': 0, 'update': 3, 'delete': 0}}}}

[root@testdb_172_16_123_101 /root/mysqlbinlog_flashback]# ll log/
total 12
-rw-r--r-- 1 root root 212 Mar 25 15:01 flashback_test_20170325_150112.sql
-rw-r--r-- 1 root root 179 Mar 25 15:01 save_data_create_table_test_20170325_150112.sql
-rw-r--r-- 1 root root 506 Mar 25 15:01 save_data_dml_test_20170325_150112.sql

4. 恢复数据
[root@testdb_172_16_123_101 /root/mysqlbinlog_flashback]# mysql -S /tmp/mysql3307.sock test < log/flashback_test_20170325_150112.sql

5. 检查数据恢复情况
root@localhost [test]>select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+

如果数据是utf8mb4字符集的，导入时一定要指定字符集

发送评论 编辑评论

发送评论编辑评论