MYSQL - How to insert data to another server DB table

Question

I am trying to insert data to Second server's DB table after inserted to First server' table like below structure,

Second server :

use SecondServerDB;

CREATE TABLE user (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

After created table in Second sever, I have created table in First server like below,

First server :

use FirstServerDB;

CREATE TABLE remote_user2 (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` smallint(6) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://MysqlUserName:MysqlUserPassword@SecondServer.MyDomain.com:3306/SecondServerDB/user';

After created FEDERATED table in second server, Created triggers in same server like below,

   DELIMITER $$

    CREATE TRIGGER remote_insert_Testing AFTER INSERT ON remote_user2 FOR EACH ROW
    BEGIN
      INSERT INTO user (ID,name, age) VALUES (NEW.ID,NEW.name, NEW.Age);
    END $$

DELIMITER ;

If i run insert query like below,

INSERT INTO remote_user2 (ID,name, age) VALUES (1,'Name',27);

Then it shows, Error Code: 1146. Table 'FirstServerDB.user' doesn't exist.

where am i doing mistake?

Note : I googled and found out What i am trying is possible. How to create trigger to insert data to a database on another server for possible

Answer 1

Try using the fully qualified table names in your trigger:

DELIMITER $$

CREATE TRIGGER remote_insert_Testing
AFTER INSERT ON FirstServerDB.remote_user2 FOR EACH ROW
BEGIN
    INSERT INTO SecondServerDB.user (ID,name, age)
    VALUES (NEW.ID, NEW.name, NEW.Age);
END $$
DELIMITER ;

If you don't mind moving the data through a normal query, you can try the following:

INSERT INTO SecondServerDB.user (ID, name, age)
SELECT ID, name, age
FROM FirstServerDB.remote_user2