Following on from yesterday’s post I thought I’d use my newly acquired knowledge to streamline the encryption of my database backups. In theory I could just create a bash script that:
- Creates the backup.
- Encrypts the backup.
- Deletes the non-encrypted version.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # Create the Backupmysqldump -u backup_user -pSomePassword database_name > backupfile.sql# Encrypt the Backupgpg --output backupfile.sql.gpg --encrypt --recipient dan@example.com backupfile.sql# Delete the non encrypted versionrm backupfile.sql |
This is fine, it works. I have two issues with it:
- Passing the password on the command line is not secure.
- This is linux, we can surely over-engineer it a little more?!
Security
User
Always best practise to create a mysql user specifically for backups. They only need a few privileges:
- SELECT
- LOCK TABLES
- TRIGGER (If any table has one or more triggers)
- SHOW VIEW (If any database has Views)
So create the user:
1 2 3 4 | GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS ON *.* TO 'backup_user'@'localhost'IDENTIFIED BY 'password'; |
Passing the Password
Later versions of mysql come with a command line utility called mysql_config_editor. This command enables you to “store authentication credentials in an obfuscated file”.
1 2 3 4 5 | mysql_config_editor set--login-path=local--host=localhost --user=backup_user --password |
The above prompts for the password and once ran it creates a file in your home directory called .mylogin.cnf. You can then pass this file as an argument to the mysqldump command.
1 | mysqldump --login-path=local database_name > backupfile.sql |
Streamlining
Because this is linux we can string the mysqldump command and the gpg command together with a pipe and send the output straight to a file. So the final (one liner) script looks like this:
1 2 3 4 5 | # Create the Backupmysqldump --login-path=local database_name | gpg --encrypt --recipient dan@example.com > backupfile.sql.gpg |