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)

Automating mantenance tasks on MySQL

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:

Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).
Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something similar to Microsoft's sp_MSforeachtable), here is the code:

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`sp_4_each_table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_4_each_table`(db_name VARCHAR(64), template VARCHAR(21845))
BEGIN
#
DECLARE done INT DEFAULT 0;
#
DECLARE tname VARCHAR(64);
#
DECLARE c CURSOR FOR
#
SELECT table_name FROM information_schema.TABLES WHERE table_schema=db_name AND table_type = 'BASE TABLE';
#
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done= 1;
#
 
#
OPEN c;
#
REPEAT
#
FETCH c INTO tname;
#
SET @stmt_text=REPLACE(template, " ?", CONCAT(" ", tname));
#
BEGIN
#
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
#
SELECT CONCAT("'", @stmt_text, "' command failed") AS "error";
#
PREPARE stmt FROM @stmt_text;
#
EXECUTE stmt;
#
DEALLOCATE prepare stmt;
#
END;
#
UNTIL done END REPEAT;
#
END $$
 
DELIMITER ; 

Now with the procedure at hand I'm going on with the scheduling part, taking advantage of the great new "EVENT" feature of MySQL 5.1, see it in action:
First of all check if events are enabled;

Code:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.18-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

Add the event to the database, we'll activate events later

Code:

mysql> create event analyze_all
-> on schedule every 1 day
-> starts timestamp '2007-05-27 23:59:59'
-> ends timestamp '2007-05-27 23:59:59' + interval 1 year
-> on completion preserve
-> comment 'updates stats by analyzing tables'
-> do call sp_4_each_table('test', 'analyze table ?');
Query OK, 0 rows affected (0.03 sec)

Check it's existance from the related information_schema table:

Code:

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+---------+
| event_name  | event_type | status  |
+-------------+------------+---------+
| analyze_all | RECURRING  | ENABLED |
+-------------+------------+---------+
1 row in set (0.02 sec)

Ok, the event is here.
Now I'll have to enable events on the server and let it rip, it's very simple
just issue an:

Code:

SET GLOBAL event_scheduler=1;

Or start the server with the:

Code:

--event_scheduler=1

Stopping an event without deleting it's also very simple:

Code:

mysql> alter event analyze_all disable;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
-> event_name,
-> event_type,
-> status
-> FROM information_schema.`EVENTS` E;
+-------------+------------+----------+
| event_name  | event_type | status   |
+-------------+------------+----------+
| analyze_all | RECURRING  | DISABLED |
+-------------+------------+----------+
1 row in set (0.00 sec)

Sooner or later I'll show you how this, with "federated tables", "csv storage engine" and "upserts" can really ease and improve an ETL process.

Featured on MySQL Newsletter June 2007. WOW !!!