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
|
include/group_replication.inc [rpl_server_count=4]
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 single-primary mode on server1 and server2.
[connection server1]
include/start_and_bootstrap_group_replication.inc
include/gr_assert_primary_member.inc
[connection server2]
[connection server2]
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.
include/start_group_replication.inc
include/gr_assert_secondary_member.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 channel ch1 SQL_THREAD is ON]
include/assert.inc [Verify replica_monitor thread is running]
############################################################
# 3. Assert failover channel configuration on server2.
[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 channel ch1 SQL_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 ##### 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 1
############################################################
# 4. Change to multi-primary mode.
# The failover channel will remain working on server1.
[connection server1]
SELECT group_replication_switch_to_multi_primary_mode();
group_replication_switch_to_multi_primary_mode()
Mode switched to multi-primary successfully.
include/gr_assert_multi_primary_mode.inc
include/check_slave_is_running.inc [FOR CHANNEL 'ch1']
[connection server2]
include/gr_assert_multi_primary_mode.inc
############################################################
# 5. Add more sources to channel 'ch1' on server1
# and change SOURCE_CONNECTION_AUTO_FAILOVER value,
# they will not be propagated to server2.
[connection server1]
SELECT asynchronous_connection_failover_add_source('ch1', '127.0.0.1', SERVER_MYPORT_4, '', 50);;
asynchronous_connection_failover_add_source('ch1', '127.0.0.1', SERVER_MYPORT_4, '', 50)
The UDF asynchronous_connection_failover_add_source() executed successfully.
include/stop_slave.inc [FOR CHANNEL 'ch1']
CHANGE REPLICATION SOURCE TO SOURCE_CONNECTION_AUTO_FAILOVER=0 FOR CHANNEL 'ch1';
include/start_slave.inc [FOR CHANNEL 'ch1']
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 127.0.0.1 ##### 50
ch1 127.0.0.1 ##### 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 2
[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 channel ch1 SQL_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 ##### 50
SELECT * FROM performance_schema.replication_group_configuration_version WHERE name LIKE 'replication_asynchronous_connection_failover';
name version
replication_asynchronous_connection_failover 1
############################################################
# 6. server1 will leave the group, despite 'ch1' is a failover
# channel, since the group is on multi-primary mode, server2
# will not start channel 'ch1'.
[connection server1]
include/stop_group_replication.inc
[connection server2]
include/assert.inc [The action mysql_start_failover_channels_if_primary is enabled]
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 channel ch1 SQL_THREAD is OFF]
include/assert.inc [Verify replica_monitor thread is not running]
############################################################
# 7. Clean up.
[connection server2]
include/stop_group_replication.inc
include/rpl_reset_slave.inc
[connection server1]
include/rpl_reset_slave.inc
include/group_replication_end.inc
|
