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.

# Create the Backup
mysqldump 
   -u backup_user 
   -pSomePassword database_name > backupfile.sql

# Encrypt the Backup
gpg 
   --output backupfile.sql.gpg 
   --encrypt 
   --recipient [email protected] 
   backupfile.sql

# Delete the non encrypted version
rm 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:

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”.

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.

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:

# Create the Backup
mysqldump --login-path=local database_name | gpg 
   --encrypt 
   --recipient [email protected] 
   > backupfile.sql.gpg