Everyone knows that backups are important—whether it is a system failure or you simply want to revert to an older state because an error was made.
Backups are often handled by cron jobs. In most cases, however, a simple MySQL dump is generated without encrypting the content.
In this article, I describe how to create backups using GPG without using a fixed password. Since there may be multiple database administrators who need to restore backups, every authorized admin should be able to restore the backup using their own passphrase.
BACKUP_DIR=/var/backup/auto/mysql
DATE=$(date -I)
TARGET_DIR=$BACKUP_DIR/$DATE
mkdir -p $TARGET_DIR
DATABASES=$(mysql -uadmin -B -s -e”show databases;”)
for db in $DATABASES; do
TARGET=$TARGET_DIR/$db.sql.gz.gpg
echo -n dumping $db to $TARGET …
mysqldump -uadmin $db | gzip | gpg -rCAFEBABE -rAFFE4711 -rAFFEFACE -e – > $TARGET
if [ $PIPESTATUS -eq 0 ]; then echo OK; else echo ERROR; fi
done
The variable BACKUP_DIR specifies where the backups should be stored. Below that, a directory is created with the date in the format YYYY-MM-DD (date -I), where the dumps of individual databases are placed. If you want to generate more than one backup per day, you can adjust this command accordingly.
Example output:
dumping mysql to /var/backup/auto/mysql/2012-04-06/mysql.sql.gz.gpg …OK
dumping test to /var/backup/auto/mysql/2012-04-06/test.sql.gz.gpg …OK
…
What needs to be adjusted in this script are the KEY/IDs of the recipients—i.e., who is allowed to decrypt the data later. In this example, three recipients are specified:
Each recipient has the ability to decrypt the backup files. GPG automatically determines the key for the “decryptor”:
You need a passphrase to unlock the secret key for
user: “XZY”
2048-bit ELG-E key, ID AFFEFACE, created…
Enter passphrase:
gpg: encrypted with 2048-bit RSA key, ID CAFEBABE…
gpg: encrypted with 2048-bit ELG-E key, ID AFFE4711…
gpg: encrypted with 2048-bit ELG-E key, ID AFFEFACE…
The actual output then appears in STDOUT:
-- MySQL dump 10.13 Distrib 5.1.56, for redhat-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.1.56
If you have not had much experience with GnuPG and the MySQL CLI, you should consider the following points before running the example:
– Keys of all recipients should be signed/trusted beforehand so that the backup script does not enter interactive mode.
– The password for database access is defined by default in the ~/.my.cnf file in the [client] section.
Have fun testing and implementing it,
C.
