MySQL备份压缩加密解决方案

文章目录

1. 1. MySQL 备份压缩加密方案
   1. 1.1. 下载安装 XtraBackup
   2. 1.2. 压缩加密备份
      1. 1.2.1. 关键参数说明：
   3. 1.3. 操作命令
      1. 1.3.1. 备份到本机
      2. 1.3.2. 备份到远端
   4. 1.4. 恢复
      1. 1.4.1. 解码文件
      2. 1.4.2. 先解密
      3. 1.4.3. 解压缩
      4. 1.4.4. Apply log
   5. 1.5. 总结

MySQL 备份压缩加密方案

数据安全性对于任何一个公司都是非常重要的, 特别是互联网公司. 对数据进行加密也是我们提高安全性的重要手段之一, 今天我们谈谈如何对MySQL进行备份加密.
xtrabackup热备工具是我们最常用的, 它从 xtrabackup 2.2.1 版本开始提供了加密支持, 现今最新版本是2.4.7.今天我们利用该特性进行备份的加密和压缩

下载安装 XtraBackup

1.在http://www.percona.com/downloads/XtraBackup/LATEST/   
2.解压安装，并配置path路径 export PATH=$JAVA_HOME/bin:/usr/local/xxx:$PATH  
3.我的mysql的配置文件是/xx/my.cnf，如果不指定，XtraBackup默认使用此文
  件识别mysql安装目录，数据文件目录等信息
 

压缩加密备份

关键参数说明：

Compress

–compress[=name]

This option instructs xtrabackup to compress backup
copies of InnoDB data files. It is passed directly to the
xtrabackup child process. Try 'xtrabackup --help' for
more details

–compress-threads=#

This option specifies the number of worker threads that
will be used for parallel compression. It is passed
directly to the xtrabackup child process. Try 'xtrabackup
--help' for more details.

–compress-chunk-size=#

Size of working buffer(s) for compression threads in
bytes. The default value is 64K.

Encryption

–encrypt=ALGORITHM

currently supported algorithms are: AES128, AES192 and AES256

–encrypt-key=ENCRYPTION_KEY

proper length encryption key to use. It is not recommended to use this
option where there is uncontrolled access to the machine as the command 
line and thus the key can be viewed as part of the process info.

–encrypt-key-file=name

This option instructs xtrabackup to use the encryption
key stored in the given ENCRYPTION-KEY-FILE when using
the --encrypt or --decrypt options.

–encrypt-threads= NUMBER

the threads number to do encrypt, default 1

操作命令

encrypt-key : 3c0efcea569021b49245e47b5d6a0e28

备份到本机

innobackupex --defaults-file=/data/mysql3306/my.cnf --user=XXXXXXXXX \ 
--password=*************** --host=127.0.0.1 --port=3306 --stream=xbstream \
--encrypt=AES256 --encrypt-key=$encrypt-key --encrypt-threads=5 --compress\
--tmpdir=/data/baks/ --slave-info /data/baks/ > backup20160101.xbstream

备份到远端

假设备份中心机器: 192.168.xxx.xxx

innobackupex --defaults-file=/data/mysql3306/my.cnf --user=XXXXXXXXX \
 --password=*************** --host=127.0.0.1 --port=3306 --stream=xbstream\
 --encrypt=AES256 --encrypt-key=$encrypt-key \ 
 --encrypt-threads=5  --compress --tmpdir=/data/baks/ \
  --slave-info /data/baks/ | pv -q -L20m | ssh -p 30000 192.168.xxx.xxx \
  "cat - > /data/tmp/backup20160101.xbstream "

备份完成之后会提示日志

xtrabackup: Transaction log of lsn (4669176459) to (4669176459) was copied.
170427 11:36:02 completed OK!s

恢复

解码文件

xbstream -x < /data/tmp/backup20160101.xbstream -C /data/tmp/test
# ls /data/tmp/test
[root@xxxxx test]# ll
    total 105220
    drwxr-x--- 2 root root     4096 Apr 27 11:37 xxxxx
    -rw-r----- 1 root root      533 Apr 27 11:37 xxxx-my.cnf.qp.xbcrypt
    -rw-r----- 1 root root   445218 Apr 27 11:37 ibdata1.qp.xbcrypt
    drwxr-x--- 2 root root     4096 Apr 27 11:37 mysql 
    ...
    ...
    ...
    -rw-r----- 1 root root      617 Apr 27 11:37 xtrabackup_logfile.qp.xbcrypt
    -rw-r----- 1 root root      242 Apr 27 11:37 xtrabackup_slave_info.qp.xbcrypt

先解密

方案1

cd /data/tmp/test
# 
for i in `find . -iname "*\.xbcrypt"`
do  
    echo $i 
    xbcrypt -d --encrypt-key=$encrypt-key  --encrypt-algo=AES256 < $i > $(dirname $i)/$(basename $i .xbcrypt)
    /bin/rm -f  $i
done

方案2

innobackupex --decrypt=AES256 --encrypt-key=$encrypt-key /data/tmp/test 

解压缩

install qpress tool first

rpmkeys --import https://www.percona.com/downloads/RPM-GPG-KEY-percona
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm 
yum install qpress -y

方案1

innobackupex --decompress . 

方案2

/data/tmp/test
for i in `find . -iname "*.qp"`
do 
    qpress -d $i  $(dirname $i) && rm -f $i
done

Apply log

innobackupex --apply-log .
...

总结

数据备份的安全性, 对于互联网公司非常重要, 建议大家都进行加密.