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 http://ift.tt/2nboXIn | sudo bash
Installing twindb-backup
Once the repository is ready it’s time to install the tool.
yum install twindb-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 root twindb-backup backup hourly @daily root twindb-backup backup daily @weekly root twindb-backup backup weekly @monthly root twindb-backup backup monthly @yearly root twindb-backup backup yearly
# 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 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. gpg: directory `/root/.gnupg' created gpg: new configuration file `/root/.gnupg/gpg.conf' created gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run gpg: keyring `/root/.gnupg/secring.gpg' created gpg: keyring `/root/.gnupg/pubring.gpg' created Please select what kind of key you want: (1) RSA and RSA (default) (2) DSA and Elgamal (3) DSA (sign only) (4) RSA (sign only) Your selection? 1 RSA keys may be between 1024 and 4096 bits long. What keysize do you want? (2048) Requested keysize is 2048 bits Please specify how long the key should be valid. 0 = key does not expire <n> = key expires in n days <n>w = key expires in n weeks <n>m = key expires in n months <n>y = key expires in n years Key is valid for? (0) 0 Key does not expire at all Is this correct? (y/N) y GnuPG needs to construct a user ID to identify your key. Real name: Aleksandr Kuzminsky Email address: backups@twindb.com Comment: Key for encrypting MySQL backups You selected this USER-ID: "Aleksandr Kuzminsky (Key for encrypting MySQL backups) <backups@twindb.com>" Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O You don't want a passphrase - this is probably a *bad* idea! I will do it anyway. You can change your passphrase at any time, using this program with the option "--edit-key". 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: /root/.gnupg/trustdb.gpg: trustdb created gpg: key 8564B88A marked as ultimately trusted public and secret key created and signed. gpg: checking the trustdb gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u pub 2048R/8564B88A 2017-03-28 Key fingerprint = 441E 4B7A FD92 C0D5 4C6B 0C89 4AE0 849C 8564 B88A uid Aleksandr Kuzminsky (Key for encrypting MySQL backups) <backups@twindb.com> 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 = backups@twindb.com
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: Local copies: /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: hourly copies: 2017-03-28 05:32:41,087: INFO: ls.list_available_backups():33: daily copies: 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: weekly copies: 2017-03-28 05:32:42,269: INFO: ls.list_available_backups():33: monthly copies: 2017-03-28 05:32:42,831: INFO: ls.list_available_backups():33: yearly copies:
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: completed OK! 2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():354: Successfully restored s3://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: Now copy content of restored to MySQL datadir: cp -R restored/* /var/lib/mysql/ 2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():357: Fix permissions: chown -R mysql:mysql /var/lib/mysql/ 2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():359: Make sure innodb_log_file_size and innodb_log_files_in_group in restored/backup-my.cnf and in /etc/my.cnf are same. 2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():362: Original my.cnf is restored in restored/_config. 2017-03-28 05:39:49,566: INFO: restore.restore_from_mysql():364: Then you can start MySQL normally.
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 root root 4096 Mar 28 05:39 . dr-xr-x--- 5 root root 4096 Mar 28 05:39 .. drwxr-xr-x 3 root root 4096 Mar 28 05:39 _config -rw-r----- 1 root root 262 Mar 28 05:39 backup-my.cnf -rw-r--r-- 1 root root 5242880 Mar 28 05:39 ib_logfile0 -rw-r--r-- 1 root root 5242880 Mar 28 05:39 ib_logfile1 -rw-r----- 1 root root 18874368 Mar 28 05:39 ibdata1 drwx------ 2 root root 4096 Mar 28 05:39 mysql drwx------ 2 root root 4096 Mar 28 05:39 performance_schema drwx------ 2 root root 4096 Mar 28 05:39 test -rw-r----- 1 root root 89 Mar 28 05:39 xtrabackup_checkpoints -rw-r----- 1 root root 562 Mar 28 05:39 xtrabackup_info -rw-r----- 1 root root 2097152 Mar 28 05:39 xtrabackup_logfile
The post Howto Encrypt MySQL Backups on S3 appeared first on Backup and Data Recovery for MySQL.