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.)

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

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.

(more…)