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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
#
# 1. Create a chain replication between server 1, 2, 3 and 4.
#.
include/rpl_init.inc [topology=1->2->3->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.
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.
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.
#
# 2. Insert data on server1 and verify that its synced to the end of
# chain i.e. server4
#
[connection server_1]
CREATE TABLE t1 (a INT) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1);
include/rpl_sync.inc
[connection server_4]
SET @old_debug= @@GLOBAL.DEBUG;
SET GLOBAL debug = '+d,async_conn_failover_disable_weight_check';
include/assert.inc ['There is one row on replica']
#
# 3. Verify Asynchronous Replication Connection Failover is initially
# disabled. Also verify Asynchronous Replication is initially
# connected to server 3.
#
include/assert.inc ['Asynchronous Replication Connection Failover is disabled']
#
# 4. Enable Asynchronous Replication Connection Failover and set retry
# count and retry time to 2 and verify its enabled in
# performance_schema.replication_connection_configuration.
#
include/stop_slave.inc
CHANGE REPLICATION SOURCE TO SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=2, SOURCE_CONNECTION_AUTO_FAILOVER=1;
include/start_slave.inc
include/assert.inc ['Asynchronous Replication Connection Failover is enabled']
#
# 5. Stop server 3.
#
include/rpl_stop_server.inc [server_number=3]
#
# 6. Verify that asynchronous replication fails with error
# ER_RPL_ASYNC_RECONNECT_FAIL_NO_SOURCE as there is no source
# added to table
# performance_schema.replication_asynchronous_connection_failover to
# connect.
#
[connection server_4]
include/wait_for_slave_io_error.inc [errno=2003]
#
# 7. Verify that asynchronous replication tries connecting to server 3
# two (retry_count=2) times before it fails.
#
include/assert_grep.inc [Assert that the IO thread tried two times connecting to server_3]
#
# 8. Add three asynchronous connection failover servers and
# verify that they are inserted successfully in
# performance_schema.replication_asynchronous_connection_failover.
#
SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_3, '', 60);;
asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_3, '', 60)
The UDF asynchronous_connection_failover_add_source() executed successfully.
SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_2, '', 50);;
asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_2, '', 50)
The UDF asynchronous_connection_failover_add_source() executed successfully.
SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_1, '', 40);;
asynchronous_connection_failover_add_source('', '127.0.0.1', SERVER_MYPORT_1, '', 40)
The UDF asynchronous_connection_failover_add_source() executed successfully.
include/assert.inc ['There are three rows in performance_schema.replication_asynchronous_connection_failover']
#
# 9. Restart replica IO so asynchronous replication connection failover
# can execute and connect to new sender/source.
#
include/start_slave_io.inc
#
# 10. Verify that asynchronous replication channel gets connected to
# server 2 and also verify that asynchronous replication tries
# connecting to server 3 two (retry_count=2) times.
#
include/assert_grep.inc [Assert that the IO thread tried two times connecting to server_3]
#
# 11. Insert more data on server1 and verify that its synced to the end
# of chain i.e. server 4.
#
[connection server_1]
INSERT INTO t1 VALUES (2);
[connection server_4]
#
# 12. Stop server 2.
#
include/rpl_stop_server.inc [server_number=2]
#
# 13. Verify that asynchronous replication channel gets connected to
# server 1 and also verify that asynchronous replication tries
# connecting to server 2 two (retry_count=2) times.
#
[connection server_4]
include/assert_grep.inc [Assert that the IO thread tried two times connecting to server_2]
#
# 14. Insert more data on server1 and verify that its synced to the
# server 4.
#
[connection server_1]
INSERT INTO t1 VALUES (3);
[connection server_4]
#
# 15. Stop server 1.
#
[connection server_4]
SET GLOBAL debug = '+d,replica_retry_count_exceed';
include/rpl_stop_server.inc [server_number=1]
#
# 16. Even after all source are stopped the replica IO thread keeps
# on re-trying to connect with all logged source in
# performance_schema.replication_asynchronous_connection_failover
# table. It tries connecting to server 3, 2, 1, and again 3, 2 1.
#
[connection server_4]
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed';
SET @@GLOBAL.DEBUG= @old_debug;
SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed';
SET DEBUG_SYNC= 'RESET';
#
# 17. Stop replica IO
#
include/stop_slave.inc
#
# 18. Clean up
#
CHANGE REPLICATION SOURCE TO SOURCE_CONNECTION_AUTO_FAILOVER=0;
SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_3, '');;
asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_3, '')
The UDF asynchronous_connection_failover_delete_source() executed successfully.
SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_2, '');;
asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_2, '')
The UDF asynchronous_connection_failover_delete_source() executed successfully.
SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_1, '');;
asynchronous_connection_failover_delete_source('', '127.0.0.1', SERVER_MYPORT_1, '')
The UDF asynchronous_connection_failover_delete_source() executed successfully.
include/rpl_start_server.inc [server_number=1]
[connection server_2]
include/rpl_start_server.inc [server_number=2]
include/start_slave.inc
[connection server_3]
include/rpl_start_server.inc [server_number=3]
include/start_slave.inc
[connection server_4]
include/start_slave.inc
[connection server_1]
DROP TABLE t1;
include/rpl_sync.inc
include/rpl_end.inc
|
