How to sync two tables in MySQL

A question pops out quite often on Devshed forums, "How do I keep table x of my local database in sync with a remote copy?"
Usually replication is the suggested answer, but it might be a little overkill, think of those who just want to push the new product catalogue from a local server to their hosted site? No permanent connection and so on ...
In this case the right tool might be a mix of the new MySQL features, federated tables, extended insert synthax, stored procedures, events, triggers ... quite a fest.
Say you have a local catalogue table which holds the products on sale:

CREATE TABLE `test`.`catalogue` (
  `product_code` CHAR(10) NOT NULL,
  `product_name` VARCHAR(45) NOT NULL,
  `product_desc` VARCHAR(500) NOT NULL,
  `product_weight` DECIMAL NOT NULL,
  `product_colour` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`product_code`)
)

Load it with a sample row

INSERT INTO 
 catalogue 
VALUES
('AA12F', 'Sm. Widget', 'This is a small widget', 5, 'Red'); 

In this sample I'll suppose that a similar table exists in the remote database, no need to check.

First of all we need to be able to issue queries against that table right from this server, we'll use federated tables:

DROP TABLE IF EXISTS `test`.`remote_catalogue`;
CREATE TABLE  `test`.`remote_catalogue` (
  `product_code` CHAR(10) NOT NULL,
  `product_name` varchar(45) NOT NULL,
  `product_desc` varchar(500) NOT NULL,
  `product_weight` decimal(10,0) NOT NULL,
  `product_colour` varchar(45) NOT NULL,
  PRIMARY KEY (`product_code`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:nt300jk@127.0.0.1:3306/remote_test/catalogue';
--don't use a privileged user in real life!!! 

Now we have our federated table pointing to the remote server, let's set up the most basic sync, one query will be enough

REPLACE INTO remote_catalogue SELECT * FROM catalogue 

A quick test:

mysql> use remote_test
Database changed
mysql> select * from catalogue;
+--------------+--------------+------------------------+----------------+-------
---------+
| product_code | product_name | product_desc           | product_weight | produc
t_colour |
+--------------+--------------+------------------------+----------------+-------
---------+
| AA12F        | Sm. Widget   | This is a small widget |              5 | Red
|
+--------------+--------------+------------------------+----------------+-------
---------+
1 row in set (0.01 sec)

mysql>

After a while data in our local database changes, a new product is added and the existing one is updated

INSERT INTO 
 catalogue 
VALUES
 ('AB12G', 'Lg. Widget', 'A large widget', 34, 'blue');
 
UPDATE 
 catalogue 
SET 
 product_weight = 4 
WHERE 
 product_code = 'AA12F'; 

we are ready to push the changes to remote site with the query used before, reissueing the REPLACE leads to this on the remote server

mysql> select * from catalogue;
+--------------+--------------+------------------------+----------------+-------
---------+
| product_code | product_name | product_desc           | product_weight | produc
t_colour |
+--------------+--------------+------------------------+----------------+-------
---------+
| AA12F        | Sm. Widget   | This is a small widget |              4 | Red
    |
| AB12G        | Lg. Widget   | A large widget         |             34 | blue
    |
+--------------+--------------+------------------------+----------------+-------
---------+
2 rows in set (0.00 sec)

So now the two tables hold the same values!
Enough for now! I'll show you the rest later

BTW, while writing this I filed two bug reports (34973 and 34971) hope those will turn into errors on my side ...

MySQL's support guys took care of the bug reports, the outcome is:

Bug #34973 is not a bug (it's a duplicate of closed bug 25511 which says that's it's fine for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE to fail with federated tables), however bug 25511 suggests that REPLACE doesn't work too, but, as you can see it works fine.

Bug #34971 is verified, from Miguel's comments it looks like it's fixed in 5.1.24 (not released yet)