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 ;