mysql binlog DDL_mysqlbinlog-flashback测试2 ddl

delete操作都能闪回成功，但是insert操作却没有任何效果，ddl语句失败后，导致后面的sql全部不执行。

需要进行测试ddl在中间情况，不可以进行恢复操作：

需要避免下面操作：

[mysql@mysql1 binlog]$ cat B9.sql |grep DROP

[mysql@mysql1 binlog]$ cat B9.sql |grep TRUNCATE

[mysql@mysql1 binlog]$ cat B9.sql |grep ALTER

mysqlbinlog

是从下面网址下来回来的raw文件。

https://github.com/wubx/mysql-binlog-statistic/blob/cc01e8e13e05ad82a2ff0be36ca8dd48f29fb347/bin/mysqlbinlog

源码文件在下面地址：

http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog

插入测试数据：

create table t_insert_xyz(id int,name varchar(100));

insert into t_insert_xyz(id,name)values(1 ,'insert01');

insert into t_insert_xyz(id,name)values(2 ,'insert02');

create table t_delete_xyz(id int,name varchar(100));

insert into t_delete_xyz(id,name)values(1 ,'delete01');

insert into t_delete_xyz(id,name)values(2 ,'delete02');

create table t_ddl_xyz(id int,name varchar(100));

insert into t_ddl_xyz(id,name)values(1 ,'ddl01');

select * from t_insert_xyz;

select * from t_delete_xyz;

select * from t_ddl_xyz;

mysql> select * from t_insert_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    1 | insert01 |

|    2 | insert02 |

+------+----------+

2 rows in set (0.00 sec)

mysql> select * from t_delete_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    1 | delete01 |

|    2 | delete02 |

+------+----------+

2 rows in set (0.00 sec)

mysql> select * from t_ddl_xyz;

+------+-------+

| id   | name  |

+------+-------+

|    1 | ddl01 |

+------+-------+

1 row in set (0.00 sec)

mysql> set  global binlog_format=row;

mysql> set  binlog_format=row;

mysql> flush logs;

mysql> show master status;

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000016 |      107 |              |                  |

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

insert into t_insert_xyz(id,name)values(3 ,'xyz03');

drop table t_ddl_xyz;

delete from t_delete_xyz where id=1;

commit;

mysql> select * from t_insert_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    1 | insert01 |

|    2 | insert02 |

|    3 | xyz03    |

+------+----------+

3 rows in set (0.00 sec)

mysql> select * from t_delete_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    2 | delete02 |

+------+----------+

1 row in set (0.00 sec)

mysql> select * from t_ddl_xyz;

ERROR 1146 (42S02): Table 'test.t_ddl_xyz' doesn't exist

mysql> show master status ;

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000016 |      616 |              |                  |

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

检查输出结果,根据输出结果，dml操作是逆时针进行的。

mysqlbinlog -v --base64-output=decode-rows -B --start-position=107 --stop-position=616 /mysql/mysqllog/binlog/binlog.000016>B16.sql

实际执行恢复脚本和命令：

[mysql@mysql1 binlog]$ mysqlbinlog -B --start-position=107 --stop-position=616 /mysql/mysqllog/binlog/binlog.000016|mysql -uroot -ppassword test

ERROR 1049 (42000) at line 35: Unknown database 'test/*!*/'

mysql> select * from t_insert_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    1 | insert01 |

|    2 | insert02 |

|    3 | xyz03    |

+------+----------+

3 rows in set (0.00 sec)

mysql> select * from t_delete_xyz;

+------+----------+

| id   | name     |

+------+----------+

|    2 | delete02 |

|    1 | delete01 |

+------+----------+

2 rows in set (0.00 sec)

delete操作都能闪回成功，但是insert操作却没有任何效果，ddl语句失败后，导致后面的sql全部不执行。

ddl执行报错，是由于没有进行屏蔽，下面是B16.sql部分脚本：

/*!*/;

#150118  8:33:33 server id 3  end_log_pos 418   Query   thread_id=6     exec_time=0     error_code=0

use test/*!*/;

SET TIMESTAMP=1421598813/*!*/;

DROP TABLE `t_ddl_xyz` /* generated by server */

/*!*/;

需要进行测试ddl在中间情况，不可以进行恢复操作：

需要避免下面操作：

[mysql@mysql1 binlog]$ cat B9.sql |grep DROP

[mysql@mysql1 binlog]$ cat B9.sql |grep TRUNCATE

[mysql@mysql1 binlog]$ cat B9.sql |grep ALTER

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29114615/viewspace-1406320/，如需转载，请注明出处，否则将追究法律责任。