MyFlash閃回初體驗(續)_ZenDei技術網路在線

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 proc ...

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.

 1 root@localhost:mysql3306.sock [(none)]09:14:31>show procedure status where db='zlm';
 2 +-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 3 | Db  | Name        | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
 4 +-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 5 | zlm | pro_flashbk | PROCEDURE | root@localhost | 2018-06-03 08:46:54 | 2018-06-03 08:46:54 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
 6 +-----+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 7 1 row in set (0.07 sec)
 8  
 9 root@localhost:mysql3306.sock [(none)]09:14:47>show tables from zlm;
10 +---------------+
11 | Tables_in_zlm |
12 +---------------+
13 | t1            |
14 | t2            |
15 | t3            |
16 | test_flashbk  |
17 +---------------+
18 4 rows in set (0.00 sec)
19  
20 root@localhost:mysql3306.sock [(none)]09:14:52>drop procedure pro_flashbk;
21 ERROR 1046 (3D000): No database selected
22 root@localhost:mysql3306.sock [(none)]09:15:08>use zlm;
23 Reading table information for completion of table and column names
24 You can turn off this feature to get a quicker startup with -A
25  
26 Database changed
27 root@localhost:mysql3306.sock [zlm]09:15:12>drop procedure pro_flashbk;
28 Query OK, 0 rows affected (0.02 sec)
29  
30 root@localhost:mysql3306.sock [zlm]09:15:20>drop table test_flashbk;
31 Query OK, 0 rows affected (0.23 sec)
32  
33 root@localhost:mysql3306.sock [zlm]09:15:29>delimiter //
34 root@localhost:mysql3306.sock [zlm]09:15:44>create procedure pro_flashbk()
35     -> begin
36     -> declare id int;
37     -> set id = 100000;
38     -> while id>0 do
39     -> insert into test_flashbk(name) values ('aaron8219');  
40     -> set id=id-1;                                                                                                                                       
41     -> end while;
42     -> end //
43 Query OK, 0 rows affected (0.00 sec)
44  
45 root@localhost:mysql3306.sock [zlm]09:15:44>delimiter ;
46 root@localhost:mysql3306.sock [zlm]09:15:45>create table test_flashbk(
47     -> id bigint not null auto_increment,
48     -> name varchar(20) not null default '',
49     -> primary key(id)
50     -> ) engine=innodb default charset=utf8mb4;
51 Query OK, 0 rows affected (0.08 sec)
52  
53 root@localhost:mysql3306.sock [zlm]09:16:05>call pro_flashbk();
54 Query OK, 1 row affected (14.77 sec)
55  
56 root@localhost:mysql3306.sock [zlm]09:16:29>select count(*) from test_flashbk;
57 +----------+
58 | count(*) |
59 +----------+
60 |   100000 |
61 +----------+
62 1 row in set (0.04 sec)
63  
64 root@localhost:mysql3306.sock [zlm]09:16:54>checksum table test_flashbk;
65 +------------------+------------+
66 | Table            | Checksum   |
67 +------------------+------------+
68 | zlm.test_flashbk | 1638389274 |
69 +------------------+------------+
70 1 row in set (0.06 sec)
71  
72 root@localhost:mysql3306.sock [zlm]09:17:05>show binary logs;
73 +------------------+-----------+
74 | Log_name         | File_size |
75 +------------------+-----------+
76 | mysql-bin.000013 |       217 |
77 | mysql-bin.000014 |      1341 |
78 | mysql-bin.000015 |       217 |
79 | mysql-bin.000016 |       680 |
80 | mysql-bin.000017 | 268435617 |
81 | mysql-bin.000018 |  72724171 |
82 | mysql-bin.000019 |       241 |
83 | mysql-bin.000020 |  11019314 |
84 | mysql-bin.000021 |       241 |
85 | mysql-bin.000022 |   1305983 |
86 | mysql-bin.000023 | 268435590 |
87 | mysql-bin.000024 |   9566499 |
88 | mysql-bin.000025 |  19081875 |
89  
90  
91 | mysql-bin.000026 |  27801320 |
92 +------------------+-----------+
93 14 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:

 1 root@localhost:mysql3306.sock [zlm]09:17:53>flush binary logs;
 2 Query OK, 0 rows affected (0.11 sec)
 3  
 4 root@localhost:mysql3306.sock [zlm]09:18:06>show binary logs;
 5 +------------------+-----------+
 6 | Log_name         | File_size |
 7 +------------------+-----------+
 8 | mysql-bin.000013 |       217 |
 9 | mysql-bin.000014 |      1341 |
