Good afternoon,

Does anyone know how to reliably replicate a table (while mysql is running) to a table in the same DB (with a different table name, naturally)?

I've tried so far:

----Version 1 (PHP)----------------------------------------------------------
#Table1 is the table we will read data from later
#Table2 is the table the perl daemon writes data to in real time
#Note that the perl daemon is stopped before this query takes place, however, mysql has to stay running.

$sql1 = "DROP TABLE IF EXISTS `DBNAME`.`TABLE1`";
mysql_query($sql1) or die ('Broken Drip'.mysql_error());

$sql = "
CREATE TABLE `DBNAME`.`TABLE1` (
`ID` int( 20 ) NOT NULL AUTO_INCREMENT ,
`Stamp` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY ( `monitor_ID` )
) TYPE = MYISAM ";

mysql_query($sql2) or die ('Broken Create'.mysql_error());


$sql3 = "INSERT INTO `DBNAME`.`TABLE1`
SELECT '', Stamp
FROM `DBNAME`.`TABLE2`";
mysql_query($sql3) or die ('Insert Broke'.mysql_error());


This works only part of the time, the other parts, it copies 225 to 228 of the desired 229 rows and in the process seems to copy incorrect values.


--Version 2---------------------------------------------------
mysqldump --quick --skip-comments --add-drop-table -K --add-locks DBNAME TABLE2> /home/user/www/table2.sql

perl /home/user/www/replace_table_name.pl (this replaces the TABLE2 listing with TABLE1 in the above .sql file

mysql DBNAME < /home/user/www/table2.sql

This one works much the same as the above, that is Sometimes.

---------------------------------------------------------------------

I don't know what else to do. Any help is GREATLY appreciated.

Regards,
Dave