开启了TDE下的mysql主从部署
环境:
OS:Centos 7
mysql:5.7.39
1.主库开启了TDE
mysql> show variables like '%keyring%';
+--------------------+------------------------------+
| Variable_name | Value |
+--------------------+------------------------------+
| keyring_file_data | /opt/mysql57/keyring/keyring |
| keyring_operations | ON |
+--------------------+------------------------------+
2 rows in set (0.00 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-----------------+---------+
| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL |
2.主库创建复制账号
grant replication slave, replication client on *.* to repl@'192.168.1.%' identified by 'mysql';
3.主库备份
/opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --socket=/opt/mysql57/data/mysql.sock --password=mysql -P3306 /tmp/xtrabackup_file
备注:秘钥文件是不会备份到备份文件的,备份目录文件如下:
[root@host135 xtrabackup_file]# ls
2024-12-30_09-15-30
[root@host135 2024-12-30_09-15-30]# ls
backup-my.cnf ibdata1 sys xtrabackup_info
db_test mysql xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool performance_schema xtrabackup_checkpoints
[root@host135 2024-12-30_09-15-30]#
4.拷贝主库的备份文件到目标机器
scp -r 2024-12-30_09-15-30 root@192.168.1.134:/tmp/xtrabackup_file
5.目的库停掉msyql
若目的机器上部署了mysql,需要将其停掉
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock shutdown
清空data目录,我们恢复的时候需要恢复到该目录
[root@localhost mysql57]#cd /opt/mysql57
[root@localhost mysql57]#mv data bakdata
[root@localhost mysql57]#mkdir data
6.恢复
应用日志
/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --apply-log /tmp/xtrabackup_file/2024-12-30_09-15-30
拷贝恢复文件到data目录
/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --copy-back --rsync /tmp/xtrabackup_file/2024-12-30_09-15-30
7.修改权限
chown -R mysql:mysql /opt/mysql57
8.目的端不开开启TDE
vi my.cnf
注释如下两行,并删除 /opt/mysql57/keyring 目录下的文件
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql57/keyring/keyring
修改为
#early-plugin-load=keyring_file.so
#keyring_file_data=/opt/mysql57/keyring/keyring
[root@localhost conf]# cd /opt/mysql57/keyring
[root@localhost keyring]# ls
bak_keyring keyring my_keyring
[root@localhost keyring]# rm *
9.启动目的库
[root@localhost conf]# /opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
10.配置主从
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
mysql> reset slave;
mysql> reset master;
mysql> set global gtid_purged='a24c0186-c400-11ef-944f-52540051cd25:1,d6696ebd-fb91-11ee-b632-52540051cd25:1-29,f58f88cb-f478-11ed-b257-525400c8dc1f:1-200000';
上面的值可以从 xtrabackup_info 文件中获取:GTID of the last change '74f5f6a5-b1c2-11ed-b523-fa163eb498c0:1-135'
binlog_pos = filename 'binlog.000007', position '554', GTID of the last change 'a24c0186-c400-11ef-944f-52540051cd25:1,d6696ebd-fb91-11ee-b632-52540051cd25:1-29,f58f88cb-f478-11ed-b257-525400c8dc1f:1-200000'
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| a24c0186-c400-11ef-944f-52540051cd25 | 1 | 1 |
| d6696ebd-fb91-11ee-b632-52540051cd25 | 1 | 29 |
| f58f88cb-f478-11ed-b257-525400c8dc1f | 1 | 200000 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)
查询是否与 xtrabackup_info 记录的一致
mysql>change master to master_host='192.168.1.135',
master_user='repl',
master_password='mysql',
master_port=3306,
master_auto_position=1;
启动从库
mysql> start slave;
Query OK, 0 rows affected (0.27 sec)
发现端口错误,修改端口
mysql> stop slave;
mysql> change master to master_port=13306;
mysql> start slave;
11.查看目前从库状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.135
Master_User: repl
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: binlog.000007
Read_Master_Log_Pos: 554
Relay_Log_File: relaylog-binlog.000002
Relay_Log_Pos: 405
Relay_Master_Log_File: binlog.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
目前主从复制是正常的,此时的从库是没有开启TDE的
mysql> show variables like '%keyring%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| keyring_operations | ON |
+--------------------+-------+
1 row in set (0.00 sec)
下面我们在主库上对加密的表进行操作,看同步是否正常.
12.主库上对非加密表进行操作
mysql> insert into tb_no_tde(name) values('name11');
Query OK, 1 row affected (0.17 sec)
从库查询
mysql> select * from tb_no_tde where name='name11';
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 11 | name11 | 2024-12-30 09:57:01 | 2024-12-30 09:57:01 |
+----+--------+---------------------+---------------------+
1 row in set (0.00 sec)
可以看到从库是同步过来了.
13.主库上对加密表进行操作
insert into tb_tde(name) values('name11');
查看从库:
mysql> select * from tb_tde where name='name11';
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
mysql> select * from tb_tde;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
从库应用报错误:
Last_Error: Error executing row event: 'Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.'
说明从库需要开启TDE,使用主库的秘钥
14.停掉从库并开启TDE
停掉从库应用
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
关闭数据库
[root@localhost conf]# /opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock shutdown
启用TDE
vi my.cnf
添加如下两行
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql57/keyring/keyring
将主库的秘钥文件拷贝过来(主库上操作),拷贝过来后注意修改权限
scp /opt/mysql57/keyring/keyring root@192.168.1.134:/opt/mysql57/keyring/
chown -R mysql:mysql /opt/mysql57/keyring
15.重新启动从库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
启动主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
这个时候查询正常了.
mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tb_tde where name='name11';
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 11 | name11 | 2024-12-30 09:58:46 | 2024-12-30 09:58:46 |
+----+--------+---------------------+---------------------+
1 row in set (0.00 sec)
16.主库更新秘钥
查看主库的秘钥
[root@host135 keyring]# cd /opt/mysql57/keyring
[root@host135 keyring]# ls -al
total 4
drwxrwxr-x 2 mysql mysql 21 Dec 26 16:07 .
drwxrwxr-x 15 mysql mysql 218 Dec 27 11:05 ..
-rw-r----- 1 mysql mysql 155 Dec 26 16:07 keyring
从库的秘钥文件
[root@localhost data]# cd /opt/mysql57/keyring
[root@localhost keyring]# ls -al
total 4
drwxrwxr-x 2 mysql mysql 21 Dec 30 10:04 .
drwxrwxr-x 16 mysql mysql 240 Dec 30 09:21 ..
-rw-r----- 1 mysql mysql 155 Dec 30 10:04 keyring
主库执行如下语句
mysql> alter instance rotate innodb master key;
Query OK, 0 rows affected (0.15 sec)
再次查看主库的秘钥
[root@host135 keyring]# ls -al
total 4
drwxrwxr-x 2 mysql mysql 21 Dec 30 10:39 .
drwxrwxr-x 15 mysql mysql 218 Dec 27 11:05 ..
-rw-r----- 1 mysql mysql 283 Dec 30 10:39 keyring
重新生成了,时间戳发生了改变
从库
[root@localhost keyring]# ls -al
total 4
drwxrwxr-x 2 mysql mysql 21 Dec 30 10:39 .
drwxrwxr-x 16 mysql mysql 240 Dec 30 09:21 ..
-rw-r----- 1 mysql mysql 283 Dec 30 10:39 keyring
从库自动跟新过来了.
结论:
1.开启了TDE的主库,从库也需要开启TDE,而且使用相同的秘钥;
2.主库执行 alter instance rotate innodb master key 从库会自动同步,从库也可以执行该语句,好像不影响主从同步;
