December 30, 2007
In part one of this tutorial, Automating Your Server Backups On Media Temple's Grid-Server, I explained how to automate backups of your files on your grid-server. Unfortunately, not all your data was covered in those backups because it did not include your database. Now I will describe how you can utilize cron and mysqldump to automate database backups on the grid-server side.
The Process
Like the last tutorial, we will go about this in a similar fashion by creating a script which we will utilize as a cron job.
- Open up your favorite text-editor and copy and paste the following snippet:
#!/bin/bash
mysqldump --add-drop-table -h internal-db.s99999.gridserver.com -udb99999 -pPASSWORD database_name > database_name.sqlWe will need to replace a few things here though:
- Replace all the instances of '99999' with your gridserver account number.
- Replace 'PASSWORD' with your mySQL password
- Replace 'database_name' with the name of the database you'd like to backup
If you've ever setup WordPress or some other CMS which needs a database, you've likely come across this info before.
You could test if your command works by first SSHing in and then entering in the command. If an sql file is created, it worked.
If you have more than one database you want to backup, just create another mysqldump command.
- Now save this file as something like 'db-backup.sh'
- Upload 'db-backup.sh' to your /data folder
I chose to put the script in this folder because when it runs, the .sql files are also dumped into this folder. This folder is nice because it is only accessible by you and you wouldn't want the public to accidentally stumble upon an entire dump of your database. - Now log into your Media Temple Account Center
- Go to the admin section of your primary domain and click the Cron Jobs link
- Click on the 'Add a new cron job' button
- Feel free to fill in your email address in the output email field if you'd like. This will send you emails whenever the cron job runs and will let you know if it was successful or not. However, the important field we need to fill out is Command to Run. In this field, enter:
/home/99999/data/db-backup.sh
Again, replace the '99999' with your grid-server account number (it's actually listed below 'Command to Run')
- In the bottom half of the page, you can specify how often you'd like your script to run. This entire section is up to you. I've set mine to run daily at 2AM when traffic on my site is slower
- Click Save and you're all done!
Now what about saving these mysql dumps to my local disk?
If you've went through part one of this tutorial and successfully set up rsync, these dumps are now part of that synchronization. 
Again, if something didn't make sense or you know of some way to improve these backup processes, please feel free to leave a comment!
Thanks for this article, it is very useful!
Thankyou so much for this post, EXACTLY what I’ve been looking for!
Glad that you guys found it useful
Can’t possibly thank you enough for this script. Searched for hours compiling bits from here and there, and this was by far the most useful.
I believe the problem with the other scripts is that I needed to define the password and username in the manner that you use in order to call the mysqldump function from a php file.
If anyone else has the (dv) dedicated virtual server, and you’re attempting to run a nightly data backup through a cron job executing a php script, here is what works for me (make sure you set the 3 constants DB_USER, DB_PASSWORD, & DB_NAME, and change the $backupfile path:
// One week of backups are maintained
function nightlyBackup(){
$backupfile = ‘../backups/’.date(‘l’).’.sql’;
// Since the mysqldump script doesn’t overwrite, we must delete the old backup
if(file_exists($backupfile)){
unlink($backupfile);
}
$command = “mysqldump -u”.DB_USER.” -p”.DB_PASSWORD.” “.DB_NAME.” > $backupfile”;
system($command);
}
nightlyBackup();
Thanks again!
Tison
Confirmation email states:
/home/xxxxx/data/db-backup.sh: line 2: dbxxxxx_db_country_areas.sql: Permission denied
any idea? Worked fine with ssh as it created .sql in my user directory.
Daniel: I never got that before. Have you SSH’d again and tried doing the dump with the file existing already? I’d try to see if the “Permission denied” error really means that the file can’t be overwritten.
Sam, If I ssh and run the file it creates the .sql files fine, and if I do it again is does overwrites them. But as a cron job it gives permission denied. Is the file run as a different user by the cron job as opposed to me via ssh?
I was also thinking along those lines Daniel. I can’t help out any further but I’m sure the folks at Media Temple (that is, if they are your host) can help you look into this as it most likely is a user permission issue.
Hey! Ihavn’t tried this, but can you explain me if there is a way to do this for multiple databases? Like, run the command several times to produce several backups? I have 13 DB’s on my grid-server and it’s a pain to manually backup everyone, everytime!
Thank for the help!
Hugo, if you follow the steps above but just insert additional commands of the following in your script for each database, it will create dumps of all your databases when it runs!
Be sure to review step one to see which parts of this command you’ll need to modify.
Sam,
Does this script overwrite the files everytime it is run?
mysqldump –add-drop-table -h internal-db.s99999.gridserver.com -udb99999 -pPASSWORD database_name > database_name.sql
Thanks!
Michael
Michael, I did find that the script did overwrite the existing sql file when it was run. If it doesn’t, you might experience something like what Daniel experienced where report emails would be sent saying that there was a permission issue.
for the permission denied, make sure you are using absolute paths…
Sam Lu…Thanks for the tip. I had the same issue and the abs path fixed it!
@EtherBunny I can’t take credit for the tip, Luca F was the one who actually said it
Sam, thanks for great post.
I have another way of resolve permission denied problem. Just add first line of your script “cd /home/99999/data” (your script directory) and you can use relative paths because current directory becomes correct.
Happy New Year to everyone!
I followed the steps perfectly on my gs. However, I get an error in the email saying No such file or directory.
I have the db-backup.sh file in my /data folder, and have tried defining it in the Cron job as both /data/db-backup.sh and /home/*****/data/db-backup.sh where ***** is my server number.
Any insights?
That’s quite odd Matt, the latter should be working fine…
I guess one way to verify that the path is correct and the script works is to SSH into your gs and try running it with the following command (no matter where you’re located once you’re SSH’d in):
Where “*****” is obviously your server number.
That would be my first step in troubleshooting it…
Hi, found this and it’s very useful. I circumvented the permission denied by adding “cd /home/99999/data” on the next line under #!/bin/bash.
No the e-mail from the cron daemon says “mysqldump: unknown option ‘–’”.
I had the same error while SSHed into my server. I googled it a bit but didn’t find much help, any clues?
Here’s my code:
cd /home/99999/data
mysqldump -add-drop-table -h internal-db.s99999.gridserver.com -udb99999 -pPASSWORD db99999_db > db99999_db.sql
Sorry dale, I seem to have posted a command with bad syntax and I’m surprised it’s gone this far without me catching it.
The first bit of it is actually supposed to be like this:
mysqldump ––add-drop-table -h (…)
Note the two dashes in front of the “add-drop-table” parameter unlike the command I posted before where it was only one dash.
Edit: Ah, looks like WordPress was the culprit. It parsed the two dashes as one long one. I’m guessing it must be a new feature as part of a recent update otherwise someone would have caught it before.
Hah, thanks that was it.
Hey, thanks for the tip. For anyone looking to backup individual databases in one place, you can use this looped script instead of entering a separate line for each database
#!/bin/bash
PREFIX=”db99999″
USER=”99999″
PASSWORD=”xxxxxx”
for DB in database1 database2 database3 databasex
do
mysqldump –add-drop-table -h internal-db.$USER.gridserver.com -udb$USER -p$PASSWORD $PREFIX_$DB > $PREFIX_$DB.sql
done
exit 0
List the name of individual databases in line 5, without the db99999_ prefix. Username and password change for your own.
Obviously this only works with mediatemple (gs) but you can adapt it for your own use if needed.
Good luck
of course, a backup is only useful if you can actually use it when the original has been corrupted. this script will append the numeric day of the week to the filename so you have a rolling 7 day backup in case you don’t see a problem within 24 hours.
#!/bin/bash
NOW=$(date +”%u”)
DBUSER=”db99999″
USER=”99999″
PASSWORD=”xxxxxx”
PREFIX=”db99999_”
for DB in database1 database2 database3 databasex
do
mysqldump –add-drop-table -h internal-db.$USER.gridserver.com -u$DBUSER -p$PASSWORD $PREFIX$DB > $PREFIX$DB.$NOW.sql
done
exit 0
How useful! Thanks for posting these scripts up!
Hi there,
I keep getting ‘permission denied’.
I’m using the following script:
NOW=$(date +”%u”)
DBUSER=”db99999?
USER=”99999?
PASSWORD=”PASSWORD”
PREFIX=”db99999_”
for DB in database1 database2 database3 databasex
do
mysqldump –-add-drop-table -h internal-db.$USER.gridserver.com -u$DBUSER -p$PASSWORD $PREFIX$DB > /nfs/c04/h03/mnt/99999/data/$PREFIX$DB.$NOW.sql
done
exit 0
I’m running my cron tab with the following command:
/nfs/c04/h03/mnt/99999/data/db-backup.sh
Any ideas on why this is generating permission denied would be much appreciated.
Thanks.
People above have mentioned that not using absolute paths can result in “permission denied” errors. However it looks like you’re using absolute paths so the best advice I can give at this point is to double check the script and make sure you’ve replaced all user id placeholders and password placeholders and try it again. If it still fails, I’m sure you can try getting some help from the mediatemple staff as they can directly access your scripts and see what’s up.
@ Sam Lu, thanks for the advice – thought i would run it by this thread first, but will indeed try mt staff. Thanks.
For those who ever get No such file or directory.
Don’t use the file name ‘db-backup.sh’, change it to something else like ‘db-new.sh’
NOW=$(date +”%d-%m %Y-%r”)
mysqldump –add-drop-table -h internal-db.s######.gridserver.com -udb###### -pPASSWORD db######_name > /home/######/data/backup/db######_name_$NOW.sql
Solution provided from (mt) support.
Is there any way of emailing the dump file to an email address?
Off the top of my head, you could probably create a separate php script that would send an email with the dump file as an attachment. This page shows how to get php to send an email with an attachment:
http://www.webcheatsheet.com/PHP/send_email_text_html_attachment.php#attachment
Then all you’d need to do is add another line to the bash script to hit that php script, probably best to do it with wget.
For those whome getting permission denied error, do not use SSH password, use database password written down on Server Guide section. They are different and i don’t get it how ssh password works for this script?
By the way, thank you for the tip.
If you keep getting User cannot connect using password yes,
try removing any special characters, I was having issues with them on login through cron, even though login works through browser.
I have an other problem right here. My script is working with no error when i connect thorugh SSH. But when i use Cron to execute it, I get an email just saying “Permission denied”. I can’t anything follow where i am doing something wrong.
Btw, I am using absloute paths.
Again me
I have solved the issue. It was becasue of my mistake, a small directory permission issue.
Thank you.
I’m getting the error message: cannot execute binary file in the cron job emails notification.
Anyone got any ideas?
Hey,
Great blog post, it helped me a lot. Just one correction:
# Replace ‘PASSWORD’ with your SSH password
Shouldn’t that be the Database password? My SSH and my MySQL passwords are different, so I had to use the MySQL password.
Thanks for pointing out that error. It’s been corrected now.
something like this works perfect!
NOW=$(date +”%Y-%m-%d”)
USER=”99999″
DBUSER=”db99999″
PASSWORD=”xxxxxxxx”
PREFIX=”db99999_”
for DB in db1 db2 db3
do
mysqldump –add-drop-table -h internal-db.s$USER.gridserver.com -u$DBUSER -p$PASSWORD $PREFIX$DB > /home/$USER/data/backups/$PREFIX$DB.$NOW.sql
done
exit 0
Where can i found /data folder or I need to create a folder
called /data and place db-backup.sh file in project root?
Kiran, at the time, the mediatemple gs service provided a data directory which was private. You can create this folder if you’re not with mediatemple’s gs service. It’s purely just for storing the database backups and it’s important that you keep it outside of a public directory so no one else can see it.
Hi Sam, it appears that mysqldump has been removed from MT(gs). What else would you suggest for mysql backup?
It’s still working for me John, where did you get your information?
Hi Dale, turns out it had been removed (no explanation how/why). A sysadmin has put it back on now. I’m glad I occasionally read the output of my backup scripts!
Do you mean it was removed for all of the (gs) or just you?
That I don’t know. I assume that (gs) accounts share the same underlying OS, so it was probably missing for all accounts I’m hosted on. I’m sure it wasn’t removed for all of (gs) though.
This was so incredibly useful.
I get the following error message
Syntax error near unexpected token `do
Please help