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
|
include/group_replication.inc [rpl_server_count=3]
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection server1]
#
# Start a new member with some group data but no replication user
#
include/start_and_bootstrap_group_replication.inc
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
SET GTID_NEXT= "aaaaaaaa-bbbb-aaaa-bbbb-aaaaaaaaaaaa:1";
BEGIN;
COMMIT;
SET GTID_NEXT= "AUTOMATIC";
SET GTID_NEXT= "aaaaaaaa-bbbb-aaaa-bbbb-aaaaaaaaaaaa:2";
BEGIN;
COMMIT;
SET GTID_NEXT= "AUTOMATIC";
#
# Add some extra data on server 2 and then start group replication
# Create a replication user so recovery can connect
#
SET GTID_NEXT= "aaaaaaaa-bbbb-aaaa-bbbb-aaaaaaaaaaaa:1";
CREATE TABLE lost_test_table (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
SET GTID_NEXT= "AUTOMATIC";
SET GTID_NEXT= "aaaaaaaa-bbbb-aaaa-bbbb-aaaaaaaaaaaa:2";
INSERT INTO lost_test_table VALUES (1);
SET GTID_NEXT= "AUTOMATIC";
SET SESSION sql_log_bin= 0;
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password";
GRANT REPLICATION SLAVE ON *.* TO "recovery_user";
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"@'%';;
FLUSH PRIVILEGES;
SET SESSION sql_log_bin= 1;
include/start_group_replication.inc
#
# Start recovery on member 3 and watch it fail for a bit as:
# 1) Server 1 does not has the correct replication user
# 2) Server 2 has conflicting data
#
SET SESSION sql_log_bin= 0;
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password";
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"@'%';;
FLUSH PRIVILEGES;
SET SESSION sql_log_bin= 1;
SET SESSION sql_log_bin= 0;
call mtr.add_suppression("There was an error when connecting to the donor*");
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.");
call mtr.add_suppression("Replica SQL for channel 'group_replication_recovery': Error 'Table 'lost_test_table'*");
call mtr.add_suppression("Replica: Table 'lost_test_table' already exists Error_code:*");
call mtr.add_suppression("Replica SQL for channel 'group_replication_recovery': Worker .* failed executing transaction .*; Error 'Table 'lost_test_table' already exists' on query.");
call mtr.add_suppression("Replica SQL for channel 'group_replication_recovery': ... The replica coordinator and worker threads are stopped.*");
call mtr.add_suppression("Error while starting the group replication incremental recovery receiver/applier threads");
call mtr.add_suppression("Replica I/O for channel 'group_replication_recovery': Source command COM_REGISTER_REPLICA failed: Access denied for user 'recovery_user'.*");
call mtr.add_suppression("Replica I/O for channel 'group_replication_recovery': Source command COM_REGISTER_REPLICA failed: failed registering on source, reconnecting to try again.*");
call mtr.add_suppression("Replica I/O thread couldn't register on source");
call mtr.add_suppression(".*This member has more executed transactions *.*");
call mtr.add_suppression("The member contains transactions not present in the group. It is only allowed to *.*");
SET SESSION sql_log_bin= 1;
SET @debug_save_rec_int= @@GLOBAL.group_replication_recovery_reconnect_interval;
CHANGE REPLICATION SOURCE TO SOURCE_USER='recovery_user', SOURCE_PASSWORD='recovery_password' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_recovery_reconnect_interval= 1;
SET SESSION sql_log_bin= 0;
CREATE TABLE lost_test_table (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO lost_test_table VALUES (1);
SET SESSION sql_log_bin= 1;
SET GLOBAL group_replication_group_name= "65b51c20-262d-11e5-867f-0800200c9a66";
START GROUP_REPLICATION;
include/gr_wait_for_member_state.inc
#
# Create the recovery user on server 1
# The joiner should now connect to server 1 when failing over.
#
SET SESSION sql_log_bin= 0;
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password";
GRANT REPLICATION SLAVE ON *.* TO "recovery_user";
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"@'%';;
FLUSH PRIVILEGES;
SET SESSION sql_log_bin= 1;
include/gr_wait_for_member_state.inc
include/assert.inc [On the recovered member, the table should exist and have 1 elements]
#
# Cleaning up
# We use RESET MASTER here due to deviations on all members GTID sets
#
SET @@GLOBAL.group_replication_recovery_reconnect_interval= @debug_save_rec_int;
DROP TABLE t1;
include/stop_group_replication.inc
DROP TABLE lost_test_table;
RESET MASTER;
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
include/stop_group_replication.inc
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
DROP TABLE lost_test_table;
RESET MASTER;
include/stop_group_replication.inc
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
RESET MASTER;
include/group_replication_end.inc
|
