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
|
include/group_replication.inc
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]
############################################################
# 1. Deploy a group in multi-primary mode on server1 and server2.
[connection server1]
include/start_and_bootstrap_group_replication.inc
[connection server2]
include/start_group_replication.inc
############################################################
# 2. Configure a failover channel on server1 that replicates
# from server3.
[connection server3]
[connection server1]
CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_AUTO_POSITION=1, SOURCE_CONNECTION_AUTO_FAILOVER=1, SOURCE_PORT=SERVER_3_PORT, SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=1 FOR CHANNEL 'ch1';
Warnings:
Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure.
Note 1760 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.
SELECT asynchronous_connection_failover_add_source('ch1', '127.0.0.1', SERVER_MYPORT_3, '', 50);;
asynchronous_connection_failover_add_source('ch1', '127.0.0.1', SERVER_MYPORT_3, '', 50)
The UDF asynchronous_connection_failover_add_source() executed successfully.
include/start_slave.inc [FOR CHANNEL 'ch1']
include/assert.inc ['There is 1 row in performance_schema.replication_asynchronous_connection_failover']
include/assert.inc ['The version of replication_asynchronous_connection_failover must be 1']
include/assert.inc ['SOURCE_CONNECTION_AUTO_FAILOVER must be enabled on ch1']
include/assert.inc [Verify channel ch1 IO_THREAD is ON and connected to server3]
include/assert.inc [Verify replica_monitor thread is running]
############################################################
# 3. Configure the same failover channel on server2.
# In single-primary mode, the mode to which we will change,
# the failover channels must exist on all members.
# The channel is created with SOURCE_CONNECTION_AUTO_FAILOVER=0
# to show that it will change to SOURCE_CONNECTION_AUTO_FAILOVER=1
# when group mode changes to single-primary mode.
[connection server2]
CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_AUTO_POSITION=1, SOURCE_CONNECTION_AUTO_FAILOVER=0, SOURCE_PORT=SERVER_3_PORT, SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=1 FOR CHANNEL 'ch1';
Warnings:
Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure.
Note 1760 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.
############################################################
# 4. Add some failovers sources to server2 that will be replaced
# when server1 becomes the primary in single-primary mode.
SELECT asynchronous_connection_failover_add_source('ch1', '10.0.0.1', 3306, '', 50);
asynchronous_connection_failover_add_source('ch1', '10.0.0.1', 3306, '', 50)
The UDF asynchronous_connection_failover_add_source() executed successfully.
SELECT asynchronous_connection_failover_add_source('ch1', '10.0.0.2', 3306, '', 50);
asynchronous_connection_failover_add_source('ch1', '10.0.0.2', 3306, '', 50)
The UDF asynchronous_connection_failover_add_source() executed successfully.
include/assert.inc ['There are 2 rows in performance_schema.replication_asynchronous_connection_failover']
SELECT * FROM performance_schema.replication_asynchronous_connection_failover;
CHANNEL_NAME HOST PORT NETWORK_NAMESPACE WEIGHT MANAGED_NAME
ch1 10.0.0.1 3306 50
ch1 10.0.0.2 3306 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 2
############################################################
# 5. Change to single-primary mode.
# The primary must be server1, since when a member has
# replication channels running it must be the primary.
[connection server1]
SELECT group_replication_switch_to_single_primary_mode("MEMBER1_UUID");
group_replication_switch_to_single_primary_mode("MEMBER1_UUID")
Mode switched to single-primary successfully.
include/gr_assert_primary_member.inc
[connection server2]
include/gr_assert_secondary_member.inc
############################################################
# 6. Validate that server1 replication failover channels
# configuration is preserved, and that it did replace the
# one on server2.
[connection server1]
include/assert.inc ['There is 1 row in performance_schema.replication_asynchronous_connection_failover']
include/assert.inc ['The version of replication_asynchronous_connection_failover must be 1']
include/assert.inc ['SOURCE_CONNECTION_AUTO_FAILOVER must be enabled on ch1']
include/assert.inc [Verify channel ch1 IO_THREAD is ON and connected to server3]
include/assert.inc ['Validate the number of workers on performance_schema.replication_applier_status_by_worker table for channel ch1']
include/assert.inc [Verify replica_monitor thread is running]
SELECT * FROM performance_schema.replication_asynchronous_connection_failover;
CHANNEL_NAME HOST PORT NETWORK_NAMESPACE WEIGHT MANAGED_NAME
ch1 127.0.0.1 PORT 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 1
[connection server2]
include/assert.inc ['There is 1 row in performance_schema.replication_asynchronous_connection_failover']
include/assert.inc ['The version of replication_asynchronous_connection_failover must be 1']
include/assert.inc ['SOURCE_CONNECTION_AUTO_FAILOVER must be enabled on ch1']
include/assert.inc [Verify channel ch1 IO_THREAD is OFF]
include/assert.inc [Verify replica_monitor thread is not running]
SELECT * FROM performance_schema.replication_asynchronous_connection_failover;
CHANNEL_NAME HOST PORT NETWORK_NAMESPACE WEIGHT MANAGED_NAME
ch1 127.0.0.1 PORT 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 1
############################################################
# 7. server1 leaves the group.
# server2 is the new primary and does start the failover
# channel.
[connection server1]
include/stop_group_replication.inc
[connection server2]
include/gr_wait_primary_member_uuid.inc
include/wait_for_slave_to_start.inc [FOR CHANNEL 'ch1']
include/assert.inc ['There is 1 row in performance_schema.replication_asynchronous_connection_failover']
include/assert.inc ['The version of replication_asynchronous_connection_failover must be 1']
include/assert.inc ['SOURCE_CONNECTION_AUTO_FAILOVER must be enabled on ch1']
include/assert.inc [Verify channel ch1 IO_THREAD is ON and connected to server3]
include/assert.inc ['Validate the number of workers on performance_schema.replication_applier_status_by_worker table for channel ch1']
include/assert.inc [Verify replica_monitor thread is running]
SELECT * FROM performance_schema.replication_asynchronous_connection_failover;
CHANNEL_NAME HOST PORT NETWORK_NAMESPACE WEIGHT MANAGED_NAME
ch1 127.0.0.1 PORT 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 1
############################################################
# 8. Clean up.
[connection server2]
include/stop_group_replication.inc
include/rpl_reset_slave.inc
[connection server1]
include/rpl_reset_slave.inc
[connection server3]
include/stop_dump_threads.inc
include/group_replication_end.inc
|
