Copying non existing rows from one database table to another database table?

Question

I have the following 1 db table in Database 1 and 1db table in Database 2, now the stucture of both tables are exactly the same. Now what happens is table 1 (DB1) gets new rows added daily, I need to update the table 1 (DB 1) new rows in table 1 (DB 2) so that these 2 tables remain the same. A cron will trigger a php script on midnight to do this task. What is the best way to do this and how using PHP/mysql?

Answer 1

Abra kadabra

insert into table2 select * from table1 where date_added>1

obviously you should replace 1 with the date the table was last updated. Or just go with the hardcore way:

replace into table2 select * from table1

Note that this will be slow with bigger tables.

Note though, that as tvanfosson mentioned in the comment, this will ignore deleted/modified rows. If these also need to be considered, you could go with:

DROP TABLE table2;
CREATE TABLE table2 SELECT * FROM table1

Answer 2

This might help you out, its what i do on my database for a similar kinda thing

    $dropSQL = "DROP TABLE IF EXISTS `$targetTable`";
    $createSQL = "CREATE TABLE `$targetTable` SELECT * FROM `$activeTable`";
    $primaryKeySQL = "ALTER TABLE `$targetTable` ADD PRIMARY KEY(`id`)";
    $autoIncSQL = "ALTER TABLE `$targetTable` CHANGE `id` `id` INT( 60 ) NOT NULL  AUTO_INCREMENT";
    mysql_query($dropSQL);
    mysql_query($createSQL);
    mysql_query($primaryKeySQL);
    mysql_query($autoIncSQL);

obviously you will have to modify the taget and active table variables. Dropping the table will lose the primary key when you do this, oh well .. easy enough to add back in

Answer 3

You might care to have a look at replication (see http://dev.mysql.com/doc/refman/5.4/en/replication-configuration.html). That's the 'proper' way to do it; it isn't to be trifled with, though, and for small tables the above solutions are probably better (and certainly easier).

Answer 4

I would recommend replication as has already been suggested. However, another option is to use mysqldump to grab the rows you need and send them to the other table.

mysqldump -uUSER -pPASSWORD -hHOST --compact -t --where="date=\"CURRENT_DATE\"" DB1 TABLE | mysql -uUSER -pPASSWORD -hHOST -D DB2

Replace USER, HOST, and PASSWORD with login info for your database. You can use different information for each part of the command if DB1 and DB2 have different access information. DB1 and DB2 are the names of your databases, and TABLE is the name of the table.

You can also modify the --where option to grab only the rows which need to updated. Hopefully you have some query you can use. As mentioned previously, if the table has a primary key, you could grab the last key which DB2 has using a command something like

KEY=`echo "SELECT MAX(KEY_COLUMN) FROM TABLE;" mysql -uUSER -pPASSWORD -hHOST -D DB2`

for a bash shell script (then use this value in the WHERE clause above). Depending on how your primary key is generated, this may be a bad idea since rows may be added in holes in the keyspace if they exist.

This solution will also work if rows are changed as long as you have a query which can select these rows. Just add the --replace option to the mysqldump command. In your situation, it would be best to add some type of value such as date updated which you can compare by.