老鬼的博客 来都来啦，那就随便看看吧~
SQL Server转MySql
发布于: 2019-08-28 更新于: 2019-11-23 分类于:  阅读次数: 

一：介绍

1
项目背景：要上阿里云，但是未提供对应的SQL Server，只提供MYSQL，所以需要将现有的SQL Server数据库迁移到MYSQL中。

二：方案

1
停机进行数据库迁移，迁移方案是，先使用navicat将sqlserver对应的表结构迁移到mysql中，然后使用阿里云的datax进行数据库同步。目前只能做到数据库表的迁移，view和function因为语法不通，目前需要手动改写，好在view和function不多。

三：名词介绍

名词 说明
salesproduct_sqlserver sqlserver数据库
salesproduct_sqlserver_1 sqlserver只有表结构没有数据的数据库
salesproduct_mysql mysql的数据库

三：迁移

3.1 新建空的数据库

1
使用sqlserver自带的管理工具，导出整个数据库的脚本，声明只导出结构，不导出数据，创建database是salesproduct_sqlserver_1

3.2 使用navicat将salesproduct_sqlserver_1导入到salesproduct_mysql

3.2.1 说明
1
使用navicat工具将sqlserver的表转成mysql的表
3.2.2 步骤如下
1
2
3
4
5
1.右键sqlserver数据库，选择Data tranfer...
2.选择source的schema：dbo
3.点击选择source的select all
4.选择Target的Connection，这里选择要导入的mysql的数据库
5.点击右下角的start即可

1.png
2.png
3.png

3.2.3 备注

navicat将sqlserver转成mysql以后，其中为自增长主键的表，未设置auto_increment，这个需要自己手动设置。

3.3 使用datax进行数据同步

3.3.1 相关参考文档
  1. 阿里云DataX官方帮助文档
  2. DataX开源地址
  3. DataX各个插件配置模板和参数说明
3.3.2 data json文件介绍
  • 整体json配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
{
     "job": {
          "setting": {
               "speed": {
                    "byte": 1073741824
               }
          },
          "content": [
               {
                    "reader": {
                         "name": "sqlserverreader",
                         "parameter": {
                              "username": "sa", 
                              "password": "a123456", 
                              "where": "1 = 1",
							  "column": ["id","user_name","password"],
                              "connection": [
                                   {
                                        "jdbcUrl": [
                                             "jdbc:sqlserver://localhost:1433;DatabaseName=salesproduct_sit"
                                        ],							
										"table": ["salesproduct_admin"]
                                   }
                              ]
                         }
                    },
                    "writer": {
                         "name": "mysqlwriter",
                         "parameter": {
                              "writeMode": "insert",  
                              "username": "root",
                              "password": "a123456",
							  "preSql": [
									"delete from salesproduct_admin"
							  ],
							  "column": ["`id`","`user_name`","`password`"],
                              "connection": [
                                   {
                                        "jdbcUrl": "jdbc:mysql://localhost:3306/salesproduct_sit?useUnicode=true&characterEncoding=utf-8",										
										"table": ["salesproduct_admin"]
                                   }
                              ]
                         }
                    }
               }
          ]
     }
}
  • JOB全局配置

1.json

1
2
3
4
5
6
7
8
9
"setting": {
   "speed": {
		"byte": 1073741824
   }
},
"errorLimit": {
	"record": 0,
	"percentage": 0.02
}

2.备注

1
2
3
4
speed为同步速度限制参数，这里有三个参数channel、record和byte。
channel：管道数，可以理解为并行数，需与splitPk一同使用，否则无效果。
record：每次同步多少条数据，取record和byte中的最小值
byte：每次同步多少字节数据，取record和byte中的最小值
  • Reader线程配置
1
2
RDBMSReader通过JDBC连接器连接到远程的RDBMS数据库，并根据用户配置的信息生成查询SELECT SQL语句并发送到远程RDBMS数据库，并将该SQL执行返回结果使用DataX自定义的数据类型拼装为抽象的数据集，并传递给下游Writer处理。
对于用户配置Table、Column、Where的信息，RDBMSReader将其拼接为SQL语句发送到RDBMS数据库；对于用户配置querySql信息，RDBMS直接将其发送到RDBMS数据库。

1.json

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
"reader": {
	 "name": "sqlserverreader",
	 "parameter": {
		  "username": "sa", 
		  "password": "a123456", 
		  "where": "1 = 1",
		  "column": ["id","user_name","password"],
		  "connection": [
			   {
					"jdbcUrl": [
						 "jdbc:sqlserver://localhost:1433;DatabaseName=salesproduct_sit"
					],							
					"table": ["salesproduct_admin"]
			   }
		  ]
	 }
}

2.备注

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
username:源数据库的用户名

password:源数据库的密码 

encoding:数据库字符集,GBK,UTF8等

