How do you sync tables in mysql between two different servers?

Applications Web Development
1

Hi all,

I have 2 mysql databases running on two different servers (both can be accessed via ssh to each other).

If I have say table ABC on server 1, how can I sync it with table ABC on server 2 if the number of records is different? Both databases are the same structure though server 2 has more tables.

Please can someone suggest an easy way to sync tables between two servers, one local one remote by naming a table and syncing it? I have tried (being dumb) to scp the MYI, MYD and FRM files over to the other server *thinking* this would work....it does to an extent until Mysql complains when you use the scp'd tables as part of a merge :mad:

2

Hi
You can look for the difference between the table's columns by column like this:

select * from table ABC  where ABC.<column name> not in (select ABC2.<ColumnName> from table ABC1 where ABC.<columnName> = ABC1.<columnName>)

Then refering to the result of the query update the tables.

Cheers

3

Hi Hammadi,

Thanks for your idea. However i cannot do this on a column by column basis...

instead, im thinking to do a mysqldump using the --opt option on one server and then using this dump into the other database provided i know the tables i need to sync. I think may automate this using a script.

Does this sound correct? :confused:

I will back up tables on both sides before attempting this :b: