mysqlbackup
1. 生成key
#使用shasum生成
echo -n "123456" | shasum -a 256
8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92 - #注意后面的中划线不算，共64个字符
注意：这种生成的密钥串mysqlbackup可以使用，但是xtrabackup不能使用，使用的过程中会报错
#使用md5sum生成
echo "123456" |md5sum
f447b20a7fcbf53a5d5be013ea0b15af - #注意后面的中划线不算，
注意：通过md5sum生成的密钥串mysqlbackup不能使用，会报错：Invalid encryption key given. It should be a string of 64 hexadecimal digits.2. 方式1：使用key
#备份
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/fullbak --backup-image=/data/backup/full.mbi.enc --user=mysqlbackup -p'Abc@12345678' --read-threads=4 --process-threads=8 --write-threads=4 --compress --encrypt -- key=8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92 backup-to-image >>/data/backup/bak.log 2>&1#恢复
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/data/backup/full.mbi.enc --backup-dir=/tmp/mysqlrestore --datadir=/data/mysql/data --decrypt --key=8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92 copy-back-and-apply-log3.方式2：使用key-file
#生成keyfile
echo -n "123456" | shasum -a 256 >/tmp/bakey#备份
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/fullbak --backup-image=/data/backup/full.mbi.enc --user=mysqlbackup -p'Abc@12345678' --read-threads=4 --process-threads=8 --write-threads=4 --compress --encrypt --key-file=/tmp/bakey backup-to-image >>/data/backup/bak.log 2>&1#校验(需要指定解密和解密的key，不然会报错)
mysqlbackup --backup-image=/data/backup/full.mbi.enc --decrypt --key-file=/tmp/bakey validate#查看备份image中备份的文件(需要指定解密和解密的key，不然会报错)
mysqlbackup --backup-image=/data/backup/full.mbi.enc --decrypt --key-file=/tmp/bakey list-image#恢复
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/data/backup/full.mbi.enc --backup-dir=/tmp/mysqlrestore --datadir=/data/mysql/data --decrypt --key-file=/tmp/bakey copy-back-and-apply-log4.方式3：使用--encrypt-password参数
#备份（密码明文）
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/fullbak --backup-image=/data/backup/full.mbi --user=mysqlbackup -p'Abc@12345678' --read-threads=4 --process-threads=8 --write-threads=4 --compress --encrypt-password="123456" backup-to-image >>/data/backup/bak.log 2>&1#恢复--不指定密码--成功
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/data/backup/full.mbi --backup-dir=/tmp/mysqlrestore --datadir=/data/mysql/data copy-back-and-apply-log#恢复--指定密码--成功
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/data/backup/full.mbi --backup-dir=/tmp/mysqlrestore --datadir=/data/mysql/data --encrypt-password="123456" copy-back-and-apply-log--encrypt-password参数只有在备份使用TED加密的表空间的时候才有用，如果使用mysqlbackup备份数据库的时候，表空间没有加密，那么添加这个参数将会被忽略
xtrabackup
1.生成密钥串
echo "123456" |md5sum
f447b20a7fcbf53a5d5be013ea0b15af - #注意后面的中划线不算2.方式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
-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