10 | mysql-bin.000015 |       217 |
11 | mysql-bin.000016 |       680 |
12 | mysql-bin.000017 | 268435617 |
13 | mysql-bin.000018 |  72724171 |
14 | mysql-bin.000019 |       241 |
15 | mysql-bin.000020 |  11019314 |
16 | mysql-bin.000021 |       241 |
17 | mysql-bin.000022 |   1305983 |
18 | mysql-bin.000023 | 268435590 |
19 | mysql-bin.000024 |   9566499 |
20 | mysql-bin.000025 |  19081875 |
21 | mysql-bin.000026 |  27801367 |
22 | mysql-bin.000027 |       194 |
23 +------------------+-----------+
24 15 rows in set (0.00 sec)
25  
26 root@localhost:mysql3306.sock [zlm]09:18:10>delete from test_flashbk;
27 Query OK, 100000 rows affected (1.19 sec)
28  
29 root@localhost:mysql3306.sock [zlm]09:18:43>select count(*) from test_flashbk;
30 +----------+
31 | count(*) |
32 +----------+
33 |        0 |
34 +----------+
35 1 row in set (0.00 sec)
36  
37 root@localhost:mysql3306.sock [zlm]09:18:50>checksum table test_flashbk;
38 +------------------+----------+
39 | Table            | Checksum |
40 +------------------+----------+
41 | zlm.test_flashbk |        0 |
42 +------------------+----------+
43 1 row in set (0.00 sec)
44  
45 root@localhost:mysql3306.sock [zlm]09:19:05>flush logs;
46 Query OK, 0 rows affected (0.12 sec)
47  
48 root@localhost:mysql3306.sock [zlm]09:19:16>show binary logs;
49 +------------------+-----------+
50 | Log_name         | File_size |
51 +------------------+-----------+
52 | mysql-bin.000013 |       217 |
53 | mysql-bin.000014 |      1341 |
54 | mysql-bin.000015 |       217 |
55 | mysql-bin.000016 |       680 |
56 | mysql-bin.000017 | 268435617 |
57 | mysql-bin.000018 |  72724171 |
58 | mysql-bin.000019 |       241 |
59 | mysql-bin.000020 |  11019314 |
60 | mysql-bin.000021 |       241 |
61 | mysql-bin.000022 |   1305983 |
62 | mysql-bin.000023 | 268435590 |
63 | mysql-bin.000024 |   9566499 |
64 | mysql-bin.000025 |  19081875 |
65 | mysql-bin.000026 |  27801367 |
66 | mysql-bin.000027 |   1908620 |
67 | mysql-bin.000028 |       194 |
68 +------------------+-----------+
69 16 rows in set (0.00 sec)
70  
71 root@localhost:mysql3306.sock [zlm]09:19:20>

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.

 1 [root@zlm1 09:24:59 /vagrant/MyFlash-master/binary]
 2 #ls -l
 3 total 7366
 4 -rwxrwxrwx 1 vagrant vagrant   78542 Oct 25  2017 flashback
 5 -rwxrwxrwx 1 vagrant vagrant 7463125 Oct 25  2017 mysqlbinlog20160408
 6  
 7 [root@zlm1 09:25:02 /vagrant/MyFlash-master/binary]
 8 #./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes='DELETE' --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin.000027
 9  
