使用otter实现MySQL跨机房双向同步

2020/03 作者：ihunter 0 次 0

在要进行双向同步的数据库执行以下语句（记得修改密码）（主从库都要执行）

/* 供 otter 使用， otter 需要对 retl.* 的读写权限，以及对业务表的读写权限*/

/* 1. 创建database retl */
    CREATE DATABASE retl;

/* 2. 用户授权 给同步用户授权 */
    

/* 业务表授权，这里可以限定只授权同步业务的表 */
    

/* 3. 创建系统表 */
    
    USE otter;
    DROP TABLE IF EXISTS retl.retl_buffer;
    DROP TABLE IF EXISTS retl.retl_mark;
    DROP TABLE IF EXISTS retl.xdual;

    CREATE TABLE retl_buffer
    (
     ID BIGINT(20) AUTO_INCREMENT,
     TABLE_ID INT(11) NOT NULL,
     FULL_NAME varchar(512),
     TYPE CHAR(1) NOT NULL,
     PK_DATA VARCHAR(256) NOT NULL,
     GMT_CREATE TIMESTAMP NOT NULL,
     GMT_MODIFIED TIMESTAMP NOT NULL,
     CONSTRAINT RETL_BUFFER_ID PRIMARY KEY (ID)
    )  ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE retl_mark
    (
     ID BIGINT AUTO_INCREMENT,
     CHANNEL_ID INT(11),
     CHANNEL_INFO varchar(128),
     CONSTRAINT RETL_MARK_ID PRIMARY KEY (ID)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    CREATE TABLE xdual (
      ID BIGINT(20) NOT NULL AUTO_INCREMENT,
      X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/* 4. 插入初始化数据 */
    INSERT INTO retl.xdual(id, x) VALUES (1,now()) ON DUPLICATE KEY UPDATE x = now();

数据库配置（主从库都要修改，请保证server-id不相同）

在需要双向同的数据库开启binlog,并设置为row模式
my.cnf (/etc/mysql/my.cnf) 的 [mysqld] 节点下启用binlog

# 开启binlog
log-bin=mysql-bin
# 设置row模式
binlog-format=ROW
character_set_server=utf8
# 双向同步保证id不相同，避免无法辨别数据库而回环
server-id=1
# 允许外部访问
# bind-address = 0.0.0.0

otter账号授权
授权需要双向授权 (主库)

# 如果有多个机器ip请全部授权

# 授予主从复制权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO otter@’%’;
# 授予retl 库操作权限
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `retl`.* TO otter@'ip';
# 授予操作库增删查改，alter，index权限 用于更新数据库记录
GRANT update, insert, delete, update, alter, indexON mytest.person_info TO otter@'ip';
flush PRIVILEGES;

授权需要双向授权 (从库)

# 如果有多个机器ip请全部授权

# 授予主从复制权限
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO otter@’%’;
# 授予retl 库操作权限
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `retl`.* TO otter@'ip';
# 授予操作库增删查改，alter，index权限 用于更新数据库记录
GRANT update, insert, delete, update, alter, indexON mytest.person_info TO otter@'ip';
flush PRIVILEGES;

配置数据源