MySQL Flashback功能测试
# 查看insert之前的数据mysql> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
+----+---------+
8 rows in set (0.00 sec)
# insert 错误数据
mysql> insert into test.t(name) values('七七八八');
Query OK, 1 row affected (0.00 sec)
# insert 错误数据
mysql> insert into test.t(name) values('二五六');
Query OK, 1 row affected (0.00 sec)
# insert 正确数据，不要恢复
mysql> insert into test.t(name) values('徐武');
Query OK, 1 row affected (0.00 sec)
# insert 正确数据，不要恢复
mysql> insert into test.t(name) values('徐陆');
Query OK, 1 row affected (0.00 sec)
# 查询insert 错误数据后的结果，现不需要'七七八八'和'二五六' 这两条数据。
mysql> select * from test.t;
+----+--------------+
| id | name |
+----+--------------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan |
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
|9 | 七七八八 |
| 10 | 二五六 |
| 11 | 徐武 |
| 12 | 徐陆 |
+----+--------------+
12 rows in set (0.00 sec)
mysql>
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 811 |
| mysql-bin.000002 | 1677 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql>
# 查看binlog
# ./mysqlbinlog -v /data/mysql/mysql_3306/logs/mysql-bin.000002|egrep"^#{6}|^#{1} at|^#{3}"
# at 865
#161221 17:02:03 server id 213306end_log_pos 896 CRC32 0xb8cb8180 Xid = 378
# at 896
#161221 17:21:44 server id 213306end_log_pos 964 CRC32 0x50a350b3 Ignorablethread_id=26exec_time=0error_code=0
# at 964
#161221 17:21:44 server id 213306end_log_pos 1011 CRC32 0x8710413b Table_map: `test`.`t` mapped to number 77
# at 1011
#161221 17:21:44 server id 213306end_log_pos 1064 CRC32 0x2bb90a24 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=9
### @2='七七八八'
# at 1064
#161221 17:21:44 server id 213306end_log_pos 1095 CRC32 0x2e48ad77 Xid = 385
# at 1095
#161221 17:21:56 server id 213306end_log_pos 1163 CRC32 0xbcc87449 Ignorablethread_id=26exec_time=0error_code=0
# at 1163
#161221 17:21:56 server id 213306end_log_pos 1210 CRC32 0x518a5946 Table_map: `test`.`t` mapped to number 77
# at 1210
#161221 17:21:56 server id 213306end_log_pos 1260 CRC32 0x935c3329 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=10
### @2='二五六'
# at 1260
#161221 17:21:56 server id 213306end_log_pos 1291 CRC32 0xebaa0357 Xid = 386
# at 1291
#161221 17:22:12 server id 213306end_log_pos 1359 CRC32 0x337d6793 Ignorablethread_id=26exec_time=0error_code=0
# at 1359
#161221 17:22:12 server id 213306end_log_pos 1406 CRC32 0x0ae07c99 Table_map: `test`.`t` mapped to number 77
# at 1406
#161221 17:22:12 server id 213306end_log_pos 1453 CRC32 0xd4af7b22 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=11
### @2='徐武'
# at 1453
#161221 17:22:12 server id 213306end_log_pos 1484 CRC32 0xc039e7f0 Xid = 387
# at 1484
#161221 17:22:20 server id 213306end_log_pos 1552 CRC32 0x789d671f Ignorablethread_id=26exec_time=0error_code=0
# at 1552
#161221 17:22:20 server id 213306end_log_pos 1599 CRC32 0xde45bfaf Table_map: `test`.`t` mapped to number 77
# at 1599
#161221 17:22:20 server id 213306end_log_pos 1646 CRC32 0xeb6b1317 Write_rows: table id 77 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=12
### @2='徐陆'
# at 1646
#161221 17:22:20 server id 213306end_log_pos 1677 CRC32 0x6902422a Xid = 388
# 加-B参数得到反转SQL,仔细看反转SQL是DELETE语句。
# ./mysqlbinlog -B -v --start-position 964 --stop-position 1291 /data/mysql/mysql_3306/logs/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#161221 16:31:23 server id 213306end_log_pos 120 CRC32 0x5e5e4653 Start: binlog v 4, server v 5.6.29-log created 161221 16:31:23
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
2z1aWA86QQMAdAAAAHgAAAABAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVNG
Xl4=
'/*!*/;
#161221 17:21:44 server id 213306end_log_pos 1011 CRC32 0x8710413b Table_map: `test`.`t` mapped to number 77
#161221 17:21:44 server id 213306end_log_pos 1095 CRC32 0x2e48ad77 Xid = 385
COMMIT/*!*/;
#161221 17:21:56 server id 213306end_log_pos 1210 CRC32 0x518a5946 Table_map: `test`.`t` mapped to number 77
#161221 17:21:56 server id 213306end_log_pos 1291 CRC32 0xebaa0357 Xid = 386
COMMIT/*!*/;
#161221 17:21:56 server id 213306end_log_pos 1260 CRC32 0x935c3329 Write_rows: table id 77 flags: STMT_END_F
BINLOG '
tElaWBM6QQMALwAAALoEAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAAEZZilE=
tElaWCA6QQMAMgAAAOwEAAAAAE0AAAAAAAEAAgAC//wKAAAACeS6jOS6lOWFrSkzXJM=
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
### @1=10
### @2='二五六'
#161221 17:21:44 server id 213306end_log_pos 1064 CRC32 0x2bb90a24 Write_rows: table id 77 flags: STMT_END_F
BINLOG '
qElaWBM6QQMALwAAAPMDAAAAAE0AAAAAAAEABHRlc3QAAXQAAgMPApYAADtBEIc=
qElaWCA6QQMANQAAACgEAAAAAE0AAAAAAAEAAgAC//wJAAAADOS4g+S4g+WFq+WFqyQKuSs=
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
### @1=9
### @2='七七八八'
DELIMITER ;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 找准POS点，直接恢复。
# ./mysqlbinlog -B -v --start-position 964 --stop-position 1291 /data/mysql/mysql_3306/logs/mysql-bin.000002 | \
>mysql -uroot -p123456 -S /data/mysql/mysql_3306/mysql.sock
#
# ./login_mysql 3306
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.6.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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> select * from test.t;
+----+---------+
| id | name |
+----+---------+
|1 | xm |
|2 | xmj |
|3 | xuwu |
|4 | chuzan|
|5 | chuzan2 |
|6 | chuzan3 |
|7 | 王五 |
|8 | 赵六 |
| 11 | 徐武 |
| 12 | 徐陆 |
+----+---------+
10 rows in set (0.00 sec)
# 至此insert 语句也恢复成功！
页:
[1]