column:
所配置的表中需要同步的列名集合，使用JSON的数组描述字段信息。用户使用代表默认使用所有列配置，例如['']。
支持列裁剪，即列可以挑选部分列进行导出。
支持列换序，即列可以不按照表schema信息进行导出。
支持常量配置，用户需要按照JSON格式: ["id", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"] id为普通列名，1为整形数字常量，'bazhen.csy'为字符串常量，null为空指针，to_char(a + 1)为表达式，2.3为浮点数，true为布尔值。
Column必须显示填写，不允许为空！

splitPk:
RDBMSReader进行数据抽取时，如果指定splitPk，表示用户希望使用splitPk代表的字段进行数据分片，DataX因此会启动并发任务进行数据同步，这样可以大大提供数据同步的效能。
推荐splitPk用户使用表主键，因为表主键通常情况下比较均匀，因此切分出来的分片也不容易出现数据热点。
目前splitPk仅支持整形数据切分，不支持浮点、字符串型、日期等其他类型。如果用户指定其他非支持类型，RDBMSReader将报错！
注意：这里并非只能是主键，拥有唯一约束的列也可。

table:所选取的需要同步的表名

jdbcUrl:
描述的是到对端数据库的JDBC连接信息，jdbcUrl按照RDBMS官方规范，并可以填写连接附件控制信息。请注意不同的数据库jdbc的格式是不同的，DataX会根据具体jdbc的格式选择合适的数据库驱动完成数据读取。

fetchSize:
该配置项定义了插件和数据库服务器端每次批量数据获取条数，该值决定了DataX和服务器端的网络交互次数，能够较大的提升数据抽取性能。
注意，该值最大建议值为2048。

where:
筛选条件，RDBMSReader根据指定的column、table、where条件拼接SQL，并根据这个SQL进行数据抽取。例如在做测试时，可以将where条件指定为limit 10；在实际业务场景中，往往会选择当天的数据进行同步，可以将where条件指定为gmt_create > $bizdate 。
  • Writer线程配置
1
2
3
4
5
OracleWriter 插件实现了写入数据到 Oracle 主库的目的表的功能。在底层实现上， OracleWriter 通过 JDBC 连接远程 Oracle 数据库，并执行相应的 insert into ... sql 语句将数据写入 Oracle，内部会分批次提交入库。
OracleWriter 面向ETL开发工程师，他们使用 OracleWriter 从数仓导入数据到 Oracle。同时 OracleWriter 亦可以作为数据迁移工具为DBA等用户提供服务。
OracleWriter 通过 DataX 框架获取 Reader 生成的协议数据，根据你配置生成相应的SQL语句
insert into...(当主键/唯一性索引冲突时会写不进去冲突的行)
对于使用datax同步到oracle的表，建议删除主键和唯一索引，数据校验完成后再进行重建。

1.json

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
"writer": {
	 "name": "mysqlwriter",
	 "parameter": {
		  "writeMode": "insert",  
		  "username": "root",
		  "password": "a123456",
		  "preSql": [
				"delete from salesproduct_admin"
		  ],
		  "column": ["`id`","`user_name`","`password`"],
		  "connection": [
			   {
					"jdbcUrl": "jdbc:mysql://localhost:3306/salesproduct_sit?useUnicode=true&characterEncoding=utf-8",										
					"table": ["salesproduct_admin"]
			   }
		  ]
	 }
}

2.备注

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
username:
目的数据库的用户名

password:
目的数据库的密码

encoding:
数据库字符集


batchSize:
一次性批量提交的记录数大小，该值可以极大减少DataX与Oracle的网络交互次数，并提升整体吞吐量。最大只能设置为1024


column:
表需要写入数据的字段,字段之间用英文逗号分隔。例如: "column": ["id","name","age"]。注意如果是mysql最好是加上``，因为如果有关键字作为字段名则会报错



preSql:
执行语句时会先检查是否存在若存在，根据条件删除


jdbcUrl:
目的数据库的 JDBC 连接信息


table:
目的表的表名称。支持写入一个或者多个表。当配置为多张表时，必须确保所有表结构保持一致。
3.3.3 同步脚本
  • 脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CHCP 65001
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_comparisons.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_data.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_detail.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_employees.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_error_log.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_faqs.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_main_csdata.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_products.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_rate_files.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_setup_info.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_staff.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_log_report.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_send_report.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_detail_excel.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_stat_daily.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_daily_log_report.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_stat_detail.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_topics.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_admin.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_trials.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_admin_password_history.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_versions.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_agency_inforce.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_agent.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_agent_city_info.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_agent_info.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_app.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_arguments.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_articles.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_categories.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_config.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_crowds.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_cs_data.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_cs_files.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_daily_log.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_ssl_files.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_product_config.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_agent_city_info_copy.json
python D:/my-tools/datax/bin/datax.py D:/my-tools/datax/salesproduct_sit/salesproduct_access_log.json
pause
  • 备注
1
2
3
1.CHCP 65001防止cmd中文乱码
2.D:/my-tools/datax/是datax的安装路径
3.D:/my-tools/datax/salesproduct_sit/是配置的json文件的路径

3.png
3.png

3.3.4 手动编写view和function
1
由于view和function，sqlserver和mysql的语法不一样，目前还没有同步的工具可以使用，需要手动编写。
3.3.5 数据校验
1
最后一部工作就是做数据校验，比对转换前后数据的一致性。
*************感谢您的阅读*************