For purpose of this article we assume that our application is an intranet website, placed on a local server in most typical directory (e.g. /var/www). Application uses a single database which is stored on the same server as files from which application is built. The final dump of database will be made by this command.
It will be compressed to tar.bz2 format and stored into /var/www/sql directory. So let’s start. Content of our backup script should began with the shebang sign.
Next, we have to change directory to our working destination, where everything will take place:
Then (using mysqldump command) we’re dumping our database (called testdb) and name the output file testdb.sql.
mysqldump --force --routines -u root my_database > my_database.sql
tar -cjf my_database.tar.bz2 my_database.sql
Because the final effect that we want to achieve it to get these backups periodically – we have to give them unique names – the better when they’ll clearly point to specific archive. The easiest way to get that is to call compressed archives with date and time of their creation. Let’s create a variable called dat. This one will contain exact year, month, day, hour, minute and second. Combining it with sed command, we’ll replace all colons into underscores.
dat=`date '+%F_%T' | sed -e 's/:/_/g'`
Thanks to that, the output time will look like:
and not like:
Now let’s change testdb.tar.bz2 into unique one.
mv my_database.tar.bz2 my_database_$dat.tar.bz2
The final step that should be done is to delete the raw and uncompressed dump – we don’t need it anymore.
The whole content of bash script should look like:
#!/bin/bash cd /var/www/sql/ mysqldump --force --routines -u root my_database > my_database.sql tar -cjf my_database.tar.bz2 my_database.sql dat=`date '+%F_%T' | sed -e 's/:/_/g'` mv my_database.tar.bz2 my_database_$dat.tar.bz2 rm my_database.sql
Now save script (e.g. in root directory) and name it like:
Last thing is to add our newly created script into crontab. We want backup to be created everyday at 11 p.m. Open crontab using:
Then press “i” letter to enable insert mode and add the following line:
00 23 * * * /make_sql_dump
Press “Esc” to exit insert mode and save changes by typing:
You should see the following confirmation:
crontab: installing new crontab
To check if everything is OK type:
We should see list of all cron jobs (with newly added):
[email protected]:~# crontab -l 01 01 * * * php /var/www/script1.php >> /var/www/log/script1.log */1 * * * * php /var/www/script2.php >> /var/www/log/script2.log 00 23 * * * /make_sql_dump
From now on, in /var/www/sql directory, everyday at 11 p.m. we should see files called:
my_database_2012-06-27_23_00_00.tar.bz2 my_database_2012-06-28_23_00_00.tar.bz2 my_database_2012-06-29_23_00_00.tar.bz2
These files clearly point to backups created at 27th, 28th and 29th of June in 2012.