As you may or may not know (depending on how many of my blog posts you've read before), all of the data for my web comics is stored in a MySQL database. The words coming out of the frogs mouths, their positions in each cell, and the SVG images of the frogs themselves - all stored in simple tables in MySQL. I needed to be doing backups.
I set it up with cron and mysqldump. I wrote a simple bash script that will call mysqldump to create the complete backup of my amphibian database. It looks something like this:
#!/bin/bash fn=/path/to/backups/amphibian-$(date +%d-%b-%Y).dump mysqldump amphibian > "$fn"
To make sure each backup file gets a unique name, I create the fn variable which includes a section made out of the formatted date, in DD-Mon-YYYY format. To the Linux date command, that is %d-%b-%Y. Then I simply call mysqldump giving it a single parameter: the database name. I redirect the output to a file of the name I made above...and that's it.
Wait, why didn't it prompt for a user name and password to dump that database? Good question! That was my initial problem - I didn't want to put the password right in the script file. I found that if I make a file in my home area named .my.cnf and have it contain a section like this,
[mysqldump] user = username password = pw12345
...I can use mysqldump with a default user and without being prompted for a password. As long as the .my.cnf file has 0600 (read-write only owner) file permissions, it is reasonably secure.
I set my backup script to be called once per week by cron and I'm feeling much better now. I made an additional script that automatically copies the backup files offsite to a remote server for added safety. Being prepared for catastrophic data loss will really let me sleep better tonight.
|Amphibian.com comic for 19 December 2014|