Last week a friend of mine lost a week’s worth of entries on her blog. Seems her host only backs up weekly when she understood it was daily.
Here’s a script you can try so you’ll always have a fresh copy of your blog and other databases:
Download the latest version of this Auto MySQL Backup Script. It’s version 2.2 as of today.
Open this file in your text editor and change the following:
# Username to access the MySQL server e.g. dbuser
USERNAME=dbuser Your hosting account username
# Username to access the MySQL server e.g. password PASSWORD=password Your hosting account password
# Host name (or IP address) of MySQL server e.g localhost DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″ DBNAMES=”DB1 DB2 DB3” Use “all” or enter each database name you want backed up
# Backup directory location e.g /backups
BACKUPDIR=”/backups” Delete the backslash so it’s just “backups”
# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
# Email Address to send mail to? (firstname.lastname@example.org)
MAILADDR=”email@example.com” Change to your email adress
Everything else stays the same.
Now into your hosting account, then File Manager
Create a folder named backups
Create a folder named scripts
In the scripts folder, upload the file automysqlbackup.sh.2.2
Once it is uploaded, click on the file and change the Permissions to 744
Leave the File Manager section and go back to your cPanel home and then into the Cron Jobs section (usually towards the bottom)
Enter the email address you are sending the backup to
Command line to run: /home/hostusername/scripts/automysqlbackup.sh.2.2
Minute Box: Click on 42
Hours: Click on 2 am
Day: Every day
Month: Every month
Weekday: Every weekday
Click Save Crontab
If set up correctly, you should be emailed a copy of each of your databases every night at 2:42 am (well unless your hosting goes down!). A copy of the databases will also be sent to the ‘backups’ folder you created. Since the backups are both named and dated, none of them will be overwritten each night. If you’re tight on space, you can clean this out every other week by simply deleting the files.
You can set any time you’d like for the cron tab, but usually it’s good practice to do this in the middle of the night when the server is less active.
You can also choose whatever folder names you like, they don’t have to be ‘backups’ and ‘scripts’. Just make sure the names you choose are reflected in both the Cron command line and the script itself.