如何创建触发器以将数据插入另一台服务器上的数据库 [英] How to create trigger to insert data to a database on another server

问题描述

我在不同的主机上有2个mysql数据库，想在将数据插入到另一个数据库中后将其触发.我是mysql的新手，在sql server中我可以创建链接服务器并完成它.但是如何在mysql中做呢? 两个数据库都有相似的表

I have 2 mysql databases on different hosts, want trigger after insert data to one database it inserted to another . I'm new in mysql , in sql server I can create linked server and do it . But how to do in mysql ? Both databases have similar table

CREATE TABLE `tsttbl` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

推荐答案

要继续进行此操作，必须首先确保两个服务器具有相同的配置.为了完成您要执行的操作，可以使用FEDERATED 存储引擎，并与触发器配合使用，以允许每台服务器更新另一台服务器的数据库.

For you to be able to proceed with this, you must first ensure that both servers have identical configurations. And to accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

您需要创建与另一台服务器上的用户表联合的本地表.因此，如果一条记录已经存在于另一台服务器上，但是在此位置不存在，我们希望另一台服务器上的插入引发一个错误，该错误会阻止我们在此处创建记录...而不是在此处创建记录冲突的ID；

You need to create the local table that is federated to the user table on the other server. So, if a record already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the record here... as opposed to creating a record here with what would be a conflicting ID;

CREATE TABLE remote_user (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

然后您可以创建触发器；

Then you can create your trigger;

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (ID,name, age) VALUES (NEW.ID,NEW.name, NEW.Age);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user 
     SET ID= NEW.ID,
         name= NEW.name
         age = NEW.Age
   WHERE ID = OLD.ID;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
   DELETE FROM remote_user
   WHERE ID= OLD.ID;
END $$

DELIMITER ;

这篇关于如何创建触发器以将数据插入另一台服务器上的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！