10 [root@zlm1 09:25:04 /vagrant/MyFlash-master/binary]
11 #ls -l
12 total 9230
13 -rwxrwxrwx 1 vagrant vagrant 1908335 Jun  3 09:25 binlog_output_base.flashback
14 -rwxrwxrwx 1 vagrant vagrant   78542 Oct 25  2017 flashback
15 -rwxrwxrwx 1 vagrant vagrant 7463125 Oct 25  2017 mysqlbinlog20160408
16  
17 [root@zlm1 09:25:06 /vagrant/MyFlash-master/binary]
18  
19 #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:

 1 root@localhost:mysql3306.sock [zlm]09:19:20>select count(*) from test_flashbk;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    20
 5 Current database: zlm
 6  
 7 +----------+
 8 | count(*) |
 9  
10  
11 +----------+
12 |   100000 |
13 +----------+
14 1 row in set (0.04 sec)
15  
16 root@localhost:mysql3306.sock [zlm]09:26:10>checksum table test_flashbk;
17 +------------------+------------+
18 | Table            | Checksum   |
19 +------------------+------------+
20 | zlm.test_flashbk | 1638389274 |
21 +------------------+------------+
22 1 row in set (0.06 sec)
23  
24 root@localhost:mysql3306.sock [zlm]09:26:24>

moreover,i test the flashback of insertion operation as below:

 1 root@localhost:mysql3306.sock [zlm]10:08:04>delete from test_flashbk;
 2 Query OK, 100000 rows affected (1.08 sec)
 3 
 4 root@localhost:mysql3306.sock [zlm]10:08:08>checksum table test_flashbk;
 5 +------------------+----------+
 6 | Table            | Checksum |
 7 +------------------+----------+
 8 | zlm.test_flashbk |        0 |
 9 +------------------+----------+
10 1 row in set (0.01 sec)
11 
12 root@localhost:mysql3306.sock [zlm]10:08:15>flush logs;
13 Query OK, 0 rows affected (0.06 sec)
14 
15 root@localhost:mysql3306.sock [zlm]10:08:50>show binary logs;
16 +------------------+-----------+
17 | Log_name         | File_size |
18 +------------------+-----------+
19 | mysql-bin.000013 |       217 |
20 | mysql-bin.000014 |      1341 |
21 | mysql-bin.000015 |       217 |
22 | mysql-bin.000016 |       680 |
23 | mysql-bin.000017 | 268435617 |
24 | mysql-bin.000018 |  72724171 |
25 | mysql-bin.000019 |       241 |
26 | mysql-bin.000020 |  11019314 |
27 | mysql-bin.000021 |       241 |
28 | mysql-bin.000022 |   1305983 |
29 | mysql-bin.000023 | 268435590 |
30 | mysql-bin.000024 |   9566499 |
31 | mysql-bin.000025 |  19081875 |
32 | mysql-bin.000026 |  27801367 |
33 | mysql-bin.000027 |   1908620 |
34 | mysql-bin.000028 |   1908617 |
35 | mysql-bin.000029 |   8337364 |
36 | mysql-bin.000030 |       194 |
37 +------------------+-----------+
38 18 rows in set (0.00 sec)
39 
40 root@localhost:mysql3306.sock [zlm]10:08:55>call pro_flashbk();
41 Query OK, 1 row affected (14.59 sec)
42 
43 root@localhost:mysql3306.sock [zlm]10:09:23>select count(*) from test_flashbk;
44 +----------+
45 | count(*) |
46 +----------+
47 |   100000 |
48 +----------+
49 1 row in set (0.03 sec)
50 
51 root@localhost:mysql3306.sock [zlm]10:09:42>checksum table test_flashbk;
52 +------------------+------------+
53 | Table            | Checksum   |
54 +------------------+------------+
55 | zlm.test_flashbk | 3779450226 |
56 +------------------+------------+
57 1 row in set (0.05 sec)
58 
59 root@localhost:mysql3306.sock [zlm]10:10:01>show binary logs;
60 +------------------+-----------+
61 | Log_name         | File_size |
62 +------------------+-----------+
63 | mysql-bin.000013 |       217 |
64 | mysql-bin.000014 |      1341 |
65 | mysql-bin.000015 |       217 |
66 | mysql-bin.000016 |       680 |
67 | mysql-bin.000017 | 268435617 |
68 | mysql-bin.000018 |  72724171 |
69 | mysql-bin.000019 |       241 |
70 | mysql-bin.000020 |  11019314 |
71 | mysql-bin.000021 |       241 |
72 | mysql-bin.000022 |   1305983 |
73 | mysql-bin.000023 | 268435590 |
74 | mysql-bin.000024 |   9566499 |
75 | mysql-bin.000025 |  19081875 |
76 | mysql-bin.000026 |  27801367 |
77 | mysql-bin.000027 |   1908620 |
78 | mysql-bin.000028 |   1908617 |
79 | mysql-bin.000029 |   8337364 |
80 | mysql-bin.000030 |  27800194 |
81 +------------------+-----------+
82 18 rows in set (0.00 sec)

 1 [root@zlm1 10:06:23 /vagrant/MyFlash-master/binary]
 2 #ls -l
 3 total 10505
 4 -rwxrwxrwx 1 vagrant vagrant 3214328 Jun  3 10:06 binlog_output_base.flashback
 5 -rwxrwxrwx 1 vagrant vagrant   78542 Oct 25  2017 flashback
 6 -rwxrwxrwx 1 vagrant vagrant 7463125 Oct 25  2017 mysqlbinlog20160408
 7 
 8 [root@zlm1 10:10:32 /vagrant/MyFlash-master/binary]
 9 #rm -f binlog_output_base.flashback 
