MySQL如何迁移数据

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

我们提供的服务有：网站设计制作、做网站、微信公众号开发、网站优化、网站认证、乐昌ssl等。为上1000家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的乐昌网站制作公司

一. 测试用例/过程

目前开发商上云（外部MySQL迁移到CDB）提供多种方案，其中开发商的MySQL实例有外网IP的可以直接使用腾讯云数据库迁移工具完成迁移（其他的迁移方法参见链接本次迁移任务中该开发商的所有MySQL实例均有外网代理IP供使用，故直接选用迁移工具完成数据导入。

迁移工具的基本原理：通过待迁移实例提供的高权限帐号获取源实例基本的MySQL实例配置，并同步到目标CDB实例；通过mysqldump直接将源实例导出传输到CDB实例后导入；源数据库实例和目标CDB建立主从关系同步新数据。其中CDB实例与源IDC之间通过NAT方式以一台带外网的服务器为中转发起通信。

1. 迁移工具基本功能

在腾讯云数据库控制台页面根据引导建立迁移任务；在后台管理页面观察迁移任务后台日志等。

任务开始运行后检测代理机器流量变化，CDB的写入等数据展示

知识点：如何为测试数据库产生较大的数据量。这里推荐一个工具mysql_gen_data。产生测试数据并导入到MySQL的过程如下：

#!/bin/bash
./mysql_gen_data -f "%1n,%100s,%100s,%100s,%100s,%100s" \
 -n 10000000 >random.dat
mysql –uroot –p*********** –e “create database cdbtest;use cdbtest; \
CREATE TABLE cdbtest_tb \
(c1 varchar(100),c2 varchar(100),c3 varchar(100), \
c4 varchar(100),c5 varchar(100),c6 varchar(100)) \
ENGINE=InnoDB DEFAULT CHARSET=utf8;”
for i in {1..10}; do
    echo "$(date '+%T') round $i start"
    echo "prepare data..."
    sed -i "s/^/$i/" random.dat
    echo "insert data..."
    mysql -uroot –p******** cdbtest -e "LOAD DATA local INFILE '/data/random.dat' into table cdbtest_tb fields terminated by',';"
    echo "$(date '+%T') round $i end"
done

后台与腾讯云管理台查看本次测试任务，迁移成功完成。

2. 主从以及从机和CDB建立主从的同步

由于本次迁移的开发商将使用他们自建IDC的从机向CDB迁移数据，简单关系如下图，之前没有使用迁移工具进行过类似操作，故进行本次测试。

知识点：如何配置MySQL的主从关系。测试的MySQL主从的配置如下：（主MySQL）

server_id = 98
log_bin = binlog
binlog_format = ROW
innodb_stats_on_metadata = off

后台与腾讯云管理台查看本次测试任务，迁移成功完成。

3. 多实例+较大binlog并发同步

开发商在经过相关测试后，一期计划15个实例并发迁移到CDB，每天总共产生约100G的binlog。由于之前迁移工具没有大并发使用，且单日有较大数据更新，故提前测试用户场景。测试的基本架构如下图：在一个服务器上开启15个MySQL实例映射到不同端口，15个MySQL实例同时和15个CDB实例建立主从，并发起迁移任务。

知识点：如何在一台服务器上创建多个MySQL实例？这里使用的MySQL自带的mysqld_multi工具，其实这只是一个perl脚本，开启多实例配置如下（/etc/my.conf）可以视内存大小，开多个mysqld的配置项：

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root 
password = ******

[mysqld1]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysqld.pid
server_id = 11 
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off

symbolic-links=0
user = root
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld2]
port = 3312
datadir=/data/mysql12
socket=/data/mysql12/mysql.sock
pid-file=/data/mysql12/mysqld.pid
server_id = 12
log_bin = binlog
binlog_format = ROW
expire_logs_days=1
innodb_stats_on_metadata = off
user = root
symbolic-links=0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3]
........
[mysqld4]
.......

然后使用mysqld_multi start 1-4启动配置项里面的对应数量实例即可。启动多个MySQL实例如图：

