| 1 |
############################################################################### |
| 2 |
# This test verifies the behaviour of GR+Keyring Plugin |
| 3 |
# |
| 4 |
# Test : |
| 5 |
# 0. This test requires 2 servers.(M1 and M2) |
| 6 |
# 1. Check behavior when keyring plugin is installed in both the servers. |
| 7 |
# 1.1 Install keyring plugin,Generate Master Key,Bootstrap start GR on M1 |
| 8 |
# 1.2 Execute DDL and DML on M1. |
| 9 |
# 1.3 Install keyring plugin and start GR on M2. |
| 10 |
# 1.4 Check if table exists and has same values on both the servers. |
| 11 |
# 2. Check behavior when keyring plugin is uninstalled in one of the servers. |
| 12 |
# 2.1 Uninstall keyring plugin on M2. |
| 13 |
# 2.2 Create table and Insert into table t3 on M1. |
| 14 |
# 2.3 Check that M2 is in ERROR state. |
| 15 |
# 2.4 Stop GR, Install keyring plugin and start GR on M2. |
| 16 |
# 2.5 Check that t3 exists and has same values on both the servers. |
| 17 |
# 3. Verify that the temporary file of binlog cache is encrypted when |
| 18 |
# the binlog cache spills to disk if binlog_encryption is on. |
| 19 |
# 3.1 Enable binlog_encryption on M1. |
| 20 |
# 3.2 Create a table t4 with TEXT column. |
| 21 |
# 3.3 Execute a trx to insert a big text into the table to make |
| 22 |
# binlog cache spill to disk and ensure that the temporary |
| 23 |
# file of binlog cache is encrypted. |
| 24 |
# 3.4 Check that t4 exists and has same values on both the servers. |
| 25 |
# 4. Clean Up. |
| 26 |
############################################################################### |
| 27 |
|
| 28 |
--source include/big_test.inc |
| 29 |
# Restrict the test runs to only debug builds, since we set DEBUG point in the test. |
| 30 |
--source include/have_debug.inc |
| 31 |
# Ensure that plugin is installed. |
| 32 |
--source include/have_group_replication_plugin_base.inc |
| 33 |
|
| 34 |
--let plugins = KEYRING_PLUGIN,GROUP_REPLICATION |
| 35 |
--source include/check_plugin_dir.inc |
| 36 |
|
| 37 |
--source include/have_group_replication_plugin.inc |
| 38 |
--let $rpl_skip_group_replication_start= 1 |
| 39 |
--source include/group_replication.inc |
| 40 |
|
| 41 |
set sql_log_bin=0; |
| 42 |
--disable_query_log |
| 43 |
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded."); |
| 44 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'Error while loading keyring content*"); |
| 45 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'keyring_file initialization failure.*"); |
| 46 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'File .*keyring' not found .*"); |
| 47 |
--enable_query_log |
| 48 |
set sql_log_bin=1; |
| 49 |
|
| 50 |
# Scenario 1 |
| 51 |
|
| 52 |
# Check that keyring plugin is not installed |
| 53 |
--error ER_CANNOT_FIND_KEY_IN_KEYRING |
| 54 |
ALTER INSTANCE ROTATE INNODB MASTER KEY; |
| 55 |
|
| 56 |
# Install keyring plugin on server1 |
| 57 |
--disable_warnings |
| 58 |
--replace_regex /\.dll/.so/ |
| 59 |
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN' |
| 60 |
--enable_warnings |
| 61 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 62 |
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server1/keyring_server1' |
| 63 |
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file' |
| 64 |
|
| 65 |
# Rotating master key when keyring file is empty. |
| 66 |
# It should generate new master key. |
| 67 |
ALTER INSTANCE ROTATE INNODB MASTER KEY; |
| 68 |
|
| 69 |
# Start Group Replication on server1 |
| 70 |
--source include/start_and_bootstrap_group_replication.inc |
| 71 |
|
| 72 |
# Creating table with encryption |
| 73 |
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; |
| 74 |
INSERT INTO t1 VALUES(1, "first"); |
| 75 |
INSERT INTO t1 VALUES(2, "second"); |
| 76 |
INSERT INTO t1 VALUES(3, "third"); |
| 77 |
CREATE TABLE t2 (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2)REFERENCES t1(c1)ON DELETE RESTRICT); |
| 78 |
INSERT INTO t2 VALUES(1,1); |
| 79 |
INSERT INTO t2 VALUEs(2,2); |
| 80 |
|
| 81 |
--let $rpl_connection_name= server2 |
| 82 |
--source include/rpl_connection.inc |
| 83 |
|
| 84 |
set sql_log_bin=0; |
| 85 |
--disable_query_log |
| 86 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded*"); |
| 87 |
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."); |
| 88 |
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*"); |
| 89 |
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*"); |
| 90 |
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.'"); |
| 91 |
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.'"); |
| 92 |
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'To prevent errors, the server read only mode was enabled following a plugin error.'"); |
| 93 |
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'The server was automatically set into read only mode after an error was detected.'"); |
| 94 |
call mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] \\[[^]]*\\] Plugin group_replication reported: 'Skipping leave operation:*"); |
| 95 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'Error while loading keyring content*"); |
| 96 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'keyring_file initialization failure.*"); |
| 97 |
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Plugin keyring_file reported: 'File .*keyring' not found .*"); |
| 98 |
--enable_query_log |
| 99 |
set sql_log_bin=1; |
| 100 |
|
| 101 |
# Check that keyring plugin is not installed |
| 102 |
--error ER_CANNOT_FIND_KEY_IN_KEYRING |
| 103 |
ALTER INSTANCE ROTATE INNODB MASTER KEY; |
| 104 |
|
| 105 |
# Install keyring plugin on server2 |
| 106 |
--disable_warnings |
| 107 |
--replace_regex /\.dll/.so/ |
| 108 |
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN' |
| 109 |
--enable_warnings |
| 110 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 111 |
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server2/keyring_server2' |
| 112 |
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file' |
| 113 |
|
| 114 |
--source include/start_group_replication.inc |
| 115 |
UPDATE t2 SET c1=3 WHERE c1=2; |
| 116 |
UPDATE t2 SET c2=NULL WHERE c2=3; |
| 117 |
DELETE FROM t1 WHERE c1=3; |
| 118 |
|
| 119 |
--source include/rpl_sync.inc |
| 120 |
--echo # check that t1 exists and has same values in both servers |
| 121 |
--let $diff_tables=server1:t1, server2:t1 |
| 122 |
--source include/diff_tables.inc |
| 123 |
--echo # check that t2 exists and has same values in both servers |
| 124 |
--let $diff_tables=server1:t2, server2:t2 |
| 125 |
--source include/diff_tables.inc |
| 126 |
|
| 127 |
# Scenario 2 |
| 128 |
|
| 129 |
# Uninstall keyring plugin on server2 |
| 130 |
UNINSTALL PLUGIN keyring_file; |
| 131 |
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file' |
| 132 |
|
| 133 |
# Even when the keyring plugin is uninstalled we can do DML and SELECT statements |
| 134 |
# on existing tables. But, we cant CREATE new tables or generate new MASTER key |
| 135 |
INSERT INTO t1 VALUES(7,'seventh'); |
| 136 |
|
| 137 |
# Cant create new tables with encryption |
| 138 |
--error ER_CANNOT_FIND_KEY_IN_KEYRING |
| 139 |
CREATE TABLE t3(c1 INT PRIMARY KEY, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; |
| 140 |
# Cant generate new Master Key |
| 141 |
--error ER_CANNOT_FIND_KEY_IN_KEYRING |
| 142 |
ALTER INSTANCE ROTATE INNODB MASTER KEY; |
| 143 |
|
| 144 |
--let $rpl_connection_name= server1 |
| 145 |
--source include/rpl_connection.inc |
| 146 |
CREATE TABLE t3(c1 INT PRIMARY KEY) ENCRYPTION="Y" ENGINE = InnoDB; |
| 147 |
INSERT INTO t3 VALUES(1); |
| 148 |
UPDATE t3 SET c1=2; |
| 149 |
|
| 150 |
# Server2 cant CREATE TABLE t3 as keyring plugin is not installed. |
| 151 |
--let $wait_timeout= 120 |
| 152 |
--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_members where CHANNEL_NAME="group_replication_applier" and MEMBER_STATE="ONLINE" |
| 153 |
--source include/wait_condition.inc |
| 154 |
|
| 155 |
--let $rpl_connection_name= server2 |
| 156 |
--source include/rpl_connection.inc |
| 157 |
--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_members where CHANNEL_NAME="group_replication_applier" and MEMBER_STATE="ERROR" |
| 158 |
--source include/wait_condition.inc |
| 159 |
|
| 160 |
--error ER_NO_SUCH_TABLE |
| 161 |
SELECT * FROM t3; |
| 162 |
--source include/stop_group_replication.inc |
| 163 |
|
| 164 |
# Install plugin on server2 and start GR |
| 165 |
--disable_warnings |
| 166 |
--replace_regex /\.dll/.so/ |
| 167 |
--eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN' |
| 168 |
--enable_warnings |
| 169 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 170 |
--eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/keyring_server2/keyring_server2' |
| 171 |
--query_vertical SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file' |
| 172 |
|
| 173 |
--source include/start_group_replication.inc |
| 174 |
DELETE FROM t3 WHERE c1=1; |
| 175 |
DELETE FROM t2 WHERE c1=1; |
| 176 |
DELETE FROM t1 WHERE c1=1; |
| 177 |
|
| 178 |
--source include/rpl_sync.inc |
| 179 |
--echo # check that t1 exists and has same values in both servers |
| 180 |
--let $diff_tables=server1:t1, server2:t1 |
| 181 |
--source include/diff_tables.inc |
| 182 |
--echo # check that t2 exists and has same values in both servers |
| 183 |
--let $diff_tables=server1:t2, server2:t2 |
| 184 |
--source include/diff_tables.inc |
| 185 |
--echo # check that t3 exists and has same values in both servers |
| 186 |
--let $diff_tables=server1:t3, server2:t3 |
| 187 |
|
| 188 |
# Scenario 3 |
| 189 |
|
| 190 |
# 3.1 Enable binlog_encryption on M1. |
| 191 |
--let $rpl_connection_name= server1 |
| 192 |
--source include/rpl_connection.inc |
| 193 |
|
| 194 |
SET GLOBAL binlog_encryption=ON; |
| 195 |
|
| 196 |
# 3.2 Create a table t4 with TEXT column. |
| 197 |
--let $data_size= `select 0.5 * @@global.binlog_cache_size` |
| 198 |
|
| 199 |
CREATE TABLE t4 (c1 TEXT,c2 INT AUTO_INCREMENT PRIMARY KEY); |
| 200 |
|
| 201 |
# 3.3 Execute a trx to insert a big text into the table to make binlog cache spill to disk |
| 202 |
--let $debug_point= ensure_binlog_cache_temporary_file_is_encrypted |
| 203 |
--source include/add_debug_point.inc |
| 204 |
|
| 205 |
eval INSERT INTO t4(c1) VALUES (REPEAT('123', $data_size)); |
| 206 |
|
| 207 |
--let $debug_point= ensure_binlog_cache_temporary_file_is_encrypted |
| 208 |
--source include/remove_debug_point.inc |
| 209 |
|
| 210 |
SET GLOBAL binlog_encryption = OFF; |
| 211 |
|
| 212 |
# 3.4 Check that t4 exists and has same values on both the servers. |
| 213 |
--source include/rpl_sync.inc |
| 214 |
--echo # check that t4 exists and has same values in both servers |
| 215 |
--let $diff_tables=server1:t4, server2:t4 |
| 216 |
|
| 217 |
# Uninstall keyring plugin and remove dummy keyring file on server2 |
| 218 |
--let $rpl_connection_name= server2 |
| 219 |
--source include/rpl_connection.inc |
| 220 |
UNINSTALL PLUGIN keyring_file; |
| 221 |
--remove_file $MYSQL_TMP_DIR/mydummy_key2 |
| 222 |
|
| 223 |
# Uninstall keyring plugin and remove dummy keyring file on server1 |
| 224 |
--let $rpl_connection_name= server1 |
| 225 |
--source include/rpl_connection.inc |
| 226 |
UNINSTALL PLUGIN keyring_file; |
| 227 |
--remove_file $MYSQL_TMP_DIR/mydummy_key1 |
| 228 |
|
| 229 |
DROP TABLE t2; |
| 230 |
DROP TABLE t3; |
| 231 |
DROP TABLE t1; |
| 232 |
DROP TABLE t4; |
| 233 |
|
| 234 |
--source include/force_restart.inc |
| 235 |
--source include/group_replication_end.inc |