Backup your MySQL databases automatically with cron job

Taking backup databases is a routine for every webmaster. Make your life a little easier by delegating this boring but extremely important chore to the shell script that will do this automatically and silently everyday, or whatever time you choose, for you

For automatic MySql database backup, please use following script to take backup using cron job by setting inside cPanel. Create a file named ‘db-backup.sh’ in home/cpanelUser/ with correct permissions/ownership.

Place this file on this location ‘/home/cPanel-User/db-backup.sh’ where cPanel-User is username of account.

Put the following code in the file db-backup.sh:

#/bin/bash
/usr/bin/mysqldump -–user=Database_User -–password=***** 
-–databases Database_Name | gzip > 
/home/cPanel-User/Database_Name-`date +%Y%m%d%H%M`.sql.gz

Where ‘Database_User’ is the database user, ‘Database_Name’ is the database name and ‘*****’ is the password of database user
+%Y%m%d%H%M is the time stamp,

The backup file will be created under directory “/home/cPanel-User/”.

Now, you need to add cron job. You can do it with the help of your control panel.

0 0 * * * /home/cPanel-User/db-backup.sh 

Make sure you have permissions to run this script and you can test it by going to shell, and just run the script manually. If it runs successfully, you have your Database dump at the specified directory.

The above cron job will executes every day at 00:00 hour and creates backup file for your MySQL Database.

How to Exclude specific table from MySQL Dump script using –ignore-table option

There will be times when you do not want one or some of the tables from the database(s) to be included in the backup dump, You can easily exclude them from the dump by adding following Option:

--ignore-table database_name.table_name

By adding ignore table option, your backup dump script will become:

#/bin/bash
/usr/bin/mysqldump -–user=Database_User -–password=***** -–databases Database_Name --ignore-table database_name.table_name | gzip > /home/cPanel-User/Database_Name-`date +%Y%m%d%H%M`.sql.gz

Running a .sh: Permission Denied Error

If you have uploaded db-backup.sh as suggested in this article, there’s good chance that you will run into this “Permission Denied” error if you invoke the script manually from shell. Just type in the following command and chmod db-backup.sh after logging into shell:

chmod +x /home/cPanel-user/db-backup.sh

Now run the script again manually by typing following:

[root@cpanel-user] /home/cPanel-User/db-backup.sh 

If you get following error:

No such file or directory
mysqldump: Got errno 32 on write

Just remove the /home/cPanel-User/ from the backup dump path. You may create a db-backups directory inside your cpanel-user directory such as db-backups/ and chmod it 755 or 777.

[root@cpanel-user] mkdir db-backups
[root@cpanel-user] chmod 777 db-backups

Updated .sh script with relative path to db dumps directory will look like this:

#/bin/bash
/usr/bin/mysqldump -–user=Database_User -–password=***** -–databases Database_Name --ignore-table database_name.table_name | gzip > db-backups/Database_Name-`date +%Y%m%d%H%M`.sql.gz

And this will definitely create the backup dump.

Also, if you intend to import the database after taking dump, you can easily avoid this by skipping –databases option in mysqldump command so that “use database;” is not added in the dump. With “–databases” option, it treats all name arguments as database names. “CREATE DATABASE IF NOT EXISTS db_name” and “USE db_name” statements are added in the output before each new database. command looks like this:

#/bin/bash
/usr/bin/mysqldump -–user=Database_User -–password=***** Database_Name | gzip > db-backups/Database_Name-`date +%Y%m%d%H%M`.sql.gz

Hope that helps.

Cheers!