项目描述:
将SqlSever 2008 R2中的数据实时或定时的同步到Mysql 5.6数据库中。
| OS | DATABASE |
来源数据库 | Window 2008 | MS SQL 2008 R2 |
目标数据库 | Linux | MySQL 5.6 |
实施方案:
如何初始化数据?
Navicat Premium--可以很方便的将MSSQL SERVER中的数据结构和数据初始化到Mysql数据库中。
如何实现实时同步?
方案1:SyncNavigator--实现MSSQL SERVER和Mysql数据库之间的数据同步(可选在调度时间和机制)。
方案2:Oracle Goldengate--灵活的实现MSSQL SERVER和Mysql数据库之间的数据同步。
两种方案的利弊?
方案1:,配置简单,但是对于目标端表中已经存在数据的情况处理麻烦(无法创建同步字段,这个字段是软件同步进程需要的)
方案2,配置复杂,效率高。
使用OGG来实现MSSQL SERVER和MYSQL之间的数据同步
OGG 软件:
V34020-01
Oracle GoldenGate V11.2.1.0.2 for SQL Server on Windows (64bit)
V32399-01
Oracle GoldenGate V11.2.1.0.1 for MySQL 5.x on Linux x86-64)
源端MSSQL SERVER配置
安装OGG
- C:\OGG>ggsci
-
- Oracle GoldenGate Command Interpreter for SQL Server
- Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
- Windows x64 (optimized), Microsoft SQL Server on Jul 25 2012 03:04:52
-
- Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
-
-
-
- GGSCI (WIN-UGCMTTOTKE7) 1> create subdirs
-
- Creating subdirectories under current directory C:\OGG
-
- Parameter files C:\OGG\dirprm: already exists
- Report files C:\OGG\dirrpt: created
- Checkpoint files C:\OGG\dirchk: created
- Process status files C:\OGG\dirpcs: created
- SQL script files C:\OGG\dirsql: created
- Database definitions files C:\OGG\dirdef: created
- Extract data files C:\OGG\dirdat: created
- Temporary files C:\OGG\dirtmp: created
- Stdout files C:\OGG\dirout: created
-
- GGSCI (WIN-UGCMTTOTKE7) 2>exit
退出后,切换到OGG目录下
- C:\OGG>INSTALL ADDSERVICE
- Service 'GGSMGR' created.
-
- Install program terminated normally.
- C:\OGG>
配置ODBC数据源
- Microsoft SQL Server ODBC 驱动程序版本 06.01.7601
-
-
- 数据源名称: oa_test
- 数据源描述:
- 服务器: WIN-UGCMTTOTKE7
- 数据库: (Default)
- 语言: (Default)
- 翻译字符数据: Yes
- 日志长运行查询: No
- 日志驱动程序统计: No
- 使用区域设置: No
- 预定义的语句选项: 在断开时删除临时存储过程
- 使用故障转移服务器: No
- 使用 ANSI 引用的标识符: Yes
- 使用 ANSI 的空值,填充和警告: Yes
- 数据加密: No
配置OGG抽取进程
- GGSCI (WIN-UGCMTTOTKE7) 8> dblogin sourcedb oa_test,userid yunwei,password "********"
-
- 2014-07-21 14:39:28 INFO OGG-03036 Database character set identified as windows-936. Locale: zh_Hans_CN
-
- 2014-07-21 14:39:28 INFO OGG-03037 Session character set identified as GBK.
- Successfully logged into database.
- GGSCI (WIN-UGCMTTOTKE7) 12> add trandata dbo.TEmployees
-
-
- 2014-07-21 14:43:51 WARNING OGG-01483 The key for table [oa_test.dbo.TEmployees] contains one or more variable length
- columns. These columns may not have their pre-images written to the transaction log during updates. <span style="color:#ffff99;"> Please use KEYCOLS</span>
- to specify a key for Oracle GoldenGate to use on this table.
-
-
- Logging of supplemental log data is enabled for table dbo.TEmployees
-
-
- GGSCI (WIN-UGCMTTOTKE7) 13> add trandata dbo.TRecords
-
-
- 2014-07-21 14:46:04 WARNING OGG-01483 The key for table [oa_test.dbo.TRecords] contains one or more variable length co
- lumns. These columns may not have their pre-images written to the transaction log during updates. Please use KEYCOLS to specify a key for Oracle GoldenGate to use on this table.
-
-
- Logging of supplemental log data is enabled for table dbo.TRecords
-
-
- GGSCI (WIN-UGCMTTOTKE7) 14> edit params oa
- defsfile c:\ogg\dirdef\oa.def
- sourcedb oa_test,userid yunwei,password "******"
- table dbo.TEmployees;
- table dbo.TRecords;
- C:\OGG>defgen paramfile c:\ogg\dirprm\oa.prm
将新生成的def文件拷贝到目标端!
查看mssql数据库处于完全恢复模式。
- GGSCI (WIN-UGCMTTOTKE7) 3> edit param mgr
填写如下内容:
port 7809
- GGSCI (WIN-UGCMTTOTKE7) 5> start mgr
-
- Starting Manager as service ('GGSMGR')...
- Service started.
- GGSCI (WIN-UGCMTTOTKE7) 8> info all
-
- Program Status Group Lag at Chkpt Time Since C
- MANAGER RUNNING
-
- GGSCI (WIN-UGCMTTOTKE7) 9> add extract emssql,tranlog,begin now
- EXTRACT added.
-
- GGSCI (WIN-UGCMTTOTKE7) 10> add rmttrail ./dirdat/ms,extract emssql
- RMTTRAIL added.
- GGSCI (WIN-UGCMTTOTKE7) 11> edit params emssql
编辑内容如下:
EXTRACT EMSSQL
SOURCEDB oa_test,userid yunwei,password "******"
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.2.34, MGRPORT 7809
RMTTRAIL ./dirdat/ms
TABLE dbo.TEmployees;
TABLE dbo.TRecords;
执行全库备份。然后启动进程。并且 trunc. log on chkpt要设置为false
可参考mysql版本的OGG安装步骤,大同小异
配置复制进程:
- GGSCI (localhost.localdomain) 2> dblogin sourcedb oa@127.0.0.1:3306,userid root,password xxxx
- Successfully logged into database.
- GGSCI (localhost.localdomain) 3> add replicat rmysql,exttrail ./dirdat/ms,nodbcheckpoint
- REPLICAT added.
-
- GGSCI (localhost.localdomain) 4> edit params rmysql
填写如下内容:
replicat rmysql
TARGETDB oa@127.0.0.1:3306,userid root,password xxx
assumetargetdefs
sourcedefs ./dirdef/oa.def
reperror default,discard
discardfile ./dirrpt/rmysql.dsc,append,megabytes 100
MAP dbo.TEmployees,TARGET oa.temployees,keycols(Employee_ID);
MAP dbo.TRecords,TARGET oa.trecords,keycols(Record_ID),colmap(usedefaults,RinOut=inOut);
至此!配置完成。
接下来就是进行insert update delete测试。
小插曲:
Sql server表中的bit数据类型在抓换到mysql中时为bool(tinyint)类型,这时候ogg会报错,提示类型不匹配。需要手动将mysql中的相关类型设置为char(1)就OK了。