1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
include/rpl_init.inc [topology=1->2->3->4]
connection server_3;
set global gtid_strict_mode=1;
include/stop_slave.inc
SET GLOBAL slave_parallel_threads=10;
set global slave_parallel_mode=optimistic;
change master to master_use_gtid=slave_pos;
include/start_slave.inc
connection server_1;
set global binlog_alter_two_phase=ON;
set binlog_alter_two_phase=ON;
connect master_node,127.0.0.1,root,,$db_name, $SERVER_MYPORT_1;
connect slave_node,127.0.0.1,root,,test, $SERVER_MYPORT_2;
# innodb
connection master_node;
create table t1(a int, b int) engine=innodb;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
include/save_master_gtid.inc
connection slave_node;
include/sync_with_master_gtid.inc
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection master_node;
drop table t1;
include/save_master_gtid.inc
connection slave_node;
include/sync_with_master_gtid.inc
disconnect master_node;
disconnect slave_node;
connection server_1;
set global binlog_alter_two_phase=0;
include/rpl_sync.inc
connection server_2;
select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1;
domain_id seq_no
0 12
connection server_1;
include/save_master_gtid.inc
connection server_3;
include/sync_with_master_gtid.inc
select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1;
domain_id seq_no
0 12
include/stop_slave.inc
set global slave_parallel_threads = 0;;
set global slave_parallel_mode = optimistic;;
set global gtid_strict_mode = 0;;
include/start_slave.inc
select @@slave_parallel_threads;
@@slave_parallel_threads
0
connection server_4;
include/sync_with_master_gtid.inc
select domain_id, seq_no from mysql.gtid_slave_pos order by seq_no desc limit 1;
domain_id seq_no
0 12
include/rpl_end.inc
|
