1. 下载 mysql and goldengate for mysql:
2. 安装：MYSQL :
rpm -ivh /download/MySQL-server-advanced-5.6.13-1.rhel5.i386.rpm
rpm -ivh /download/MySQL-client-advanced-5.6.13-1.rhel5.i386.rpm
3. 安装goldengate
$ Su – oracle $ mkdir -p /opt/app/ggs/11.2
$ chown -R oracle:oinstall /opt/app/ggs/11.2 $ chmod -R 775 /opt/app/ggs/11.2
$ cd /opt/app/ggs/11.2
$ unzip V32420-01.zip
$ tar xvf ggs_Linux_x86_MySQL_32bit.tar
cd /opt/app/ggs/11.2
[oracle@dg2 11.2]$./ggsci
4 配置源端服务器
4.1 在源端服务器配置好GOLDENGATE
GGSCI (dg1) 1> create subdirs
Creating subdirectories under current directory /opt/app/ggs/11.2
Parameter files /opt/app/ggs/11.2/dirprm: already exists
Report files /opt/app/ggs/11.2/dirrpt: created
Checkpoint files /opt/app/ggs/11.2/dirchk: created
Process status files /opt/app/ggs/11.2/dirpcs: created
SQL script files /opt/app/ggs/11.2/dirsql: created
Database definitions files /opt/app/ggs/11.2/dirdef: created
Extract data files /opt/app/ggs/11.2/dirdat: created
Temporary files /opt/app/ggs/11.2/dirtmp: created
Stdout files /opt/app/ggs/11.2/dirout: created
GGSCI (dg1) 2>
4.2 建立用户,并赋权限：
主库：
SQL> create tablespace ggs_tbs datafile '/opt/app/oracle/oradata/DB1/ggs_tbs01.dbf' size 50m;
SQL> create user ggs identified by ggs default tablespace ggs_tbs;
User created.
SQL> grant create session,alter session to ggs;
Grant succeeded.
SQL> grant execute on utl_file to ggs;
Grant succeeded.
SQL> grant select any dictionary, select any table to ggs;
SQL> grant alter any table to ggs;
Grant succeeded.
SQL> grant flashback any table to ggs;
Grant succeeded.
SQL> grant select any transaction to ggs;
Grant succeeded.
SQL> grant sysdba to ggs;
Grant succeeded.
SQL> grant create table,insert any table,lock any table to ggs;
Grant succeeded.
SQL> grant execute on dbms_flashback to ggs;
Grant succeeded.
4.3 源、目标库打开辅助日志
SQL> alter database force logging;
alter database force logging
*
ERROR at line 1:
ORA-12920: database is already in force logging mode
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
4.4 源库支持sequence
在源库、目标库上执行：
GGSCI (dg2) 2> edit params ./globals
在统计模式下输入并保存：ggschema ggs
在SQLPLUS 下去运行：
sql> @sequence.sql 根据提示输入：gg
4.5 支持ddl复制
4.5.1 主库配置
cd /ggs/11.2
sqlplus / as sysdba
sql> alter system set recyclebin=off deferred scope=both; #必须，针对ddl复制
sql> @marker_setup.sql prompt: ggs
sql> @ddl_setup.sql prompt: ggs
sql> @role_setup.sql
sql> grant GGS_GGSUSER_ROLE to ggs;
SQL> @ddl_enable.sql
10g需要安装dbms_share_pool包：
sql> @?/rdbms/admin/dbmspool.sql sql> @ddl_pin ggs;
4.6 配置MANAGER
主库、目标库配置：
[oracle@dg1 11.2]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (dg1) 1> edit params mgr
PORT 7809
GGSCI (dg1) 2> start mgr
Manager started.
GGSCI (dg1) 3> info mgr
Manager is running (IP port dg1.7809).
GGSCI (dg1) 4>
4.7 添加extract
GGSCI (dg1) 1> add extract ext2my,tranlog,begin now
EXTRACT added.
GGSCI (dg1) 2> add exttrail ./dirdat/my,extract ext2my,megabytes 100
EXTTRAIL added.
GGSCI (dg1) 3> edit params ext2my
EXTRACT ext2my
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS EXCLUDEUSER ggs
RMTHOST 192.168.10.85, MGRPORT 7809
RMTTRAIL ./dirdat/my
TABLE hr.* ;
GGSCI (dg1) 5>start extract EXT2MY
GGSCI (dg1) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT ABENDED EXT1 00:00:00 17:48:59
EXTRACT STOPPED EXT2MY 00:00:00 00:00:46
REPLICAT ABENDED REP2 00:00:00 17:48:45
4.8 添加对象定义文件
GGSCI (dg1) 7> edit param defgen
GGSCI (dg1) 9> view param defgen
defsfile ./dirdef/ext2my.def
userid ggs,password ggs
table hr.*;
生成定义文件并拷贝到目标服务器
在 $GGHOME 目录下执行
[ -bash-4.1]$ ./defgen paramfile dirprm/defgen.prm
[oracle@dg1 11.2]$ cat dirdef/ext2my.def
*+- Defgen version 2.0, Encoding UTF-8
*
* Definitions created/modified 2013-08-31 16:49
*
* Field descriptions for each column entry:
*
* 1 Name
* 2 Data Type
* 3 External Length
* 4 Fetch Offset
* 5 Scale
* 6 Level
* 7 Null
* 8 Bump if Odd
* 9 Internal Length
* 10 Binary Length
* 11 Table Length
* 12 Most Significant DT
* 13 Least Significant DT
* 14 High Precision
* 15 Low Precision
* 16 Elementary Item
* 17 Occurs
* 18 Key Column
* 19 Sub Data Type
*
Database type: ORACLE
Character set ID: windows-936
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
*
Definition for table HR.DEPARTMENT
Record length: 104
Syskey: 0
Columns: 3
DEP_ID 134 11 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 3
DEP_NAME 64 30 12 0 0 1 0 30 30 0 0 0 0 0 1 0 0 0
DEP_DESC 64 50 48 0 0 1 0 50 50 0 0 0 0 0 1 0 0 0
End of definition
*
Definition for table HR.EMPLOYEE
Record length: 48
Syskey: 0
Columns: 2
EMP_ID 134 11 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 3
EMP_NAME 64 30 12 0 0 1 0 30 30 0 0 0 0 0 1 0 0 0
End of definition
*
Definition for table HR.TST
Record length: 12
Syskey: 0
Columns: 1
ID 134 8 0 0 0 1 0 8 8 8 0 0 0 0 1 0 1 3
End of definition
scp ./dirdef/ext2my.def root@192.168.61.217:/ggmysql/dirdef/
5 配置目标端服务器
5.1 配置MYSQL SERVER
# mysql -u root -p password '123456'
mysql> create database hr;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hr |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysql
mysql>update user set host='%' where host='localhost';
mysql>flush privileges;
5.2 配置目标端
GGSCI (ractdg3) 1> create subdirs
Creating subdirectories under current directory /opt/app/ggs/11.2
Parameter files /opt/app/ggs/11.2/dirprm: already exists
Report files /opt/app/ggs/11.2/dirrpt: created
Checkpoint files /opt/app/ggs/11.2/dirchk: created
Process status files /opt/app/ggs/11.2/dirpcs: created
SQL script files /opt/app/ggs/11.2/dirsql: created
Database definitions files /opt/app/ggs/11.2/dirdef: created
Extract data files /opt/app/ggs/11.2/dirdat: created
Temporary files /opt/app/ggs/11.2/dirtmp: created
Stdout files /opt/app/ggs/11.2/dirout: created
GGSCI (ractdg3) 2>start mgr
GGSCI (ractdg3) 3>info mgr
GGSCI (ractdg3) 5> edit params mgr
GGSCI (ractdg3) 10> view params mgr
Port 7809
--DYNAMICPORTLIST 7840-7939
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
--LAGREPORTHOURS 1
--LAGINFOMINUTES 30
--LAGCRITICALMINUTES 45
GGSCI (ractdg3) 10> edit param globals
GGSCI (ractdg3) 11> view param globals
checkpointtable hr.checkpoint
GGSCI (ractdg3) 12>
5.3创建checkpoint表
ggsci>dblogin sourcedb hr userid root
(这里纠结了很久，MYSQL 登录总是报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
vi /usr/my.cnf
添加以下内容后
socket =/tmp/mysql.sock
在ggsci 登录 没有问题了，但在LINUX下，登录又报ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
在LINUX下登录，只好先指定 mysql -u root -p -S '/tmp/mysql.sock'
进行登录，后面再另行解决。
)
ggsci>add checkpointtable hr.checkpoint
ggsci> edit params ./GLOBALS #GLOBALS必须大写，编辑GLOBALS需要推出ggsci再进入，输入：
CHECKPOINTTABLE hr.checkpoint
查看CHECKPOINT TABLE
mysql> use hr
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------+
| Tables_in_hr |
+--------------+
| checkpoint |
+--------------+
1 row in set (0.00 sec)
mysql>
5.4 创建replicat：
GGSCI (dg2) 10> add replicat rep2my,exttrail ./dirdat/my,checkpointtable hr.checkpoint
REPLICAT added.
GGSCI (ractdg3) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP2MY 00:00:00 00:00:02
GGSCI (ractdg3) 9> edit params rep2my
GGSCI (ractdg3) 10> view params rep2my
REPLICAT rep2my
sourcedefs ./dirdef/ext2my.def
--APPLYNOOPUPDATES
--HANDLECOLLISIONS
SOURCEDB hr,userid root,password 123456
discardfile ./dirrpt/rep2my_discard.log,megabytes 10
MAP hr.*, TARGET hr.*;
GGSCI (ractdg3) 19> start REPLICAT REP2MY
GGSCI (ractdg3) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP2MY 00:00:00 00:00:05
5.5 添加同步表并测试
[root@ractdg3 11.2]# mysql -u root -p -S '/tmp/mysql.sock'
mysql> use hr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> create table tst(id integer,constraint pk_tst_id primary key (id));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------+
| Tables_in_hr |
+--------------+
| checkpoint |
| tst |
+--------------+
2 rows in set (0.00 sec)
mysql> create table TST(ID INT,CONSTRAINT PK_TST_ID PRIMARY KEY(ID));
mysql> DESC TST;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from TST;
+----+
| ID |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
看到数据已同步过来了，MYSQL 中，表名有大小写区分，开始报表名TST找不到，是在建立表中使用的是tablename:tst
阅读(1505) | 评论(0) | 转发(0) |
