Bash: Database backup

2012-06-29 Bash

In this article we won’t be talking about the idea of backup in general aspect. We’ll just focus on how to improve backup process (of anything) using cron and several commands in bash.

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.

mysqldump

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.

#!/bin/bash

Next, we have to change directory to our working destination, where everything will take place:

cd /var/www/sql/

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

You’ll find out more about mysqldump’s parameters (force, routines) in official documentation of MySQL. Now we have to pack already created dump (testdb.sql) to compressed file called testdb.tar.bz2.

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:

12_34_56

and not like:

12:34:56

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.

rm my_database.sql

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:

make_sql_dump

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:

crontab -e

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:

:wq

You should see the following confirmation:

crontab: installing new crontab

To check if everything is OK type:

crontab -l

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.

Do NOT follow this link or you will be banned from the site!