Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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!!!

share|improve this answer
    
This is excellent I will have a look into all this. Thank you so much for the detailed explanation. –  BobFranz Feb 15 '11 at 4:38
    
This answer is absolutely fantastic - many thanks @RolandoMySQLDBA for taking the time to document the steps involved. Any inserts/updates/deletes (I also added a AFTER DELETE trigger) on my original table are now replicated to my FEDERATED table on the same server (via triggers). That FEDERATED table then pushes the changes to a table on a different server. This seems to be a good alternative for those on shared hosting who aren't permitted to implement MySQL Replication. –  w5m Mar 15 '13 at 11:14
    
Note: the remote table does not have to be MyISAM. It can be any storage engine, but on the local server, it will behave similar to a MyISAM table in the sense that it will be treated as a non-transactional table & on the far end, everything is executed in AUTOCOMMIT mode. For an interesting twist that's guaranteed to amuse, you can even do this in BEFORE triggers to test whether your change would violate foreign key constraints on the remote server, and prevent the local change from happening, because the error that the query causes on the remote server will stop local execution. :) –  Michael - sqlbot Sep 30 '13 at 4:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.