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 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205
|
change master 'abc' to relay_log_file='';
ERROR HY000: Failed initializing relay log position: Could not find target log during relay log initialization
change master 'abc2' to master_host='';
ERROR HY000: Incorrect arguments to MASTER_HOST
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
#
# Checking SHOW SLAVE 'master1' STATUS
#
Master_Port = 'MYPORT_1'
Relay_Log_File = 'mysqld-relay-bin-master1.000002'
Slave_IO_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Last_Errno = '0'
Last_SQL_Errno = '0'
#
# Checking SHOW SLAVE STATUS
#
Master_Port = 'MYPORT_1'
Relay_Log_File = 'mysqld-relay-bin-master1.000002'
Slave_IO_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Last_Errno = '0'
Last_SQL_Errno = '0'
#
# Checking SHOW ALL SLAVES STATUS
#
Connection_name = 'master1'
Master_Port = 'MYPORT_1'
Relay_Log_File = 'mysqld-relay-bin-master1.000002'
Slave_IO_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Last_Errno = '0'
Last_SQL_Errno = '0'
Slave_heartbeat_period = '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 248 Server version
mysqld-relay-bin-master1.000001 248 Rotate 3 306 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 248 Server version
mysqld-relay-bin-master1.000002 248 Rotate 1 0 master-bin.000001;pos=4
mysqld-relay-bin-master1.000002 292 Format_desc 1 248 Server version
mysqld-relay-bin-master1.000002 536 Gtid_list 1 273 []
mysqld-relay-bin-master1.000002 561 Binlog_checkpoint 1 313 master-bin.000001
mysqld-relay-bin-master1.000002 601 Gtid 1 351 GTID 0-1-1
mysqld-relay-bin-master1.000002 639 Query 1 440 drop database if exists db1
mysqld-relay-bin-master1.000002 728 Gtid 1 478 GTID 0-1-2
mysqld-relay-bin-master1.000002 766 Query 1 559 create database db1
mysqld-relay-bin-master1.000002 847 Gtid 1 597 GTID 0-1-3
mysqld-relay-bin-master1.000002 885 Query 1 750 use `db1`; create table t1 (i int auto_increment, f1 varchar(16), primary key pk (i,f1)) engine=MyISAM
mysqld-relay-bin-master1.000002 1038 Gtid 1 788 BEGIN GTID 0-1-4
mysqld-relay-bin-master1.000002 1076 Intvar 1 816 INSERT_ID=1
mysqld-relay-bin-master1.000002 1104 Query 1 920 use `db1`; insert into t1 (f1) values ('one'),('two')
mysqld-relay-bin-master1.000002 1208 Query 1 988 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
#
# Checking SHOW ALL SLAVES STATUS
#
Connection_name = ''
Connection_name = 'master1'
Master_Port = 'MYPORT_2'
Master_Port = 'MYPORT_1'
Relay_Log_File = 'mysqld-relay-bin.000002'
Relay_Log_File = 'mysqld-relay-bin-master1.000002'
Slave_IO_Running = 'Yes'
Slave_IO_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Last_Errno = '0'
Last_Errno = '0'
Last_SQL_Errno = '0'
Last_SQL_Errno = '0'
Slave_heartbeat_period = '60.000'
Slave_heartbeat_period = '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 367
insert into t1 (f1) values ('four');
create table db1.t3 (f1 int) engine=InnoDB;
#
# Checking SHOW ALL SLAVES STATUS
#
Connection_name = ''
Connection_name = 'master1'
Master_Port = 'MYPORT_2'
Master_Port = 'MYPORT_1'
Relay_Log_File = 'mysqld-relay-bin.000002'
Relay_Log_File = 'mysqld-relay-bin-master1.000004'
Slave_IO_Running = 'Yes'
Slave_IO_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Slave_SQL_Running = 'Yes'
Last_Errno = '0'
Last_Errno = '0'
Last_SQL_Errno = '0'
Last_SQL_Errno = '0'
Slave_heartbeat_period = '60.000'
Slave_heartbeat_period = '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 248 Server version
mysqld-relay-bin.000001 248 Rotate 3 298 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 248 Server version
mysqld-relay-bin.000002 248 Rotate 2 0 master-bin.000001;pos=4
mysqld-relay-bin.000002 292 Format_desc 2 248 Server version
mysqld-relay-bin.000002 536 Gtid_list 2 273 []
mysqld-relay-bin.000002 561 Binlog_checkpoint 2 313 master-bin.000001
mysqld-relay-bin.000002 601 Gtid 2 351 GTID 0-2-1
mysqld-relay-bin.000002 639 Query 2 440 drop database if exists db2
mysqld-relay-bin.000002 728 Gtid 2 478 GTID 0-2-2
mysqld-relay-bin.000002 766 Query 2 559 create database db2
mysqld-relay-bin.000002 847 Gtid 2 597 GTID 0-2-3
mysqld-relay-bin.000002 885 Query 2 732 use `db2`; create table t1 (pk int auto_increment primary key, f1 int) engine=InnoDB
mysqld-relay-bin.000002 1020 Gtid 2 770 BEGIN GTID 0-2-4
mysqld-relay-bin.000002 1058 Intvar 2 798 INSERT_ID=1
mysqld-relay-bin.000002 1086 Query 2 894 use `db2`; insert into t1 (f1) values (1),(2)
mysqld-relay-bin.000002 1182 Xid 2 921 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
|