Free Automatic MySQL Database Backup

Manual MySQL Database backup may take lot of time. To get automatic backup there are several plugins which you have to install and in return they cluster your database tables. There are also few websites which offer same service in return of some dollars.

Without using plugins and losing money you can backup your local Database with the help of mysqldump. mysqldump is a program to dump Database for backup. With Shell commands and cron jobs you can take automatic backup of MySQL database and send it to your mail. But before starting with the steps take a manual backup of your database.

Step 1

First of all create a text file in your desktop. You have to add 4 lines in the text file. But before adding the lines you should know about each line and their functions.

Line 1 :

mysqldump DB_NAME -uDB_USERNAME -pDB_PASSWORD > /home/username/path/db_backup.sql

Replace DB_NAME, DB_USERNAME and DB_PASSWORD with your MySQL Database name, Database Username and Database Password. In most cases Database name and Database Username are same. You have to enter your Database password and not cpanel password. If you are using wordpress and not sure about the Databse name, username and password you can also view your wp-config.php file or ask your hosting provider.

The first line says to start mysqldump on given database name with username and password and store it in the given path. You have to use absolute path to store the file. It is safe to create a folder above public_html dir and save the backup file in it.

Line 2:

zip -q /home/username/path/db_backup.zip  /home/username/path/db_backup.sql

This line will compress the backup file by converting backup.sql file to backup.zip file. It is better to store the sql and compressed file under same folder. You have to mention the absolute path for both the files.

Line 3:

mail -s “Backup Notification” -a /home/username/path/db_backup.zip username@address.com

This line will send the compressed backup file to your email address with subject Backup Notification. Replace username@address.com to the email address you like to send the back up file. you can also change the email subject to anything else but within double quotes.

Line 4:

rm -f /home/username/path/db_backup.zip /home/username/path/db_backup.sql

This line is fully optional. This line will delete both backup and compressed backup file. For security reasons, it is better to remove the file from server.

Step 2

Now save the file as backup.sh and upload it to server above public_html dir. It will be a good idea to store it in the same dir where you gonna store backup files. Change the permission to 0700.

Till now you have completed the scripting task. Now it’s time to set up cron jobs which will do the further automatic work by running the script.

Step 3

Go to cpanel>Advanced>Cron Jobs.

Choose the time period from common setting drop down box. For testing purpose you can select 1 min or 5 min. Just add absolute path of the shell script in command field.

/home/username/path/backup.sh

Click add new cron job button. Wait for few seconds and once you get a mail with your database attached you have successfully completed the steps and ready to get automatic database backup in your mail. If you didn’t got the mail after waiting for long time you should again check the steps or your Database Name, username and password. After completing the step change the time period on Cron Jobs to daily or weekly or according to your need by edit line option.