mysql之间同步数据

一、业务场景说明

两台mysql服务器

第一台：A-E10

第二台：B-T100

A-E10的条码数据需要同步到B-T100上，并且要做新老料号的替换。

一、方案

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

1. A-E10 条码档写入的时候，触发器写入映射表。
2. A-E10 的映射表和 B-T100 的映射表做连接。
3. 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

• 查看任务事件的状态 show variables like 'event_scheduler';
• 如果没有开启，开启它 set global event_scheduler=on;

注意 B-T100 中的srm_t100映射表不要修改，会把 A-E10 的srm_t100表也同步改掉

B-T100 中的srm_t100表结构无法修改，如果 A-E10 的srm_t100表结构发生变化，请及时重建B-T100 的srm_t100