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;      

参考链接