CODEWITHSUNDEEP
mysql
shruthi
shruthi

Reputation: 71

synchronize two tables stored in different databases

I have two tables in different databases. The tables are exactly alike (same name,same columns,etc). My question is, how can I retrieve new rows from parent table and store into the child table? I thought of using mysqldbcompare but it compares Two Databases and Identify Differences but i need to do it on a table.

mysqldbcompare --server1=root:[email protected]\ 
--server2=root:[email protected] \
inteliviz:inteliviz\
--run-all-test --changes-for=server2 --difftype=sql

how to pass tables in here. Thanks in advance.

Upvotes: 2

Views: 1182

Answers (1)

Matt Caton
Matt Caton

Reputation: 3503

mysqldbcompare can be made to generate the required sql to synchronise the two databases. My approach would be to use the --difftype=sql option to generate the sql required to generate the changed or missing rows. To reduce the surrounding "noise" in the mysqldbcompare report, you could limit the comparison to data consistency checks using --skip-... for all but the data-check option.

Something like:

mysqldbcompare --server1=... \
    --server2=... \
    --difftype=sql \
    --skip-object-compare --skip-object-diff --skip-option-count > report.txt

Alternatively, you could use INSERT IGNORE or REPLACE as per this answer:

MySQL INSERT INTO WHERE NOT EXIST

Upvotes: 1

Related Questions