开启TDE后的xtrabackup的备份恢复
环境:
OS:Centos 7
mysql:5.7.39
xtrabackup:2.4.7
说明:源库开启了TDE
1.备份
/opt/xtrabackup247/bin/innobackupex --defaults-file=/opt/mysql57/conf/my.cnf --user=root --password=mysql -P13306 --socket=/opt/mysql57/data/mysql.sock /tmp/xtrabackup_file
开启TDE的备份,需要备份只能使用xtrabackup命令,
/opt/xtrabackup247/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --password=mysql --port=13306 --socket=/opt/mysql57/data/mysql.sock --keyring-file-data=/opt/mysql57/keyring/keyring --target-dir=/tmp/xtrabackup_file --backup
查看备份文件是没有备份tde文件的
[root@host135 xtrabackup_file]#find ./ -name keyring
[root@host135 xtrabackup_file]#pwd
/tmp/xtrabackup_file
带上--keyring-file-data参数也没什么用
结论:xtrabackup不会把秘钥文件拷贝到备份目录
#####################################本地恢复####################################################
1 停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql --default-character-set=utf8 -S /opt/mysql57/data/mysql.sock shutdown
2 备份原有数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata
同时创建新的目录
[root@host135 mysql57]# mkdir data
3 恢复(直接恢复到数据目录,不需要--copy-back --rsync)
正确
一致性准备
/opt/xtrabackup247/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --prepare
这个时候会生成ib_logfile0和ib_buffer_pool这些文件
[root@host135 xtrabackup_file]# ls -al
total 2654268
drwxrwxr-x 6 root root 4096 Dec 27 11:09 .
drwxrwxrwt. 26 root root 4096 Dec 27 11:10 ..
-rw-r----- 1 root root 427 Dec 27 09:51 backup-my.cnf
drwxr-x--- 2 root root 98 Dec 27 09:51 db_test
-rw-r----- 1 root root 313 Dec 27 09:51 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec 27 11:09 ibdata1
-rw-r----- 1 root root 536870912 Dec 27 11:09 ib_logfile0
-rw-r----- 1 root root 536870912 Dec 27 11:09 ib_logfile1
-rw-r----- 1 root root 536870912 Dec 27 11:09 ib_logfile2
-rw-r----- 1 root root 536870912 Dec 27 11:09 ib_logfile3
-rw-r----- 1 root root 536870912 Dec 27 11:09 ib_logfile4
-rw-r----- 1 root root 12582912 Dec 27 11:09 ibtmp1
drwxr-x--- 2 root root 4096 Dec 27 09:51 mysql
drwxr-x--- 2 root root 8192 Dec 27 09:51 performance_schema
drwxr-x--- 2 root root 8192 Dec 27 09:51 sys
-rw-r----- 1 root root 108 Dec 27 09:51 xtrabackup_binlog_info
-rw-rw-r-- 1 root root 19 Dec 27 11:09 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 113 Dec 27 11:09 xtrabackup_checkpoints
-rw-r----- 1 root root 751 Dec 27 09:51 xtrabackup_info
-rw-r----- 1 root root 8388608 Dec 27 11:09 xtrabackup_logfile
拷贝文件到数据目录(若备份文件直接放到数据目录的话,这一步可以不需要)
/opt/xtrabackup247/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --copy-back
4 修改权限
chown -R mysql:mysql /opt/mysql57
5 启动
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
6 登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
#######################异地恢复(实例开启了TDE)##################################
说明:
目的库也开启了TDE,keyring_file使用的是本身自己的keyring_file
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql57/keyring/keyring
1.源库的备份文件拷贝到目的库
scp -r /tmp/xtrabackup_file root@192.168.1.134:/tmp/
2.停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql --default-character-set=utf8 -S /opt/mysql57/data/mysql.sock shutdown
3. 备份原有数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata
同时创建新的目录
[root@host135 mysql57]# mkdir data
4.尝试恢复
一致性准备
/opt/xtrabackup-2.4.7/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --prepare
拷贝文件到数据目录(若备份文件直接放到数据目录的话,这一步可以不需要)
/opt/xtrabackup-2.4.7/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --copy-back
5.修改权限
chown -R mysql:mysql /opt/mysql57
6.启动
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
7.登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
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.
发现不可用
8.拷贝源端的秘钥文件到目的机器
停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql --default-character-set=utf8 -S /opt/mysql57/data/mysql.sock shutdown
备份现有的秘钥
[root@localhost keyring]# pwd
/opt/mysql57/keyring
[root@localhost keyring]# cp keyring bak_keyring
拷贝源端的秘钥到目的机器
scp /opt/mysql57/keyring/keyring root@192.168.1.134:/opt/mysql57/keyring/
修改权限
chown -R mysql:mysql /opt/mysql57
启动数据库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
再次查看
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
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> show tables
-> ;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_no_tde |
| tb_tde |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from tb_tde;
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 1 | name1 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 2 | name2 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 3 | name3 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 4 | name4 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 5 | name5 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 6 | name6 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 7 | name7 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 8 | name8 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 9 | name9 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 10 | name10 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
+----+--------+---------------------+---------------------+
10 rows in set (0.03 sec)
结论:若没有源实例的密钥文件,恢复xtrabackup备份,但是无法查看表数据.
#######################################异地恢复(没有开启TDE)##################################
1.源库的备份文件拷贝到目的库
scp -r /tmp/xtrabackup_file root@192.168.1.134:/tmp/
2.停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql --default-character-set=utf8 -S /opt/mysql57/data/mysql.sock shutdown
3. 备份原有数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata
同时创建新的目录
[root@host135 mysql57]# mkdir data
4.尝试恢复
一致性准备
/opt/xtrabackup-2.4.7/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --prepare
拷贝文件到数据目录(若备份文件直接放到数据目录的话,这一步可以不需要)
/opt/xtrabackup-2.4.7/bin/xtrabackup --defaults-file=/opt/mysql57/conf/my.cnf --user=root --target-dir=/tmp/xtrabackup_file --copy-back
5.修改权限
chown -R mysql:mysql /opt/mysql57
6.启动
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
7.登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tb_no_tde |
| tb_tde |
+-------------------+
2 rows in set (0.00 sec)
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.
加密表无法访问,不加密表可以访问
mysql> select * from tb_no_tde;
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 1 | name1 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 2 | name2 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 3 | name3 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 4 | name4 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 5 | name5 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 6 | name6 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 7 | name7 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 8 | name8 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 9 | name9 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
| 10 | name10 | 2024-12-26 17:08:46 | 2024-12-26 17:08:46 |
+----+--------+---------------------+---------------------+
10 rows in set (0.00 sec)
8.拷贝源端的秘钥文件到目的机器
停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -pmysql --default-character-set=utf8 -S /opt/mysql57/data/mysql.sock shutdown
拷贝源端的秘钥到目的机器
scp /opt/mysql57/keyring/keyring root@192.168.1.134:/opt/mysql57/keyring/
修改配置文件,启用TDE
我这里尝试使用新的秘钥文件,发现是不行的,必须使用原数据库的秘钥文件才可以
mkdir -p /opt/mysql57/keyring
vi my.cnf
early-plugin-load=keyring_file.so
keyring_file_data=/opt/mysql57/keyring/keyring
修改权限
chown -R mysql:mysql /opt/mysql57
启动数据库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
再次查看
/opt/mysql57/bin/mysql -h localhost -uroot -pmysql -P3306 -S /opt/mysql57/data/mysql.sock
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;
+----+--------+---------------------+---------------------+
| id | name | create_time | update_time |
+----+--------+---------------------+---------------------+
| 1 | name1 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 2 | name2 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 3 | name3 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 4 | name4 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 5 | name5 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 6 | name6 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 7 | name7 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 8 | name8 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 9 | name9 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
| 10 | name10 | 2024-12-26 16:09:01 | 2024-12-26 16:09:01 |
+----+--------+---------------------+---------------------+
10 rows in set (0.00 sec)
结论：目标需要开启TDE，密钥必须是源实例的密钥
