Mysql 8.0 OGG21C 安装使用




  • OGG安装

    环境介绍,为了节省资源OGG我选择和原库安装在了同一台服务器


    1.1 解压ogg的安装包
    上传并解压mysql ogg安装包,无需安装解压即可使用

     

    # mkdir /ogg
    # unzip 213000_ggs_Linux_x64_MySQL_64bit.zip
    # tar -xvf ggs_Linux_x64_MySQL_64bit.tar

     

    1.2 Mysql数据库配置
    源库配置
    OGG21C可以使用基于日志的DDL复制,要求添binlog_row_metadata为full模式才可以实现

     

     

    # vi /etc/my.cnf
    [mysqld]
    datadir=/usr/local/mysql/data
    basedir=/usr/local/mysql
    socket=/tmp/mysql.sock
    user=mysql
    port=3306
    character-set-server=utf8mb4
    symbolic-links=0

     

    server_id = 1
    log_bin = mysql-bin
    expire_logs_days = 1
    binlog_format = row
    binlog_row_metadata=full

     

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    目标库配置

    [mysqld]
    datadir=/usr/local/mysql/data
    basedir=/usr/local/mysql
    socket=/tmp/mysql.sock
    user=mysql
    port=3306
    character-set-server=utf8mb4
    symbolic-links=0

    server_id = 2
    log_bin = mysql-bin
    expire_logs_days = 1
    binlog_format = row
    binlog_row_metadata=full

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    主备数据库创建同步用户并附权

    CREATE USER 'ogg'@'%' IDENTIFIED BY 'Sandata@123';
    GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

    1.3 OGG配置

    在21C的OGG中ogg可以单独部署并不需要每台服务器都安装,只要网络可达即可

     

    [root@mysql ogg]# ./ggsci

    Oracle GoldenGate Command Interpreter for MySQL
    Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
    Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
    Operating system character set identified as UTF-8.

    Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.

    GGSCI (mysql) 1> CREATE SUBDIRS
    Creating subdirectories under current directory /ogg
    Parameter file /ogg/dirprm: created.
    Report file /ogg/dirrpt: created.
    Checkpoint file /ogg/dirchk: created.
    Process status files /ogg/dirpcs: created.
    SQL script files /ogg/dirsql: created.
    Database definitions files /ogg/dirdef: created.
    Extract data files /ogg/dirdat: created.
    Temporary files /ogg/dirtmp: created.
    Credential store files /ogg/dircrd: created.
    Master encryption key wallet files /ogg/dirwlt: created.
    Dump files /ogg/dirdmp: created.

     

    配置mgr进程

    GGSCI (mysql) 36> edit param mgr

    PORT 17809
    DYNAMICPORTLIST 17810-17909
    AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
    PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

    配置EXTRACT进程

    GGSCI (mysql) 38> edit param EXM8

    extract exm8
    sourcedb wutong@192.168.2.251:3306, userid ogg, password Sandata@123
    tranLogOptions altlogDest REMOTE
    ddl include mapped
    exttrail ./dirdat/m8
    table wutong.*;

    GGSCI (mysql) 39> add ext exm8, tranlog, begin now
    GGSCI (mysql) 40> add exttrail ./dirdat/m8, ext exm8

    配置配置REPLICAT进程,在21C的版本中已经不用配置pump进程

    GGSCI (mysql) 2> edit param REP252

    replicat rep252
    targetdb wutong@192.168.2.252:3306, userid ogg, password Sandata@123
    DDLERROR DEFAULT IGNORE RETRYOP
    map wutong.*, target wutong.*;

    启动所有进程

    GGSCI (mysql) 3> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING
    EXTRACT RUNNING EXM8 00:00:00 00:00:08
    REPLICAT RUNNING REP252 00:00:00 00:00:00

    1.4 DDL、DML测试

    主库
    mysql> use wutong;
    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_wutong |
    +------------------+
    | test2 |
    +------------------+
    1 row in set (0.00 sec)

    目标库
    mysql> use wutong;
    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_wutong |
    +------------------+
    | test2 |
    +------------------+
    1 row in set (0.00 sec)

    主库
    mysql> create table sandata (id int,name varchar(20));
    Query OK, 0 rows affected (0.07 sec)

    mysql> insert into sandata values (1,'wutong');
    Query OK, 1 row affected (0.04 sec)

    mysql> select * from sandata
    -> ;
    +------+--------+
    | id | name |
    +------+--------+
    | 1 | wutong |
    +------+--------+
    1 row in set (0.00 sec)

    目标库
    mysql> show tables;
    +------------------+
    | Tables_in_wutong |
    +------------------+
    | test2 |
    +------------------+
    1 row in set (0.00 sec)

    mysql> show tables;
    +------------------+
    | Tables_in_wutong |
    +------------------+
    | sandata |
    | test2 |
    +------------------+
    2 rows in set (0.00 sec)

    mysql> select * from sandata;
    +------+--------+
    | id | name |
    +------+--------+
    | 1 | wutong |
    +------+--------+
    1 row in set (0.00 sec)

    参考文档oracle官方手册

    https://docs.oracle.com/en/middleware/goldengate/core/21.3/gghdb/using-oracle-goldengate-mysql.html

     

    Speak Your Mind

    *