Oracle数据库迁移至MySQL的常见问题和解决方案

数据库迁移，从来都不是一件轻松的工作。尤其是当遇到跨数据库种类的迁移任务时，不淌过几个坑便压根不可能成功。这次，就来了解一下Oracle迁移到MySQL时，最常见到的一些坑和对应解决方案...

今天我们要谈的不实用日志分析需求

迁移工具选择

其他对象迁移

数据检验

一些性能参数

迁移工具选择

在选择了合适的DB来替换Oracle后，下一步就是选择一个合适的迁移工具来做迁移。迁移是一个漫长而困难的工作，不同的项目可能会有许多可供选择的工具。但似乎并没有哪一种工具是被万能适用的。

接下来我们对常见的各种工具进行一个简单的介绍。

这一种最简单的数据迁移方式。

所有的操作步骤比把大象放进冰箱还要简单，简单得只要分两步，第一步把Oracle的数据导成CSV或者SQL，然后再load或者source到MySQL中就可以了。

把Oracle的数据导成CSV或者SQL可以用很多工具，比如SQL developer或者toad，不过这里更推荐spool，它可以结合set把内容输出到指定的文件中，然后选择合理的行列分隔符，就可以产生csv文件了。

使用SQL LOAD的优点就是速度快和超级简单，不过同样的，它也会有很多弊端，它很难做成自动化和全面普及到很多张表上，每有一张表的操作就要写SQL拼CSV，然后还不能保证是一样的分隔符，大多数时候对lob字段操作也很麻烦。对类似于comments的评论字段也很难原样的copy过去。

来看一个简单的例子：

第一步先在Oracle这边创建了一张表，很简单只有四列，然后insert了三条数据查看了一下内容。

做一些简单的可能会用到的查询。

看一下导出用的spool的内容，实际用的时候肯定会比这个更复杂，要对换行、time、lob等进行更多的函数处理。然后把数据导了出来看一下。

接着再在MySQL创建一张一样的表把数据load了进去， 可以看到数据load进去了三行，同时也给出了三个警告，第二行一个，第三行两个，分别是int类型的列传了一个空字符串和时间类型的被截取了。查看一下表里的数据，发现和预期的不一样。

然后把刚刚在Oracle那边进行的查询再次查询一下，发现结果都变得不一样了。

这是因为在MySQL里int类型如果插入的为空，结果会自动转成0。

这在官方文档上有明确的说明：

An empty field value is interpreted different from a missing field:

For string types, the column is set to the empty string.

For numeric types, the column is set to 0.

For date and time types, the column is set to the appropriate “zero” value for the type.

再看一下用etl工具迁移过来的数据，可以发现数据被insert成了null ，符合了Oracle的意思，其实这就是sqlload时一些弊端，数据类型可能弄得不是原来的数据了。同样的，我们也可以设置成严格的模式，int类型的不允许插入null，具体到下面的sql_mode里来看。

使用python迁移数据库也很简单，可以分为三步，第一步就是建立配置表，同时和MySQL的表进行mapping，标识出是全量的还是增量的，如果是增量的，以什么做为增量来处理。第二步就是根据mapping进行code、code、code，最后根据不同的入参写好crontab就可以进行调度就可以了。

使用python处理的过程中可以对一些数据进行转换，也更加灵活地配置了一些选项，实现了较强的逻辑控制，当然也有一些缺点：它的速度慢了太多（不过也只比load慢，比起来后面要介绍的Java编写的软件还是快很多）。对于异常的处理也花费了大量的代码逻辑，同时也要会写代码。

简单来看一下它的实现：

这一个代码片断，显示了增量同步每一天的数据逻辑。

这是每天跑批之后生成的log，可以看出来把warning和error都列了出来，同时也对行数进行了统计。已经可以说是一个不错的小型产品了。可看出来6w条数据用了4s和load来比算是慢的，但是和Java之类的比算是快的了。

OGG的用法说起来简单，只要配置好Oracle端，再配置好MySQL端，然后对应的进程起起来就可以了。但用过OGG的人都知道配置一套OGG本身却很麻烦。异构数据库之间再进行同步的话，调通并可用需要很久的配置时间。

OGG的过程和注意事项：

1、 5.6版本需要12.1.2版本的OGG才支持

2、异构数据库之间不支持DDL复制

• 从Oracle同步到MySQL，属于异构架构，不支持DDL同步，包括添加和删除字段，添加和删除索引，重命名表，表分析统计数据。
  

• 若是涉及到源端和目标端DDL操作，需要进行源端和目标端同时手工操作。

3、必须要配置defgen，且文件必须放在相同的目录。

4、如果要是双向的话，就必须把MySQL端的binglog设置成row

binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Any other log format (MIXED or STATEMENT) causes Extract to abend.

5、GoldenGate对MySQL只支持InnoDB引擎。所以，在创建MySQL端的表的时候，要指定表为InnoDB引擎。

