\n#!\/bin\/bash<\/p>\n
BACKUP_DIR=\/var\/backup\/auto\/mysql
\nDATE=$(date -I)
\nTARGET_DIR=$BACKUP_DIR\/$DATE
\nmkdir -p $TARGET_DIR<\/p>\n
DATABASES=$(mysql -uadmin -B -s -e”show databases;”)<\/p>\n
for db in $DATABASES; do
\nTARGET=$TARGET_DIR\/$db.sql.gz.gpg
\necho -n dumping $db to $TARGET …
\nmysqldump -uadmin $db | gzip | gpg -rCAFEBABE -rAFFE4711 -rAFFEFACE -e – > $TARGET
\nif [ $PIPESTATUS -eq 0 ]; then echo OK; else echo ERROR; fi
\ndone\n<\/p><\/div>\n
The variable BACKUP_DIR<\/code> specifies where the backups should be stored. Below that, a directory is created with the date in the format YYYY-MM-DD (date -I<\/code>), 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. <\/p>\nExample output:<\/p>\n
…
\ndumping mysql to \/var\/backup\/auto\/mysql\/2012-04-06\/mysql.sql.gz.gpg …OK
\ndumping test to \/var\/backup\/auto\/mysql\/2012-04-06\/test.sql.gz.gpg …OK
\n…<\/div>\n
What needs to be adjusted in this script are the KEY\/IDs of the recipients\u2014i.e., who is allowed to decrypt the data later. In this example, three recipients are specified: <\/p>\n
\ngpg -rCAFEBABE -rAFFE4711 -rAFFEFACE\n<\/div>\n
Each recipient has the ability to decrypt the backup files. GPG automatically determines the key for the “decryptor”: <\/p>\n
\ngpg -d mysql\/2012-04-06\/test.sql.gz.gpg | gunzip | head -n 5<\/p>\n
You need a passphrase to unlock the secret key for
\nuser: “XZY”
\n2048-bit ELG-E key, ID AFFEFACE, created…
\nEnter passphrase:<\/p>\n
gpg: encrypted with 2048-bit RSA key, ID CAFEBABE…
\ngpg: encrypted with 2048-bit ELG-E key, ID AFFE4711…
\ngpg: encrypted with 2048-bit ELG-E key, ID AFFEFACE…\n<\/p><\/div>\n
The actual output then appears in STDOUT:<\/p>\n
-- MySQL dump 10.13 Distrib 5.1.56, for redhat-linux-gnu (x86_64)
\n--
\n-- Host: localhost Database: test
\n-- ------------------------------------------------------
\n-- Server version 5.1.56
\n<\/code><\/div>\nIf you have not had much experience with GnuPG and the MySQL CLI, you should consider the following points before running the example:
\n– Keys of all recipients should be signed\/trusted beforehand so that the backup script does not enter interactive mode.
\n– The password for database access is defined by default in the ~\/.my.cnf file in the [client] section.<\/p>\n
Have fun testing and implementing it,
\nC.<\/p>\n","protected":false},"excerpt":{"rendered":"
Everyone knows that backups are important\u2014whether 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 \u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[981,983],"tags":[988,1008,1145,1179,1102,1353,1003,1855,1036,1662,1058],"class_list":["post-11188","post","type-post","status-publish","format-standard","hentry","category-programming","category-tutorial","tag-administration","tag-backup","tag-backup-2","tag-database","tag-dump","tag-encryption","tag-gpg","tag-mysql","tag-mysqldump","tag-mysqldump-encrypt","tag-security-2"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/posts\/11188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/comments?post=11188"}],"version-history":[{"count":0,"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/posts\/11188\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/media?parent=11188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/categories?post=11188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.saasweb.agency\/en\/wp-json\/wp\/v2\/tags?post=11188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.saasweb.agency\/wp-content\/uploads\/2012\/04\/logo-mysql-110x57-1.png\" "\\"logo-mysql-110x57\\""){kind=logo}
Everyone knows that backups are important\u2014whether it is a system failure or you simply want to revert to an older state because an error was made.