Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2100890
  • 博文数量: 281
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2075
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(281)

分类: Mysql/postgreSQL

2014-11-05 18:59:08



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
阅读(1132) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~