环境信息
| 角色 | 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/ root@10.186.61.26:/data/mysql/backup/
scp -r 20201207/ root@10.186.61.27:/data/mysql/backup/
## 拷贝主库master key到从库
scp /usr/local/mysql/keyring/keyring root@10.186.61.26:/tmp
从节点环境准备
部署相同版本的MySQL:
过程省略
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 statusG
更新master key
-- 主库触发master key更新会自动同步到从库,不影响复制
ALTER INSTANCE ROTATE INNODB MASTER KEY;