Mysql – Is a MySQL stored procedure able to insert/update to a remote backup MySQL server

database-backupfederatedMySQLstored-procedures

Is it possible to create a MySQL stored procedure that can execute a given insert or update and then also have it connect to a 2nd server and run the same commands? I want to basically call one server and in real-time have it backup to the second server. Just a yah or ney would suffice unless you have a link or two handy or another solution.

Thank you for your time.
Aaron

Best Answer

You could but only under 3 specific conditions:

The second table you are updating on another server is:

  1. a MyISAM table
  2. identically the same table structure as in the first server
  3. represented in MySQL in the first server as storage engine FEDERATED

First, make sure you have the FEDERATED engine enabled in MySQL
Just run SHOW ENGINES;

If the FEDERATED storage engine does not exist or is disabled, read no further.

Otherwise, you can try this out by writing it as an INSERT AFTER and an UPDATE AFTER trigger.

For this example:

  • SERVER1 has database this_db with table this_table
  • SERVER2 has database that_db with table that_table
  • SERVER2 has IP address 10.20.30.250

Table structure looks like this:

CREATE TABLE `this _table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fruit` char(10) NOT NULL,
  `number` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

  1. First, make sure the table structures are MyISAM and identical...
    On the first server, do SHOW CREATE TABLE this_db.this_table\G
    On the other server, do SHOW CREATE TABLE that_db.that_table\G

  2. On the first server, run this:

    CREATE TABLE this_db.that_table LIKE this_db.this_table;

  3. On the first server, run this:

    ALTER TABLE this_db.that_table ENGINE=FEDERATED
CONNECTION='mysql://10.20.30.250/that_db/that_table';

  4. On the first server, create an INSERT AFTER trigger against this_db.this_table:

    use this_db
DELIMITER $$
CREATE TRIGGER this_table afterinsert AFTER INSERT ON this_table 
  FOR EACH ROW
    INSERT INTO that_table (id,fruit,number) 
    VALUES (NEW.id,NEW.fruit,NEW.number);
$$
DELIMITER ;

  5. On the first server, create an UPDATE AFTER trigger against this_db.this_table:

    use this_db
DELIMITER $$
CREATE TRIGGER this_table afterupdate AFTER UPDATE ON this_table 
  FOR EACH ROW
    UPDATE that_table SET fruit=NEW.fruit,number=NEW.number WHERE id=OLD.id;
$$
DELIMITER ;

That's it.

Give it a try!!!

Related Topic