| 1 |
reset master; |
| 2 |
reset master; |
| 3 |
Initial setup |
| 4 |
Create some tables and put some data in them |
| 5 |
use test; |
| 6 |
create table test.ndbt1 (a int primary key, b int) engine=ndb; |
| 7 |
create table test.myisamt3s1 (a int primary key, b int) engine=myisam; |
| 8 |
create table test.myisamt3s2 (a int primary key, b int) engine=myisam; |
| 9 |
insert into ndbt1 values (1,1); |
| 10 |
Show + reset Binlog contents on Server 1 |
| 11 |
include/show_binlog_events.inc |
| 12 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 13 |
binlog.000001 # Query # # use `test`; create table test.ndbt1 (a int primary key, b int) engine=ndb |
| 14 |
binlog.000001 # Query # # use `test`; create table test.myisamt3s1 (a int primary key, b int) engine=myisam |
| 15 |
binlog.000001 # Query # # BEGIN |
| 16 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 17 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 18 |
binlog.000001 # Write_rows # # table_id: # |
| 19 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 20 |
binlog.000001 # Query # # COMMIT |
| 21 |
reset master; |
| 22 |
Show + reset Binlog contents on Server 2 |
| 23 |
include/show_binlog_events.inc |
| 24 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 25 |
binlog.000001 # Query # # use `test`; create table test.ndbt1 (a int primary key, b int) engine=ndb |
| 26 |
binlog.000001 # Query # # use `test`; create table test.myisamt3s2 (a int primary key, b int) engine=myisam |
| 27 |
binlog.000001 # Query # # BEGIN |
| 28 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 29 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 30 |
binlog.000001 # Write_rows # # table_id: # |
| 31 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 32 |
binlog.000001 # Query # # COMMIT |
| 33 |
reset master; |
| 34 |
Now perform MyIsam table change followed by |
| 35 |
flush tables with read lock on Server 1 |
| 36 |
insert into test.myisamt3s1 values (3,3); |
| 37 |
FLUSH TABLES WITH READ LOCK; |
| 38 |
The MyIsam change definitely will be in the local |
| 39 |
binlog as it occurred 'before' the lock |
| 40 |
include/show_binlog_events.inc |
| 41 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 42 |
binlog.000001 # Query # # BEGIN |
| 43 |
binlog.000001 # Table_map # # table_id: # (test.myisamt3s1) |
| 44 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 45 |
binlog.000001 # Query # # COMMIT |
| 46 |
Show that Ndb and local tables are still |
| 47 |
Read-Write from Server2 |
| 48 |
use test; |
| 49 |
insert into ndbt1 values (4,4); |
| 50 |
Look at the Binlog events on Server 2 - contains |
| 51 |
rows written at Server2 |
| 52 |
include/show_binlog_events.inc |
| 53 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 54 |
binlog.000001 # Query # # BEGIN |
| 55 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 56 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 57 |
binlog.000001 # Write_rows # # table_id: # |
| 58 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 59 |
binlog.000001 # Query # # COMMIT |
| 60 |
insert into myisamt3s2 values (5,5); |
| 61 |
Look at the Binlog events on Server 2 - contains |
| 62 |
rows written at Server2 |
| 63 |
include/show_binlog_events.inc |
| 64 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 65 |
binlog.000001 # Query # # BEGIN |
| 66 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 67 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 68 |
binlog.000001 # Write_rows # # table_id: # |
| 69 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 70 |
binlog.000001 # Query # # COMMIT |
| 71 |
binlog.000001 # Query # # BEGIN |
| 72 |
binlog.000001 # Table_map # # table_id: # (test.myisamt3s2) |
| 73 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 74 |
binlog.000001 # Query # # COMMIT |
| 75 |
Now look at the Binlog events on Server 1 |
| 76 |
include/show_binlog_events.inc |
| 77 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 78 |
binlog.000001 # Query # # BEGIN |
| 79 |
binlog.000001 # Table_map # # table_id: # (test.myisamt3s1) |
| 80 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 81 |
binlog.000001 # Query # # COMMIT |
| 82 |
Now remove the read lock at server 1 |
| 83 |
unlock tables; |
| 84 |
Local event to ensure show binlog events sees |
| 85 |
'latest' binlog |
| 86 |
insert into ndbt1 values (6,6); |
| 87 |
Show that server 1 binlog now contains all the relevant |
| 88 |
events. |
| 89 |
include/show_binlog_events.inc |
| 90 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 91 |
binlog.000001 # Query # # BEGIN |
| 92 |
binlog.000001 # Table_map # # table_id: # (test.myisamt3s1) |
| 93 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 94 |
binlog.000001 # Query # # COMMIT |
| 95 |
binlog.000001 # Query # # BEGIN |
| 96 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 97 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 98 |
binlog.000001 # Write_rows # # table_id: # |
| 99 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 100 |
binlog.000001 # Query # # COMMIT |
| 101 |
binlog.000001 # Query # # BEGIN |
| 102 |
binlog.000001 # Table_map # # table_id: # (test.ndbt1) |
| 103 |
binlog.000001 # Table_map # # table_id: # (mysql.ndb_apply_status) |
| 104 |
binlog.000001 # Write_rows # # table_id: # |
| 105 |
binlog.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 106 |
binlog.000001 # Query # # COMMIT |
| 107 |
Reset both binlogs; |
| 108 |
reset master; |
| 109 |
reset master; |
| 110 |
Now show that a Server 1 Ndb update can |
| 111 |
be committed, but won't always make it to |
| 112 |
the Binlog |
| 113 |
This is not 100% guaranteed as in theory |
| 114 |
a commit can occur, and be Binlogged before |
| 115 |
the read lock stops the binlog injector. |
| 116 |
but in reality its very likely |
| 117 |
To remove any uncertainty we loop until |
| 118 |
we see a case where a committed ndb insert |
| 119 |
is not binlogged by the server. |
| 120 |
We do this work on Server2 as it doesn't have |
| 121 |
a read lock :) |
| 122 |
create table ndbt2 (a int primary key, b varchar(100)) engine=ndb; |
| 123 |
Outcome |
| 124 |
Server 1 has insert : 0 |
| 125 |
Server 2 has insert : 1 |
| 126 |
Now cleanup |
| 127 |
drop table ndbt1; |
| 128 |
drop table ndbt2; |
| 129 |
drop table myisamt3s1; |
| 130 |
drop table myisamt3s2; |