2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
10
SET SESSION BINLOG_FORMAT=ROW;
11
CREATE TABLE t1 (a INT, b INT);
12
CREATE TABLE t2 (c INT, d INT);
13
INSERT INTO t1 VALUES (1,1),(2,4),(3,9);
14
INSERT INTO t2 VALUES (1,1),(2,8),(3,27);
15
UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;
16
show binlog events from <binlog_start>;
17
Log_name Pos Event_type Server_id End_log_pos Info
18
master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT)
19
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (c INT, d INT)
20
master-bin.000001 # Query # # BEGIN
21
master-bin.000001 # Table_map # # table_id: # (test.t1)
22
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
23
master-bin.000001 # Query # # COMMIT
24
master-bin.000001 # Query # # BEGIN
25
master-bin.000001 # Table_map # # table_id: # (test.t2)
26
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
27
master-bin.000001 # Query # # COMMIT
28
master-bin.000001 # Query # # BEGIN
29
master-bin.000001 # Table_map # # table_id: # (test.t1)
30
master-bin.000001 # Table_map # # table_id: # (test.t2)
31
master-bin.000001 # Update_rows # # table_id: #
32
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
33
master-bin.000001 # Query # # COMMIT
45
START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762;
50
Master_Port MASTER_PORT
52
Master_Log_File master-bin.000001
53
Read_Master_Log_Pos 1115
56
Relay_Master_Log_File master-bin.000001
62
Replicate_Ignore_Table
63
Replicate_Wild_Do_Table
64
Replicate_Wild_Ignore_Table
68
Exec_Master_Log_Pos 762
70
Until_Condition Master
71
Until_Log_File master-bin.000001
79
Seconds_Behind_Master #
80
Master_SSL_Verify_Server_Cert No
85
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
100
SET SESSION BINLOG_FORMAT=STATEMENT;
102
INSERT INTO t1 VALUES(@foo, 2*@foo);
103
show binlog events from <binlog_start>;
104
Log_name Pos Event_type Server_id End_log_pos Info
105
master-bin.000001 # User var # # @`foo`=12
106
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(@foo, 2*@foo)
107
START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=106;
108
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
112
Master_Host 127.0.0.1
114
Master_Port MASTER_PORT
116
Master_Log_File master-bin.000001
117
Read_Master_Log_Pos 248
120
Relay_Master_Log_File master-bin.000001
122
Slave_SQL_Running Yes
126
Replicate_Ignore_Table
127
Replicate_Wild_Do_Table
128
Replicate_Wild_Ignore_Table
132
Exec_Master_Log_Pos 248
137
Master_SSL_Allowed No
143
Seconds_Behind_Master #
144
Master_SSL_Verify_Server_Cert No
151
SET SESSION BINLOG_FORMAT=ROW;
153
CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;
154
CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;
155
CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;
156
INSERT INTO t1 VALUES (1,'master/slave');
157
INSERT INTO t2 VALUES (1,'master/slave');
158
INSERT INTO t3 VALUES (1,'master/slave');
159
CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW
161
INSERT INTO t2 VALUES (NEW.a,NEW.b);
162
DELETE FROM t2 WHERE a < NEW.a;
164
CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW
166
UPDATE t3 SET a =2, b = 'master only';
169
include/stop_slave.inc
171
UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;
174
INSERT INTO t1 VALUES (3,'master/slave');
175
INSERT INTO t2 VALUES (3,'master/slave');
176
INSERT INTO t3 VALUES (3,'master/slave');
178
SELECT * FROM t1 ORDER BY a;
182
SELECT * FROM t2 ORDER BY a;
186
SELECT * FROM t3 ORDER BY a;
191
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
192
include/start_slave.inc
193
SELECT * FROM t1 ORDER BY a;
197
SELECT * FROM t2 ORDER BY a;
201
SELECT * FROM t3 ORDER BY a;
205
DROP TABLE t1, t2, t3;
206
**** Case 2: Row binlog format and transactional tables ****
208
CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb;
209
CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb;
210
CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb;
212
include/stop_slave.inc
215
INSERT INTO t4 VALUES (2, 'master only');
216
INSERT INTO t5 VALUES (2, 'master only');
217
INSERT INTO t6 VALUES (2, 'master only');
220
INSERT INTO t4 VALUES (3, 'master/slave');
221
INSERT INTO t5 VALUES (3, 'master/slave');
222
INSERT INTO t6 VALUES (3, 'master/slave');
224
SELECT * FROM t4 ORDER BY a;
228
SELECT * FROM t5 ORDER BY a;
232
SELECT * FROM t6 ORDER BY a;
237
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
238
include/start_slave.inc
239
SELECT * FROM t4 ORDER BY a;
242
SELECT * FROM t5 ORDER BY a;
245
SELECT * FROM t6 ORDER BY a;
249
include/stop_slave.inc
252
INSERT INTO t4 VALUES (6, 'master only');
253
INSERT INTO t5 VALUES (6, 'master only');
254
INSERT INTO t6 VALUES (6, 'master only');
257
INSERT INTO t4 VALUES (7, 'master only');
258
INSERT INTO t5 VALUES (7, 'master only');
259
INSERT INTO t6 VALUES (7, 'master only');
261
SELECT * FROM t4 ORDER BY a;
267
SELECT * FROM t5 ORDER BY a;
273
SELECT * FROM t6 ORDER BY a;
280
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10;
281
include/start_slave.inc
282
SELECT * FROM t4 ORDER BY a;
285
SELECT * FROM t5 ORDER BY a;
288
SELECT * FROM t6 ORDER BY a;
291
include/stop_slave.inc
293
INSERT INTO t4 VALUES (4, 'master only');
294
INSERT INTO t5 VALUES (4, 'master only');
295
INSERT INTO t6 VALUES (4, 'master only');
297
INSERT INTO t4 VALUES (5, 'master/slave');
298
INSERT INTO t5 VALUES (5, 'master/slave');
299
INSERT INTO t6 VALUES (5, 'master/slave');
301
SELECT * FROM t4 ORDER BY a;
309
SELECT * FROM t5 ORDER BY a;
317
SELECT * FROM t6 ORDER BY a;
326
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
327
include/start_slave.inc
328
SELECT * FROM t4 ORDER BY a;
332
SELECT * FROM t5 ORDER BY a;
336
SELECT * FROM t6 ORDER BY a;
340
DROP TABLE t4, t5, t6;
341
**** Case 3: Statement logging format and LOAD DATA with non-transactional table ****
343
CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;
345
include/stop_slave.inc
347
SET SESSION BINLOG_FORMAT=STATEMENT;
348
LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';
349
SELECT * FROM t10 ORDER BY a;
355
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
356
include/start_slave.inc
357
SELECT * FROM t10 ORDER BY a;