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

Question

I am new to GAPI.. I want to find top 10 pages of site using GAPI for both mobile devices and desktop.. I coded up to this..

<?php
define('ga_email','[email protected]');
define('ga_password','xxxxxxxxxxxxxxx');
define('ga_profile_id','xxxxxxxxxxx');

require 'gapi.class.php';

$ga = new gapi(ga_email,ga_password);

$ga->requestReportData(ga_profile_id,array('browser','browserVersion'),array('pageviews','visits'));

//echo '<pre>';print_r($ga->getResults());exit;

?>
<table>
   <tr>
     <th>Browser &amp; Browser Version</th>
     <th>Pageviews</th>
     <th>Visits</th>
   </tr>
<?php
  foreach($ga->getResults() as $result):
?>
  <tr>
    <td><?php echo $result ?></td>
    <td><?php echo $result->getPageviews() ?></td>
    <td><?php echo $result->getVisits() ?></td>
  </tr>
<?php
  endforeach
?> 

Solution

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

Comments

[BobFranz]

This is excellent I will have a look into all this. Thank you so much for the detailed explanation.

[w5m]

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.

[Michael - sqlbot]

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. :)