1.技术说明
以逻辑改变记录(logical change records,即:LCR)的方式接收变化数据。
1.1 GoldenGate经典模式架构
日志解析是在数据库外完成
1.2 GoldenGate集成模式架构
2. 环境说明
2.1 GoldenGate版本
[mysql@test1 ogg_target]$ ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51 Operating system character set identified as UTF-8.
[oracle@test1 ogg_source]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
2.2 主机说明
IP | 主机 |
---|---|
192.168.1.45 | test1 |
192.168.1.46 | test2 |
在45和46之间配置了MySQL主从复制,45为主,46为从
在45机器上安装了Oracle 12c多租户,OGG从45上的Oracle复制到45上的MySQL,MySQL再由46(从)同步45(主)
2.3 数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0
root@localhost[(none)]> select version(); +------------+ | version() | +------------+ | 5.7.17-log | +------------+ 1 row in set (0.02 sec)
2.4 GoldenGate安装位置
安装位置 | 安装用户 |
---|---|
源:test1主机:/data/ogg_source | 属主:oracle:oinstall |
目标:test1主机:/data/ogg_target | 属主:mysql:mysql |
3. GoldenGate初始化
3.1 目标端(MySQL)
GGSCI (test1) 4> create subdirs Creating subdirectories under current directory /data/ogg_target Parameter files /data/ogg_target/dirprm: created Report files /data/ogg_target/dirrpt: created Checkpoint files /data/ogg_target/dirchk: created Process status files /data/ogg_target/dirpcs: created SQL script files /data/ogg_target/dirsql: created Database definitions files /data/ogg_target/dirdef: created Extract data files /data/ogg_target/dirdat: created Temporary files /data/ogg_target/dirtmp: created Credential store files /data/ogg_target/dircrd: created Masterkey wallet files /data/ogg_target/dirwlt: created Dump files /data/ogg_target/dirdmp: created
GGSCI (test1) 5> edit params mgr Port 7810 DYNAMICPORTLIST 7840-7849 GGSCI (test1) 6> start mgr Manager started. GGSCI (test1) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
GGSCI (test1) 8> dblogin sourcedb test userid root Password: 2017-02-24 16:20:12 WARNING OGG-00769 MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE , USER root, PORT 3306.
添加链接文件
[root@test1 ~]# ln -s /data/mysql/mysql.sock /tmp/mysql.sock
或者设置环境变量
$ export MYSQL_UNIX_PORT=/data/mysql/mysql.sock
重新登录
GGSCI (test1) 10> dblogin sourcedb test userid root Password: Successfully logged into database. GGSCI (test1 DBLOGIN as root) 11> add checkpointtable test.checkpoint Successfully created checkpoint table test.checkpoint.
3.2 源端(Oracle 12c多租户)
SQL> alter system set log_archive_dest_1='location=/data/oracle/arch' scope=both; System altered. SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /data/oracle/arch Oldest online log sequence 299 Current log sequence 301 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 6442450944 bytes Fixed Size 5298088 bytes Variable Size 1543506008 bytes Database Buffers 4882169856 bytes Redo Buffers 11476992 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED SQL> alter pluggable database all open; Pluggable database altered.
SQL> alter database add supplemental log data(primary key,unique) columns; Database altered.
SQL> ALTER DATABASE FORCE LOGGING; Database altered.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> create user c##ogg identified by ogg ; User created. SQL> grant dba to c##ogg container=all; Grant succeeded. SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##OGG',container=>'all'); PL/SQL procedure successfully completed.
SQL> alter system set enable_goldengate_replication=true scope=both; System altered.
4. 抽取样例表
4.1 源端(oracle)
$ sqlplus pei/123456@192.168.1.45:1621/pdb1 SQL> create table books 2 ( id int, 3 name varchar2(10), 4 primary key(id) 5 ); Table created.
4.2 目标端(MySQL)
[mysql@test1 ~]$ mysql -uroot -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost[(none)]> use test Database changed root@localhost[test]> create table books -> (id int, -> name varchar(10), -> primary key(id) -> ); Query OK, 0 rows affected (0.07 sec)
5. 生成模型定义文件
由于是Oracle到MySQL的复制,所以在目标端必须要用源端的模型定义文件
在源端生成定义文件如下:
GGSCI (test1) 1> edit params defgendefsfile ./dirdef/defgen.defs userid c##ogg, password ogg SOURCECATALOG pdb1 TABLE pei.books; [oracle@test1 ogg_source]$ ./defgen paramfile ./dirprm/defgen.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux, x64, 64bit (optimized), Oracle 12c on Dec 11 2015 17:22:38 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. Starting at 2017-02-25 10:15:44 *********************************************************************** Operating System Version: Linux Version #1 SMP Mon Oct 24 10:22:33 EDT 2016, Release 2.6.32-642.6.2.el6.x86_64 Node: test1 Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 55941 *********************************************************************** ** Running with the following parameters ** *********************************************************************** defsfile ./dirdef/defgen.defs userid c##ogg, password *** SOURCECATALOG pdb1 TABLE pei.books; Default source catalog name pdb1 will be used for table specification pei.books. Retrieving definition for PDB1.PEI.BOOKS. Definitions generated for 1 table in ./dirdef/defgen.defs.
把生成的定义文件复制到OGG目标端
[root@test1 ~]# cp /data/ogg_source/dirdef/defgen.defs /data/ogg_target/dirdef/ [root@test1 ~]# chown mysql:mysql /data/ogg_target/dirdef/defgen.defs
6. 配置源端
GGSCI (test1) 4> create subdirs Creating subdirectories under current directory /data/ogg_source Parameter files /data/ogg_source/dirprm: created Report files /data/ogg_source/dirrpt: created Checkpoint files /data/ogg_source/dirchk: created Process status files /data/ogg_source/dirpcs: created SQL script files /data/ogg_source/dirsql: created Database definitions files /data/ogg_source/dirdef: created Extract data files /data/ogg_source/dirdat: created Temporary files /data/ogg_source/dirtmp: created Credential store files /data/ogg_source/dircrd: created Masterkey wallet files /data/ogg_source/dirwlt: created Dump files /data/ogg_source/dirdmp: created
GGSCI (test1) 5> edit params mgr Port 7809 DYNAMICPORTLIST 7830-7839 GGSCI (test1) 6> start mgr Manager started. GGSCI (test1) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
GGSCI (test1) 12> dblogin userid c##ogg@pdb1, password ogg Successfully logged into database PDB1. GGSCI (test1 as c##ogg@t12c/PDB1) 14> add trandata pdb1.pei.books Logging of supplemental redo data enabled for table PDB1.PEI.BOOKS. TRANDATA for scheduling columns has been added on table 'PDB1.PEI.BOOKS'. TRANDATA for instantiation CSN has been added on table 'PDB1.PEI.BOOKS'.
GGSCI (test1) 8> add extract ex_ora,integrated tranlog,begin now EXTRACT added. GGSCI (test1) 9> add exttrail ./dirdat/my,extract ex_ora,megabytes 100 EXTTRAIL added. GGSCI (test1) 11> edit param ex_ora EXTRACT ex_ora SETENV (ORACLE_SID='t12c') userid c##ogg, password ogg EXTTRAIL ./dirdat/my TABLE pdb1.pei.books; -- SOURCECATALOG pdb1 -- TABLE pei.books; GGSCI (test1 as c##ogg@t12c/PDB1) 43> dblogin userid c##ogg, password ogg Successfully logged into database CDB$ROOT. GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 44> register extract ex_ora database container(pdb1,pdb2) 2017-02-24 17:15:30 INFO OGG-02003 Extract ex_ora successfully registered with database at SCN 5093503.
GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 48> add extract dp_ora, exttrailsource ./dirdat/my EXTRACT added. GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 49> add rmttrail ./dirdat/my, extract dp_ora, megabytes 100 RMTTRAIL added. GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 50> edit param dp_ora Extract dp_ora Userid c##ogg,password ogg rmthost 192.168.1.45, mgrport 7810 rmttrail ./dirdat/my passthru TABLE pdb1.pei.books;
GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 51> start ex_ora GGSCI (test1 as c##ogg@t12c/CDB$ROOT) 52> start dp_ora
7. 配置目标端
GGSCI (test1 DBLOGIN as root) 2> add replicat rp_my,exttrail ./dirdat/my, checkpointtable test.checkpoint REPLICAT added. GGSCI (test1 DBLOGIN as root) 3> edit param rp_myREPLICAT rp_my SETENV (MYSQL_UNIX_PORT=/data/mysql/mysql.sock) targetdb test@localhost:3306 userid root password 123456 sourcedefs /data/ogg_target/dirdef/defgen.defs SQLEXEC "select CURRENT_TIME();" EVERY 10 MINUTES REPORT AT 6:00 discardfile ./dirrpt/rp_my.dsc,megabytes 10 MAP pdb1.pei.books, TARGET test.books;
GGSCI (test1) 21> start rp_my Sending START request to MANAGER ... REPLICAT rp_my starting