MySQL官方同样也提供一个用于异构之间的数据迁移工具，从MySQL到其它数据库，或者从其它数据库到MySQL都是可以的。这个工具就是MySQL Migration Toolkit。这个工具可以单独被下载，也被集成到了MySQL wrokbench里。不过如果单独下载的话 只有windows的版本。

https://downloads.MySQL.com/archives/migration/

这是一个基于Java的程序，所以依赖于jar包，使用它的第一步就是load一个odbc.jar。接着就可以把源端和目标端进行配置连接，选择要导入的对象（可以包含视图，但是一般有子查询的会报错），进行导入就可以了。

使用它的优点就是可以在MySQL端自动创建表，但有可能自动convert的类型若有问题，需要人为参与一下进行处理，比如Oracle中通常会对Timestamp类型的数据设置默认值sysdate，但在MySQL中是不能识别的。

缺点就是只有windows的平台有，在导大数据量时，极有可能就宕住了。

它是一款纯Java编写的软件，就像它的名字（水壶）一样，是用来把各种数据放到一个壶里，然后以一种指定的格式流出。当然你也可以使用DS(datastage)或者Informatica。不过这两个是收费的，而kettle是免费开源的。

这里只介绍它最简单的能满足把数据从Oracle迁移到MySQL的功能。

同理，第一步把jar包load进去，不同的是，这次要load的是MySQL的jar包。需要注意的是，如果你的MySQL版本不同可能需要load不同的jar包。第二步同也是配置连接信息，保证你的源和目标都连接成功，最后一步就是简单的拖拖拽拽。最后run一下就可以了。

它的优点就是配置起来比OGG快，但是同样可以通过job做到实时同步，处理速度和Python旗鼓相当，却不用自己来写mapping关系，并且提供了图形化界面。也能和Migration Toolkit一样同时创建表（新增一个Java脚本），进行类型转换，但日志更详细。只是可能学习成本高一点，要看的懂一些Java报错方便调试。

通过一段demo了解一下：

运行spoon.sh之后可以打开这个界面。view一界显示了这个转换的名字、数据源、处理步骤等，中间区域是你拖拽出来的操作，一个输入，一个输出。这就是数据迁移的所有步骤。

打开input的内容，就是很简单的一条SQL在哪个源数据库上抽取数据，当然这条SQL也可以是拖拽生成出来，类似于congos的拖拽生成报表。千万要注意的是，不要加分号。

output的内容就显示丰富了很多，选择目标数据源，以及会自动的mapping列的信息，还有在迁移之间要不要先清空，迁移过程中如果遇到问题了会不会中止。

这里就是显示了它超越Migration tools的log最细粒度到行级别，可以更快地分析出问题。

这里则是详细的日志输出。一般如果定时跑批处理的话，把它重定向到具体的log里，然后当做发送邮件。

其他对象的迁移

上面介绍的几种迁移工具，主要解决的是表和数据对象的迁移。是实际的数据库当然还会有一些其它的对象，像视图、物化视图、存储过程、函数、包，或者一个索引，同样的SQL是不是也需要改写，都是需要考虑到的因素。

1、view

在MySQL里view是不可以嵌套子查询的：

create view v_test as select * from (select * from test) t;

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

解决方法就是view的嵌套：

create view v_sub_test as select * from test;

Query OK, 0 rows affected (0.02 sec)

create view v_test as select * from v_sub_test;

Query OK, 0 rows affected (0.00 sec)

2、物化视图

物化视图用于预先计算并保存表连接或聚集等耗时较多的操作结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速得到结果。但是MySQL里没有这个功能。通过事件调度和存储过程模拟物化视图，实现的难点在于更新物化视图，如果要求实时性高的更新，并且表太大的话，可能会有一些性能问题。

3、Trigger、存储过程、package

1）Oracle创建触发器时允许or，但是MySQL不允许。所以迁移时如果有需要写两个。

2）两种数据库定义变量的位置不同，而且MySQL里不支持%type。这个在Oracle中用得太频繁了，是个好习惯。

3）elseif的逻辑分支语法不同，并且MySQL里也没有for循环。

4）在MySQL中不可以返回cursor，并且声明时就要赋对象。

5）Oracle用包来把存储过程分门别类，而且在package里可以定义公共的变量/类型，既方便了编程，又减少了服务器的编译开销。可MySQL里根本没有这个概念。所以MySQL的函数也不可以重载。

6）预定义函数。MySQL里没有to_char() to_date()之类的函数，也并不是所有的Oracle都是好的，就像substring()和load_file()这样的函数，MySQL有，Oracle却没有。

7）MySQL里可以使用set和=号给变量赋值，但不可以使用：=。 而且在MySQL里没 || 来拼接字符串。

8）MySQL的注释必须要求-- 和内容之间有一个空格。

9）MySQL存储过程中只能使用leave退出当前存储过程，不可以使用return。

10）MySQL异常对象不同，MySQL同样的可以定义和处理异常，但对象名字不一样。

4分页语句

MySQL中使用的是limit关键字，但在Oracle中使用的是rownum关键字。所以每有的和分页相关的语句都要进行调整。

5JOIN

如果你的SQL里有大量的（ ），这绝对是一个很头疼的问题。需要改写。

