Howto Encrypt MySQL Backups on S3

TwinDB Backup supports encrypted backup copies since version 2.11.0. As usual the tool supports natively backup and restore operations, if backup copies are encrypted the tool takes care of decryption.

Installing TwinDB Packages repository

I will work with CentOS 7 system to show the example, but there are also packages for Ubuntu trusty and Debian jessie.

We host our packages in PackageCloud which provides a great installation guide
if you need to install the repo via puppet, chef etc. The manual way is pretty straightforward as well. A PackageCloud script installs and configures the repository.

curl -s https://packagecloud.io/install/repositories/twindb/main/script.rpm.sh | sudobash

Installing twindb-backup

Once the repository is ready it’s time to install the tool.

yuminstalltwindb-backup

Let’s review what files the tool actually installs.

# rpm -ql twindb-backup
/opt
/opt/twindb-backup
...
/opt/twindb-backup/bin
...
/opt/twindb-backup/bin/twindb-backup
...

The RPM installs the files in opt
because we use OmniBus
to package twindb-backup
. We package with the tool itself its own python, dependencies. That way we make sure there are no conflicts, no surprises due to different modules versions etc.

The post installation script also creates a cron config and a sample tool configuration file.

# cat /etc/cron.d/twindb-backup
@hourly  roottwindb-backupbackuphourly
@daily  roottwindb-backupbackupdaily
@weekly  roottwindb-backupbackupweekly
@monthlyroottwindb-backupbackupmonthly
@yearly  roottwindb-backupbackupyearly
# cat /etc/twindb/twindb-backup.cfg
# NOTE: don't quote option values
# What to backup
[source]
backup_dirs=/etc /root /home
backup_mysql=no
 
# Destination
[destination]
# backup destination can be ssh or s3
backup_destination=ssh
keep_local_path=/var/backup/local
 
 
[s3]
 
# S3 destination settings
 
AWS_ACCESS_KEY_ID=XXXXX
AWS_SECRET_ACCESS_KEY=YYYYY
AWS_DEFAULT_REGION=us-east-1
BUCKET=twindb-backups
 
[ssh]
 
# SSH destination settings
 
backup_host=127.0.0.1
backup_dir=/tmp/backup
ssh_user=root
ssh_key=/root/.ssh/id_rsa
 
[mysql]
 
# MySQL
 
mysql_defaults_file=/etc/twindb/my.cnf
 
full_backup=daily
 
[retention]
 
# Remote retention policy
 
hourly_copies=24
daily_copies=7
weekly_copies=4
monthly_copies=12
yearly_copies=3
 
[retention_local]
 
# Local retention policy
 
hourly_copies=1
daily_copies=1
weekly_copies=0
monthly_copies=0
yearly_copies=0
 
[intervals]
 
# Run intervals
 
run_hourly=yes
run_daily=yes
run_weekly=yes
run_monthly=yes
run_yearly=yes

Preparing Encryption Key

We use GPG to encrypt the backups. The tool doesn’t manage the keys so it’s all user responsibility to create and save a backup copy of the key.

Let’s generate the key first.

# gpg --gen-key
gpg (GnuPG) 2.0.22; Copyright (C) 2013 FreeSoftwareFoundation, Inc.
This is freesoftware: youarefreeto changeand redistributeit.
Thereis NOWARRANTY, to theextentpermittedbylaw.
 
