How do you dump specific table(s)? Hi i have a shell script for doing a selected databases backup.
Now my requirement is i wanted to take a particular tables in a database to be backed up.
Each database backup need to be created in seperate file. The script is 




#!/bin/bash


MyUSER="user"       # USERNAME
MyPASS="pw"         # PASSWORD
MyHOST="localhost"  # Hostname

# If cleanup is set to "1", backups older than $OLDERTHAN days will be deleted!
CLEANUP=1
OLDERTHAN=60

# Backup Dest directory
DEST="/backup/mysql"

# Directory, where a copy of the "latest" dumps will be stored
LATEST=$DEST/latest

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y-%m-%d")"

# DO NOT BACKUP these databases (separate database names by space)
EXCLUDE=""


### Libraries ###
MYSQL="$(which mysql)"
if [ -z "$MYSQL" ]; then
    echo "Error: MYSQL not found"
    exit 1
fi
MYSQLDUMP="$(which mysqldump)"
if [ -z "$MYSQLDUMP" ]; then
    echo "Error: MYSQLDUMP not found"
    exit 1
fi
CHOWN="$(which chown)"
if [ -z "$CHOWN" ]; then
    echo "Error: CHOWN not found"
    exit 1
fi
CHMOD="$(which chmod)"
if [ -z "$CHMOD" ]; then
    echo "Error: CHMOD not found"
    exit 1
fi
GZIP="$(which gzip)"
if [ -z "$GZIP" ]; then
    echo "Error: GZIP not found"
    exit 1
fi
CP="$(which cp)"
if [ -z "$CP" ]; then
    echo "Error: CP not found"
    exit 1
fi

[ ! -d $DEST ] &amp;&amp; mkdir -p $DEST || :
[ ! -d $LATEST ] &amp;&amp; mkdir -p $LATEST || :

# Only root can access it!
#$CHOWN 0.0 -R $DEST
#$CHMOD 0600 $DEST

# Get a list of all databases available
DBS="$($MYSQL -u$MyUSER -p$MyPASS -h $MyHOST -Bse 'show databases')"

# start dumping databases
for db in $DBS
do
    skipdb=-1
    if [ "$EXCLUDE" != "" ];
    then
        for i in $EXCLUDE
        do
            [ "$db" == "$i" ] &amp;&amp; skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then


            FILE="$DEST/$db.$HOST.$NOW.gz"
            # do all in one job in pipe,
            # connect to mysql using mysqldump for select mysql database
            # and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u$MyUSER -p$MyPASS -h $MyHOST --single-transaction $db $j  | $GZIP -9 &gt; $FILE
        $CP $FILE "$LATEST/$db.$HOST.latest.gz"
    fi
done


# Remove files older than x days if cleanup is activated
if [ $CLEANUP == 1 ]; then
    find $DEST/ -name "*.gz" -type f -mtime +$OLDERTHAN -delete
fi
I will first answer your question, what you ask, but that is probably not what you want.

If you want to dump a single table using mysqldump, you can just put its name after the database name. So, if you execute:

mysqldump [connection parameters here] a_database a_table &gt; $DEST/a_table.sql


It will backup only the a_table table from the database a_database. If you write:

mysqldump [connection parameters here] a_database a_table another_table yet_another_table \
&gt; $DEST/a_database.sql


It will dump a_database.a_table, a_database.another_table and a_database.yet_another_table.

I mentioned that this (exporting each table separatelly) is probably not what you want because if you execute mysqldump several times and the server is not in read_only mode, you will end up with several tables in an inconsistent state (the backup was done at different times). In many cases, that is undesirable because if you have to restore more than one table they could end up with incompatible data. Using several runs of mysqldump in parallel can be interesting, as it can speed up the backup to some point, but you must makes sure that you lock the tables for a consistent backup.

Another approach is to use mysqldump --tab: This will let you export each table on a couple of separate files (a .sql and a .txt), which you can later import with mysqlimport (an it will be faster than a .sql file). You can still select specific tables in the same way:

mysqldump [connection parameters here] --tab=$DEST \
a_database a_table another_table yet_another_table

$ ls $DEST

a_table.sql
a_table.txt
another_table.sql
another_table.txt
yet_another_table.sql
yet_another_table.txt


The idea is that you should avoid exporting the tables at different times, as you will lose the consistency that mysqldump --lock-tables (default) or --single-transaction will give you.