加密备份 MySQL

netkiller 2021-10-09

623

6.10. 加密备份 MySQL

准备环境：

数据库服务器一台，备份服务器一台。

我们将在备份服务器上创建密钥，然后将公钥导出并在数据库服务器上导入。

数据库服务器运行定时备份脚本，加密备份文件，同时每日将加密后的备份文件同步到本地。

备份内容只能在备份服务器上解密和查看

6.10.1. 创建密钥对

过程 6.1. 密钥管理

创建密钥

				
[root@netkiller ~]# gpg --generate-key
gpg (GnuPG) 2.2.20; Copyright (C) 2020 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Note: Use "gpg --full-generate-key" for a full featured key generation dialog.

GnuPG needs to construct a user ID to identify your key.

Real name: Backup
Email address: backup@netkiller.cn
You selected this USER-ID:
    "Backup <backup@netkiller.cn>"

Change (N)ame, (E)mail, or (O)kay/(Q)uit? O				
				
					复制

数据备份不需要 Passphrase 直接回车

				
        ┌─.....................................................┐
        │ Please enter the passphrase to                       │
        │ protect your new key                                 │
        │                                                      │
        │ Passphrase: ________________________________________ │
        │                                                      │
        │       <OK>                              <Cancel>     │
        └─                                                     ┘				
				
					复制

选择 “Yes, protection is not needed” 直接回车。

				

                                        ┌─                                                                                              ┐
                                        │ You have not entered a passphrase - this is in general a bad idea!                            │
                                        │ Please confirm that you do not want to have any protection on your key.                       │
                                        │                                                                                               │
                                        │ <Yes, protection is not needed>                                    <Enter new passphrase>     │
                                        └─                                                                                              ┘				
				
					复制

系统会重复上面👆步骤两次。然后创建密钥

				
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: key 0C835D03507C8536 marked as ultimately trusted
gpg: revocation certificate stored as '/root/.gnupg/openpgp-revocs.d/18235CBA04497C42EFAC78210C835D03507C8536.rev'
public and secret key created and signed.

pub   rsa2048 2021-10-09 [SC] [expires: 2023-10-09]
      18235CBA04497C42EFAC78210C835D03507C8536
uid                      Backup <backup@netkiller.cn>
sub   rsa2048 2021-10-09 [E] [expires: 2023-10-09]				
				
					复制

导出公钥

查看用户ID

				
[root@netkiller ~]# gpg --list-keys backup@netkiller.cn
pub   rsa2048 2021-10-09 [SC] [expires: 2023-10-09]
      18235CBA04497C42EFAC78210C835D03507C8536
uid           [ultimate] Backup <backup@netkiller.cn>
sub   rsa2048 2021-10-09 [E] [expires: 2023-10-09]				
				
					复制

导出 Backup 用户公钥

				
[root@netkiller ~]# gpg --armor --output backup.gpg --export 18235CBA04497C42EFAC78210C835D03507C8536				
				
					复制

把公钥发送给数据库服务器

				
[root@netkiller ~]# scp backup.gpg www@192.168.30.10:/home/www
Warning: Permanently added '192.168.30.10' (ECDSA) to the list of known hosts.
www@192.168.30.10's password: 
backup.gpg				
				
					复制

6.10.2. 数据库备份

过程 6.2. 数据库备份

导入公钥

				
[www@testing ~]$ gpg --import backup.gpg 
gpg: directory '/home/www/.gnupg' created
gpg: keybox '/home/www/.gnupg/pubring.kbx' created
gpg: /home/www/.gnupg/trustdb.gpg: trustdb created
gpg: key 0C835D03507C8536: public key "Backup <backup@netkiller.cn>" imported
gpg: Total number processed: 1
gpg:               imported: 1				
				
					复制

				
[www@testing ~]$ gpg -k
/home/www/.gnupg/pubring.kbx
----------------------------
pub   rsa2048 2021-10-09 [SC] [expires: 2023-10-09]
      18235CBA04497C42EFAC78210C835D03507C8536
