Mysql 8.0 OGG21C 安装使用 mysql数据库sql

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=0server_id= 1 log_bin= mysql-bin expire_logs_days= 1 binlog_format= row binlog_row_metadata=https://www.it610.com/article/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=0server_id= 2 log_bin= mysql-bin expire_logs_days= 1 binlog_format= row binlog_row_metadata=https://www.it610.com/article/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), MySQLon 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 mgrPORT 17809 DYNAMICPORTLIST 17810-17909 AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3 PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

配置EXTRACT进程

GGSCI (mysql) 38> edit param EXM8extract 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 REP252replicat 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 allProgramStatusGroupLag at ChkptTime Since ChkptMANAGERRUNNING EXTRACTRUNNINGEXM800:00:0000:00:08 REPLICATRUNNINGREP25200:00:0000: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 -ADatabase changedmysql> 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 -ADatabase 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官方手册
【Mysql 8.0 OGG21C 安装使用】https://docs.oracle.com/en/mi...