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

This could be done easily provided the trigger running on a user should have access to the both databases. And the trigger is easy to create check here http://stackoverflow.com/questions/8910617/using-trigger-to-update-table-in-another-database , the example is after update you can change it to after insert. — Abhik Chakraborty, Oct 22 '14 at 08:51
Thanks for reply, i Know how create trigger but I dont know how to connect to another database table from trigger — GeoVIP, Oct 22 '14 at 08:53
well you need the mysql user having access to both the db and then create the trigger to execute on that user `definer` that should do the job. But this works only on the same host. Since your DB are in different hosts you may need some sort of application layer to do the job using REST or CURL or SOAP. Or may be FEDERATED Storage Engine http://dev.mysql.com/doc/refman/5.6/en/federated-storage-engine.html — Abhik Chakraborty, Oct 22 '14 at 08:56
I read another question/answers on stackoverflow and what about federated engine ? — GeoVIP, Oct 22 '14 at 08:59
Yes I updated my last comment to include about FEDERATED Storage Engine — Abhik Chakraborty, Oct 22 '14 at 09:00

score 11 · Accepted Answer · answered Oct 22 '14 at 09:27

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 ;

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

1 Answers1

Linked