1 Star 1 Fork 0

Rey Wong / mongodb-to-mysql

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
克隆/下载
贡献代码
同步代码
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README
Apache-2.0

说明:

  • 1.该程序主要的目的是在不停服的情况下,将应用的数据库从MongoDB切换到Mysql

  • 2.datasysc-mongo-to-mysql:数据迁移服务,将MongoDB中的数据迁移到Mysql中,支持多线程和断点续传

  • 3.mongo-to-mysql-adapter: 将MongoDB的语法适配成Mysql的语法,支持如下命令:


// $set $unset $push $pull $pop
// $mod $inc
// $exists $type $size 实现
// distinct() group() count() $elemMatch $sample $geometry
// $not $geoWithin  $near $nearSphere $geoIntersects $maxDistance $minDistance
// $and $or $nor 实现
// $in $nin $all $regex $lg $ne $lge $gt $gte  实现

该方案已经在国内某大型快递物流企业的网关中成功实施,上线一年多运行平稳

切换思路

Mongodb切换成mysql.png

目录

1.问题概要

事件:

 将EDS的数据库从Mongodb切换成Mysql.

为什么是Mongodb?

市面上(公司)对Mongodb精通的运维人员比较稀缺,遇到问题难以解决

为什么是Mysql?

免费,配合公司的使用数据库规范,统一使用Mysql

2.面临挑战

1.XXX作为网关和MQ代理使用,没有停机窗口

2.XXX有多个节点,上线过程中涉及到新老并行

3.系统切换失败,需要支持回退

4.切换过程中,子系统要求无感知,数据无异常

5.不了解XXX,文档不全

6.不了解MongoDB及其语法

3.系统分析

image.png

4.修改思路

1.历史数据迁移

MongoDB中的数据如何迁移到Mysql

2.修改应用代码

如何尽量减少代码的改动,降低风险降低测试周期

3.如何分步上线

如何能无感知分步上线,并且支持回滚

4.1 修改思路-数据迁移

第一步要将MongoDB的数据同步到Mysql,数据库分析如下

image.png

同步数据相当于将JSON拆分成列,就会面临下面的问题:

  1. JSON的层级格式随意,转成mysql建表难度大

2.value的值无法判断,Mysql创建表的时候字段长度无法确定的问题

3.JSON的的字段不定,无法判断建表字段

如下:

image.png

面临切换的各种问题,决定先进行验证,看看数据质量,因为会面临不断的调整,最终选择使用Python进行验证,果然坑一个比一个深,同一个集合数据如下:

image.png

经过之前的可行性研究,最终决定另想思路。

此时Mysql5.6以后的版本已经开始支持JSON格式了,所以打算验证该方案的可行性,需要解决两个问题:

1.JSON对象查询的效率如何?

  1. Mysql存储成JSON对象如何创建索引?

做了如下优化:

  1. 将_id(MongoDB内置对象)映射成虚拟字段,并创建索引

  2. 将MongoDB中的索引字段映射成虚拟字段,并创建索引

image.png

4.2 修改思路-代码修改

代码修改初步想法,有两种方案:

第一种方案

内容:修改所有涉及操作MongoDB的代码

优点:开发简单

缺点 :涉及范围太广,需要阅读项目代码,周期长,风险大

第二种方案

内容:使用Mysql语法重写MongoDB的驱动

优点:涉及范围小不用关心调用代码,周期短,风险小

缺点:开发难度较大

由于XXX项目出问题影响较大,为了后期测试能全覆盖,最终选择了第二种方案

方案如下:

  • 1.重写mongodb驱动MongoDBTemplate

image.png

  • 2.编写的mongodb-context.xml

image.png

  • 3.替换原有配置(可以通过参数决定是否切换mysql,方便切换)

image.png

4.3 修改思路-上线步骤

image.png

  1. 序列号双写,发灰度没问题发全量

可并行,可回滚

2.迁移数据,切换驱动开关到Mysql-JVM配置

在节点没有发完之前,Manager不能做修改,可并行,可回滚

3.切换双写开关-disconf配置

读写Mysql,备份到MongoDB

不能并行,需要同时切换开关

  1. 去掉辅助代码

5.遇到的问题

5.1 遇到的问题-数据迁移

1.MongoDB数特殊格式转换

_id 字段默认为ObjectId 类型,但EDS中有覆写该字段

image.png

2.添加索引麻烦

需要添加虚拟字段映射到JSON对应的字段

image.png

3.Date类型转换

MongoDB的Date类型需要转成String类型,格式需要和mongoDB的驱动一致,为:yyyy-MM-dd HH:mm:ss.SSS

image.png

4.数据量大,需要提升同步效率

需要支持多线程和断点续传

image.png

5.2 遇到的问题-代码修改

1._id字段被覆写,系统类型转换错误

_id默认转成了String类型,但被覆写的_id中存在Date类型,代码在查询该字段没有特殊处理,自动转成Date类型

image.png

2.JSON中同一个Key的Value类型不一样,操作mysql的时候报类型转换错误

image.png

3.MongoTemplate自动将_id和id均属性映射成_id字段

覆写MongoTemplate的时候需要兼容

image.png

4.MongoDB读不存在的集合的时候不会报错,Mysql缺表会报错

 XXX系统中有两张表在项目启动的时候会读取,而该表数据库中一直都不存在,MongoDB不会报异常,Mysql直接报错,系统无法启动

image.png

5.3遇到的问题-系统上线

1.测试环境没有灰度环境 ,无法验证,序列号双写直接写到了灰度库

灰度环境的序列号应该也是写到正式库

2.切换过程出现了一个覆写hashcode导致CPU飚高的问题

测试环境没有使用缓存,实施环境并发不够,均没有复现

3.序列号双写切换主库失败问题

思路:配置节点数,当切换的时候,会计数,当前服务阻塞读取切换节点数,如果节点数和配置的参数

相等就进行同时切换,超时时间5S

过于依赖disconf下发,在5S内engine的14个节点没有全部处理完成,导致切换失败

解决:通过redis配置,所有节点读取redis的开关

Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

简介

暂无描述 展开 收起
Java 等 3 种语言
Apache-2.0
取消

发行版

暂无发行版

贡献者

全部

近期动态

加载更多
不能加载更多了
1
https://gitee.com/reywong/mongodb-to-mysql.git
git@gitee.com:reywong/mongodb-to-mysql.git
reywong
mongodb-to-mysql
mongodb-to-mysql
master

搜索帮助