gpg: directory `/root/.gnupg' created
gpg: new configurationfile `/root/.gnupg/gpg.conf' created
gpg: WARNING: optionsin `/root/.gnupg/gpg.conf' arenot yetactiveduringthis run
gpg: keyring `/root/.gnupg/secring.gpg' created
gpg: keyring `/root/.gnupg/pubring.gpg' created
Pleaseselectwhat kindofkeyyouwant:
(1) RSAand RSA (default)
(2) DSAand Elgamal
(3) DSA (signonly)
(4) RSA (signonly)
Yourselection? 1
RSAkeysmaybebetween 1024 and 4096 bitslong.
What keysizedo youwant? (2048)
Requestedkeysizeis 2048 bits
Pleasespecifyhowlong thekeyshouldbevalid.
0 = keydoesnot expire
 = keyexpiresin n days
w = keyexpiresin n weeks
m = keyexpiresin n months
y = keyexpiresin n years
Keyis validfor? (0) 0
Keydoesnot expireat all
Is this correct? (y/N) y
 
GnuPGneedsto construct a userID to identifyyourkey.
 
Realname: AleksandrKuzminsky
Emailaddress: [email protected]
Comment: Keyfor encryptingMySQLbackups
Youselectedthis USER-ID:
"Aleksandr Kuzminsky (Key for encrypting MySQL backups) "
 
Change (N)ame, (C)omment, (E)mailor (O)kay/(Q)uit? O
 
Youdon't want a passphrase - this is probably a *bad* idea!
I willdo itanyway. Youcanchangeyourpassphraseat anytime,
usingthis programwiththeoption "--edit-key".
 
Weneedto generate a lotofrandombytes. Itis a goodideato perform
someotheraction (type onthekeyboard, movethemouse, utilizethe
disks) duringtheprimegeneration; this givestherandomnumber
generator a betterchanceto gainenoughentropy.
Weneedto generate a lotofrandombytes. Itis a goodideato perform
someotheraction (type onthekeyboard, movethemouse, utilizethe
disks) duringtheprimegeneration; this givestherandomnumber
generator a betterchanceto gainenoughentropy.
gpg: /root/.gnupg/trustdb.gpg: trustdbcreated
gpg: key 8564B88A markedas ultimatelytrusted
publicand secretkeycreatedand signed.
 
gpg: checkingthetrustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGPtrustmodel
gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u
pub 2048R/8564B88A 2017-03-28
Keyfingerprint = 441E 4B7A FD92C0D5 4C6B 0C89 4AE0 849C 8564 B88A
uidAleksandrKuzminsky (Keyfor encryptingMySQLbackups) 
sub 2048R/0CE02576 2017-03-28

We don’t use passphrase for the key.

Preparing twindb-backup configuration

We need to change default config. Let’s review the changes.

[source]
backup_dirs=/etc
backup_mysql=yes

It’s always nice to save backup copies of /etc
. If you don’t want to backup directories, comment out backup_dirs
.

# Destination
[destination]
# backup destination can be ssh or s3
backup_destination=s3
keep_local_path=/var/backup/local

We store backups in s3 and we will also keep a local copy (for faster restore time).

[s3]
 
# S3 destination settings
 
AWS_ACCESS_KEY_ID=XXXXX
AWS_SECRET_ACCESS_KEY=YYYYY
AWS_DEFAULT_REGION=us-east-1
BUCKET=twindb-backups

We will store backups in S3, so change these options to your key and bucket values.

[mysql]
 
# MySQL
mysql_defaults_file=/etc/twindb/my.cnf
full_backup=daily

The tool uses a defaults file to connect to MySQL, so specify it here.

# cat /etc/twindb/my.cnf
[client]
user=root

Don’t forget to chmod 600 /etc/twindb/my.cnf
.

The config also tells how often to take daily full copies. The hourly copies will be the difference between the last full copy and the current state. It’s so-called differential backups.

To encrypt the backup copies add a [gpg]
section

[gpg]
keyring = /root/.gnupg/pubring.gpg
secret-keyring = /root/.gnupg/secring.gpg
recipient = [email protected]

It specifies where GnuPG can find private and public keys of the recipient
.

Optionally you may want to change local and remote retention policies, but the defaults should be good enough.

Test backup run

Now let’s run the tool manually to see how it works.

# twindb-backup backup daily

The tool should produce no output unless there is an error.

Listing available backup copies

The tool can tell you what backup copies are available now.

# twindb-backup ls
2017-03-28 05:32:40,412: INFO: ls.list_available_backups():22: Localcopies:
/var/backup/local/d312b5e3a877/status
/var/backup/local/d312b5e3a877/daily/files/_etc-2017-03-28_05_32_26.tar.gz
/var/backup/local/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz
2017-03-28 05:32:40,417: INFO: ls.list_available_backups():33: hourlycopies:
2017-03-28 05:32:41,087: INFO: ls.list_available_backups():33: dailycopies:
s3://twindb-backup-test-0/d312b5e3a877/daily/files/_etc-2017-03-28_05_32_26.tar.gz.gpg
s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg
2017-03-28 05:32:41,687: INFO: ls.list_available_backups():33: weeklycopies:
2017-03-28 05:32:42,269: INFO: ls.list_available_backups():33: monthlycopies:
2017-03-28 05:32:42,831: INFO: ls.list_available_backups():33: yearlycopies:

The encrypted copies have .gpg
suffix. Note the local copies are not encrypted.

Restore MySQL from backup

Now we have a backup copy s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg
. Let’s restore MySQL database from it.

# twindb-backup restore mysql s3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg --dst restored
...
170328 05:39:49  innobackupex: completedOK!
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():354: Successfullyrestoreds3://twindb-backup-test-0/d312b5e3a877/daily/mysql/mysql-2017-03-28_05_32_30.xbstream.gz.gpg in restored.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():356: Nowcopycontentofrestoredto MySQLdatadir: cp -R restored/* /var/lib/mysql/
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():357: Fixpermissions: chown -R mysql:mysql /var/lib/mysql/
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():359: Make sureinnodb_log_file_sizeand innodb_log_files_in_groupin restored/backup-my.cnf and in /etc/my.cnf aresame.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():362: Originalmy.cnf is restoredin restored/_config.
2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():364: Then youcanstartMySQLnormally.

Now we have a restored database in restored
directory that we can copy to /var/lib/mysql

# ls -la restored/
total 30756
drwxr-xr-x 6 rootroot    4096 Mar 28 05:39 .
dr-xr-x--- 5 rootroot    4096 Mar 28 05:39 ..
drwxr-xr-x 3 rootroot    4096 Mar 28 05:39 _config
-rw-r----- 1 rootroot      262 Mar 28 05:39 backup-my.cnf
-rw-r--r-- 1 rootroot  5242880 Mar 28 05:39 ib_logfile0
-rw-r--r-- 1 rootroot  5242880 Mar 28 05:39 ib_logfile1
-rw-r----- 1 rootroot 18874368 Mar 28 05:39 ibdata1
drwx------ 2 rootroot    4096 Mar 28 05:39 mysql
drwx------ 2 rootroot    4096 Mar 28 05:39 performance_schema
drwx------ 2 rootroot    4096 Mar 28 05:39 test
-rw-r----- 1 rootroot      89 Mar 28 05:39 xtrabackup_checkpoints
-rw-r----- 1 rootroot      562 Mar 28 05:39 xtrabackup_info
-rw-r----- 1 rootroot  2097152 Mar 28 05:39 xtrabackup_logfile
稿源:Backup and Data Recovery for MySQL (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Howto Encrypt MySQL Backups on S3

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录