6group by语句

Oracle里在查询字段出现的列一定要出现在group by后面，而MySQL里却不用。只是这样出来的结果可能并不是预期的结果。造成MySQL这种奇怪的特性的归因于sql_mode的设置，一会会详细说一下sql_mode。不过从Oracle迁移到MySQL的过程中，group by语句不会有跑不通的情况，反过来迁移可能就需要很长的时间来调整了。

7bitmap位图索引

在Oracle里可以利用bitmap来实现布隆过滤，进行一些查询的优化，同时这一特性也为Oracle一些数据仓库相关的操作提供了很好的支持，但在MySQL里没有这种索引，所以以前在Oracle里利于bitmap进行优化的SQL可能在MySQL会有很大的性能问题。

目前也没有什么较好的解决方案，可以尝试着建btree的索引看是否能解决问题。要求MySQL提供bitmap索引在MySQL的bug库里被人当作一个中级的问题提交了上去，不过至今还是没有解决。

8分区表（Partitioned table）

需要特殊处理，与Oracle的做法不同，MySQL会将分区键视作主键和唯一键的一部分。为确保不对应用逻辑和查询产生影响，必须用恰当的分区键重新定义目标架构。

9角色

MySQL8.0以前也没有role的对象。在迁移过程中如果遇到的角色则是需要拼SQL来重新赋权。不过MySQL更好的一点是MySQL的用户与主机有关。

10表情和特殊字符

在Oracle里我们一般都选择AL32UTF8的字符集，已经可以支付生僻字和emoji的表情了，因为在迁移的时候有的表包含了大量的表情字符，在MySQL里设置了为utf8却不行，导过去之后所有的都是问号，后来改成了utf8mb4才解决问题，所以推荐默认就把所有的DB都装成utf8mb4吧。

数据检验

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

数据校检工作主要分为在导入过程中的log和警告，在load的时候SHOW WARNINGS和errors，在使用Python、OGG、Kettle等工具时详细去看每个errors信息。

1、count(*)

迁移或增量操作完成以后，用最简单的count(*)去检查，在MySQL和Oracle上检查进行比对。如果数据量一致，再进行数据内容的验证。由于数据量太大，只进行了抽样检测。人工的手动检验如果没有问题了，可以使用应用程序对生产数据库的副本进行测试，在备库上进行应用程序的测试，从而进行再一次的验检。

2、etl工具

另外推荐的一种方式就是使用etl工具配置好MySQL和Oracle的数据源，分别对数据进行抽取，然后生成cube，进行多纬度的报表展现。数据是否有偏差，可以一目了然看清。

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

3、SQL_MODE

MySQL服务器能够工作在不同的SQL模式下，针对不同的客户端，以不同的方式应用这些模式。这样应用程序就能对服务器操作进行量身定制，以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。

• TRADITIONAL

设置“严格模式”，**可接受的数据库输入数据值（类似于其它数据库服务器），该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。

• ONLY_FULL_GROUP_BY

在MySQL的sql_mode=default的情况下是非ONLY_FULL_GROUP_BY语义，也就是说一条select语句，MySQL允许target list中输出的表达式是除聚集函数、group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined，无法确定(实际上MySQL的表现是分组内第一行对应列的值)

select list中的所有列的值都是明确语义。

简单来说，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

MySQL校对规则属于PADSPACE，MySQL对CHAR和VARCHAR值进行比较都忽略尾部空格，和服务器配置以及MySQL版本都没关系。

• explicit_defauls_for_timestamp

MySQL中TIMESTAMP类型和其它的类型有点不一样(在没有设置explicit_defaults_for_timestamp=1的情况下），在默认情况下，如果TIMESTAMP列没有显式的指明null属性，那么该列会被自动加上not null属性(而其他类型的列如果没有被显式的指定not null，那么是允许null值的)，如果往这个列中插入null值，会自动设置该列的值为current timestamp值，表中的第一个TIMESTAMP列，如果没有指定null属性或者没有指定默认值，也没有指定ON UPDATE语句，那么该列会自动被加上DEFAULT 。

CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。第一个TIMESTAMP列之后的其它的TIMESTAMP类型的列，如果没有指定null属性，也没有指定默认值，那该列会被自动加上DEFAULT '0000-00-00 00:00:00'属性。如果insert语句中没有为该列指定值，那么该列中插入'0000-00-00 00:00:00'，并且没有warning。

如果启动时在配置文件中指定了explicit_defaults_for_timestamp=1，MySQL会按照如下的方式处理TIMESTAMP列。

此时如果TIMESTAMP列没有显式的指定not null属性，那么默认的该列可以为null，此时向该列中插入null值时，会直接记录null，而不是current timestamp。并且不会自动的为表中的第一个TIMESTAMP列加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP属性，除非在建表时显式的指明。

一些性能参数

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

• 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%的“每秒”。

• sync_binlog

sync_binlog 的默认值是0，像操作系统刷其它文件的机制一样，MySQL不会同步到磁盘中去，而是依赖操作系统来刷新binary log。

当sync_binlog =N (N