1

Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This is only 1 way.

I cannot use replication here because my databases are not same. I need to take the changes from db1 and then sync into db2 and this needs to happen in real time. Are there any tools or scripts available on linux which does this or do we need to write custom scripts? If custom scripts, any suggestions would be helpful.

Improve this question

2 Answers 2

Reset to default
3

mysqldbcompare, part of mysql utils, can check differences in structure and data among two servers. However, if you are making updates to one of the servers at the same time, this may not be enough.

pt-table-checksum and pt-table-sync, from Percona Toolkit, can respectively check and synchronise differences in data between a master and a slave in real time. It doesn't matter if both servers have different data, but it requires having the same tables with the same definition.

So probably you can use a combination of both. Both MySQL utils and Percona Toolkit are open source, freely downloadable and well established utilities among MySQL DBAs.

Improve this answer
4
  • 1
    However, i want to synchronize only few tables and not all the tables from one db to another. The 2nd db might have additional data which i am not bothered about. I want only X tables of db1 to be synced completely into db2, which means data of those tables in db1 should be same as in db2. Can i achieve the same with the above given options?
    – Asif
    Commented Nov 20, 2013 at 13:56
  • Not with the first one, but the second has a variety of filters: --tables , --tables-regex , etc. You can even make it work without replication, but it will be more "manual".
    – jynus
    Commented Nov 20, 2013 at 14:04
  • Okay, will check that out. But i am actually looking at some trigger which basically logs the changes happened in db1 and then update the same into db2. Or some script which gets all the changes done in db1 and then apply the same in db2.
    – Asif
    Commented Nov 20, 2013 at 14:10
  • Your best bet would be using the binary log, but you are basically reinventing the replication protocol. You can use mysqlbinlog from 5.6 to get bin log events, convert them to SQL and filter/apply it to the right database/table. Maybe Tungsten can do such magic automatically?
    – jynus
    Commented Nov 20, 2013 at 14:16
0

You can have a look at these tools:

pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.

pt-table-sync - This tool changes data, so for maximum safety, you should back up your data before you use it. When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.