last night,i've tested flashback by MyFlash tool,but failed,now let's do some other test with it.first of all,clear the test environment:drop the procedure and table created yesterday.
root@localhost:mysql3306.sock [(none)]::>show procedure status where db='zlm';
+-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| zlm | pro_flashbk | PROCEDURE | root@localhost | -- :: | -- :: | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
row in set (0.07 sec) root@localhost:mysql3306.sock [(none)]::>show tables from zlm;
+---------------+
| Tables_in_zlm |
+---------------+
| t1 |
| t2 |
| t3 |
| test_flashbk |
+---------------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [(none)]::>drop procedure pro_flashbk;
ERROR (3D000): No database selected
root@localhost:mysql3306.sock [(none)]::>use zlm;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
root@localhost:mysql3306.sock [zlm]::>drop procedure pro_flashbk;
Query OK, rows affected (0.02 sec) root@localhost:mysql3306.sock [zlm]::>drop table test_flashbk;
Query OK, rows affected (0.23 sec) root@localhost:mysql3306.sock [zlm]::>delimiter //
root@localhost:mysql3306.sock [zlm]::>create procedure pro_flashbk()
-> begin
-> declare id int;
-> set id = ;
-> while id> do
-> insert into test_flashbk(name) values ('aaron8219');
-> set id=id-;
-> end while;
-> end //
Query OK, rows affected (0.00 sec) root@localhost:mysql3306.sock [zlm]::>delimiter ;
root@localhost:mysql3306.sock [zlm]::>create table test_flashbk(
-> id bigint not null auto_increment,
-> name varchar() not null default '',
-> primary key(id)
-> ) engine=innodb default charset=utf8mb4;
Query OK, rows affected (0.08 sec) root@localhost:mysql3306.sock [zlm]::>call pro_flashbk();
Query OK, row affected (14.77 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.04 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+------------+
| Table | Checksum |
+------------------+------------+
| zlm.test_flashbk | |
+------------------+------------+
row in set (0.06 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | | | mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
in order to make sure what binary log i need to generate the flashback file,i use "flush binary logs" and "show binary logs" to mark the explicit binary log(here is the mysql-bin.000027).after that,i do the deletion operation by intentionally forget to use where clause,obviously all the records in the test table "test_flashbk" will be deleted in the end as below:
root@localhost:mysql3306.sock [zlm]::>flush binary logs;
Query OK, rows affected (0.11 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>delete from test_flashbk;
Query OK, rows affected (1.19 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+----------+
| Table | Checksum |
+------------------+----------+
| zlm.test_flashbk | |
+------------------+----------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>flush logs;
Query OK, rows affected (0.12 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>
then,i will use MyFlash tool to generate the flashback file which will contain the miss deleted records of the test table by the operatoin just now.
[root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes='DELETE' --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin. [root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.flashback
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary] #mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql
now,let's check the records of the test table "test_flashbk" in another session with mysql client,finally you'll find the data turns back again:
root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: zlm +----------+
| count(*) | +----------+
| |
+----------+
row in set (0.04 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+------------+
| Table | Checksum |
+------------------+------------+
| zlm.test_flashbk | |
+------------------+------------+
row in set (0.06 sec) root@localhost:mysql3306.sock [zlm]::>
moreover,i test the flashback of insertion operation as below:
root@localhost:mysql3306.sock [zlm]::>delete from test_flashbk;
Query OK, rows affected (1.08 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+----------+
| Table | Checksum |
+------------------+----------+
| zlm.test_flashbk | |
+------------------+----------+
row in set (0.01 sec) root@localhost:mysql3306.sock [zlm]::>flush logs;
Query OK, rows affected (0.06 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>call pro_flashbk();
Query OK, row affected (14.59 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.03 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+------------+
| Table | Checksum |
+------------------+------------+
| zlm.test_flashbk | |
+------------------+------------+
row in set (0.05 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
[root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.flashback
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#rm -f binlog_output_base.flashback [root@zlm1 :: /vagrant/MyFlash-master/binary]
#./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes='INSERT' --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin. [root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.flashback
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql
root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: zlm +----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: zlm +------------------+----------+
| Table | Checksum |
+------------------+----------+
| zlm.test_flashbk | |
+------------------+----------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>
in the end, i test the flashback of update operation,i still get some faults:
root@localhost:mysql3306.sock [zlm]::>call pro_flashbk();
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: zlm Query OK, row affected (14.02 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.04 sec) root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
+------------------+------------+
| Table | Checksum |
+------------------+------------+
| zlm.test_flashbk | |
+------------------+------------+
row in set (0.06 sec) root@localhost:mysql3306.sock [zlm]::>flush logs;
Query OK, rows affected (0.07 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>update test_flashbk set name='zlm';
Query OK, rows affected (2.65 sec)
Rows matched: Changed: Warnings: root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
[root@zlm1 :: /vagrant/MyFlash-master/binary]
#rm -f binlog_output_base.flashback [root@zlm1 :: /vagrant/MyFlash-master/binary]
#./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes='UPDATE' --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin. [root@zlm1 :: /vagrant/MyFlash-master/binary]
#ls -l
total
-rwxrwxrwx vagrant vagrant Jun : binlog_output_base.flashback
-rwxrwxrwx vagrant vagrant Oct flashback
-rwxrwxrwx vagrant vagrant Oct mysqlbinlog20160408 [root@zlm1 :: /vagrant/MyFlash-master/binary]
#mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql
ERROR (HY000) at line : MySQL server has gone away [root@zlm1 :: /vagrant/MyFlash-master/binary]
#
root@localhost:mysql3306.sock [zlm]::>checksum table test_flashbk;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: zlm +------------------+------------+
| Table | Checksum |
+------------------+------------+
| zlm.test_flashbk | |
+------------------+------------+
row in set (0.07 sec) root@localhost:mysql3306.sock [zlm]::>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zlm]::>select count(*) from test_flashbk where name='aaron8219';
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.04 sec) root@localhost:mysql3306.sock [zlm]::>
i guess,there're some compatibility issues with the tool,'cause i got the inscrutable phenomenon when using it doing flashback of update operation last night.
Summary:
- MyFlash is fast and convenient to flashback some insertion and deletion operations.
- it still has some unpredictable issue when doing flashback of update.
- as far as i know,it is faster then another tool named "binlog2sql" which one is released by Mertuan-Dianping too.