Mysql Dump multipurpose script.

12 / Jan / 2011 by Hitesh Bhatia 0 comments

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.

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

_________________________________

Hitesh Bhatia

Mail,LinkedIn,Facebook,Twitter

_________________________________

FOUND THIS USEFUL? SHARE IT

Leave a comment -