Mysql Dump multipurpose script.

Taking MYSQL dump is usual requirement in my project . And Since some of the tables in project are huge so often I have to take dumps of selected tables and often have to ignore some tables.

since we had to do it frequently in our project so I created a script  which does following for me.

1. Takes database dump
2. Takes dump of specified tables.
3. Takes dump of all the tables other than specified.

Inside the script I have added variables like username and password which are required by Mysql. (So if you are using this ,you will need to change them.)

[shell]
USER="username"
PASSWORD="password"
DATABASE="$1"
DUMPS="Dumps"
DUMPDIR="$DATABASE$DUMPS"
FILENAME="latest.sql"
cd;mkdir "$DUMPDIR"

if [ $# -eq 0 ]; then
echo "Usage : <databasename> <tablename*> # if table name(s) is specified only that/those table will be dumped"
echo "Altername Usage : <databasename> -k <table(s)dToBeIgnored>"
exit
fi

if [ $# -eq 1 ];then
echo "Dumping $DATABASE to $DUMPDIR/$FILENAME"
mysqldump –user="$USER" –password="$PASSWORD" "$DATABASE" > ~/"$DUMPDIR"/"$FILENAME"
fi

COMMAND="mysqldump –user=$USER –password=$PASSWORD $DATABASE"
if [ $# -gt 1 -a $2 != "-i" ];
then
for i in $*
do
if [ $i != "$DATABASE" -a $i != "-i" ];then
$COMMAND $i > ~/"$DUMPDIR"/"$i.sql";
fi
done
fi

if [ $# -gt 2 -a $2 == "-i" ];
then
COMMAND2=" "
for i in $*
do
if [ $i != "$DATABASE" -a $i != "-i" ];then
COMMAND2="$COMMAND2 –ignore-table=$DATABASE.$i"
fi
done
$COMMAND $COMMAND2 > ~/"$DUMPDIR"/"$DATABASE"-"Ignored.sql"
fi
[/shell]

Now what this can be used in three ways –
(Note – I have named this script sqlDump and all of following examples use this name )

1. ./sqlDump.sh databasename
   This will take a complete dump of all my tables in specified database and store it in file named latest.sql
2. ./sqlDump.sh databasename table1 table2 … tablenN
   This will take dump of all tables specified.And will store them in file named – table1.sql , table2.sql … tableN.sql
3. ./sqlDump.sh databasename -i table1 table2 .. tableN
   This will take dump off all the tables in database ignoring specified tables. And will store it in file named <databaseName>-Ignored.sql.

If file is executed without any arguments it will just describe its Usage
Note – All of these tables will be stored in directory named <databasename>Dumps in home directory.

This has helped me many times and has saved my time too . Hope this helps you too

_________________________________

Hitesh Bhatia

Mail,LinkedIn,Facebook,Twitter

_________________________________