Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑、怎样修改MySQL参数获取最大性能,加入分库分表的需求如何实现?文章的最后,作者做了很多可行性的总结,码字不易,如果对您有帮助,感谢转发。

迁移方案的选择:


抛开业务逻辑的因素,根据不同的版本、不同平台、不同停机时间需求,有不同的可选路径决定迁移方

法和工具:

迁移方法优点缺点
SQL LOAD操作简单、速度快、选择数据范围灵活需自定义开发批量操作、对于CLOB等特殊字段无法支持
OGG商用软件,广泛的数据库平台支持、灵活的复制架构、基于日志的实时数据同步、稳定性高对维护技能有一定的要求、费用高
ETL 软件使用方便简单、定时同步批量处理大量表需定制化配置
MYSQL移植工具安装简单、可自动创建表不可定制、技术支持较弱
定制迁移工具可高度定制,保证最佳性能和最短停机时间暂无


由于不同的数据库版本、不同的组件安装、不同的应用开发特征都会导致迁移计划的复杂性和差异性。

调研中,除了OGG,有几个MySQL迁移的工具,推荐的比较多,但是收费的。

【工具:OGG (goldengate) 

同时支持Oracle,Mssql 迁移到 MySQL 上

参数:filter,COMPUTE 进行分库分表逻辑】

