Taking backup databases is a must 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 using cron job, It would be quite important that you are at least a bit familiar with how cron works and how to use it to schedule your jobs.
Let’s now see the actual bash script code first (file named backup.sh) :
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#!/bin/bash
date=`date -I`
mysqldump -h your_domain -u userDB -pPASSWORD DBname | gzip > /home/hthought/backup-$date.sql.gz
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
It first gets the current date and stores it to a variable. The idea is that we will use this to form the name of the gzip file depending on the date it was created. You can see that on “backup-$date.sql.gz”. Therefore, if you create a backup on 23th of November 20011, you get a filename like “backup-2011-11-23.sql.gz”.
The command that is used in order to create the actual dump is mysqldump. Using the -h switch, we can specify the hostname of the server where the mySQL server is located. Thus, if mySQL runs on another server, let’s say whatever.com, you will be using “-h whatever.com”. If the server runs on the actual machine executing the script, you do not need to specify this switch, because it defaults to localhost. The idea of using -h is that we would want to create a backup on a remote server so that if a problem comes up on the server where mysql is located, we will be sure that the backups are intact, since they are located in another server.
Running the Script Daily
As you see the script is pretty simple. The only thing that we need to do now is have it run automatically every day (or whenever you like to do that). As i mentioned before, we will be using cron to do that. We open up cron using “crontab -e” as the user we currently are (not root). Then, we add a line like :
0 0 * * * /bin/sh /home/hthought/backupPlace/backup.sh > /dev/null 2>&1
Simply, this script runs when the minute is 0 and the hour is 0 on whatever weekday of whatever month. Simply put, on each day there is only one time when the hour is 0 and the minute is 0 and that is the midnight. Therefore, this script runs once per day, every midnight. Moreover, since i do not really care about cron’s logs (and don’t really want to get useless mails), i redirect output to /dev/null/ , shamelessly trashing it.
For automatic MySql database backup using cron job, It would be quite important that you are at least a bit familiar with how cron works and how to use it to schedule your jobs.
Let’s now see the actual bash script code first (file named backup.sh) :
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#!/bin/bash
date=`date -I`
mysqldump -h your_domain -u userDB -pPASSWORD DBname | gzip > /home/hthought/backup-$date.sql.gz
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
It first gets the current date and stores it to a variable. The idea is that we will use this to form the name of the gzip file depending on the date it was created. You can see that on “backup-$date.sql.gz”. Therefore, if you create a backup on 23th of November 20011, you get a filename like “backup-2011-11-23.sql.gz”.
The command that is used in order to create the actual dump is mysqldump. Using the -h switch, we can specify the hostname of the server where the mySQL server is located. Thus, if mySQL runs on another server, let’s say whatever.com, you will be using “-h whatever.com”. If the server runs on the actual machine executing the script, you do not need to specify this switch, because it defaults to localhost. The idea of using -h is that we would want to create a backup on a remote server so that if a problem comes up on the server where mysql is located, we will be sure that the backups are intact, since they are located in another server.
Running the Script Daily
As you see the script is pretty simple. The only thing that we need to do now is have it run automatically every day (or whenever you like to do that). As i mentioned before, we will be using cron to do that. We open up cron using “crontab -e” as the user we currently are (not root). Then, we add a line like :
0 0 * * * /bin/sh /home/hthought/backupPlace/backup.sh > /dev/null 2>&1
Simply, this script runs when the minute is 0 and the hour is 0 on whatever weekday of whatever month. Simply put, on each day there is only one time when the hour is 0 and the minute is 0 and that is the midnight. Therefore, this script runs once per day, every midnight. Moreover, since i do not really care about cron’s logs (and don’t really want to get useless mails), i redirect output to /dev/null/ , shamelessly trashing it.
