本篇内容介绍了“怎么迁移mysql数据库中的表”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
作为写脚本的,这次的重点在于实现了类似于其他语言的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"`
backupdir="backup"
structdir="struct"
logfile="logs/test_${datetimes3}.log"
tablist="ltab.txt"
# Set the echo color
gray='\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 function
function 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;;
**)
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}"
done
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"
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 "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
fi
done
# 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"
fi
done
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数据库中的表”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。