MySQL 5.7.31 開啟TDE功能後使用xtrabackup搭建主從配置

目錄

• ​​環境資訊​​
• ​​主節點環境準備​​

• ​​開啟keyring-file方式TDE加密​​
• ​​配置表開啟TDE加密​​
• ​​資料備份​​

• ​​從節點環境準備​​

• ​​MySQL準備​​
• ​​恢複準備​​
• ​​啟動資料庫​​

• ​​建立複制同步​​
• ​​更新master key​​
• ​​參考連結​​

環境資訊

角色	IP	端口	版本
主節點	10.186.61.25	3306	5.7.31
從節點	10.186.61.25	3306	5.7.31
壓力機	10.186.61.162	/	/

主節點環境準備

-- 清理環境資訊
stop slave;
stop slave all;
reset master;

-- 建立測試庫
create database demo;

-- 建立sysbench使用者(模拟應用壓力)
create user 'sysbench'@'10.186.%' identified WITH mysql_native_password by 'sysbench';
grant all on demo.* to 'sysbench'@'10.186.%';

-- 建立複制同步使用者
CREATE USER 'repl'@'10.186.%' IDENTIFIED WITH mysql_native_password BY 'repl';
grant replication client,replication slave on *.* to 'repl'@'10.186.%';

-- 建立備份使用者
create user 'backup'@'10.186.%' identified by 'backup';
grant SELECT,SHOW VIEW,EVENT,TRIGGER,LOCK TABLES,RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'backup'@'10.186.%';

-- 模拟壓力
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=10.186.61.25 --mysql-port=3306 --mysql-user=sysbench --mysql-password='sysbench' --mysql-db=demo --db-ps-mode=disable --tables=4 --table-size=1000000 --report-interval=1 --threads=4 --time=300 prepare

sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=10.186.61.25 --mysql-port=3306 --mysql-user=sysbench --mysql-password='sysbench' --mysql-db=demo --db-ps-mode=disable --tables=4 --table-size=1000000 --report-interval=1 --threads=4 --time=300 run      

開啟keyring-file方式TDE加密

install plugin keyring_file soname "keyring_file.so";

root@localhost[(none)]> select plugin_name,plugin_status from information_schema.plugins where plugin_name='keyring_file';
+--------------+---------------+
| plugin_name  | plugin_status |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+

root@localhost[(none)]> show variables like '%keyring%';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| keyring_file_data  | /usr/local/mysql/keyring/keyring |
| keyring_operations | ON                               |
+--------------------+----------------------------------+

-- 配置my.cnf
# keyring
early-plugin-load          = keyring_file.so
keyring_file_data          = /usr/local/mysql/keyring/keyring      

配置表開啟TDE加密

root@localhost[demo]> CREATE TABLE t1 (c1 bigint auto_increment primary key) ENCRYPTION='Y';

root@localhost[demo]> insert into t1 select null;
root@localhost[demo]> insert into t1 select null from t1;

root@localhost[demo]> alter table demo.sbtest1 encryption='Y';
Query OK, 1000000 rows affected (13.48 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

root@localhost[demo]> alter table demo.sbtest2 encryption='Y';
Query OK, 1000000 rows affected (14.68 sec)
Records: 1000000  Duplicates: 0  Warnings: 0      

資料備份

## 備份-innobackupex
innobackupex --defaults-file=/etc/my.cnf --slave-info --host=10.186.61.25 --port=3306 --user=backup --password=backup /data/mysql/backup/

## 備份-xtrabackup
mkdir /data/mysql/backup/20201207/

xtrabackup --defaults-file=/etc/my.cnf --slave-info --host=10.186.61.25 --port=3306 --user=backup --password=backup --target-dir=/data/mysql/backup/20201207/ --keyring-file-data=/usr/local/mysql/keyring/keyring --backup

## 拷貝至從庫伺服器
cd /data/mysql/backup/
scp -r 2020-12-07_13-37-19/ [email protected]:/data/mysql/backup/
scp -r 20201207/ [email protected]:/data/mysql/backup/

## 拷貝主庫master key到從庫
scp /usr/local/mysql/keyring/keyring [email protected]:/tmp      

從節點環境準備

MySQL準備

## 停止從庫新建立的資料庫
systemctl stop mysql_3306

## 删除資料目錄下所有資料
cd /data/mysql/data/
rm -rf *      

恢複準備

## apply log - innobackupex
innobackupex --apply-log --keyring-file-data=/tmp/keyring 2020-12-07_13-37-19/

## apply log - xtrabackup
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/mysql/backup/20201207/ --keyring-file-data=/tmp/keyring

## move back - innobackupex
innobackupex --defaults-file=/etc/my.cnf --move-back 2020-12-07_13-37-19/

## move back - xtrabackup
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/mysql/backup/20201207/ --keyring-file-data=/tmp/keyring

## chown
chown -R mysql:mysql /data/mysql/data

## master key
cp /tmp/keyring /usr/local/mysql/keyring/
chown mysql:mysql /usr/local/mysql/keyring/keyring      

啟動資料庫

## 啟動資料庫
systemctl start mysql_3306

## 觀測日志無異常輸出
tail -f /data/mysql/data/mysql-error.log      

建立複制同步

reset master;

-- GTID點從xtrabackup目錄中xtrabackup_binlog_info檔案擷取
set global gtid_purged='02f4f4a7-383d-11eb-aabd-02000aba3d19:1-93080';

CHANGE MASTER TO
  MASTER_HOST='10.186.61.25',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=10;
start slave;
show slave status\G      

更新master key

-- 主庫觸發master key更新會自動同步到從庫,不影響複制
ALTER INSTANCE ROTATE INNODB MASTER KEY;      

參考連結