Cron and mySQL Database Backups
As anyone with much computer experience knows, file backups are a good thing, and better too many than none. This bit of good sense applies absolutely to database websites, where the content of nearly your entire website is contained in a database on your web server and nowhere else. If something happens (for instance, if the database gets corrupted), you’ll get to experience the thrilling -poof!- of lost data. Sure, there may be a backup on the server, but that’s a maybe, so planning for the future is always good.
How you back up your database is your choice; many web hosts offer automated solutions. Me, I have some virtual private servers, so whatever might be automated is going to have to be automated by me. I’ve been playing with various options but, interestingly enough, just found a link in my server logs (as an Internet marketer, you *do* read your server logs, don’t you?) and found a link to BloggingPro.com — specifically, a post that just happened to be about database backup scripts on *nix.
So. My preferred method of backing up databases and other server tasks is via SSH line commands. I prefer it because, while a nice GUI interface may spare you the pain of having to understand whatever you’re doing and then doing it manually, the interface somewhat masks what’s going on — and I like to know precisely what’s happening. So, line commands first, interfaces later. Maybe. The thing is that server syntax — the precise commands used to achieve an end — seem to vary somewhat from server to server. Miss a character and the command bombs out. So, on my FreeBSD server, using the mysqldump functionality, this line command works via SSH to create a “dump” file of the entire database in ASCII format in whatever directory you’re in; no need to go into mysql or to specify *where* the database is:
mysqldump -u root -p DATABASENAME > BACKUP.sql
(Courtesy of ModWest Hosting.) Remembering that I’m not a server guru, here’s the translation:
mysqldump — start the dump sequence
-u — specifies a user
root — the user
-p — specifies that it should ask for a password
DATABASENAME — specifies the specific database
> — kind of a “to” symbol
BACKUP.sql — the new file name for the dump results
Hint: if you’ve ever changed the main server password, root’s password is likely to be the *original* one.
Now, since typing line commands into a server on a regular basis is not a good plan (I’m a big city kinda girl, doncha know), my next move was to write a shell script to run the above via the server’s cron function at intervals of my choosing, which is precisely where our unknown friend from BloggingPro.com came in. Nice, eh? Just remember that you may have to play around with his line commands before it works on your server.
Cron mySQL backup script on *nix
/usr/local/bin/mysqldump -uroot -pROOTPASSWORD –opt DATABASENAME > BACKUPNAME.sql
/bin/mv BACKUPNAME.sql.gz MYBACKUPS/`/bin/date +BACKUPNAME.sql-%Y%m%d.gz`
You’ll have to ensure that the pathnames are correct (e.g., /usr/local/whatever).
What the above does is request the mysqldump sequence by root, give root’s password, and then specify the database and the final .sql file output name.
Then (I told you I was a city girl), it gzips the backup, then moves it to an existing folder (here, MYBACKUPS) with the date appended.
Nice and tidy, eh?
<added> Some references on restoring the mysqldump file:
- FreeBSD.org — restoring and backing up in both binary *and* ASCII
- ModWest Hosting — how to import the mysqldump file
This code should work on a VPS, but you’ll have to allow for server location of the particular programs (usr/local/whatever — see the encoding above):
mysql < dbname.sql
Update: You *may* run into a situation where the database doesn’t restore with the above commands. One solution (given graciously by an amiable Verio tech) is to add the following to the top of the mysql dump file:
That said, this is what works on some servers: log in to server as root, but do not go into mysql:
mysql -u root -p -D DATABASENAME < DUMPNAME.sql
Hit Return/Enter, you’ll be prompted for your password, and life is good.
34 Comments to "Cron and mySQL Database Backups"
Have your say ...
First-time comments will be held for moderation (but comments are appreciated). Otherwise, just be courteous. If your name is a bunch of keywords, your comment will be deleted. Don't post links unless highly pertinent. Posters must be 16 or older.