Shell scripts migrate tables in the MySQL database

As a script, the focus of this time is to implement the output function of logging modules similar to other languages. The feeling is quite useful, simple and intuitive.

The output log is as follows:

2017-03-31 16:26:57 --- INFO --- you choose the name of the  table below:2017-03-31 16:26:57 --- INFO --- 2016_06_24_record_base_ log2017-03-31 16:26:57 --- INFO --- 2016_06_16_record_base_log2017-03-31  16:26:57 --- INFO --- table insert statements 2016_06_24_record_base_log  backuping2017-03-31 16:26:57 --- INFO --- table struct 2016_06_24_ record_base_log backuping2017-03-31 16:26:57 --- INFO --- table insert  statements 2016_06_16_record_base_log backuping2017-03-31 16:26:57 --- info  --- table struct 2016_06_16_record_base_log backuping2017-03-31 16:26:57 - -- info --- append the engine=innodb --> data directory\=\ ' \/data2\ /db\/mysql\ '/ to struct/2016_06_24_record_base_log.sql2017-03-31 16:26:57 --- INFO --- append the engine =innodb --> data directory\=\ ' \/data2\/db\/mysql\ '/ to struct/2016_06_16_record_ base_log.sql2017-03-31 16:26:57 --- INFO --- there is no back up  the table2017-03-31 16:26:57 --- INFO --- The import table  structure2017-03-31 16:26:57 --- INFO --- Insert data to the  table2017-03-31 16:26:57 --- INFO --- There is no back up  the table2017-03-31 16:26:57 --- INFO --- The import table  structure2017-03-31 16:26:57 --- INFO --- Insert data to the  table2017-03-31 16:26:57 --- INFO --- Successfully completed the  operation !

The

 shell script, as shown below, is tested in the CentOS system by:

#!/bin/bashmysqluser= ' root ' mysqlpass= ' Dbpassword ' mysqlhost= ' 127.0.0.1 ' mysqldb= ' dbname ' mysqlpath= '/usr/local/ Mysql/bin ' mysqlport=3306datetimes= ' date  "+%y-%m-%d %h:%m:%s" ' datetimes2= ' date  "+%Y%m%d%H%M" ' Datetimes3= ' date  "+%y%m%d%h%m%s" ' backupdir= "Backup" structdir= "struct" logfile= "Logs/test_${datetimes3}.log "Tablist=" Ltab.txt "# set the echo colorgray= ' \033[30;1m ' red= ' \033[31;1m ' green= ' \033[32;1m ' yellow= ' \033[33;1m ' blue= ' \033[34;1m ' pink= ' \033[35;1m "white= ' \033[37;1m ' reset= ' \033[0m ' [ ! -d $ backupdir ] && mkdir -p  $backupdir [ ! -d  $structdir  ]  && mkdir  $structdir [ ! -d logs ] && mkdir  Logs# logging functionfunction logging {    if [ ! -z   "$"  ] && [ ! -z  "$"  ];then         echo -e  "${green} ${1} --- ${2} ${reset}"          echo -e  "${datetimes} --- ${1} --- ${2}"  >>  $logfile      fi}function error {    if [ $? -eq 0  ];then        logging  "INFO"   "$"      else        logging  "ERROR"   "${RESET}${RED}&NBSP;$1&NBSP; have an error occurred! "         exit 1    fi}function yesorno  {    echo -e  "${yellow} $1 ${reset}"     read  var    case  "$var"  in    [yY][eE][sS] )           echo  "Your input is yes,prograM to continue " ;;     [nN][oO] )          echo  "Your  input is no. ";         exit 0;;     **)         echo -e  "${red}  Input error! ${reset} "         exit 0     ;;     esac}echo -e  "${yellow} this script is used to  mysql table data directory and index directory set to/data2/db/ mysql and the migration of data to /data2/db/mysql directory. ${ reset}  " yesorno " Do you want to continue, yes or no? " logging  "INFO"   "You choose the name of the table below:" #  Confirm the table to by updatefor tab in  ' cat  $tablist ';d o         logging  "INFO"   "${tab}" doneyesorno  "The above is  the table you choose, you want to continue? yes or no? " # dump table data and struct to  $backupdir  and  $structdirfor  tab in  ' cat  $tablist ';d o$mysqlpath/mysqldump -u$mysqluser -p$mysqlpass -h$ mysqlhost -p$mysqlport --no-create-info  $mysqldb   $tab  > ${backupdir}/${tab}. sqlerror  "table insert statements  $tab  backuping" $mysqlpath/mysqldump -u$ mysqluser -p$mysqlpass -h$mysqlhost -p$mysqlport --no-data  $mysqldb   $tab   > ${structdir}/${tab}.sqlerror  "table struct  $tab  backuping" Done# append  the data directoy and data dirindex to table struct.for tab in  ' cat $ Tablist ';d oif grep  ' engine=innodb '  ${structdir}/${tab}.sql;thensed -i  ' s/engine= Innodb/& data directory\=\ ' \/data2\/db\/mysql\ '  index directory\=\ ' \/data2\/db\/mysql\ '/'  ${structdir}/${tab}.sqlerror  ' append the engine=innodb --> data  Directory\=\ ' \/data2\/db\/mysql\ '/ to ${structdir}/${tab}.sql "elif grep  ' ENGINE=MyISAM '  ${structdir}/${tab}.sql;thensed -i  "S/engine=myisam/& data directory\=\ ' \/data2\ /db\/mysql\ '  index directory\=\ ' \/data2\/db\/mysql\ '/'  ${structdir}/${tab}.sqlerror  ' Append the engine=innodb --> data directory\=\ ' \/data2\/db\/mysql\ '/ to  ${structdir}/${tab}.sql "elselogging " error,table structure is not found in  the match engine ." exit 1fidone# drop old database for tab in  ' cat  $tablist ';d OIF  [ -f ${backupdir}/${tab}.sql ] && [ -f ${structdir}/${tab}. sql ];then$mysqlpath/mysql -u$mysqluser -p$mysqlpass -h$mysqlhost -p$mysqlport $ mysqldb -e  "drop table ${tab};" error  "There is no back up the table" fi# import table struct  to dbif [ -f ${structdir}/${tab}.sql ];then$mysqlpath/mysql -u$mysqluser  -p$mysqlpass -h$mysqlhost -P$mysqlport  $mysqldb  < ${structdir}/${tab}. sqlerror  "The import table structure" fi# import table data to  dbif [ -f ${backupdir}/${tab}.sql ];then$mysqlpath/mysql -u$mysqluser -p$ mysqlpass -h$mysqlhost -p$mysqlport  $mysqldb  < ${backupdir}/${tab}.sqlerror  "insert data to the table" fidonelogging  "INFO"   "Successfully  completed the operation ! "

Ltab.txt stores the name of the table you want to migrate as follows:

[Email protected]_db] Cat Ltab.txt2016_06_24_record_base_log2016_06_16_record_base_log

Finally, remember to execute the script under screen, even if the terminal is disconnected, do not worry, the script terminates execution. If an error is encountered, the script terminates execution immediately and needs to be handled manually.

This article is from the "-= lake-side Bamboo =-" blog, please be sure to keep this source http://bronte.blog.51cto.com/2418552/1912290

Shell scripts migrate tables in the MySQL database