uid           [ unknown] Backup <backup@netkiller.cn>
sub   rsa2048 2021-10-09 [E] [expires: 2023-10-09]
				
					复制

测试

				
[www@testing ~]$ gpg -r 18235CBA04497C42EFAC78210C835D03507C8536 -e netkiller.sql.gz 
gpg: 339634D92F842BE7: There is no assurance this key belongs to the named user

sub  rsa2048/339634D92F842BE7 2021-10-09 Backup <backup@netkiller.cn>
 Primary key fingerprint: 1823 5CBA 0449 7C42 EFAC  7821 0C83 5D03 507C 8536
      Subkey fingerprint: BA6F 7A53 C82B 9945 C1B4  AB09 3396 34D9 2F84 2BE7

It is NOT certain that the key belongs to the person named
in the user ID.  If you *really* know what you are doing,
you may answer the next question with yes.

Use this key anyway? (y/N) y
[www@testing ~]$ ls netkiller.sql.gz*
netkiller.sql.gz  netkiller.sql.gz.gpg				
				
					复制

信任密钥

				
[www@testing ~]$ gpg --edit-key 18235CBA04497C42EFAC78210C835D03507C8536
gpg (GnuPG) 2.2.20; Copyright (C) 2020 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.


pub  rsa2048/0C835D03507C8536
     created: 2021-10-09  expires: 2023-10-09  usage: SC  
     trust: unknown       validity: unknown
sub  rsa2048/339634D92F842BE7
     created: 2021-10-09  expires: 2023-10-09  usage: E   
[ unknown] (1). Backup <backup@netkiller.cn>

gpg> trust
pub  rsa2048/0C835D03507C8536
     created: 2021-10-09  expires: 2023-10-09  usage: SC  
     trust: unknown       validity: unknown
sub  rsa2048/339634D92F842BE7
     created: 2021-10-09  expires: 2023-10-09  usage: E   
[ unknown] (1). Backup <backup@netkiller.cn>

Please decide how far you trust this user to correctly verify other users' keys
(by looking at passports, checking fingerprints from different sources, etc.)

  1 = I don't know or won't say
  2 = I do NOT trust
  3 = I trust marginally
  4 = I trust fully
  5 = I trust ultimately
  m = back to the main menu

Your decision? 5
Do you really want to set this key to ultimate trust? (y/N) y

pub  rsa2048/0C835D03507C8536
     created: 2021-10-09  expires: 2023-10-09  usage: SC  
     trust: ultimate      validity: unknown
sub  rsa2048/339634D92F842BE7
     created: 2021-10-09  expires: 2023-10-09  usage: E   
[ unknown] (1). Backup <backup@netkiller.cn>
Please note that the shown key validity is not necessarily correct
unless you restart the program.

gpg> quit				
				
					复制

再次测试，密钥已信任

				
[www@testing ~]$ rm netkiller.sql.gz.gpg 
[www@testing ~]$ gpg -r 18235CBA04497C42EFAC78210C835D03507C8536 -e netkiller.sql.gz 			
				
					复制

数据库备份

