dump/load mysql-ib databases

# the final version ,by hfu

#!/bin/sh
 # dump or load the tables under the specified databases automatically.
 # only apply to infobright ,untest on mysql.
 # the following  leaves just for debugging :)

 #set -x

 unalias -a

 #source ~/.bash_profile

 USER=$1
 PWD=$2
 HOST=$3
 PORT=$4
 DB=$5
 LOCATION=$6
 MODE=$7
 # how much threads you want to run when dump/load tables

 threads=1

 function usage(){

cat <<!

Usage : sh $0 user pwd host port dbname path {load|dump}
database is the specified db name which you plan to load/dump data ,while path is the location where the export files store"
e.g sh $0 mysql pwd 10.1.1.1 3306 app /tmp dump (Warning: /tmp couldn't type as /tmp/)

!
      }

 if [ $# -ne 7 ] ;then

     echo "==================================================================================="
     echo "first of all, you must be authorized to access remote mysql servers ,or the exception will be raised when you try to run this script"
     usage
     exit 1
 fi

 my_cmd="/usr/bin/mysql-ib -u$USER -p$PWD -h$HOST -P$PORT"

 [ ! -d "$LOCATION/$DB" ] && sudo mkdir -p $LOCATION/$DB && sudo chown -R mysql:mysql $LOCATION/$DB || sudo  chown -R mysql:mysql $LOCATION

 function dump_tables () {

      # get the table lists belong to the database that you specified in advance.

     $my_cmd -e "show tables from $DB\\G" |awk -F : '/Tables/{print $2}' > $LOCATION/$DB/${DB}_table_lists

     # obtain the sql statement "create database $DB "

     $my_cmd -e "show create database $DB\\G" | awk -F "Create Database:" 'NR==3{v=$2}NR>3{v=v"\n"$0}END{print v";"}'  > $LOCATION/$DB/create_${DB}_and_tabs.sql

     # now create_${DB}_and_tabs.sql starts with the line "create database xxx;",but we should append "use $DB" so that this sql statement could run smoothly

     echo " USE $DB;" >> $LOCATION/$DB/create_${DB}_and_tabs.sql

      ############################ run multiple threads #################################

      i=0
      counter=0

      lines=$(awk 'END{print NR}' $LOCATION/$DB/${DB}_table_lists )

      # make sure that threads no. is smaller than  the no. of tables
      if [ $lines -lt $threads ] ;then  threads=$lines ;fi

      N=$(echo "$lines/$threads" |bc )

      for j in `cat $LOCATION/$DB/${DB}_table_lists ` ; do

            $my_cmd -A $DB -e "show create table $j\\G"  | awk -F : 'NR==3{v=$2}NR>3{v=v"\n"$0}END{print v";"}' >> $LOCATION/$DB/create_${DB}_and_tabs.sql

            $my_cmd -A $DB -e "show create table $j\\G"  | awk -F : 'NR==3{v=$2}NR>3{v=v"\n"$0}END{print v";"}' > $LOCATION/$DB/create_${j}.sql

           {
            # generate sql statement ,like "select id ,name,xxx from xxx fields terminate by xxx escaped by xxx;

            awk -F \` -v d=$DB 'NR==1{t=$2}NR>1&&!/ENGINE/{if(v)v=v",replace("$2",\047\\\\\047,\047\\\\\\\\\047)";else v="replace("$2",\047\\\\\047,\047\\\\\\\\\047)"}END{print "select "v " from " t  " in
to OUTFILE \047/tmp/"d"/"t "\047 fields terminated by \047;\047  escaped by \047\\\\\047" }' $LOCATION/$DB/create_${j}.sql > $LOCATION/$DB/source_${j}.sql

             $my_cmd -A $DB -e "source $LOCATION/$DB/source_${j}.sql";

            echo "export table $j .... done "
            } &

            #get the sql statement "create table"


           ((i++))

        if [ $counter -lt $N ] ;then

               if [ $i -ge $threads ] ; then
                  wait
                  i=0

                   ((counter++))
                   echo "       "
                   echo "================================$((lines-counter*threads)) tables left ==========================="
                   echo "       "
               fi

       else
           wait
       fi

       done

          }


 function load_tables () {

cat <<eof
------------------------------------------------------

  "now create the database and table structure "

-------------------------------------------------------
eof

      $my_cmd -e "DROP DATABASE IF EXISTS $DB"
      $my_cmd -e "source $LOCATION/$DB/create_${DB}_and_tabs.sql"

      echo " $DB and related tables rebuilt ....done "
      i=0
      counter=0

     lines=$(awk 'END{print NR}'  $LOCATION/${DB}/${DB}_table_lists )

     N=$(echo $lines/$threads |bc )

      for j in `cat $LOCATION/${DB}/${DB}_table_lists`; do
          echo "load ...  $j "
          { $my_cmd -A $DB -e "SET character_set_database=utf8;load data local infile '$LOCATION/$DB/$j' into table $j fields terminated by ';' escaped by '\\\\';"
            } &

           ((i++))

        if [ $counter -lt $N ] ;then

               if [ $i -ge $threads ] ; then
                  wait
                  i=0

                   ((counter++))
                   echo "       "
                   echo "================================$((lines-counter*threads)) tables left =============================="
                   echo "       "

               fi

       else

           wait
       fi

      done

          }


case $MODE in

   dump)
        dump_tables;
        echo "----------------------------------------------------------------------------------------"
        echo "now please move towards to $LOCATION, tar jcvf $DB.tar.bz2 $LOCATION/$DB,then sync to the objective hosts "
        echo " "
    ;;

   load)
cat <<!

Best practice : if your dumped package located at /tmp/$DB.tar.{gz|bz2} ,cd /tmp and umcompress it, then run the script,pay much attention to the path

in this case,it