How to create trigger to insert data to a database on another server

Question

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

Answer 1

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.

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 ;