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.
- Takes database dump
- Takes dump of specified tables.
- 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 )
- ./sqlDump.sh databasename
This will take a complete dump of all my tables in specified database and store it in file named latest.sql
- ./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
- ./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