共计 4702 个字符，预计需要花费 12 分钟才能阅读完成。
本篇内容介绍了“怎么迁移 mysql 数据库中的表”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
作为写脚本的，这次的重点在于实现了类似于其他语言的 logging 模块的输出功能。感觉还是蛮有用的，简单直观。
输出 log 如下所示：
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_log
2017-03-31 16:26:57 --- INFO --- 2016_06_16_record_base_log
2017-03-31 16:26:57 --- INFO --- table insert statements 2016_06_24_record_base_log backuping
2017-03-31 16:26:57 --- INFO --- table struct 2016_06_24_record_base_log backuping
2017-03-31 16:26:57 --- INFO --- table insert statements 2016_06_16_record_base_log backuping
2017-03-31 16:26:57 --- INFO --- table struct 2016_06_16_record_base_log backuping
2017-03-31 16:26:57 --- INFO --- append the ENGINE=InnoDB -- DATA DIRECTORY\=\ \/data2\/db\/mysql\ / to struct/2016_06_24_record_base_log.sql
2017-03-31 16:26:57 --- INFO --- append the ENGINE=InnoDB -- DATA DIRECTORY\=\ \/data2\/db\/mysql\ / to struct/2016_06_16_record_base_log.sql
2017-03-31 16:26:57 --- INFO --- There is no back up the table
2017-03-31 16:26:57 --- INFO --- The import table structure
2017-03-31 16:26:57 --- INFO --- Insert data to the table
2017-03-31 16:26:57 --- INFO --- There is no back up the table
2017-03-31 16:26:57 --- INFO --- The import table structure
2017-03-31 16:26:57 --- INFO --- Insert data to the table
2017-03-31 16:26:57 --- INFO --- Successfully completed the operation !shell 脚本如下所示, 此脚本在 centos 系统测试通过：
#!/bin/bash
mysqluser= root
mysqlpass= dbpassword
mysqlhost= 127.0.0.1
mysqldb= dbname
mysqlpath= /usr/local/mysql/bin
mysqlport=3306
datetimes=`date +%Y-%m-%d %H:%M:%S `
datetimes2=`date +%Y%m%d%H%M `
datetimes3=`date +%Y%m%d%H%M%S `
[ ! -d $backupdir ] mkdir -p $backupdir
[ ! -d $structdir ] mkdir $structdir
[ ! -d logs ] mkdir logs
function logging { if [ ! -z $1 ] [ ! -z $2 ];then
echo -e ${green} ${1} --- ${2} ${reset}
echo -e ${datetimes} --- ${1} --- ${2} $logfile
fi
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;;
**)
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 update
for tab in `cat $tablist`;do
logging INFO ${tab}
yesorno The above is the table you choose, you want to continue? Yes or no?
# dump table data and struct to $backupdir and $structdir
for 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
# 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 s/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 s/ENGINE=MyISAM/ 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
# 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
# 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
# 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
logging INFO Successfully completed the operation !ltab.txt 中存储你要想迁移的表名，如下所示：
[root@SERVER_DB] cat ltab.txt
2016_06_24_record_base_log
2016_06_16_record_base_log最后，记得在 screen 下执行脚本，即使终端断开，也不用担心，脚本终止执行。如果遇到错误，脚本会立即终止执行，此时需要手动来处理。
“怎么迁移 mysql 数据库中的表”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
