(gs):Create timestamped database backups
- This page was last modified on July 28, 2011, at 04:54.
From (mt) Community Wiki
Contents |
MySQL databases are the heart and soul of practically every website these days. From shopping carts, to blogs, to web forums, all would be lost without a database. Making regular backups of your database is the best way to ensure your data is safe, in the event of a database failure or other problems. This article will show you how easy it is to create a very simply daily backup of a database.
Setup, configuration, and troubleshooting is not supported by (mt) Media Temple.
Requirements
This article requires using SSH, basic command-line scripting, vi (or your favorite text editor), chmod, and cron. The steps below are written with the assumption that you are connected to the server via SSH.
"-u DBUser" needs to be replaced with your database username, e.g. "-U db12345", "-U foodistasty", etc.
"-pSecret" needs to be replaced with your database user password, e.g. "-ppassword", "-p1a2b3c4d", etc.
When you see 00000, you need to insert your Site Number. This can be obtained from the Server Guide inside the AccountCenter.
Steps
1. Decide which database you want to back up. I want to back-up a WordPress database called "db0000_dbname", for the website "foobar.com". Let's log into SSH and change into the main directory for foobar, and use the vi text editor to create a new file called "backup.sh":
hostname:~$ cd domains/foobar.com hostname:~$:~/domains/foobar.com$ vi backup.sh
2. Add the following information into the backup.sh file, and then save (if you are unfamiliar with how to use vi, refer to (gs):Use vi to edit files):
#!/bin/sh
cd ~/domains/foobar.com
mysqldump -h internal-db.s00000.gridserver.com --add-drop-table -u DBUser -pSecret db00000_dbname > database-backup-`date '+%Y.%m.%d'`.sql
3. Run the command chmod 744 backup.sh, which will make the file executable by the owner (that's you!):
hostname:~$:~/domains/foobar.com$ chmod 744 backup.sh
4. Just to make sure it works, test it out:
hostname:~$s:~/domains/foobar.com$ ./backup.sh
5. If you entered the mysqldump command correctly, you should not see any error messages on the screen. To show that the command ran, when you perform a list directory command (ls), you should see a file called "database-backup11.15.10.zip", or whatever is the corresponding date. So now that we have a working script, it's time to make a regularly occurring job out of it.
6. From inside the AccountCenter, choose the admin button and then click Cron Jobs. Look for this icon:
7. Click Add new cron job
8. Fill out the notification email field. This is important, so you are notified if the cron job fails.
9. Fill out the "Command or script to execute" field with the full path to your backup. In this example, it will be
/home/00000/domains/foobar.com/backup.sh
10. Choose what time you want the cron job to run. Since this will be a daily backup, you might want to specify 12:00am daily. Or perhaps you would rather back things up at 6:00pm, or at 8:45am; it is up to you.
11. Once you are satisfied, press the save button.
Notes
The (gs) only allows five cron jobs, and also institute restrictions into the length of time a cron job can be run.
- 5 minutes of CPU time
- 15 minutes of actual time
- 200 simultaneous file descriptors
- 100MB of RAM
- 5 simultaneous processes
Final Notes
The script provided can be made much more advanced, to include backing up multiple domains, backing up and then zipping the databases, etc. Those modifications are beyond the scope of this document.