Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to migrate tables in mysql database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

As a script writer, the focus of this time is to implement the output function of logging modules similar to other languages. It feels 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, which passed the centos system test, is as follows:

#! / 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" `date "+% Y%m%d%H%M" `datetimes3= `date "+% Y%m%d%H%M%S" `backupdir= "backup" struct "logfile=" logs/test_$ {datetimes3} .log "tablist=" ltab.txt "# Set the echo colorgray='\ 033 [30 \ 033 [31 countries\ 033\\ 033 [32 countries\% fellowship\ 033\\ 033]\ 033 [34 countries\ 1m trees pinkstones'\ 033 [35 countries 1m trees whitestones'\ 033 [37 033 [0m' [!-d $backupdir] & & mkdir-p $backupdir [!-d $structdir] & & mkdir $structdir [!-d logs] & & mkdir logs# logging functionfunction logging {if [!-z "$1"] & & [!-z "$2"] Then echo-e "${green} ${1}-- ${2} ${reset}" echo-e "${datetimes}-- ${1}-- ${2}" > $logfile fi} function error {if [$?-eq 0]; then logging "INFO"$1" else logging "ERROR"${reset} ${red} $1, 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 political; * *) 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, yesorno? "logging" INFO "You choose the name of the table below:" # Confirm the table to by updatefor tab in `cat $tablist`; do 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`; do $mysqlpath/mysqldump-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport-- no-create-info $mysqldb $tab > ${backupdir} / ${tab} .SQL error" table insert statements $tab backuping "$mysqlpath/mysqldump-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport-no-data $mysqldb $tab > ${structdir} / ${tab} .sql error" table struct $tab backuping "done# append the DATA DIRECTOY and DATA DIRINDEX to table struct.for tab in `cat $tablist` Do if grep 'ENGINE=InnoDB' ${structdir} / ${tab} .sql Then sed-I "sCharger engine InnoDB & DATA DIRECTORY\ =\ / data2\ / db\ / mysql\ 'INDEX DIRECTORY\ =\' / data2\ / db\ / mysql\'/" ${structdir} / ${tab} .SQL error "append the ENGINE=InnoDB-> DATA DIRECTORY\ =\ / data2\ / db\ / mysql\'/ to ${structdir} / ${tab} .sql" elif grep 'ENGINE=MyISAM' ${structdir} / ${tab} .sql Then sed-I "sbinder MyISAM error & DATA DIRECTORY\ =\'\ / data2\ / db\ / mysql\ 'INDEX DIRECTORY\ =\' / data2\ / db\ / mysql\'/" ${structdir} / ${tab} .SQL error "append the ENGINE=InnoDB-> DATA DIRECTORY\ =\\ / data2\ / db\ / mysql\'/ to ${structdir} / ${tab} .sql" else logging "ERROR Table structure is not found in the match engine. " Exit 1 fidone# drop old database for tab in `cat $tablist`; do if [- 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 db if [- f ${structdir} / ${tab} .SQL] Then $mysqlpath/mysql-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport $mysqldb < ${structdir} / ${tab} .SQL error "The import table structure" fi # import table data to db if [- f ${backupdir} / ${tab} .sql] Then $mysqlpath/mysql-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport $mysqldb < ${backupdir} / ${tab} .SQL error "Insert data to the table" fidonelogging "INFO"Successfully completed the operation!"

The table name you want to migrate is stored in ltab.txt, as follows:

[root@SERVER_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, don't worry, the script terminates execution. If an error is encountered, the script immediately terminates execution, which needs to be handled manually.

This is the end of "how to migrate tables in mysql database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.