I accidentally deleted my MySQL backup script. No backups is bad news. I was looking for replacements and found a bunch, but they are all overkill. And half of them don’t even zip your backups.
The only two elements I really need control over are the database name and directory to store the backups. Those are easily handled with shell variables.
Things to note:
- Don’t store passwords in the file! You need to enter a password, so use the password shadow file. On systems using Plesk, this should be /etc/psa/psa.shadow. If you don’t have a password shadow file, you can store the password in an option in the user’s .my.cnf file in their home directory. Make sure to make it readable only by the user.
- While you’re at it, make sure the user has only the necessary privileges. Don’t use a superuser account or an account with access to other databases.
- Ensure unique file names. I use the date command for this. The format string “%b-%d-%y_%H” produces 2011-05-12_08, but adjust to your own needs.
Then just stick it in your crontab and you’re done!
If anyone knows a nice way to do incremental backups, that’d be nice.
#!/bin/bash BACKUP_DIR="/YOUR/BACKUP/DIR" DB="DATABASE_NAME" mysqldump -uUSER -p`cat /PATH/TO/PASSWDSHADOW` $DB | gzip > $BACKUP_DIR/$DB.`date +%b-%d-%y_%H`.sql.gz