Free Auto MySQL Backup Script

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.
MAILCONTENT=”files”

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″

# Email Address to send mail to? (user@domain.com)
MAILADDR=”user@domain.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)
Select ‘Standard’
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.

That’s it!

๐Ÿ˜€

admin

I've been trying to find my way online for more years than I care to admit.

5 thoughts to “Free Auto MySQL Backup Script”

  1. This worked like a charm! Just a note, in case anyone else runs into this – when I opened up the script in Notepad, for some reason it had no line breaks, which made it really hard to find the parts I needed to change. So I just uploaded it to my server, then went in through Cpanel File Manager to edit it with my config settings. In File Manager, it looked like it was supposed to, and the editing was very simple.

    Your instructions were very clear! Thank you!

  2. Another note: some hosts forbid the use of “unattended scripts”, like this one. Fortunately, the only one I’ve found who forbids it does actually provide daily backups as part of the service, so that’s fine.

    Just be sure to check the fine print when choosing a host, or putting this script on your site.

  3. Thanks for that notice dani, I never thought to check my webhost’s TOS to see if I could run the script! Who would have thought a backup script could break TOS? ๐Ÿ˜† I do see a couple of my hosts not allowing cron jobs at all, so I can’t run it on those ones.

  4. should the path not be …./backups/scripts/… ????

    and not only …/scripts/…

  5. Hi real1,

    Actually no, the scripts folder is not created within the backup folder so it is just–/scripts/–

    The /scripts/ folder contains the script
    The /backups/ folder is where copies of the backups go (as well as to your email).

    You’ll have:

    /home/hostusername/scripts/automysqlbackup.sh.2.2

    and

    /home/hostusername/backups/

    That wasn’t too clear in my original post was it, sorry bout that ;).

Comments are closed.