Thread Rating:
  • 6 Vote(s) - 4.33 Average
  • 1
  • 2
  • 3
  • 4
  • 5
HOW TO: Automated database backups (1 line of code + cPanel)
#1
Automated daily backups shouldn't be a chore and will save you many headaches in the future. This is the simplest script I've been able to find. This works with any Linux server but the guide is cPanel specific. Wink

Backup script:
/usr/bin/mysqldump -hDBHOST -uDBLOGIN -pDBPASSWORD DATABASE | gzip > /MY/BACKUP/LOCATION/BACKUPNAME.sql.gz
Open up notepad then copy and paste the above code in a blank file to edit.

Replace:
DBHOST = Your database server (usually localhost, consult your host if you are unsure).
DBLOGIN = Username for your database.
DBPASSWORD = Password for database username.
DATABASE = The database you wish to backup.
/MY/BACKUP/LOCATION/ = Location you wish the backup to be saved (A folder above the public directory for security). CHMOD to atleast 755.
BACKUPNAME = The name you wish to have the backup saved as in the directory listed.

After you've editing, save it to your PC and rename the file (you can name this anything but for this example I will be using mybackup).

Upload the file to your site above the public directory and remove the extention (.txt). Change the permission of the file to allow it to be executed. CHMOD 755 works.



--------------------------------------------------------------------
Now go to cPanel and go to the Cron Jobs area and create a cron job pointing to the file you uploaded.

Example:
Command to run: /MY/DIRECTORY/mybackup
Minutes: 15
Hours: 1 = 1 AM
Day(s) = Every Day
Month(s) = Every Month
Weekday(s) = Every Week Day
Replace /MY/DIRECTORY/ with the location you uploaded your "mybackup" script. If you are using cPanel and uploaded the file in your home directory then you would put: /home/YOURCPANELLOGIN/

This will create a daily backup of your database every day at 1:15AM server time (adjust as needed).
EACH DAY @ 1:15AM YOUR BACKUP WILL BE OVERWRITTEN!



--------------------------------------------------------------------
E-mail the backup:
To have the backup automatically e-mailed to you add this line to the file:
uuencode /MY/BACKUP/LOCATION/BACKUPNAME.sql.gz BACKUPNAME.sql.gz | mail [email protected]



--------------------------------------------------------------------
Keep in mind:
*This is only a LOCAL backup. If your server dies all is lost, be sure to download the backups to your PC to have both local and offsite backups!
*If your site is hacked or your database gets corrupt, disable your cronjob and download your backup so that any current backup you have is not overwritten.
*Relying on a single backup is risky. It is best to have a daily backup, weekly backup, and monthly backup. If you have plenty of space (or a small database) then have as many local and offsite backups as possible to lessen the damages done if you need to restore.
-Joe
AFreeCloud - Free Cloud-Based Web Hosting!
Reply
#2
Very Nice. Just to let everyone know, this will work with any system unix/linux based system. It doesn't need cPanel as cron is usually built into the OS.

I'll put this code in my DirectAdmin (better than cPanel) system to test it out.

This should be made MANDATORY for all admins.
Reply
#3
Thanks, I've only used cPanel/WHM but phocis is right, any Linux server will support this, but my guide might not be accurate for other control panels other than cPanel. Wink

I currently have this cron setup to backup each of my forums once a day + 1 every other day. Then again I also run daily, weekly, and monthly backups for my whole server but this method makes it easy to do a single database restore. Big Grin
-Joe
AFreeCloud - Free Cloud-Based Web Hosting!
Reply
#4
It's all fine except to where it points to the file. Not everyone has a "home/cpanelllogin/mybackup" directory Wink... But that wasn't intended to be left un-edited, right?

And just so you know, this works with vDeck 3.
"A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools." - Doug Adams
Reply
#5
Correct, I thought that was implied but I'll edit the original post. Big Grin
-Joe
AFreeCloud - Free Cloud-Based Web Hosting!
Reply
#6
Cool, thanks Big Grin! Just looking out for others of my kind, n00bs! xD
"A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools." - Doug Adams
Reply
#7
I was making that by SSH with putty

thanks for this tip Big Grin
Reply
#8
With this couldn't CRON be created to email this backup? or is that unsafe?
[Image: forum_sig.gif]
Reply
#9
Yes, you can add the option to have the backup e-mailed to you. I haven't done this myself but I know it is possible as I've seen others set it up. The only problem is if you have a big database it could take a while to e-mail. I don't remember how to do it off hand but I'll add it to the main post as soon as I find it.

Just tested it and it worked. Smile Added to the original post. Wink
-Joe
AFreeCloud - Free Cloud-Based Web Hosting!
Reply
#10
Just so I know I'm doing this right, when I replace things like DBHOST with my host server, do I just add it after the -h or put it in quotes?

For example would:

/usr/bin/mysqldump -hDBHOST -uDBLOGIN

look like this:

/usr/bin/mysqldump -hlocalhost -ujudel

or

/usr/bin/mysqldump -h"localhost" -u"judel"

Also, is that /usr/bin/mysqldump universal or will it only work with Cpanel?

edit: and I think I'm missing something. When I did a test run, it seemed to find everything ok but gives me an error when saving the file. It says that permission denied when trying to save mybackup.sql.gz. Any idea why?
Jude

If I post in your thread, please do not PM me for more help! Thanks!


Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)