2

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:root@192.100.0.0\ 
--server2=root:root@192.160.0.01 \
inteliviz:inteliviz\
--run-all-test --changes-for=server2 --difftype=sql

how to pass tables in here. Thanks in advance.

shruthi
  • 71
  • 1
  • 2
  • 10

1 Answers1

1

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

Community
  • 1
  • 1
Matt Caton
  • 3,373
  • 20
  • 25