MYSQL 8 加密和流失备份
加密备份（Encrypting Backups）
Percona XtraBackup支持使用xbstream选项加密和解密本地和流式备份，从而增加了另一层保护。使用GnuPG的libgcrypt库实现加密 加密 --encrypt-key选项 通过ssl生成密钥 openssl rand -base64 24 LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D
加密时指定密钥
xtrabackup --backup --encrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/u01/mysql/mysql8020debug/EncryptFullBack/ --socket=/tmp/mysql.sock --host=localhost --user=root --password=123456 --port=3306
前提一样要有目录并且为空
[root@localhost mysql8020debug]# cd EncryptFullBack/ [root@localhost EncryptFullBack]# ll 总用量 0 -rw-r----- 1 root root 0 2月 9 05:39 xtrabackup_logfile.xbcrypt [root@localhost EncryptFullBack]# rm *.* rm：是否删除普通空文件 "xtrabackup_logfile.xbcrypt"？y [root@localhost EncryptFullBack]# xtrabackup --backup --encrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/u01/mysql/mysql8020debug/EncryptFullBack/ --socket=/tmp/mysql.sock --host=localhost --user=root --password=123456 --port=3306 xtrabackup: recognized client arguments: --backup=1 --encrypt=AES256 --encrypt-key=* --target-dir=/u01/mysql/mysql8020debug/EncryptFullBack/ --socket=/tmp/mysql.sock --host=localhost --user=root --password=* --port=3306 xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210209 05:40:58 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). 210209 05:40:58 version_check Connected to MySQL server 210209 05:40:58 version_check Executing a version check against the server... 210209 05:40:58 version_check Done. 210209 05:40:58 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock Using server version 8.0.20-debug xtrabackup: uses posix_fadvise(). xtrabackup: cd to /u01/mysql/mysql8020debug/data/ xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 Number of pools: 1 210209 05:40:58 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock xtrabackup: Redo Log Archiving is not set up. 210209 05:40:58 >> log scanned up to (18311253) xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. 210209 05:40:59 [01] Encrypting ./ibdata1 to /u01/mysql/mysql8020debug/EncryptFullBack/ibdata1.xbcrypt 210209 05:40:59 >> log scanned up to (18311253) 210209 05:41:00 [01] ...done 210209 05:41:00 [01] Encrypting ./sys/sys_config.ibd to /u01/mysql/mysql8020debug/EncryptFullBack/sys/sys_config.ibd.xbcrypt 210209 05:41:00 [01] ...done 210209 05:41:00 [01] Encrypting ./bookstore/books.ibd to /u01/mysql/mysql8020debug/EncryptFullBack/bookstore/books.ibd.xbcrypt 。。。。。。。。。。。。。。。。。。 /u01/mysql/mysql8020debug/EncryptFullBack/xtrabackup_binlog_info.xbcrypt 210209 05:41:03 [00] ...done 210209 05:41:03 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '18311253' xtrabackup: Stopping log copying thread at LSN 18311273. 210209 05:41:03 >> log scanned up to (18311273) Starting to parse redo log at lsn = 18311223 210209 05:41:04 All tables unlocked 210209 05:41:04 [00] Encrypting ib_buffer_pool to /u01/mysql/mysql8020debug/EncryptFullBack/ib_buffer_pool.xbcrypt 210209 05:41:04 [00] ...done 210209 05:41:04 Backup created in directory '/u01/mysql/mysql8020debug/EncryptFullBack/' MySQL binlog position: filename 'binlog.000033', position '156' 210209 05:41:04 [00] Encrypting /u01/mysql/mysql8020debug/EncryptFullBack/backup-my.cnf.xbcrypt 210209 05:41:04 [00] ...done 210209 05:41:04 [00] Encrypting /u01/mysql/mysql8020debug/EncryptFullBack/xtrabackup_info.xbcrypt 210209 05:41:04 [00] ...done xtrabackup: Transaction log of lsn (18311253) to (18311283) was copied. 210209 05:41:05 completed OK!
查看备份的文件
[root@localhost EncryptFullBack]# ll -ht 总用量 57M -rw-r----- 1 root root 131 2月 9 05:41 xtrabackup_tablespaces.xbcrypt -rw-r----- 1 root root 689 2月 9 05:41 xtrabackup_info.xbcrypt -rw-r----- 1 root root 567 2月 9 05:41 backup-my.cnf.xbcrypt -rw-r----- 1 root root 3.6K 2月 9 05:41 ib_buffer_pool.xbcrypt -rw-r----- 1 root root 95 2月 9 05:41 xtrabackup_checkpoints -rw-r----- 1 root root 2.7K 2月 9 05:41 xtrabackup_logfile.xbcrypt -rw-r----- 1 root root 110 2月 9 05:41 xtrabackup_binlog_info.xbcrypt -rw-r----- 1 root root 108 2月 9 05:41 binlog.index.xbcrypt -rw-r----- 1 root root 248 2月 9 05:41 binlog.000033.xbcrypt drwxr-x--- 2 root root 12K 2月 9 05:41 performance_schema drwxr-x--- 2 root root 4.0K 2月 9 05:41 mysql -rw-r----- 1 root root 11M 2月 9 05:41 undo_001.xbcrypt -rw-r----- 1 root root 11M 2月 9 05:41 undo_002.xbcrypt -rw-r----- 1 root root 25M 2月 9 05:41 mysql.ibd.xbcrypt drwxr-x--- 2 root root 4.0K 2月 9 05:41 bookstore drwxr-x--- 2 root root 4.0K 2月 9 05:41 sys -rw-r----- 1 root root 13M 2月 9 05:41 ibdata1.xbcrypt [root@localhost EncryptFullBack]#
Tips: 通过使用--encrypt-threads选项，可以指定多个线程并行使用加密。选项--encrypt-chunk-size可用于指定每个加密线程的工作加密缓冲区的大小（以字节为单位）（默认为64K）。
解密 --decrypt 选项
执行解密，如果不想保留原压缩文件，可以使用 --remove-original 命令
[root@localhost EncryptFullBack]# xtrabackup --remove-original --decrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/u01/mysql/mysql8020debug/EncryptFullBack/ xtrabackup: recognized client arguments: --remove-original=1 --decrypt=AES256 --encrypt-key=* --target-dir=/u01/mysql/mysql8020debug/EncryptFullBack/ xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210209 05:44:34 [01] decrypting ./mysql.ibd.xbcrypt 210209 05:44:35 [01] removing ./mysql.ibd.xbcrypt 210209 05:44:35 [01] decrypting ./mysql/general_log.CSM.xbcrypt 210209 05:44:35 [01] removing ./mysql/general_log.CSM.xbcrypt 。。。。。。。。 210209 05:44:37 [01] decrypting ./performance_schema/hosts_139.sdi.xbcrypt 210209 05:44:37 [01] removing ./performance_schema/hosts_139.sdi.xbcrypt 210209 05:44:37 completed OK! [root@localhost EncryptFullBack]# ll -ht 总用量 57M drwxr-x--- 2 root root 12K 2月 9 05:44 performance_schema -rw-r--r-- 1 root root 475 2月 9 05:44 backup-my.cnf -rw-r--r-- 1 root root 18 2月 9 05:44 xtrabackup_binlog_info -rw-r--r-- 1 root root 156 2月 9 05:44 binlog.000033 -rw-r--r-- 1 root root 39 2月 9 05:44 xtrabackup_tablespaces -rw-r--r-- 1 root root 10M 2月 9 05:44 undo_002 drwxr-x--- 2 root root 4.0K 2月 9 05:44 sys -rw-r--r-- 1 root root 10M 2月 9 05:44 undo_001 -rw-r--r-- 1 root root 3.5K 2月 9 05:44 ib_buffer_pool -rw-r--r-- 1 root root 16 2月 9 05:44 binlog.index -rw-r--r-- 1 root root 597 2月 9 05:44 xtrabackup_info -rw-r--r-- 1 root root 12M 2月 9 05:44 ibdata1 drwxr-x--- 2 root root 4.0K 2月 9 05:44 bookstore -rw-r--r-- 1 root root 2.5K 2月 9 05:44 xtrabackup_logfile drwxr-x--- 2 root root 4.0K 2月 9 05:44 mysql -rw-r--r-- 1 root root 24M 2月 9 05:44 mysql.ibd -rw-r----- 1 root root 95 2月 9 05:41 xtrabackup_checkpoints [root@localhost EncryptFullBack]#
Tips: --parallel可以与--decrypt选项一起使用来同时解密多个文件。
流式备份(Streaming Backups)
Percona XtraBackup支持xbstream流模式将备份发送到STDOUT，而不是将文件复制到备份目录。这允许您使用其他程序过滤备份的输出，从而为备份的存储提供更大的灵活性。例如，通过输出管道的方式可以实现压缩且备份可以自动加密。
使用xbstream作为流选项，可以并行复制和压缩备份，这可以显着加快备份过程。如果备份既压缩又加密，则需要先解密才能解压缩。
--并行压缩且并行复制备份
xtrabackup --backup --socket=/tmp/mysql.sock --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/xtrabackup/ >backup.xbstream
[root@localhost ~]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --backup --socket=/tmp/mysql.sock --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/u01/mysql/mysql8020debug/StreamFullBack/ >Stream_backup.xbstream
xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --host=localhost --user=root --password=* --port=3306 --backup=1 --socket=/tmp/mysql.sock --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/u01/mysql/mysql8020debug/StreamFullBack/ xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210209 05:52:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=localhost;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). 210209 05:52:13 version_check Connected to MySQL server 210209 05:52:13 version_check Executing a version check against the server... 210209 05:52:13 version_check Done. 210209 05:52:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock Using server version 8.0.20-debug xtrabackup: uses posix_fadvise(). xtrabackup: cd to /u01/mysql/mysql8020debug/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 Number of pools: 1 210209 05:52:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock xtrabackup: Redo Log Archiving is not set up. 210209 05:52:13 >> log scanned up to (18311343) xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. xtrabackup: Starting 4 threads for parallel data files transfer 210209 05:52:13 [01] Compressing and streaming ./ibdata1 210209 05:52:14 [03] Compressing and streaming ./bookstore/books.ibd 210209 05:52:14 [04] Compressing and streaming ./bookstore/books2.ibd 210209 05:52:14 [02] Compressing and streaming ./sys/sys_config.ibd 210209 05:52:16 [01] ...done 210209 05:52:16 [02] ...done 210209 05:52:16 [01] Compressing and streaming performance_schema/setup_consumers_96.sdi to <STDOUT> 210209 05:52:16 [01] ...done 210209 05:52:16 Finished backing up non-InnoDB tables and files 210209 05:52:16 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 210209 05:52:16 Selecting LSN and binary log position from p_s.log_status 210209 05:52:16 [00] Compressing and streaming /u01/mysql/mysql8020debug/data/binlog.000036 to <STDOUT> up to position 156 210209 05:52:16 [00] ...done 210209 05:52:16 [00] Compressing and streaming <STDOUT> 210209 05:52:16 [00] ...done 210209 05:52:16 [00] Compressing and streaming <STDOUT> 210209 05:52:16 [00] ...done 210209 05:52:16 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '18311363' xtrabackup: Stopping log copying thread at LSN 18311363. 210209 05:52:16 >> log scanned up to (18311373) Starting to parse redo log at lsn = 18311223 210209 05:52:17 All tables unlocked 210209 05:52:17 [00] Compressing and streaming ib_buffer_pool to <STDOUT> 210209 05:52:17 [00] ...done 210209 05:52:17 Backup created in directory '/u01/mysql/mysql8020debug/StreamFullBack/' MySQL binlog position: filename 'binlog.000036', position '156' 210209 05:52:17 [00] Compressing and streaming <STDOUT> 210209 05:52:17 [00] ...done 210209 05:52:17 [00] Compressing and streaming <STDOUT> 210209 05:52:17 [00] ...done xtrabackup: Transaction log of lsn (18311343) to (18311373) was copied. 210209 05:52:18 completed OK! [root@localhost ~]#
查看结果 backup.xbstream
恢复时必须先解压xbstream格式，再解压qb
xbstream -x < backup.xbstream -C /xtrabackup/
--解压qp
xtrabackup --decompress --remove-original --target-dir=/xtrabackup
流式远程备份(Streaming Backups) 使用xbstream备份
xtrabackup --defaults-file=/home/mysql8.0.16/mysql/my.cnf --socket=/tmp/mysql.sock --user=aa --password='aa' --port=3307 --host=192.168.11.253 --no-timestamp --parallel=4 --throttle=2000 --compress-threads=8 --backup --stream=xbstream|gzip |ssh 192.168.8.3 "cat - > /home/backup/mysql/test/FULL-253database-`date +%Y-%m-%d-%H-%M-%S`.xbstream.gz"
其实就是利用 linux系统 的管道和SSH免密技术
备份脚本仅供参考
#!/bin/bash
name=root
host=192.168.0.1
DATE=`date +"%Y%m%d"`
allbackup="/home/dbback/hotbackup/"
###所有集合顺序上下一一对应吗，即vpwd[0]、vcnf[0]、vsocket[0]、vport[0]代表一个数据库实例
#root用户密码集
vpwd=("")
#配置文件集
vcnf=("/data/mysql-5.5.43/etc/my.cnf")
#socket集
vsocket=("/tmp/mysql.sock")
#设置端口集
vport=("3306")
###获取端口个数
k=${#vport[*]}
if [ -d $allbackup ];
then
echo "完全备份目录存在"
else
echo "完全备份目录不存在，开始创建......."
/bin/mkdir -p $allbackup
fi
###for循环，循环备份不同端口数据库
for ((l=0;l<$k;++l))
do
if [ -d $allbackup/$DATE/$host/${vport[$l]} ];
then
echo "完全备份目录存在"
else
echo "完全备份目录不存在，开始创建......."
/bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}
#fi
find $allbackup -type d -mtime +1 -exec rm -rf {} \;
cd $allbackup/$DATE/$host/${vport[$l]}
innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]} --slave-info --user=root --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8 ./ 2>bak.$DATE.log
result=`cat bak.$DATE.log | grep "completed OK\!" | grep -v prints`
coun=1
if [[ -z $result ]];
then
if [[ $coun -lt 2 ]];
then
rm -rf $allbackup/$DATE/$host/${vport[$l]}
/bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}
cd $allbackup/$DATE/$host/${vport[$l]}
innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]} --slave-info --user=root --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8 ./ 2>bak.$DATE.log
coun=`expr $coun + 1`;
echo $coun
fi
else
rsync -zavP --password-file=/etc/rsync.pass --bwlimit=30000 $allbackup/$DATE dbback@192.168.1.1::KAPROBAK/DBBAK
echo "备份文件推送成功！"
fi
fi
done
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did162077