本篇内容介绍了“怎么迁移mysql数据库中的表”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

作为写脚本的，这次的重点在于实现了类似于其他语言的logging模块的输出功能。感觉还是蛮有用的，简单直观。

输出log如下所示：

2017-03-3116:26:57---INFO---Youchoosethenameofthetablebelow:2017-03-3116:26:57---INFO---2016_06_24_record_base_log2017-03-3116:26:57---INFO---2016_06_16_record_base_log2017-03-3116:26:57---INFO---tableinsertstatements2016_06_24_record_base_logbackuping2017-03-3116:26:57---INFO---tablestruct2016_06_24_record_base_logbackuping2017-03-3116:26:57---INFO---tableinsertstatements2016_06_16_record_base_logbackuping2017-03-3116:26:57---INFO---tablestruct2016_06_16_record_base_logbackuping2017-03-3116:26:57---INFO---appendtheENGINE=InnoDB-->DATADIRECTORY\=\'\/data2\/db\/mysql\'/tostruct/2016_06_24_record_base_log.sql2017-03-3116:26:57---INFO---appendtheENGINE=InnoDB-->DATADIRECTORY\=\'\/data2\/db\/mysql\'/tostruct/2016_06_16_record_base_log.sql2017-03-3116:26:57---INFO---Thereisnobackupthetable2017-03-3116:26:57---INFO---Theimporttablestructure2017-03-3116:26:57---INFO---Insertdatatothetable2017-03-3116:26:57---INFO---Thereisnobackupthetable2017-03-3116:26:57---INFO---Theimporttablestructure2017-03-3116:26:57---INFO---Insertdatatothetable2017-03-3116:26:57---INFO---Successfullycompletedtheoperation!

shell脚本如下所示,此脚本在centos系统测试通过：

#!/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"#Settheechocolorgray='\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[!-dlogs]&&mkdirlogs#loggingfunctionfunctionlogging{if[!-z"$1"]&&[!-z"$2"];thenecho-e"${green}${1}---${2}${reset}"echo-e"${datetimes}---${1}---${2}">>$logfilefi}functionerror{if[$?-eq0];thenlogging"INFO""$1"elselogging"ERROR""${reset}${red}$1,haveanerroroccurred!"exit1fi}functionyesorno{echo-e"${yellow}$1${reset}"readvarcase"$var"in[yY][eE][sS])echo"YourinputisYES,Programtocontinue";;[nN][oO])echo"Yourinputisno.";exit0;;**)echo-e"${red}InputError!${reset}"exit0;;esac}echo-e"${yellow}ThisscriptisusedtomysqltableDATADIRECTORYandINDEXDIRECTORYsetto/data2/db/mysqlandthemigrationofdatato/data2/db/mysqldirectory.${reset}"yesorno"Doyouwanttocontinue,yesorno?"logging"INFO""Youchoosethenameofthetablebelow:"#Confirmthetabletobyupdatefortabin`cat$tablist`;dologging"INFO""${tab}"doneyesorno"Theaboveisthetableyouchoose,youwanttocontinue?Yesorno?"#dumptabledataandstructto$backupdirand$structdirfortabin`cat$tablist`;do$mysqlpath/mysqldump-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport--no-create-info$mysqldb$tab>${backupdir}/${tab}.sqlerror"tableinsertstatements$tabbackuping"$mysqlpath/mysqldump-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport--no-data$mysqldb$tab>${structdir}/${tab}.sqlerror"tablestruct$tabbackuping"done#appendtheDATADIRECTOYandDATADIRINDEXtotablestruct.fortabin`cat$tablist`;doifgrep'ENGINE=InnoDB'${structdir}/${tab}.sql;thensed-i"s/ENGINE=InnoDB/&DATADIRECTORY\=\'\/data2\/db\/mysql\'INDEXDIRECTORY\=\'\/data2\/db\/mysql\'/"${structdir}/${tab}.sqlerror"appendtheENGINE=InnoDB-->DATADIRECTORY\=\'\/data2\/db\/mysql\'/to${structdir}/${tab}.sql"elifgrep'ENGINE=MyISAM'${structdir}/${tab}.sql;thensed-i"s/ENGINE=MyISAM/&DATADIRECTORY\=\'\/data2\/db\/mysql\'INDEXDIRECTORY\=\'\/data2\/db\/mysql\'/"${structdir}/${tab}.sqlerror"appendtheENGINE=InnoDB-->DATADIRECTORY\=\'\/data2\/db\/mysql\'/to${structdir}/${tab}.sql"elselogging"ERROR,Tablestructureisnotfoundinthematchengine."exit1fidone#dropolddatabasefortabin`cat$tablist`;doif[-f${backupdir}/${tab}.sql]&&[-f${structdir}/${tab}.sql];then$mysqlpath/mysql-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport$mysqldb-e"droptable${tab};"error"Thereisnobackupthetable"fi#importtablestructtodbif[-f${structdir}/${tab}.sql];then$mysqlpath/mysql-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport$mysqldb<${structdir}/${tab}.sqlerror"Theimporttablestructure"fi#importtabledatatodbif[-f${backupdir}/${tab}.sql];then$mysqlpath/mysql-u$mysqluser-p$mysqlpass-h$mysqlhost-P$mysqlport$mysqldb<${backupdir}/${tab}.sqlerror"Insertdatatothetable"fidonelogging"INFO""Successfullycompletedtheoperation!"

ltab.txt中存储你要想迁移的表名，如下所示：

[root@SERVER_DB]catltab.txt2016_06_24_record_base_log2016_06_16_record_base_log

最后，记得在screen下执行脚本，即使终端断开，也不用担心，脚本终止执行。如果遇到错误，脚本会立即终止执行，此时需要手动来处理。

“怎么迁移mysql数据库中的表”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！