10 
11 [root@zlm1 10:10:35 /vagrant/MyFlash-master/binary]
12 #./flashback --databaseNames zlm --tableNames test_flashbk --sqlTypes='INSERT' --binlogFileNames=/data/mysql/mysql3306/logs/mysql-bin.000030
13 
14 [root@zlm1 10:12:21 /vagrant/MyFlash-master/binary]
15 #ls -l
16 total 18206
17 -rwxrwxrwx 1 vagrant vagrant 11100123 Jun  3 10:12 binlog_output_base.flashback
18 -rwxrwxrwx 1 vagrant vagrant    78542 Oct 25  2017 flashback
19 -rwxrwxrwx 1 vagrant vagrant  7463125 Oct 25  2017 mysqlbinlog20160408
20 
21 [root@zlm1 10:12:58 /vagrant/MyFlash-master/binary]
22 #mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql

 1 root@localhost:mysql3306.sock [zlm]10:10:16>select count(*) from test_flashbk;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    27
 5 Current database: zlm
 6 
 7 +----------+
 8 | count(*) |
 9 +----------+
10 |        0 |
11 +----------+
12 1 row in set (0.00 sec)
13 
14 root@localhost:mysql3306.sock [zlm]10:23:48>show binary logs;
15 +------------------+-----------+
16 | Log_name         | File_size |
17 +------------------+-----------+
18 | mysql-bin.000013 |       217 |
19 | mysql-bin.000014 |      1341 |
20 | mysql-bin.000015 |       217 |
21 | mysql-bin.000016 |       680 |
22 | mysql-bin.000017 | 268435617 |
23 | mysql-bin.000018 |  72724171 |
24 | mysql-bin.000019 |       241 |
25 | mysql-bin.000020 |  11019314 |
26 | mysql-bin.000021 |       241 |
27 | mysql-bin.000022 |   1305983 |
28 | mysql-bin.000023 | 268435590 |
29 | mysql-bin.000024 |   9566499 |
30 | mysql-bin.000025 |  19081875 |
31 | mysql-bin.000026 |  27801367 |
32 | mysql-bin.000027 |   1908620 |
33 | mysql-bin.000028 |   1908617 |
34 | mysql-bin.000029 |   8337364 |
35 | mysql-bin.000030 |  55300194 |
36 +------------------+-----------+
37 18 rows in set (0.00 sec)
38 
39 root@localhost:mysql3306.sock [zlm]10:24:22>checksum table test_flashbk;
40 ERROR 2006 (HY000): MySQL server has gone away
41 No connection. Trying to reconnect...
42 Connection id:    28
43 Current database: zlm
44 
45 +------------------+----------+
46 | Table            | Checksum |
47 +------------------+----------+
48 | zlm.test_flashbk |        0 |
49 +------------------+----------+
50 1 row in set (0.00 sec)
51 
52 root@localhost:mysql3306.sock [zlm]10:31:04>

