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
|
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]
#
# Setup a new 2 member group
#
include/start_and_bootstrap_group_replication.inc
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
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;
include/start_group_replication.inc
#
# Purge member 1 binlog after a flush and create a replication user
#
FLUSH BINARY LOGS;
include/stop_dump_threads.inc
include/wait_for_file_closed.inc
SET SESSION sql_log_bin= 0;
CALL mtr.add_suppression("Cannot replicate to server with server_uuid*");
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;
#
# Start recovery and watch it fail for a bit as:
# 1) Server 1 has purged GTIDs
# 2) Server 2 does not has the correct replication user
#
SET SESSION sql_log_bin= 0;
CREATE USER "recovery_user" IDENTIFIED BY "recovery_password";
GRANT GROUP_REPLICATION_STREAM ON *.* TO "recovery_user"@'%';;
FLUSH PRIVILEGES;
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 I/O for channel 'group_replication_recovery': Got fatal error 1236*");
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("Error while creating the group replication recovery channel with donor.*");
call mtr.add_suppression("Error when configuring the asynchronous recovery channel connection to the donor.*");
SET SESSION sql_log_bin= 1;
CHANGE REPLICATION SOURCE TO SOURCE_USER= 'recovery_user', SOURCE_PASSWORD= 'recovery_password' FOR CHANNEL 'group_replication_recovery';
SET @debug_save_rec_int= @@GLOBAL.group_replication_recovery_reconnect_interval;
SET GLOBAL group_replication_recovery_reconnect_interval= 1 # seconds;
START GROUP_REPLICATION;
include/gr_wait_for_member_state.inc
#
# Create the recovery user on server 2
# The joiner should now be able to connect to server 2 when failing over.
#
SET SESSION sql_log_bin= 0;
GRANT REPLICATION SLAVE 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
#
SET @@GLOBAL.group_replication_recovery_reconnect_interval= @debug_save_rec_int;
DROP TABLE t1;
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
SET SESSION sql_log_bin= 0;
DROP USER "recovery_user";
SET SESSION sql_log_bin= 1;
include/group_replication_end.inc
|