在 /etc/cron.daily/ 目录下创建 mysql 脚本，然后赋予执行权限

				
root@production:~# cat /etc/cron.daily/mysql 
#!/bin/bash
###################################
# $Id: backup 379 2012-04-02 08:43:42Z netkiller $
# Author: netkiller@msn.com
# Home:	http://netkiller.github.com
###################################
# SELECT `user`, `host`, `password` FROM `mysql`.`user`;
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 'SaJePoM6BAPOmOFOd7Xo3e1A52vEPE';
# GRANT SELECT, LOCK TABLES  ON *.* TO 'backup'@'localhost';
# FLUSH PRIVILEGES;
# SHOW GRANTS FOR 'backup'@'localhost';
###################################
BACKUP_HOST="172.188.122.155"
BACKUP_USER="dba"
BACKUP_PASS=""
BACKUP_DIR=/opt/database/mysql
BACKUP_DBNAME="netkiller neo test"
#TIMEPOINT=$(date -u +%Y-%m-%d)
TIMEPOINT=$(date +%Y-%m-%d.%H:%M:%S)
#Number of copies
COPIES=30
####################################
MYSQLDUMP="/usr/bin/mysqldump"
MYSQLDUMP_OPTS="-h $BACKUP_HOST -u$BACKUP_USER -p$BACKUP_PASS --compress --events --triggers --routines --set-gtid-purged=OFF"
# --skip-lock-tables
####################################
umask 0077
test ! -d "$BACKUP_DIR" && mkdir -p "$BACKUP_DIR"
test ! -w $BACKUP_DIR && echo "Error: $BACKUP_DIR is un-writeable." && exit 0

for dbname in $BACKUP_DBNAME
do
	test ! -d "$BACKUP_DIR/$dbname" && mkdir -p "$BACKUP_DIR/$dbname"
	LOGFILE=$BACKUP_DIR/$dbname/error.log
	$MYSQLDUMP $MYSQLDUMP_OPTS --log-error=$LOGFILE $dbname | gpg -r backup@netkiller.cn -e -o $BACKUP_DIR/$dbname/$dbname.$TIMEPOINT.sql.gpg
done
find $BACKUP_DIR -type f -mtime +$COPIES -delete				
				
					复制

	提示
gpg 自带压缩，所以备份数据无需使用 gzip 压缩

				
[www@testing ~]$ gpg -r backup@netkiller.cn -e netkiller.2021-8-28.sql
[www@testing ~]$ ll
-rw-r--r-- 1 www www 588143144 2021-08-28 10:31 netkiller.2021-8-28.sql
-rw-r--r-- 1 www www  41395738 2021-10-09 12:01 netkiller.2021-8-28.sql.gpg				
				
					复制

源文件大小是 588143144，经过 gpg 压缩后 41395738

使用 -z 参数可以设置压缩级别，这里设置为最高级别9，压缩后大小是 39847904，但是通常我不建议设置，这会影响数据被备份时常，数据备份过程需要锁表，会影响用户访问，所以要尽快完成备份。

				
[www@testing ~]$ gpg -r backup@netkiller.cn -z 9 -e netkiller.2021-8-28.sql
File 'netkiller.2021-8-28.sql.gpg' exists. Overwrite? (y/N) y

[www@testing ~]$ ll netkiller.2021-8-28.sql*
-rw-r--r-- 1 www www 588143144 2021-08-28 10:31 netkiller.2021-8-28.sql
-rw-r--r-- 1 www www  39847904 2021-10-09 12:17 netkiller.2021-8-28.sql.gpg				
				
					复制

6.10.3. 数据库还原

过程 6.3. 数据库还原

定时同步

				
[root@netkiller ~]# cat /etc/cron.daily/mysql 
rsync -auzv www@db.netkiller.cn:/opt/database/mysql /opt/backup/database/				
				
					复制

解密数据库备份文件

				
[root@netkiller ~]# gpg netkiller.2021-8-28.sql.gpg				
				
					复制

--output 指定文件名

				
[root@netkiller ~]# gpg --output netkiller.2021-8-28.sql --decrypt netkiller.2021-8-28.sql.gpg
gpg: encrypted with 2048-bit RSA key, ID 339634D92F842BE7, created 2021-10-09
      "Backup <backup@netkiller.cn>"
				
					复制

直接恢复数据库

				
[root@netkiller ~]# gpg --decrypt netkiller.2021-8-28.sql.gpg | mysql netkiller				
				
					复制

mysql

文章转载自netkiller，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

加密备份 MySQL

6.10. 加密备份 MySQL

6.10.1. 创建密钥对

6.10.2. 数据库备份

6.10.3. 数据库还原

评论

相关阅读