Weekly database backup (compressed and encrypted)

General Tech and Hosting

I had the task of doing regular database backup on a server here at school and i needed some automatization. And encryption, of course, because there is a lot of info in database dumps including usernames and md5 hashed passwords and we don’t want that just laying around. That means that we have to leave phpmyadmin and do some command line stuff.

How to backup your database from the command line

The regular syntax that backups your database is the following (and its without  the “>”, “<“):

mysqldump –opt -u <username> -p <database name> > <db-backup-.sql>
The problem with this is that it prompts you for your password. And that can be an inconvenience when doing automated stuff, because – there’s no more automatization. To solve this, i made a configuration file (nano ~/.my.cnf) in my home directory with the following lines:
[mysqldump]
user = yoursqluser
password = yoursqlpassword
Yes, that means username and password in plaintext. Not that security-wise, but let’s just say the server is safe enough and i can do this.Then i had to give the appropiate permisions (chmod 600 ~/.my.cnf). Now we can use the same command, only without the -p parameter that will prompt us for the password.
mysqldump –opt -u <username> <database name> > <backup-file.sql>
I also wanted to include the date in the filename for a better overview. I did that by naming the file as so:
db-backup-$(date +”%m-%d-%y”).sql
So instead of the generic line from above, mine looks like the following:
mysqldump –opt -u myusername mydatabasename > db-backup-$(date +”%m-%d-%y”).sql

How to encrypt your database dump

The next line archieves, compresses and encrypts your sql log. The archieving/compressing part is relevant when working with big databases so that we can save space and send it faster. I’m using the AES256 cyper because of my teachers recommendation. There’s a lot of talk around which cypher is the most efficient, i haven’t looked into this yet so i’m just going with his suggestion.

tar cvzf – db-backup-12-17-13.sql | gzip –best -c | openssl enc -aes-256-cbc -out db-backup-12-17-13.tar.gz.enc -k <password>

You can probably notice the last parameter, “-k <password>” . This is to be specifiecd when you are using it within scripts or automated setups (like i’m doing now). You will not be prompted for any password, as you are specifing it in the command.

How to ship your encrypted data over to another location

We would like to keep the backups on a separate machine because of “reasons” – if the machine that we are backing up gets compromised, the backups in there are going to be useless. We have a network attached storage that has an FTP server, so i’m going to send the file there using CURL. Too bad the NAS can’t do SCP, it would had been better, but i’m going to stick with what i have.

curl -T db-backup-12-17-13.tar.gz.enc ftp://ftp.example.com –user user:password

How to scheldue a command to run weekly

I want it to run weekly and preferably at the end of the week. I have created a file(e.g. cronjobfile) with the following line:

0 20 * * 6 /path/to/my/script

That line will run my script every Saturday at 20:00 . If you don’t (want to) know the cron syntax you can easily use an online cron generator. Instead of the path to the script i could have also placed the command i wanted. I prefered to wrap everything up into a script because i can work with variables and such, which will make a later edit easy.

After creating the file i ran the cron tab command on so that it will start running.

crontab /path/to/cronjobfile

That was it. I’ll return with a blogpost about the script i made but first i have to figure some things out:

  • the best way to include login credentianls
  • notifing myself or somebody from the IT department when the script doesn’t work
  • what to do if the script can’t communicate wih the NAS

Resources

  • http://stackoverflow.com/questions/9293042/mysqldump-without-the-password-prompt
  • http://www.techiecorner.com/1619/how-to-setup-mysqldump-without-password-in-cronjob/
  • http://webcheatsheet.com/sql/mysql_backup_restore.php
  • http://superuser.com/questions/323214/how-to-upload-one-file-by-ftp-from-command-line