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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
change master 'master1' to
master_port=MYPORT_1,
master_host='127.0.0.1',
master_user='root';
start slave 'master1';
set default_master_connection = 'master1';
include/wait_for_slave_to_start.inc
show slave 'master1' status;
Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath
Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1
show slave status;
Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath
Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1
show all slaves status;
Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period
master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 286 mysqld-relay-bin-master1.000002 572 master-bin.000001 Yes Yes 0 0 286 876 None 0 No 0 No 0 0 1 0 1073741824 6 0 60.000
drop database if exists db1;
create database db1;
use db1;
create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM;
insert into t1 (f1) values ('one'),('two');
select * from db1.t1;
i f1
1 one
2 two
# List of relay log files in the datadir
mysqld-relay-bin-master1.000001
mysqld-relay-bin-master1.000002
mysqld-relay-bin-master1.index
show relaylog events;
Log_name Pos Event_type Server_id End_log_pos Info
mysqld-relay-bin-master1.000001 4 Format_desc 3 246 Server version
mysqld-relay-bin-master1.000001 246 Rotate 3 304 mysqld-relay-bin-master1.000002;pos=4
show relaylog events in 'mysqld-relay-bin-master1.000002';
Log_name Pos Event_type Server_id End_log_pos Info
mysqld-relay-bin-master1.000002 4 Format_desc 3 246 Server version
mysqld-relay-bin-master1.000002 246 Rotate 1 0 master-bin.000001;pos=4
mysqld-relay-bin-master1.000002 290 Format_desc 1 246 Server version
mysqld-relay-bin-master1.000002 532 Binlog_checkpoint 1 286 master-bin.000001
mysqld-relay-bin-master1.000002 572 Query 1 375 drop database if exists db1
mysqld-relay-bin-master1.000002 661 Query 1 456 create database db1
mysqld-relay-bin-master1.000002 742 Query 1 609 use `db1`; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM
mysqld-relay-bin-master1.000002 895 Query 1 676 BEGIN
mysqld-relay-bin-master1.000002 962 Intvar 1 704 INSERT_ID=1
mysqld-relay-bin-master1.000002 990 Query 1 808 use `db1`; insert into t1 (f1) values ('one'),('two')
mysqld-relay-bin-master1.000002 1094 Query 1 876 COMMIT
change master 'master1' to
master_port=MYPORT_2,
master_host='127.0.0.1',
master_user='root';
ERROR HY000: This operation cannot be performed as you have a running slave 'master1'; run STOP SLAVE 'master1' first
change master to
master_port=MYPORT_2,
master_host='127.0.0.1',
master_user='root';
ERROR HY000: This operation cannot be performed as you have a running slave 'master1'; run STOP SLAVE 'master1' first
change master 'master2' to
master_port=MYPORT_1,
master_host='127.0.0.1',
master_user='root';
ERROR HY000: Connection 'master2' conflicts with existing connection 'master1'
set default_master_connection = '';
change master to
master_port=MYPORT_2,
master_host='127.0.0.1',
master_user='root';
start slave;
include/wait_for_slave_to_start.inc
show all slaves status;
Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period
Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_2 60 master-bin.000001 286 mysqld-relay-bin.000002 572 master-bin.000001 Yes Yes 0 0 286 868 None 0 No 0 No 0 0 2 0 1073741824 6 0 60.000
master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000001 876 mysqld-relay-bin-master1.000002 1162 master-bin.000001 Yes Yes 0 0 876 1466 None 0 No 0 No 0 0 1 0 1073741824 13 0 60.000
insert into t1 (f1) values ('three');
drop database if exists db2;
create database db2;
use db2;
create table t1 (pk int auto_increment primary key, f1 int) engine=InnoDB;
begin;
insert into t1 (f1) values (1),(2);
select * from db1.t1;
i f1
1 one
2 two
3 three
select * from db2.t1;
pk f1
commit;
select * from db2.t1;
pk f1
1 1
2 2
flush logs;
purge binary logs to 'master-bin.000002';
show binary logs;
Log_name File_size
master-bin.000002 326
insert into t1 (f1) values ('four');
create table db1.t3 (f1 int) engine=InnoDB;
show all slaves status;
Connection_name Slave_SQL_State Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_SSL_Crl Master_SSL_Crlpath Retried_transactions Max_relay_log_size Executed_log_entries Slave_received_heartbeats Slave_heartbeat_period
Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_2 60 master-bin.000001 809 mysqld-relay-bin.000002 1095 master-bin.000001 Yes Yes 0 0 809 1391 None 0 No 0 No 0 0 2 0 1073741824 13 0 60.000
master1 Slave has read all relay log; waiting for the slave I/O thread to update it Waiting for master to send event 127.0.0.1 root MYPORT_1 60 master-bin.000002 690 mysqld-relay-bin-master1.000004 976 master-bin.000002 Yes Yes 0 0 690 1324 None 0 No 0 No 0 0 1 0 1073741824 31 0 60.000
select * from db1.t1;
i f1
1 one
2 two
3 three
4 four
show relaylog events;
Log_name Pos Event_type Server_id End_log_pos Info
mysqld-relay-bin.000001 4 Format_desc 3 246 Server version
mysqld-relay-bin.000001 246 Rotate 3 296 mysqld-relay-bin.000002;pos=4
show relaylog events in 'mysqld-relay-bin.000002';
Log_name Pos Event_type Server_id End_log_pos Info
mysqld-relay-bin.000002 4 Format_desc 3 246 Server version
mysqld-relay-bin.000002 246 Rotate 2 0 master-bin.000001;pos=4
mysqld-relay-bin.000002 290 Format_desc 2 246 Server version
mysqld-relay-bin.000002 532 Binlog_checkpoint 2 286 master-bin.000001
mysqld-relay-bin.000002 572 Query 2 375 drop database if exists db2
mysqld-relay-bin.000002 661 Query 2 456 create database db2
mysqld-relay-bin.000002 742 Query 2 591 use `db2`; create table t1 (pk int auto_increment primary key, f1 int) engine=InnoDB
mysqld-relay-bin.000002 877 Query 2 658 BEGIN
mysqld-relay-bin.000002 944 Intvar 2 686 INSERT_ID=1
mysqld-relay-bin.000002 972 Query 2 782 use `db2`; insert into t1 (f1) values (1),(2)
mysqld-relay-bin.000002 1068 Xid 2 809 COMMIT /* xid=<num> */
stop slave io_thread;
show status like 'Slave_running';
Variable_name Value
Slave_running OFF
set default_master_connection = 'master1';
show status like 'Slave_running';
Variable_name Value
Slave_running ON
drop database db1;
drop database db2;
include/reset_master_slave.inc
drop database db1;
include/reset_master_slave.inc
drop database db2;
include/reset_master_slave.inc
|
