A little bit more than MySQL dump

As MySQL is widely used, it is often database administrators have to backup database to save potential hazards. I believe "mysqldump" must be one of the most popular tool to backup MySQL database. Some other tech geeks may prefer GUI tool like phpMyAdmin. But whatever tool you use, you will realise that the output file is in plain text. In another word, it is wasting space, why can we not just do a bit more lazy click to make it compressed? Here is what I do under Linux.

#mysqldump -u dillon --password=xxxx database | gzip -c > /mnt/backup/db_`date +%Y%m%d%H%M`.sql.gz

It dumps database to a compressed file with date and time in the file name.

If you want to make it as a script or put it in corn job. Don't forget % need to be escaped.

#!/bin/bash
mysqldump -u dillon --password=xxxx database | gzip -c > /mnt/backup/db_`date +\%Y\%m\%d\%H\%M`.sql.gz

SimPLE BackuP script under Linux

Data backup is something that I do not want to bother too much but I will worry if I do not do it. There are many ways to backup your files under Linux, however, I personally prefer something you can find in everywhere and free. Because people like me will have a lot of servers to look after.

In here, I will give you an example script to backup data under Linux by "tar". You can find it in almost all Linux and Unix distributions even in embedded systems.

First of all, what I want to achieve is obviously "BACKUP". It is not only a copy of existing data, but also easy to manage. So I will give the "BACKUP" a nice name which tells me what and when.

Next, let us make a few assumptions.

1. DEST_PATH: This is the place where the backup data will go in to.
2. SRC_PATH: It is the source place storing data you want to make a backup.
3. FOLDERS: A list of directories you are going to backup.
4. TIME_STAMP: It is self explain, this will be part of the backup file name.

Choose your favourite editor such as "vi" and "nano", copy and paste the following code to  a new file, let us call it "script.sh".

#!/bin/bash

DEST_PATH=/mnt/backup

SRC_PATH=/srv/share

FOLDERS="

company

finance

marketing

"

for ITEM in $FOLDERS; do

        TIME_STAMP=`date +\%Y\%m\%d\%H\%M`

        tar cjvpPf $DEST_PATH/$ITEM.$TIME_STAMP.tar.bz2 $SRC_PATH/$ITEM > $DEST_PATH/$ITEM.$TIME_STAMP.idx 2> $DEST_PATH/$ITEM.$TIME_STAMP.log

done

Change DEST_PATH, SRC_PATH and FOLDERS to your needs. In this case, I have "company", "finance" and "marketing" under directory "/srv/share" , backup file will be name.datetime.tar.bz2 for example company.201009301544.tar.bz2, and an index file ending with idx, a log file tells you errors during backup.
The tar parameters used in this example are "cjvpPf".

c - create file.
j - use bzip to compress the file to save space.
v - verbose mode, it produces idx contents.
p - lower case p, preserve source files privileges.
P - upper case P, preserve source absolute path.
f - the backup file you are going to create.


Then make it runnable by execute command


#chmod u+x script.sh 


Make sure you have sufficient privilege to read the source files. I normally run it as root so I will have no "access deny" problem.


You can add it to your system cron job to do the over night backup. However, what I have to mention is that it is good to save your backup on an external device like USB External HDD. You can then take it away to somewhere secure, that is so call off-site backup. If you save the backup in the same server, be aware of one day the backup would eat up all your space.