Backup mySQL
Just thought I’d post and remind you (because we all know) to backup your mySQL databases. I recently lost my localhost databases (about 8 of them). I had about a week-old backup that I had to resort to. I had a lot of things I had to re-apply on my database.
For those who don’t know how I’ll show you how. For smaller databases you might get away with using the “Export” feature in phpMyAdmin, but when you have big databases the script might hang and timeout. So you’ll want to use the mysqldump application instead.
The command is the same accross platforms. Get your command line goin’ (Windows users: Start->Run->cmd. I doubt I have to tell Linux users how to access the terminal :-P) and cruise to the mysql bin directory. Once there, simply run the command:
If you want to backup all databases on the server, replace “database_name” with the “–all-databases” option:
You can also pipe your SQL output to gzip if you’d like (most likely, only Linux users will be interested in doing this):
If you want to automate the process (perhapse put it as a cron job) here’s a little bash script I picked up from somebody at WHT:
mysqldump -uusername –password=password –add-locks –add-drop-table database_name > /path/to/database_`date | awk ‘{print $2$3}’`.sql;
This will create files named database_<date>. For example: database_feb01 (note that ‘01′ is the day, not the year. You’ll have to hack it if you want the year as well. I never had a use for the year because I never kept backups that old).