利用GoldenGate 21实现mysql8远程DDL同步

测试架构

配置

修改mysql8的配置my.cnf

[mysqld]
server_id=1
#binlog-ignore-db=oggddl
binlog_format=ROW
log-bin=mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
max_binlog_size=1073741824 
binlog_row-metadata=FULL  # for DDl replication in 8.0
#log_bin_basename=/var/lib/mysql/binlog

chown -R mysql.mysql /var/lib/mysql
service mysqld restart

创建ogg同步用户

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

ogg param参数配置

ogg extract

extract exm8
sourcedb testdb@192.168.7.91:3306, userid etl, password ogg
tranLogOptions altlogDest REMOTE
ddl include mapped
exttrail ./dirdat/m8
table testdb.*;

add ext exm8, tranlog, begin now
add exttrail ./dirdat/m8, ext exm8

replicat

replicat re57
targetdb testdb@192.168.7.20:3306, userid ogg, password ogg
map testdb.*, target testdb.*;

add rep re57, exttrail ./dirdat/m8, nodbcheckpoint

测试

db operation

mysql> create table tb2 (id int, name varchar(50), age int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tb2 values (1,'222',33) ,(2,'abc',44);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

ogg 同步信息

抽取进程

GGSCI> stats exm8, total

Sending STATS request to Extract group EXM8 ...
Start of statistics at 2021-08-30 16:37:42.
DDL replication statistics (for all trails):
*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  1.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to ./dirdat/m8:
Extracting from testdb.tb2 to testdb.tb2:

*** Total statistics since 2021-08-30 16:37:36 ***
    Total inserts                              2.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           2.00

End of statistics.

投递进程

GGSCI (ol76db DBLOGIN as ogg) 54> stats re57, total
Sending STATS request to Replicat group RE57 ...
Start of statistics at 2021-08-30 17:11:02.

DDL replication statistics:
*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  1.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

Replicating from testdb.tb2 to testdb.tb2:

*** Total statistics since 2021-08-30 17:10:52 ***
    Total inserts                              2.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           2.00

End of statistics.

小结

利用GoldenGate 21，可实现MySQL 8.0的远程同步，包括DDL同步，且不需要额外的DDL安装配置，该版本简化了MySQL之间的数据同步。如有必要，也可以从MySQL5.7同步到MySQL8.0，从而实现数据库的升级迁移。