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>
[mysqldump]user = yoursqluserpassword = yoursqlpassword
mysqldump –opt -u <username> <database name> > <backup-file.sql>
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.
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