通过定时update对应数据库实例的数据，产生较大量的binlog，单次update产生700Mbinlog，每2小时执行一次，每天产生7001215=126G.简单代码如下：

#!/bin/sh

SET_STRING=`date +"%s"`
LOG_NAME="/data/log/update.log"
NOW_STRING=`date +"[%Y-%m-%d %H:%M:%S]"`

for i in {12..26}

do
BEGIN_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
echo ${BEGIN_TIME}" Update data in this time is:"$SET_STRING >> $LOG_NAME
echo ${BEGIN_TIME}" Update database"${i} "start..." >> $LOG_NAME
mysql -uroot migrate${i} -S /data/mysql${i}/mysql.sock -e "update tb set data1="${SET_STRING}""
END_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
echo ${END_TIME}" Update database"${i} "end..." >> $LOG_NAME
done

使用数据库迁移工具建立15个迁移任务，控制台和后台检查均迁移成功：

同时为了检验大量binlog情况下数据完整性，写了简单脚本定时检查数据是否有更新，脚本如下：（这里经过测试发现可以通过广州跳板机直接连接CDB实例的masterIP，故直接在广州跳板机脚本拉取IDC更新数据，同时对比CDB实例数据，写入日志）

#!/bin/sh

DATA_CORRECT=$1
NOW_TIME=`date +"[%Y-%m-%d %H:%M:%S]"`
cat my.file | while read line
do

IP=`echo $line | awk -F " " '{print $1}'`
PORT=`echo $line | awk -F " " '{print $2}'`
DATABASE=`echo $line | awk -F " " '{print $3}'`
DATA_INBASE=`mysql -uroot -P${PORT} -h${IP} -p123456cdb ${DATABASE} -e "select data1 from tb limit 1\G" | grep data1 | awk -F " " '{print $2}'`

echo ${NOW_TIME}"[INFO]Data you want to update to ${DATABASE} is:"$DATA_CORRECT
echo ${NOW_TIME}"[INFO]Data from Database "$DATABASE" is:"$DATA_INBASE

if [ $DATA_INBASE -eq $DATA_CORRECT ]
then
echo ${NOW_TIME}"[SUCCESS]"$DATABASE" update succesfully!"
else
echo ${NOW_TIME}"[ERROR]"$DATABASE" update ERROR!" 
fi

done

通过校验日志可以看到，数据更新均成功完成。

二. 开发商迁移测试数据记录

以上我方内部测试完成后，开发商自行进行了3次迁移，相关数据如下：

某次迁移的带宽表现。
由于开发商出口带宽只有约500Mbps，经过测试发现迁移瓶颈主要出现在带宽限制上。实际并发时带宽大小待二期迁移时确认。

三.遇到的问题

1. 首次创建主从无法连接源数据库
   现象：如图所示，每次建任务后总提示源数据库无法连接
   Error:Can’t connect to MySQL server on 10.*.*.*
   分析解决：由于迁移工具本质是CDB代理经过NAT通过外网和IDCMySQL实例相连，CDB的代理系统时间和NAT外网机器有差异，同时IDC开启连接重用，导致建立连接时前后时间不一致，系统认为为异常包，丢弃，连接失败。直接修改IDC服务器的内核参数，即net.ipv4.tcp_timestamps = 0和net.ipv4.tcp_tw_recycle = 0即可

2. 跨版本迁移的存储过程迁移失败
   现象：如图所示，开发商在迁移过程中出现proc表无法迁移的现象
   ERROR:Can’t load from mysql.proc. The table is probably corrupted
   解决：经CDB开发同事确认跨版本迁移的proc表因字段定义不同存在异常，发布版本跳过proc表解决。

3. 迁移测试中创建新数据库导致binlog导入失败
   现象：迁移任务出现错误，无法迁移存储过程，binlog追加失败
   errno:1049:Error ‘Unknown database ‘xxxx’on query.
   解决：原因为本次迁移选定了只迁移某个数据库，迁移过程中新建了一个数据库，并开启binlog，导致CDB拉到的binlog有新数据库信息，和迁移数据库不匹配。解决方法为迁移过程不要出现DDL操作。

“MySQL如何迁移数据”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！