Visit The New, Modern Unix Linux Community


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


 
Thread Tools Search this Thread
Top Forums Web Development How do you sync tables in mysql between two different servers?
# 1  
Question How do you sync tables in mysql between two different servers?

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 Smilie
# 2  
Hi
You can look for the difference between the table's columns by column like this:
PHP Code:
select from table ABC  where ABC.<column namenot in (select ABC2.<ColumnNamefrom table ABC1 where ABC.<columnName> = ABC1.<columnName>) 
Then refering to the result of the query update the tables.

Cheers
# 3  
Quote:
Originally Posted by Hammadi dali
Hi
You can look for the difference between the table's columns by column like this:
PHP Code:
select from table ABC  where ABC.<column namenot in (select ABC2.<ColumnNamefrom table ABC1 where ABC.<columnName> = ABC1.<columnName>) 
Then refering to the result of the query update the tables.

Cheers
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? Smilie

I will back up tables on both sides before attempting this Smilie

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #417
Difficulty: Medium
The terms Vanilla JavaScript and Vanilla JS refer to JavaScript which uses very small cookies during web sessions.
True or False?

7 More Discussions You Might Find Interesting

1. Solaris

Best practise for keeping cronjobs across 2 servers in sync

Hi all, I have 2 server A and B. B is acting as standby for A. The cronjobs running in A must not be run in B until failover. The activation of cronjobs in B can be manual. In server A, I am doing the following 1) create a cron/job script that does "crontab -l >... (5 Replies)
Discussion started by: javanoob
5 Replies

2. Solaris

Client does not sync date with ntp servers

We had a network problem a couple of days before that caused 1 interface to down and up. But today I have noticed that our date is wrong, our system have 4 servers and the other 3 are OK. I used <date> command, the result is: Fri Jan 2 17:57:55 ICT 1970 I have tried set date to be the same as... (3 Replies)
Discussion started by: salvation3103
3 Replies

3. Programming

MySQL join four tables!

Hello; I want merge four MySQL tables to get the intersection that have a common field for all of them. Join two tables is fine to me, but my this case is different from common situations and there are not very many discussions about it. Can anybody give me some idea? Thanks a lot! Here is part... (8 Replies)
Discussion started by: yifangt
8 Replies

4. Programming

MySQL: Create a relation between two tables.

Hello everybody, I'm having troubles creating a relation between two tables in a MySQL database. Having two tables, being one which contains users information (username, password, user ID, etc) and the other the one which contains transactions information (operation type, user ID of the user who... (2 Replies)
Discussion started by: semash!
2 Replies

5. UNIX for Advanced & Expert Users

How to Sync two servers

Hi All, want to sync. two servers , both are having solaris os. If am updating any thing in one server , it will automatically sync. with the other server . Is it possible , if so den how ? (3 Replies)
Discussion started by: natraj005
3 Replies

6. Shell Programming and Scripting

sync files from two different servers.

I have a directory called UNIX 1 which contains 2 files and uploaded into two different servers. Now I want to check whether the directory contains both the files. If not, then need to sync the directory. Please let me know how to do that in shell scripting. (2 Replies)
Discussion started by: madan1
2 Replies

7. Post Here to Contact Site Administrators and Moderators

TODO: Sync User Tables Between Databases

Need to eventually sync the MY.UNIX userdata to the forums database (and others). Suggestions on the best approach welcome. (0 Replies)
Discussion started by: Neo
0 Replies

Featured Tech Videos