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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
###############################################################################
# This test verifies the behaviour of GR+Keyring Plugin
#
# Test :
# 0. This test requires 2 servers.(M1 and M2)
# 1. Check behavior when keyring plugin is installed in both the servers.
# 1.1 Install keyring plugin,Generate Master Key,Bootstrap start GR on M1
# 1.2 Execute DDL and DML on M1.
# 1.3 Install keyring plugin and start GR on M2.
# 1.4 Check if table exists and has same values on both the servers.
# 2. Check behavior when keyring plugin is uninstalled in one of the servers.
# 2.1 Uninstall keyring plugin on M2.
# 2.2 Create table and Insert into table t3 on M1.
# 2.3 Check that M2 is in ERROR state.
# 2.4 Stop GR, Install keyring plugin and start GR on M2.
# 2.5 Check that t3 exists and has same values on both the servers.
# 3. Verify that the temporary file of binlog cache is encrypted when
# the binlog cache spills to disk if binlog_encryption is on.
# 3.1 Enable binlog_encryption on M1.
# 3.2 Create a table t4 with TEXT column.
# 3.3 Execute a trx to insert a big text into the table to make
# binlog cache spill to disk and ensure that the temporary
# file of binlog cache is encrypted.
# 3.4 Check that t4 exists and has same values on both the servers.
# 4. Clean Up.
###############################################################################
--source include/big_test.inc
# Restrict the test runs to only debug builds, since we set DEBUG point in the test.
--source include/have_debug.inc
# Ensure that plugin is installed.
--source include/have_group_replication_plugin_base.inc
--let plugins = KEYRING_PLUGIN,GROUP_REPLICATION
--source include/check_plugin_dir.inc
--source include/have_group_replication_plugin.inc
--let $rpl_skip_group_replication_start= 1
--source include/group_replication.inc
set sql_log_bin=0;
--disable_query_log
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded.");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'Error while loading keyring content*");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'keyring_file initialization failure.*");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'File .*keyring' not found .*");
--enable_query_log
set sql_log_bin=1;
# Scenario 1
# Check that keyring plugin is not installed
--error ER_CANNOT_FIND_KEY_IN_KEYRING
ALTER INSTANCE ROTATE INNODB MASTER KEY;
# Install keyring plugin on server1
--disable_warnings
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN'
--enable_warnings
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server1/keyring_server1'
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file'
# Rotating master key when keyring file is empty.
# It should generate new master key.
ALTER INSTANCE ROTATE INNODB MASTER KEY;
# Start Group Replication on server1
--source include/start_and_bootstrap_group_replication.inc
# Creating table with encryption
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
INSERT INTO t1 VALUES(1, "first");
INSERT INTO t1 VALUES(2, "second");
INSERT INTO t1 VALUES(3, "third");
CREATE TABLE t2 (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2)REFERENCES t1(c1)ON DELETE RESTRICT);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t2 VALUEs(2,2);
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
set sql_log_bin=0;
--disable_query_log
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded*");
call mtr.add_suppression(".*Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.");
call mtr.add_suppression(".*Replica SQL for channel 'group_replication_applier': Error 'Can't find master key from keyring, please check keyring is loaded.' on query*");
call mtr.add_suppression(".*Replica SQL for channel 'group_replication_applier': ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state*");
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'");
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'");
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'To prevent errors, the server read only mode was enabled following a plugin error.'");
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'");
call mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'Skipping leave operation:*");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'Error while loading keyring content*");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'keyring_file initialization failure.*");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'File .*keyring' not found .*");
--enable_query_log
set sql_log_bin=1;
# Check that keyring plugin is not installed
--error ER_CANNOT_FIND_KEY_IN_KEYRING
ALTER INSTANCE ROTATE INNODB MASTER KEY;
# Install keyring plugin on server2
--disable_warnings
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN'
--enable_warnings
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server2/keyring_server2'
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file'
--source include/start_group_replication.inc
UPDATE t2 SET c1=3 WHERE c1=2;
UPDATE t2 SET c2=NULL WHERE c2=3;
DELETE FROM t1 WHERE c1=3;
--source include/rpl_sync.inc
--echo # check that t1 exists and has same values in both servers
--let $diff_tables=server1:t1, server2:t1
--source include/diff_tables.inc
--echo # check that t2 exists and has same values in both servers
--let $diff_tables=server1:t2, server2:t2
--source include/diff_tables.inc
# Scenario 2
# Uninstall keyring plugin on server2
UNINSTALL PLUGIN keyring_file;
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file'
# Even when the keyring plugin is uninstalled we can do DML and SELECT statements
# on existing tables. But, we cant CREATE new tables or generate new MASTER key
INSERT INTO t1 VALUES(7,'seventh');
# Cant create new tables with encryption
--error ER_CANNOT_FIND_KEY_IN_KEYRING
CREATE TABLE t3(c1 INT PRIMARY KEY, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
# Cant generate new Master Key
--error ER_CANNOT_FIND_KEY_IN_KEYRING
ALTER INSTANCE ROTATE INNODB MASTER KEY;
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
CREATE TABLE t3(c1 INT PRIMARY KEY) ENCRYPTION="Y" ENGINE = InnoDB;
INSERT INTO t3 VALUES(1);
UPDATE t3 SET c1=2;
# Server2 cant CREATE TABLE t3 as keyring plugin is not installed.
--let $wait_timeout= 120
--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_members where CHANNEL_NAME="group_replication_applier" and MEMBER_STATE="ONLINE"
--source include/wait_condition.inc
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_members where CHANNEL_NAME="group_replication_applier" and MEMBER_STATE="ERROR"
--source include/wait_condition.inc
--error ER_NO_SUCH_TABLE
SELECT * FROM t3;
--source include/stop_group_replication.inc
# Install plugin on server2 and start GR
--disable_warnings
--replace_regex /\.dll/.so/
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN'
--enable_warnings
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server2/keyring_server2'
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file'
--source include/start_group_replication.inc
DELETE FROM t3 WHERE c1=1;
DELETE FROM t2 WHERE c1=1;
DELETE FROM t1 WHERE c1=1;
--source include/rpl_sync.inc
--echo # check that t1 exists and has same values in both servers
--let $diff_tables=server1:t1, server2:t1
--source include/diff_tables.inc
--echo # check that t2 exists and has same values in both servers
--let $diff_tables=server1:t2, server2:t2
--source include/diff_tables.inc
--echo # check that t3 exists and has same values in both servers
--let $diff_tables=server1:t3, server2:t3
# Scenario 3
# 3.1 Enable binlog_encryption on M1.
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
SET GLOBAL binlog_encryption=ON;
# 3.2 Create a table t4 with TEXT column.
--let $data_size= `select 0.5 * @@global.binlog_cache_size`
CREATE TABLE t4 (c1 TEXT,c2 INT AUTO_INCREMENT PRIMARY KEY);
# 3.3 Execute a trx to insert a big text into the table to make binlog cache spill to disk
--let $debug_point= ensure_binlog_cache_temporary_file_is_encrypted
--source include/add_debug_point.inc
eval INSERT INTO t4(c1) VALUES (REPEAT('123', $data_size));
--let $debug_point= ensure_binlog_cache_temporary_file_is_encrypted
--source include/remove_debug_point.inc
SET GLOBAL binlog_encryption = OFF;
# 3.4 Check that t4 exists and has same values on both the servers.
--source include/rpl_sync.inc
--echo # check that t4 exists and has same values in both servers
--let $diff_tables=server1:t4, server2:t4
# Uninstall keyring plugin and remove dummy keyring file on server2
--let $rpl_connection_name= server2
--source include/rpl_connection.inc
UNINSTALL PLUGIN keyring_file;
--remove_file $MYSQL_TMP_DIR/mydummy_key2
# Uninstall keyring plugin and remove dummy keyring file on server1
--let $rpl_connection_name= server1
--source include/rpl_connection.inc
UNINSTALL PLUGIN keyring_file;
--remove_file $MYSQL_TMP_DIR/mydummy_key1
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t1;
DROP TABLE t4;
--source include/force_restart.inc
--source include/group_replication_end.inc
|
