| 1 |
include/group_replication.inc [rpl_server_count=3] |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection server1] |
| 6 |
|
| 7 |
# 1. Start 2 servers. |
| 8 |
# Only setup recovery_user with GRANT REPLICATION SLAVE on server2 |
| 9 |
# for force server3 to recover from it. |
| 10 |
[connection server1] |
| 11 |
SET SESSION sql_log_bin= 0; |
| 12 |
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password"; |
| 13 |
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"; |
| 14 |
FLUSH PRIVILEGES; |
| 15 |
SET SESSION sql_log_bin= 1; |
| 16 |
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
| 17 |
INSERT INTO t1 VALUES (0); |
| 18 |
include/start_and_bootstrap_group_replication.inc |
| 19 |
[connection server2] |
| 20 |
SET SESSION sql_log_bin= 0; |
| 21 |
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password"; |
| 22 |
GRANT REPLICATION SLAVE ON *.* TO "recovery_user"; |
| 23 |
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"; |
| 24 |
FLUSH PRIVILEGES; |
| 25 |
SET SESSION sql_log_bin= 1; |
| 26 |
include/start_group_replication.inc |
| 27 |
|
| 28 |
# 2. Block applier on server 2 to force the delayed |
| 29 |
# View_change_log_event log to binary log. |
| 30 |
[connection server2] |
| 31 |
SET @@GLOBAL.DEBUG='+d,block_applier_updates'; |
| 32 |
|
| 33 |
# 3. Start Group Replication on server3. |
| 34 |
# It will recover from server2 and will stay on |
| 35 |
# RECOVERING state waiting for View_change_log_event log. |
| 36 |
[connection server3] |
| 37 |
SET SESSION sql_log_bin= 0; |
| 38 |
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password"; |
| 39 |
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"; |
| 40 |
FLUSH PRIVILEGES; |
| 41 |
SET SESSION sql_log_bin= 1; |
| 42 |
SET SESSION sql_log_bin= 0; |
| 43 |
call mtr.add_suppression("There was an error when connecting to the donor server.*"); |
| 44 |
call mtr.add_suppression("For details please check performance_schema.replication_connection_status table and error log messages of Replica I/O for channel group_replication_recovery."); |
| 45 |
call mtr.add_suppression("Replica I/O for channel 'group_replication_recovery': Source command COM_REGISTER_REPLICA failed*"); |
| 46 |
call mtr.add_suppression("Replica I/O thread couldn't register on source*"); |
| 47 |
SET SESSION sql_log_bin= 1; |
| 48 |
CHANGE REPLICATION SOURCE TO SOURCE_USER= 'recovery_user', SOURCE_PASSWORD= 'recovery_password' FOR CHANNEL 'group_replication_recovery'; |
| 49 |
include/start_group_replication.inc |
| 50 |
|
| 51 |
# 4. Execute 2 transactions on server1 and 2 while server3 |
| 52 |
# is on RECOVERING. |
| 53 |
[connection server1] |
| 54 |
INSERT INTO t1 VALUES (1); |
| 55 |
[connection server_2] |
| 56 |
INSERT INTO t1 VALUES (2); |
| 57 |
|
| 58 |
# 5. INSERT(1) will only be applied after member is ONLINE. |
| 59 |
# INSERT(2) will only be applied after member is ONLINE. |
| 60 |
[connection server3] |
| 61 |
include/assert.inc [value of 1 is not received.] |
| 62 |
include/assert.inc [value of 2 is not received.] |
| 63 |
|
| 64 |
# 6. Resume applier on server2. |
| 65 |
# Server3 state will change to ONLINE. |
| 66 |
[connection server2] |
| 67 |
SET DEBUG_SYNC = "now WAIT_FOR applier_read_blocked"; |
| 68 |
SET @@GLOBAL.DEBUG='-d,block_applier_updates'; |
| 69 |
SET DEBUG_SYNC = "now SIGNAL resume_applier_read"; |
| 70 |
[connection server_2] |
| 71 |
[connection server3] |
| 72 |
include/gr_wait_for_member_state.inc |
| 73 |
include/rpl_sync.inc |
| 74 |
|
| 75 |
# 7. Assert COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE is 0 on all servers. |
| 76 |
[connection server1] |
| 77 |
[connection server2] |
| 78 |
[connection server3] |
| 79 |
include/diff_tables.inc [server1:test.t1, server2:test.t1, server3:test.t1] |
| 80 |
[connection server1] |
| 81 |
include/show_binlog_events.inc |
| 82 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 83 |
server-binary-log.000001 # Previous_gtids # # |
| 84 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 85 |
server-binary-log.000001 # Query # # use `test`; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB |
| 86 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 87 |
server-binary-log.000001 # Query # # BEGIN |
| 88 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 89 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 90 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 91 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 92 |
server-binary-log.000001 # Query # # BEGIN |
| 93 |
server-binary-log.000001 # Query # # COMMIT |
| 94 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 95 |
server-binary-log.000001 # Query # # BEGIN |
| 96 |
server-binary-log.000001 # Query # # COMMIT |
| 97 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 98 |
server-binary-log.000001 # Query # # BEGIN |
| 99 |
server-binary-log.000001 # Query # # COMMIT |
| 100 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 101 |
server-binary-log.000001 # Query # # BEGIN |
| 102 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 103 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 104 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 105 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 106 |
server-binary-log.000001 # Query # # BEGIN |
| 107 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 108 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 109 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 110 |
[connection server2] |
| 111 |
include/show_binlog_events.inc |
| 112 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 113 |
server-binary-log.000001 # Previous_gtids # # |
| 114 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 115 |
server-binary-log.000001 # Query # # use `test`; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB |
| 116 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 117 |
server-binary-log.000001 # Query # # BEGIN |
| 118 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 119 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 120 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 121 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 122 |
server-binary-log.000001 # Query # # BEGIN |
| 123 |
server-binary-log.000001 # Query # # COMMIT |
| 124 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 125 |
server-binary-log.000001 # Query # # BEGIN |
| 126 |
server-binary-log.000001 # Query # # COMMIT |
| 127 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 128 |
server-binary-log.000001 # Query # # BEGIN |
| 129 |
server-binary-log.000001 # Query # # COMMIT |
| 130 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 131 |
server-binary-log.000001 # Query # # BEGIN |
| 132 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 133 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 134 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 135 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 136 |
server-binary-log.000001 # Query # # BEGIN |
| 137 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 138 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 139 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 140 |
[connection server3] |
| 141 |
include/show_binlog_events.inc |
| 142 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 143 |
server-binary-log.000001 # Previous_gtids # # |
| 144 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 145 |
server-binary-log.000001 # Query # # use `test`; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB |
| 146 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 147 |
server-binary-log.000001 # Query # # BEGIN |
| 148 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 149 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 150 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 151 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 152 |
server-binary-log.000001 # Query # # BEGIN |
| 153 |
server-binary-log.000001 # Query # # COMMIT |
| 154 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 155 |
server-binary-log.000001 # Query # # BEGIN |
| 156 |
server-binary-log.000001 # Query # # COMMIT |
| 157 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 158 |
server-binary-log.000001 # Query # # BEGIN |
| 159 |
server-binary-log.000001 # Query # # COMMIT |
| 160 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 161 |
server-binary-log.000001 # Query # # BEGIN |
| 162 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 163 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 164 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 165 |
server-binary-log.000001 # Gtid # # SET @@SESSION.GTID_NEXT= 'Gtid_set' |
| 166 |
server-binary-log.000001 # Query # # BEGIN |
| 167 |
server-binary-log.000001 # Table_map # # table_id: # (test.t1) |
| 168 |
server-binary-log.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 169 |
server-binary-log.000001 # Xid # # COMMIT /* XID */ |
| 170 |
|
| 171 |
# 8. Clean up. |
| 172 |
[connection server1] |
| 173 |
SET SESSION sql_log_bin= 0; |
| 174 |
DROP USER "recovery_user"; |
| 175 |
SET SESSION sql_log_bin= 1; |
| 176 |
[connection server2] |
| 177 |
SET DEBUG_SYNC= 'RESET'; |
| 178 |
DROP TABLE t1; |
| 179 |
SET SESSION sql_log_bin= 0; |
| 180 |
DROP USER "recovery_user"; |
| 181 |
SET SESSION sql_log_bin= 1; |
| 182 |
[connection server3] |
| 183 |
SET SESSION sql_log_bin= 0; |
| 184 |
DROP USER "recovery_user"; |
| 185 |
SET SESSION sql_log_bin= 1; |
| 186 |
include/group_replication_end.inc |