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:
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:
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.
Thanks for the detailed tutorial! The first part was chrystal clear from my beginner’s perspective. I’m stuck at the Permission Denied, and I thank you for providing further information about that, which I am chasing down now.
What does this mean: “just type in the following command and chmod db-backup.sh after logging into shell” and then “Now run the script again mamnaully by following”
These are two different skills from the earlier part. How do you “chmod something” and how do you “run a script manually”?
You can chmod like this, +x means making the script executable. >>
chmod +x /home/cPanel-user/db-backup.sh
And you type this command by logging into shell. You can login using any SSH client like Putty.
Please note that you need SSH access to your web server, provided it’s enabled by your web host which is usually diabled.
By “running a script manually” I meant typing the command on shell and checking if it runs successfully and creates the DB backup file in the desired directory.
Again inside shell, you invoke the .sh script by typing >> /home/cPanel-User/db-backup.sh
This script is supposed to be invoked by a cron job at the specified time automatically which was the original idea of post.