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 ;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…