otter全量同步

港控/mmm° 2021-11-23 11:52 442阅读 0赞

**原理**：使用canel读取了源库的retl\_buffer操作binlog来进行实现的，所以在使用此功能时，channel必须处于工作状态。  
**步骤一**：在otter配置otter同步的库/表。  
配置好channel—>pipeline—>映射关系列表，canal  
**步骤二**：执行以下sql  
/\*  
供 otter 使用， otter 需要对 retl.\* 的读写权限，以及对业务表的读写权限  
1. 创建database retl  
\*/  
CREATE DATABASE retl;

/* 2. 用户授权 给同步用户授权 */
    CREATE USER retl@'%' IDENTIFIED BY 'retl';
    GRANT USAGE ON *.* TO `retl`@'%';
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `retl`@'%';
    GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `retl`.* TO `retl`@'%';
    /* 业务表授权，这里可以限定只授权同步业务的表 */
    GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `retl`@'%';  
    
    /* 3. 创建系统表 */
    USE retl;
    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();

**步骤三**：  
把你想要同步表的表名及记录ID插入到retl\_buffer表即可。  
insert into retl.retl\_buffer(ID,TABLE\_ID, FULL\_NAME,TYPE,PK\_DATA,GMT\_CREATE,GMT\_MODIFIED) (select null,0,’ s c h e m a . t a b l e schema.table schema.table’,‘I’,id,now(),now() from  s c h e m a . t a b l e schema.table schema.table);  
如果针对多主键时，对应的PK\_DATA需要将需要同步表几个主键按照(char)1进行拼接。

注意：  
1、同步表必须有主键。  
2、需要先配置同步库/表，再往retl\_buffer插入数据才能成功同步。  
3、使用retl\_buffer type 设置为 I 时会先判断是否该id已有记录有则进行更新，没有则进行插入操作。  
4、sql执行报错 1067，解决如下：  
show variables like ‘sql\_mode’  
（1）临时修改：  
set session sql\_mode=‘ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’;  
（2）永久修改：  
可以直接修改my.cnf文件  
在\[mysqld\]下面添加如下列：  
sql\_mode=ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION

参考：https://blog.csdn.net/Frank\_Drebin/article/details/90170213