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
|
#################
# Initialization
#################
include/rpl_init.inc [topology=1->2]
connection server_2;
include/stop_slave_sql.inc
#################
# Test flow
#################
connection server_1;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
CREATE DATABASE database_name_to_encrypt;
USE database_name_to_encrypt;
CREATE USER user_name_to_encrypt;
GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
CREATE TABLE innodb_table_name_to_encrypt (
int_column_name_to_encrypt INT PRIMARY KEY,
timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
blob_column_name_to_encrypt BLOB,
virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
) ENGINE=InnoDB
PARTITION BY RANGE (int_column_name_to_encrypt)
SUBPARTITION BY KEY (int_column_name_to_encrypt)
SUBPARTITIONS 2 (
PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
)
;
CREATE TABLE myisam_table_name_to_encrypt (
int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
char_column_name_to_encrypt VARCHAR(255),
datetime_column_name_to_encrypt DATETIME,
text_column_name_to_encrypt TEXT
) ENGINE=MyISAM;
CREATE TABLE aria_table_name_to_encrypt (
int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
varchar_column_name_to_encrypt VARCHAR(1024),
enum_column_name_to_encrypt ENUM(
'enum_value1_to_encrypt',
'enum_value2_to_encrypt'
),
timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
blob_column_name_to_encrypt BLOB
) ENGINE=Aria;
CREATE TRIGGER trigger_name_to_encrypt
AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
VALUES (NEW.char_column_name_to_encrypt);
CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
AS SELECT * FROM innodb_table_name_to_encrypt;
CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
RETURNS VARCHAR(64)
RETURN 'func_result_to_encrypt';
CREATE PROCEDURE proc_name_to_encrypt (
IN proc_in_parameter_to_encrypt CHAR(32),
OUT proc_out_parameter_to_encrypt INT
)
BEGIN
DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
DECLARE cursor_name_to_encrypt CURSOR FOR
SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
DECLARE EXIT HANDLER FOR NOT FOUND
BEGIN
SET @stmt_var_to_encrypt = CONCAT(
"SELECT
IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
FROM innodb_table_name_to_encrypt
INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
EXECUTE stmt_to_encrypt;
DEALLOCATE PREPARE stmt_to_encrypt;
END;
OPEN cursor_name_to_encrypt;
proc_label_to_encrypt: LOOP
FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
END LOOP;
CLOSE cursor_name_to_encrypt;
END $$
CREATE SERVER server_name_to_encrypt
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST 'host_name_to_encrypt');
connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
float_column_name_to_encrypt FLOAT,
binary_column_name_to_encrypt BINARY(64)
);
disconnect con1;
connection server_1;
CREATE INDEX index_name_to_encrypt
ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
ALTER TABLE innodb_table_name_to_encrypt
MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
;
ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
AS SELECT * FROM innodb_table_name_to_encrypt;
RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
INSERT INTO view_name_to_encrypt VALUES
(1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
(2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
;
BEGIN NOT ATOMIC
DECLARE counter_name_to_encrypt INT DEFAULT 0;
START TRANSACTION;
WHILE counter_name_to_encrypt<12 DO
SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
INSERT INTO innodb_table_name_to_encrypt
SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
FROM innodb_table_name_to_encrypt
ORDER BY int_column_name_to_encrypt;
SET counter_name_to_encrypt = counter_name_to_encrypt+1;
END WHILE;
COMMIT;
END
$$
INSERT INTO myisam_table_name_to_encrypt
SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
TRUNCATE TABLE aria_table_name_to_encrypt;
LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
(enum_column_name_to_encrypt);
LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
;
DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
ANALYZE TABLE myisam_table_name_to_encrypt;
CHECK TABLE aria_table_name_to_encrypt;
CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
DROP DATABASE database_name_to_encrypt;
DROP USER new_user_name_to_encrypt;
DROP SERVER server_name_to_encrypt;
#################
# Master binlog checks
#################
FOUND 1 /_to_encrypt.*/ in master-bin.0*
FOUND 1 /COMMIT.*/ in master-bin.0*
FOUND 1 /TIMESTAMP.*/ in master-bin.0*
include/save_master_pos.inc
#################
# Relay log checks
#################
connection server_2;
include/sync_io_with_master.inc
NOT FOUND /_to_encrypt/ in slave-relay-bin.0*
NOT FOUND /COMMIT/ in slave-relay-bin.0*
NOT FOUND /TIMESTAMP/ in slave-relay-bin.0*
#################
# Slave binlog checks
#################
include/start_slave.inc
include/sync_slave_sql_with_io.inc
include/sync_io_with_master.inc
NOT FOUND /_to_encrypt/ in slave-bin.0*
NOT FOUND /COMMIT/ in slave-bin.0*
NOT FOUND /TIMESTAMP/ in slave-bin.0*
##########
# Cleanup
##########
include/rpl_end.inc
|