mysql 数据迁移失败原因总结

柔光的暖阳◎ 2022-08-08 13:41 9阅读 0赞

今年4月15号我们进行了一次数据库迁移，迁移的步聚如下：

<table> 
 <tbody> 
 <tr> 
 <td>修改mysql配置，开启binlog 并设置保留日志15天</td> 
 <td>直接修改配置文件/usr/my.cnf&nbsp; binlog_cache_size=4M log-bin=master-bin.log log-bin-index=master-bin.index max_binlog_size=1G max_binlog_cache_size=2G expire_logs_days=15 binlog_format=mixed server-id=1 binlog-do-db=wewe log-short-format binlog-row-image=minimal master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1</td> 
 </tr> 
 <tr> 
 <td>关闭所有数据库事件</td> 
 </tr> 
 <tr> 
 <td>停止所有应用服务</td> 
 <td>209wewe服务器 service tomcatwewe1 stop service tomcatwewe2 stop service tomcatwewe3 stop service tomcatwewebatch stop service tomcatwewereport stop service tomcatweweapp3 stop service tomcatweweapp stop service httpd stop 121商城服务器 service tomcatweixin restart service tomcatqy stop service tomcatoa stop 203代理服务器 tomcatwmall tomcat6 27.154.54.202商城前端 tomcatwewecity tomcatwewemall Apache2.2</td> 
 </tr> 
 <tr> 
 <td>重启数据库,记录首日志文件</td> 
 <td>service mysql stop service mysql start</td> 
 </tr> 
 <tr> 
 <td>开启不含报表中心的全备份 观察并确保启动备份</td> 
 </tr> 
 <tr> 
 <td>检查备份情况并启动所有服务</td> 
 </tr> 
 <tr> 
 <td>检查备份中主要业务数据的数据量是否完全一致 与还原后需一致</td> 
 <td>select count(1) from smm_sale_invoice where CREATE_TIME &lt;='2015-04-09'; select count(1) from smm_sale_invoice_dtl where CREATE_TIME &lt;='2015-04-09'; select count(1) from pss_invoice where CREATE_TIME &lt;='2015-04-09'; select count(1) from pss_invoice_dtl where CREATE_TIME &lt;='2015-04-09'; select count(1) from imm_stock_total where CREATE_TIME &lt;='2015-04-09'; select count(1) from imm_stock_detail where CREATE_TIME &lt;='2015-04-09';</td> 
 </tr> 
 <tr> 
 <td>上传（FTP）备份至阿里云</td> 
 <td>ftp://120.55.98.156/db/</td> 
 </tr> 
 <tr> 
 <td>还原备份至阿里云数据库(去掉事务)</td> 
 <td>navicate</td> 
 </tr> 
 <tr> 
 <td>还原后数据核对</td> 
 </tr> 
 <tr> 
 <td>阿里云RDS进行物理备份</td> 
 </tr> 
 <tr> 
 <td>关闭报表中心抽数，并开启报表中心备份</td> 
 </tr> 
 <tr> 
 <td>手动增量同步binlog数据</td> 
 <td>mysqlbinlog --start-datetime="2015-04-09 00:00:00" --stop-datetime="2015-04-10 00:00:00" "/home/wewe/mysql/data/master-bin.？" -s -r "/home/wewe/qydata/？.sql"; D:\data\mysql -h rdsjmnyi3jmnyi3.mysql.rds.aliyuncs.com -u pssmis -ptofanepos wewe; source D:\data\bin.000352.sql; source D:\data\master-bin.000353.sql; mysql -h rdsjmnyi3jmnyi3.mysql.rds.aliyuncs.com -u pssmis -ptofanepos wewe &lt; /home/date/master-bin.000353.sql</td> 
 </tr> 
 <tr> 
 <td>手动增量同步binlog数据</td> 
 <td>mysqlbinlog&nbsp; "/home/wewe/mysql/data/master-bin.000353" -s -r "/home/wewe/qydata/master-bin.000353.sql";</td> 
 </tr> 
 <tr> 
 <td>重启数据库</td> 
 <td>service mysql stop service mysql start</td> 
 </tr> 
 <tr> 
 <td>同步最后一个binlog数据（注意检查中文乱码）</td> 
 <td>mysqlbinlog&nbsp; "/home/wewe/mysql/data/master-bin.000360" |sed -n -e '/^update/I,/;/p' -e '/^insert/I,/;/p' -e '/^delete/I,/;/p' -e '/^alter/I,/;/p' -e '/^drop/I,/;/p' -e '/^create/I,/;/p' &gt;"/home/wewe/qydata/60.sql";</td> 
 </tr> 
 <tr> 
 <td>服务发布</td> 
 <td>e服宝：wewe e服宝：wewegs</td> 
 </tr> 
 <tr> 
 <td>服务地址修改</td> 
 </tr> 
 <tr> 
 <td>邮件通知用户访问地址切换</td> 
 </tr> 
 </tbody> 
</table>

4月15日做迁移时失败了，失败的原因是因为有一部分单据数据没有被记录到mysql的binlog日志中。

4月23日做了第二次迁移，通过手工导过去的。