mysql之间同步数据

一、业务场景说明

两台mysql服务器
第一台：A-E10
第二台：B-T100
A-E10的条码数据需要同步到B-T100上，并且要做新老料号的替换。

一、方案

利用mysql的federated引擎及触发器、定时任务实现：

A-E10 条码档写入的时候，触发器写入映射表。
A-E10 的映射表和 B-T100 的映射表做连接。
B-T100 数据库做定时任务，写入条码表，实现信息同步。

核心流程：mysql之间做dblink，利用FEDERATED引擎。

二、实现

1、连接数据库

xshell中输入mysql -uroot -p(root表示用户名)，会车后输入密码，出现

表示连接成功

2、同步tmp表

a. 开启FEDERATED引擎(只需要开启 B-T100 端的即可)

创建FEDERATED引擎表，则目标端实例要开启FEDERATED引擎。从MySQL5.5开始FEDERATED引擎默认安装，只是没有启用。

进入命令行输入

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
 9 rows in set (0.00 sec)

可以看到FEDERATED行状态为NO

在配置文件[mysqld]中加入一行：federated（配置文件一般在/etc/my.cnf；federated一定要小写）
然后重启数据库service mysql restart，FEDERATED引擎就开启了

b. 建立中间表

A-E10

建立映射表

CREATE TABLE `srm_t100` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'id',
`barcode` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '原始条码',
`barcode_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '条码编号',
`gen_moment` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '条码产生时机点\n',
`source_no` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '来源单号\n',
`source_item_no` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '来源项次',
PRIMARY KEY (`id`),
UNIQUE KEY `id_pk` (`id`) USING HASH
) ENGINE=INNODB

B-T100

step1：建立连接

create SERVER t100_link 
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'zhilink', HOST '192.168.0.6', DATABASE 'wmstest',PORT 3306,Password 'Gsyywm@2020.com');

(删除链接drop server t100_link)

step2：建立映射表（要与A-E10的表结构一致）

CREATE TABLE `srm_t100` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'id',
`barcode` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '原始条码',
`barcode_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '条码编号',
`gen_moment` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '条码产生时机点\n',
`source_no` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '来源单号\n',
`source_item_no` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '来源项次',
PRIMARY KEY (`id`),
UNIQUE KEY `id_pk` (`id`) USING HASH
) ENGINE=FEDERATED CONNECTION='t100_link/srm_t100'

注意区别

查看 B-T100 中的srm_t100，数据与 A-E10 中的一致

3、A-E10上实现触发器，条码表更新后写入到中间表

create trigger T100_zhilink 
after insert
on srm_barcode_detail

for each row 
begin

DECLARE new_item_no VARCHAR(40)character set utf8;
DECLARE l_cnt decimal(10,0);
    
    /*  从srm_barcode_detail 中同步到srm_t100*/
    insert into srm_t100 select * from srm_barcode_detail where id=new.id;
    
    /* 更新srm_t100 中udf01 为N， N表示未同步 */
    update srm_t100 set udf01 = 'N' where id = new.id;
    
    /* 变更老系统新料号 */
    select count(1) INTO l_cnt FROM item_file where old_item = new.item_no;
    IF l_cnt > 0 then 
        SELECT new_item INTO new_item_no FROM item_file where old_item = new.item_no;
        update srm_t100 set item_no = new_item_no where id = new.id;
    END IF;     
end

4、B-T100上通过定时任务，每分钟插入数据表

DROP EVENT IF EXISTS JOB_ALARM;  
CREATE EVENT JOB_ALARM  
 ON SCHEDULE EVERY 1 MINUTE  
DO  
  BEGIN  
    INSERT INTO srm_barcode_detail select * from srm_t100 where 1=1 and id not in (select id from srm_barcode_detail);
END