● SQLyog

   (https://www.webyog.com/product/sqlyog)

● Navicat Premium

  (https://www.navicat.com/products/navicat-premium)

● Mss2sql

   (http://www.convert-in.com/)

● DB2DB

   (http://www.szmesoft.com/DB2DB)

选择迁移软件,必须要考虑 软件易用性, 处理速度和内存占用,数据完整性。这部分很重要。

以上四款软件中:

1. 最不推荐使用的是 Navicat Premium,主要原因是数据的完整性表现较差,转换后的数

据不能立即用于生产环境,需要程序员仔细自行查找原因和分析。

2. SQLyog 有较好的数据完整性,但整体处理速度非常的慢,如果数据较大的情况下,需要浪费非常多宝

贵的时间。比较推荐的是

3. DB2DB,处理速度,数据完整性,整体表现较好,操作起来实在方便。

我本人趋向于自己写python脚本。

迁移中会存在哪些细节上的问题?


1. 字符集

字符集转化:Oracle字符集AL32UTF8,ZHS16GBK,转换成MySQL支持的字符集Latin1,utf8,utf8mb4(emoji的表情符)

Mysql对于字符集里有两个概念:一个是”Character set”另一个是”Collations”。

Collations:Mysql对字符的比较,排序规则 

Character set:字符的编码方式

2. 字段类型

Oracle Row, Clob,BINARY_DOUBLE类型转化成MySQL支持的字段类型。

如:Oracle CLOB字段最大长度4G对应MySQL LONGTEXT 等等,但要是把数据这些数据迁移到MySQL上,可以想象到会发生什么事情。

3. 主键

有些源表没有设置主键, 但对于MySQL来说主键的意思非常大,特别是复制环节里。

4. 迁移时间和数据量 

对于现在在线不间断提供的业务非常重要,按照这个指标可以制定全量或者增量方式进行迁移。

5. 考虑因素

除了以上内容源数据库还有账号、视图、存储过程、函数、触发器,索引等,同样也很重要,都是需要考虑的一个因素。

6. 校验数据

这一关最后门卡,当数据迁移完成后,如何确保数据的正确迁移、没有遗漏和错误是一个很难的问题。这里的难不是实现起来困难,而是要把它自动化,达到节省人力的目标有点难,因为两者的数据类型不同,数据量偏大,写一些脚本去做检查效果不大。

数据的完整性验证是十分重要的,千万不要怕验证到错误后要花好长时候去抽取同步的操作这一步。因为一旦没有验证到错误,让数据进行了使用却乱掉了,后果将更严重。

一般场景下都是对应查询数据行数count来判断数据的是否存在问题。或则 是用create_time时间字段进行验证数据。或则抽取部分数据进行验证。还有导入过程中的log和警告 ,errors 等信息。

MySQL一些性能参数

可以在导入数据的时候预先修改一些参数,来获取最大性能的处理,比如可以把自适应hash关掉,Doublewrite关掉,然后调整缓存区,log文件的大小,把能变大的都变大,把能关的都关掉来获取最大的性能,接下来说几个常用的:

1. innodb_flush_log_at_trx_commit

  • 如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下,在事务提交时,不会主动触发写入磁盘的操作。
  • 如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去。
  • 如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file。但是flush(刷到磁盘)的操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意:由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

2. sync_binlog

  • sync_binlog 的默认值是0,像操作系统刷其它文件的机制一样,MySQL不会同步到磁盘中去,而是依赖操作系统来刷新binary log。
  • 当sync_binlog =N (N>0) ,MySQL 在每写N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注意:如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。

3. max_allowed_packet

  • 在导大容量数据特别是CLOB数据时,可能会出现异常:“Packets larger than max_allowed_packet are not allowed”。这是由于MySQL数据库有一个系统参数max_allowed_packet,其默认值为1048576(1M),可以通过如下语句在数据库中查询其值:show VARIABLES like ‘%max_allowed_packet%’; 
  • 修改此参数的方法是在MySQL文件夹找到my.cnf文件,在my.cnf文件[MySQLd]中添加一行:max_allowed_packet=16777216

4. innodb_log_file_size

InnoDB日志文件太大,会影响MySQL崩溃恢复的时间,太小会增加IO负担,所以我们要调整合适的日志大小。在数据导入时先把这个值调大一点。避免无谓的buffer pool的flush操作。但也不能把innodb_log_file_size开得太大,会明显增加 InnoDB的log写入操作,而且会造成操作系统需要更多的Disk Cache开销。

5. innodb_log_buffer_size

InnoDB用于将日志文件写入磁盘时的缓冲区大小字节数。为了实现较高写入吞吐率,可增大该参数的默认值。一个大的log buffer让一个大的事务运行,不需要在事务提交前写日志到磁盘,因此,如果你有事务比如update、insert或者delete 很多的记录,让log buffer 足够大来节约磁盘I/O。

6. innodb_buffer_pool_size

这个参数主要缓存InnoDB表的索引、数据、插入数据时的缓冲。为InnoDN加速优化首要参数。一般让它等于你所有的innodb_log_buffer_size的大小就可以,innodb_log_file_size要越大越好。 

7. innodb_buffer_pool_instances

InnoDB缓冲池拆分成的区域数量。对于数GB规模缓冲池的系统,通过减少不同线程读写缓冲页面的争用,将缓冲池拆分为不同实例有助于改善并发性。

分库分表方案


现在加难度加入分库分表需求。

这种情况建议选择传统的方式写一个迁移程序,读源数据库,通过中间件写入目标库db1,db2,db3里

如果源数据源设计的合理完全可以用全量+增量方式实现。如下图所示

虽然这种方式很灵活,自行控制,但也有缺点,所有业务逻辑,分库分表方案,验证都需要手动编写

下次可以在不同的平台下使用。

现在业界比较常用的分库分表的中间件有两种:

  • proxy形,如:基于阿里开源的Cobar产品而研发的mycat, 需要部署另外服务器,作为分库分表的代理,对外服务,包含分库分表的配置信息,现在版本是mycat2.0。 
  • client形式,如当当出的sharding-jdbc,现在有京东金融进行维护,现在版本sharding-jdbc4.0开发中。是jar包,使用非常方便。我个人趋向于Sharding-JDBC,这种方式,无需额外部署,替换原有jdbc,DBA也无需改变原有的运维方式,减轻了DBA的任务。

总结


1. 一定要选择合适你的迁移工具,没有哪一个工具是最好的。

2. 数据的检验非常重要,有的时候我们迁过去很开心,校验时发生错误,这个时候必须要重来。

3. 重复地迁移是很正常的,合乎每次迁移可能需要很长时间,总会是有错误的,要做好再迁的心态

4. 迁移过程中的日志记录非常重要,一段出现故障,可以再问题点开始继续进行迁移。

Image placeholder
重庆的土匪
未设置
  88人点赞

没有讨论,发表一下自己的看法吧

推荐文章
【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

开发中常见的Oracle三大故障与调优方法

墨墨导读:怀晓明先生(网名lastwinner),是具有多年数据库开发与项目管理经验的数据库专家。曾获得第一届ITPUB较佳建议奖,在多个大型IT企业多年的工作历练中,积累了丰富的系统架构设计经验。合

“小应用”背后的“大改变” 爱奇艺赋能流媒体播放服务

热门视频里,“弹幕盖脸”几乎是必然事件,然而有一个地方看视频,你会发现密密麻麻的弹幕都绕开主角飘过,这个地方就是爱奇艺。对于大家观看视频时喜闻乐见的弹幕,爱奇艺提供了蒙版弹幕服务,可以让用户实现“弹幕

NVIDIA再放“大招”,拓宽高性能计算边界

11月19日,在丹佛2019全球超级计算大会(SC19)上,NVIDIA创始人兼首席执行官黄仁勋进行了主题演讲,并宣布了NVIDIA在高性能计算领域的最新动态与成果。这些成果表明,NVIDIA正在将自

从MySQL到POLARDB, 三位CTO讲述迁移背后的故事!

摘要: 去年9月21日,阿里云发布了自主研发的通用云数据库POLARDB公测版,今年4月,POLARDB正式商业化。此前,POLARDB核心卖点是100%向下兼容MySQL5.6,100TB存储容量,

上万条数据撕开微博热搜的真相!

作者:徐麟,某互联网公司数据分析狮,个人公众号数据森麟(id:shujusenlin)吃瓜前言关于新浪微博,向来都是各路吃瓜群众聚集之地,大家在微博中可以尽情吃瓜,各种类型的瓜应有尽有,只有你想不到的

走近科学,探究阿里闲鱼团队通过数据提升Flutter体验的真相

背景闲鱼客户端的Flutter页面已经服务上亿级用户,因此用户体验尤其重要,完善Flutter性能稳定性监控体系,以便及早发现线上性能问题,也可以作为用户体验提升的衡量标准。那么Flutter的性能到

三分钟带你分清Mysql 和Oracle之间的误区

课程推荐:PHP开发工程师--学习猿地精品课程 mysql和Oracle在开发中的使用是随处可见的,那就简单去了解一下这俩款火的不行的数据库。 本质区别:Oracle数据库是一个对象关系数据库管理系统

MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询

作者:逸宸a链接:https://www.jianshu.com/p/cbdef47fb837对MySQL的性能和亿级数据的处理方法思考,以及分库分表到底该如何做,在什么场景比较合适?比如银行交易流水

从Oracle到PostgreSQL,某保险公司迁移实践

摘要:去O一直是金融保险行业永恒的话题,但去O的难度之大也只有真正经历过的人才知其中的艰辛。此次笔者结合实际去O工作,对去O过程中碰到的DBLINK、SEQUENCE最大值、空串、SQL语句中的别名等

从 Oracle 到 PostgreSQL ,某保险公司迁移实践

作者 |章晨曦编辑 | 老鱼摘要:去O一直是金融保险行业永恒的话题,但去O的难度之大也只有真正经历过的人才知其中的艰辛。此次笔者结合实际去O工作,对去O过程中碰到的DBLINK、SEQUENCE最大值

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

Mysql中,21个写SQL的好习惯,你值得拥有呀

课程推荐:PHP开发工程师--学习猿地精品课程 前言每一个好习惯都是一笔财富,本文分SQL后悔药,SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~github地址

MySQL Batched Key Access (BKA)原理和设置使用方法举例

MySQL5.6版本开始增加了提高表join性能的算法:BatchedKeyAccess(BKA)的新特性。BKA算法原理:将外层循环的行/结果集存入joinbuffer,内存循环的每一行数据与整个b

mysql5.7 General tablespace使用说明

GeneraltablespaceGeneraltablespace 是一种共享的 innodb 表空间,有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表,即使这些表来自不同的 sch

前端培训-中级阶段(31)- Class 的基本语法、Class 的继承(2019-12-26期)

前端最基础的就是HTML+CSS+Javascript。掌握了这三门技术就算入门,但也仅仅是入门,现在前端开发的定义已经远远不止这些。前端小课堂(HTML/CSS/JS),本着提升技术水平,打牢基础知

2019全球PostgreSQL生态报告出炉,PG为何从RDBMS中脱颖而出?

墨墨导读:本文是近期ScaleGrid发布的2019PG趋势报告,从不同的角度解读了PostgreSQL如何在众多优秀的RDBMS中脱颖而出,原文:https://scalegrid.io/blog/

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS