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
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 ;
definerthat 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 dev.mysql.com/doc/refman/5.6/en/federated-storage-engine.html