使用xtrabackup进行加密备份
原创
©著作权归作者所有：来自51CTO博客作者dber_ablewang的原创作品，请联系作者获取转载授权，否则将追究法律责任
- 生成密钥串
echo "123456" |md5sum
f447b20a7fcbf53a5d5be013ea0b15af - #注意后面的中划线不包含
- 加密方式1：使用key
#备份
xtrabackup --user=mysqlbackup --password='Abc@12345678' --backup --compress --compress-threads=4 --encrypt=AES256 --encrypt-key="f447b20a7fcbf53a5d5be013ea0b15af" --target-dir=/data/backup/full > /data/backup/bak.log 2>&1
[root@servername backup]# ll /data/backup/full/
total 1524
-rw-r----- 1 root root 394 Jun 20 16:56 backup-my.cnf.zst.xbcrypt
-rw-r----- 1 root root 204 Jun 20 16:56 binlog.000003.zst.xbcrypt
-rw-r----- 1 root root 121 Jun 20 16:56 binlog.index.zst.xbcrypt
-rw-r----- 1 root root 730 Jun 20 16:56 ib_buffer_pool.zst.xbcrypt
-rw-r----- 1 root root 4052 Jun 20 16:56 ibdata1.zst.xbcrypt
drwxr-x--- 2 root root 4096 Jun 20 16:56 mysql
-rw-r----- 1 root root 1415399 Jun 20 16:56 mysql.ibd.zst.xbcrypt
drwxr-x--- 2 root root 8192 Jun 20 16:56 performance_schema
drwxr-x--- 2 root root 40 Jun 20 16:56 sys
-rw-r----- 1 root root 42823 Jun 20 16:56 undo_001.zst.xbcrypt
-rw-r----- 1 root root 40182 Jun 20 16:56 undo_002.zst.xbcrypt
drwxr-x--- 2 root root 34 Jun 20 16:56 wxbtest
-rw-r----- 1 root root 123 Jun 20 16:56 xtrabackup_binlog_info.zst.xbcrypt
-rw-r----- 1 root root 134 Jun 20 16:56 xtrabackup_checkpoints
-rw-r----- 1 root root 440 Jun 20 16:56 xtrabackup_info.zst.xbcrypt
-rw-r----- 1 root root 373 Jun 20 16:56 xtrabackup_logfile.zst.xbcrypt
-rw-r----- 1 root root 144 Jun 20 16:56 xtrabackup_tablespaces.zst.xbcrypt
#解密解压
xtrabackup --defaults-file=/etc/my.cnf --decompress --parallel=4 --decrypt=AES256 --encrypt-key="f447b20a7fcbf53a5d5be013ea0b15af" --target-dir=/data/backup/full --remove-original
注意：--remove-original选项在解密后删除加密文件
[root@servername backup]# ll /data/backup/full/
total 70708
-rw-r--r-- 1 root root 447 Jun 20 16:57 backup-my.cnf
-rw-r--r-- 1 root root 157 Jun 20 16:57 binlog.000003
-rw-r--r-- 1 root root 16 Jun 20 16:57 binlog.index
-rw-r--r-- 1 root root 6238 Jun 20 16:57 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jun 20 16:57 ibdata1
drwxr-x--- 2 root root 143 Jun 20 16:57 mysql
-rw-r--r-- 1 root root 26214400 Jun 20 16:57 mysql.ibd
drwxr-x--- 2 root root 8192 Jun 20 16:57 performance_schema
drwxr-x--- 2 root root 28 Jun 20 16:57 sys
-rw-r--r-- 1 root root 16777216 Jun 20 16:57 undo_001
-rw-r--r-- 1 root root 16777216 Jun 20 16:57 undo_002
drwxr-x--- 2 root root 22 Jun 20 16:57 wxbtest
-rw-r--r-- 1 root root 18 Jun 20 16:57 xtrabackup_binlog_info
-rw-r----- 1 root root 134 Jun 20 16:56 xtrabackup_checkpoints
-rw-r--r-- 1 root root 566 Jun 20 16:57 xtrabackup_info
-rw-r--r-- 1 root root 2560 Jun 20 16:57 xtrabackup_logfile
-rw-r--r-- 1 root root 39 Jun 20 16:57 xtrabackup_tablespaces
#准备
xtrabackup --prepare --target-dir=/data/backup/full**
[root@servername backup]# ll full/
total 115760
-rw-r--r-- 1 root root 447 Jun 20 16:57 backup-my.cnf
-rw-r--r-- 1 root root 157 Jun 20 16:57 binlog.000003
-rw-r--r-- 1 root root 16 Jun 20 16:57 binlog.index
-rw-r--r-- 1 root root 6238 Jun 20 16:57 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jun 20 16:58 ibdata1
-rw-r----- 1 root root 12582912 Jun 20 16:58 ibtmp1
drwxr-x--- 2 root root 6 Jun 20 16:58 '#innodb_redo'
drwxr-x--- 2 root root 143 Jun 20 16:57 mysql
-rw-r--r-- 1 root root 26214400 Jun 20 16:57 mysql.ibd
drwxr-x--- 2 root root 8192 Jun 20 16:57 performance_schema
drwxr-x--- 2 root root 28 Jun 20 16:57 sys
-rw-r--r-- 1 root root 16777216 Jun 20 16:57 undo_001
-rw-r--r-- 1 root root 16777216 Jun 20 16:57 undo_002
drwxr-x--- 2 root root 22 Jun 20 16:57 wxbtest
-rw-r--r-- 1 root root 18 Jun 20 16:57 xtrabackup_binlog_info
-rw-r----- 1 root root 134 Jun 20 16:58 xtrabackup_checkpoints
-rw-r--r-- 1 root root 566 Jun 20 16:57 xtrabackup_info
-rw-r----- 1 root root 33554432 Jun 20 16:58 xtrabackup_logfile
-rw-r--r-- 1 root root 39 Jun 20 16:58 xtrabackup_tablespaces
#恢复
[root@servername backup]# systemctl stop mysqld
[root@servername backup]# rm -rf /var/lib/mysql/*
xtrabackup --host=127.0.0.1 --user=mysqlbackup --password='Abc@12345678' --port=3306 --datadir=/var/lib/mysql --copy-back --target-dir=/data/backup/full**
[root@servername backup]# chown -R mysql.mysql /var/lib/mysql
[root@servername backup]# systemctl start mysqld
- 加密方式2：使用key-file
#生成key
[root@servername backup]# echo "123456" |md5sum
f447b20a7fcbf53a5d5be013ea0b15af - #注意：需要去掉里面最后的中划线
[root@servername backup]# echo -n "f447b20a7fcbf53a5d5be013ea0b15af" >/tmp/bak.key
#官方说明：
Depending on the text editor used for making the KEYFILE, text file in some cases can contain the CRLF and this will cause the key size to grow and thus making it invalid. Suggested way to do this would be to create the file with: echo -n "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile
如果不按照上面的说明执行，会有如下报错
encryption: unable to set libgcrypt cipher key - User defined source 1 : Invalid key length
Fatal error: gcry_cipher_close: already closed/invalid handle
2024-06-20T09:45:38Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=
Thread pointer: 0x7ff87c000b60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ff892ffc7b0 thread_stack 0x100000
xtrabackup(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x259c331]
xtrabackup(print_fatal_signal(int)+0x3c3) [0x13a0bf3]
xtrabackup(handle_fatal_signal+0x6d) [0x13a0c6d]
/lib64/libpthread.so.0(+0x12cf0) [0x7ff8bcf8ccf0]
/lib64/libc.so.6(gsignal+0x10f) [0x7ff8ba927acf]
/lib64/libc.so.6(abort+0x127) [0x7ff8ba8faea5]
/lib64/libgcrypt.so.20(+0xe053) [0x7ff8bca67053]
/lib64/libgcrypt.so.20(+0x18f7b) [0x7ff8bca71f7b]
xtrabackup() [0xe079de]
xtrabackup() [0xe01f52]
xtrabackup() [0xe0700b]
xtrabackup() [0xde52e6]
xtrabackup() [0xdf6a15]
xtrabackup(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)(data_thread_ctxt_t*), data_thread_ctxt_t*> > >::_M_run()+0xbd) [0xde115d]
/lib64/libstdc++.so.6(+0xc2b23) [0x7ff8bb2fab23]
/lib64/libpthread.so.0(+0x81ca) [0x7ff8bcf821ca]
/lib64/libc.so.6(clone+0x43) [0x7ff8ba912e73]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 2
Status: NOT_KILLED
Please report a bug at https://jira.percona.com/projects/PXB
#备份
[root@servername backup]# xtrabackup --user=mysqlbackup --password='Abc@12345678' --backup --compress --compress-threads=4 --encrypt=AES256 --encrypt-key-file=/tmp/bak.key --target-dir=/data/backup/full > /data/backup/bak.log 2>&1
[root@servername backup]# ll full
total 1528
-rw-r----- 1 root root 395 Jun 20 17:55 backup-my.cnf.zst.xbcrypt
-rw-r----- 1 root root 201 Jun 20 17:55 binlog.000005.zst.xbcrypt
-rw-r----- 1 root root 121 Jun 20 17:55 binlog.index.zst.xbcrypt
-rw-r----- 1 root root 730 Jun 20 17:55 ib_buffer_pool.zst.xbcrypt
-rw-r----- 1 root root 4051 Jun 20 17:55 ibdata1.zst.xbcrypt
drwxr-x--- 2 root root 4096 Jun 20 17:55 mysql
-rw-r----- 1 root root 1415970 Jun 20 17:55 mysql.ibd.zst.xbcrypt
drwxr-x--- 2 root root 8192 Jun 20 17:55 performance_schema
drwxr-x--- 2 root root 40 Jun 20 17:55 sys
-rw-r----- 1 root root 43253 Jun 20 17:55 undo_001.zst.xbcrypt
-rw-r----- 1 root root 43645 Jun 20 17:55 undo_002.zst.xbcrypt
drwxr-x--- 2 root root 34 Jun 20 17:55 wxbtest
-rw-r----- 1 root root 123 Jun 20 17:55 xtrabackup_binlog_info.zst.xbcrypt
-rw-r----- 1 root root 134 Jun 20 17:55 xtrabackup_checkpoints
-rw-r----- 1 root root 440 Jun 20 17:55 xtrabackup_info.zst.xbcrypt
-rw-r----- 1 root root 330 Jun 20 17:55 xtrabackup_logfile.zst.xbcrypt
-rw-r----- 1 root root 144 Jun 20 17:55 xtrabackup_tablespaces.zst.xbcrypt
#解密解压
[root@servername backup]# xtrabackup --defaults-file=/etc/my.cnf --decompress --parallel=4 --decrypt=AES256 --encrypt-key-file=/tmp/bak.key --target-dir=/data/backup/full --remove-original
注意：--remove-original选项在解密后删除加密文件
[root@servername backup]# ll full
total 70708
-rw-r--r-- 1 root root 447 Jun 20 17:56 backup-my.cnf
-rw-r--r-- 1 root root 157 Jun 20 17:56 binlog.000005
-rw-r--r-- 1 root root 16 Jun 20 17:56 binlog.index
-rw-r--r-- 1 root root 6238 Jun 20 17:56 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jun 20 17:56 ibdata1
drwxr-x--- 2 root root 143 Jun 20 17:56 mysql
-rw-r--r-- 1 root root 26214400 Jun 20 17:56 mysql.ibd
drwxr-x--- 2 root root 8192 Jun 20 17:56 performance_schema
drwxr-x--- 2 root root 28 Jun 20 17:56 sys
-rw-r--r-- 1 root root 16777216 Jun 20 17:56 undo_001
-rw-r--r-- 1 root root 16777216 Jun 20 17:56 undo_002
drwxr-x--- 2 root root 22 Jun 20 17:56 wxbtest
-rw-r--r-- 1 root root 18 Jun 20 17:56 xtrabackup_binlog_info
-rw-r----- 1 root root 134 Jun 20 17:55 xtrabackup_checkpoints
-rw-r--r-- 1 root root 566 Jun 20 17:56 xtrabackup_info
-rw-r--r-- 1 root root 2560 Jun 20 17:56 xtrabackup_logfile
-rw-r--r-- 1 root root 39 Jun 20 17:56 xtrabackup_tablespaces
#准备
[root@servername backup]# xtrabackup --prepare --target-dir=/data/backup/full
[root@servername backup]# ll full
total 115760
-rw-r--r-- 1 root root 447 Jun 20 17:56 backup-my.cnf
-rw-r--r-- 1 root root 157 Jun 20 17:56 binlog.000005
-rw-r--r-- 1 root root 16 Jun 20 17:56 binlog.index
-rw-r--r-- 1 root root 6238 Jun 20 17:56 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Jun 20 17:57 ibdata1
-rw-r----- 1 root root 12582912 Jun 20 17:57 ibtmp1
drwxr-x--- 2 root root 6 Jun 20 17:57 '#innodb_redo'
drwxr-x--- 2 root root 143 Jun 20 17:56 mysql
-rw-r--r-- 1 root root 26214400 Jun 20 17:56 mysql.ibd
drwxr-x--- 2 root root 8192 Jun 20 17:56 performance_schema
drwxr-x--- 2 root root 28 Jun 20 17:56 sys
-rw-r--r-- 1 root root 16777216 Jun 20 17:56 undo_001
-rw-r--r-- 1 root root 16777216 Jun 20 17:56 undo_002
drwxr-x--- 2 root root 22 Jun 20 17:56 wxbtest
-rw-r--r-- 1 root root 18 Jun 20 17:56 xtrabackup_binlog_info
-rw-r----- 1 root root 134 Jun 20 17:57 xtrabackup_checkpoints
-rw-r--r-- 1 root root 566 Jun 20 17:56 xtrabackup_info
-rw-r----- 1 root root 33554432 Jun 20 17:57 xtrabackup_logfile
-rw-r--r-- 1 root root 39 Jun 20 17:57 xtrabackup_tablespaces
#恢复
[root@servername backup]# systemctl stop mysqld
[root@servername backup]# rm -rf /var/lib/mysql/*
[root@servername backup]# xtrabackup --host=127.0.0.1 --user=mysqlbackup --password='Abc@12345678' --port=3306 --datadir=/var/lib/mysql --copy-back --target-dir=/data/backup/full
[root@servername backup]# chown -R mysql.mysql /var/lib/mysql
[root@servername backup]# systemctl start mysqld