in the end, i test the flashback of update operation,i still get some faults:

 1 root@localhost:mysql3306.sock [zlm]10:40:15>call pro_flashbk();
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    29
 5 Current database: zlm
 6 
 7 Query OK, 1 row affected (14.02 sec)
 8 
 9 root@localhost:mysql3306.sock [zlm]10:40:43>select count(*) from test_flashbk;
10 +----------+
11 | count(*) |
12 +----------+
13 |   100000 |
14 +----------+
15 1 row in set (0.04 sec)
16 
17 root@localhost:mysql3306.sock [zlm]10:41:41>checksum table test_flashbk;
18 +------------------+------------+
19 | Table            | Checksum   |
20 +------------------+------------+
21 | zlm.test_flashbk | 3977604217 |
22 +------------------+------------+
23 1 row in set (0.06 sec)
24 
25 root@localhost:mysql3306.sock [zlm]10:41:50>flush logs;
26 Query OK, 0 rows affected (0.07 sec)
27 
28 root@localhost:mysql3306.sock [zlm]10:42:09>show binary logs;
29 +------------------+-----------+
30 | Log_name         | File_size |
31 +------------------+-----------+
32 | mysql-bin.000013 |       217 |
33 | mysql-bin.000014 |      1341 |
34 | mysql-bin.000015 |       217 |
35 | mysql-bin.000016 |       680 |
36 | mysql-bin.000017 | 268435617 |
37 | mysql-bin.000018 |  72724171 |
38 | mysql-bin.000019 |       241 |
39 | mysql-bin.000020 |  11019314 |
40 | mysql-bin.000021 |       241 |
41 | mysql-bin.000022 |   1305983 |
42 | mysql-bin.000023 | 268435590 |
43 | mysql-bin.000024 |   9566499 |
44 | mysql-bin.000025 |  19081875 |
45 | mysql-bin.000026 |  27801367 |
46 | mysql-bin.000027 |   1908620 |
47 | mysql-bin.000028 |   1908617 |
48 | mysql-bin.000029 |   8337364 |
49 | mysql-bin.000030 |  83100241 |
50 | mysql-bin.000031 |       194 |
51 +------------------+-----------+
52 19 rows in set (0.00 sec)
53 
54 root@localhost:mysql3306.sock [zlm]10:42:18>update test_flashbk set name='zlm';
55 Query OK, 100000 rows affected (2.65 sec)
56 Rows matched: 100000  Changed: 100000  Warnings: 0
57 
58 root@localhost:mysql3306.sock [zlm]10:42:53>show binary logs;
59 +------------------+-----------+
60 | Log_name         | File_size |
61 +------------------+-----------+
62 | mysql-bin.000013 |       217 |
63 | mysql-bin.000014 |      1341 |
64 | mysql-bin.000015 |       217 |
65 | mysql-bin.000016 |       680 |
66 | mysql-bin.000017 | 268435617 |
67 | mysql-bin.000018 |  72724171 |
68 | mysql-bin.000019 |       241 |
69 | mysql-bin.000020 |  11019314 |
70 | mysql-bin.000021 |       241 |
71 | mysql-bin.000022 |   1305983 |
72 | mysql-bin.000023 | 268435590 |
73 | mysql-bin.000024 |   9566499 |
74 | mysql-bin.000025 |  19081875 |
75 | mysql-bin.000026 |  27801367 |
76 | mysql-bin.000027 |   1908620 |