2-way Sinchronisation – I Need Help

[joru]

hi all,

i need some help in realising two way replication between 2 mysqls:

let say i have 2 equal tables in both linux servers:

ID – int (11) primary key

text1 – varchar (200)

last_edit – timestamp

so, i need some help in realising this:

1.when i Edit a single record in server1.mytable – i need this to be transfered to server2.mytable

and in opposit

2.when i Edit one record in server2.mytable – this to be updated in server1.mytable

i have last_edit field wich can be used as compare condition.is not good idea but is the one.

and now, i need your help to realise this, because when i'm editing in source table – this leads target table to be up-to-date , but when i'm editing the target table – my changes are discard during sinchronisation process , and my target table is become identical to my source table ,no matter i don't need this.

this is the same problems with deleted rows .

what i need is some special condition in where clause which can compare last_edit fields in both servers and to use record with youngest last_edit.

i keen of receiving your answers,because i need this tool a lot.

thanks in advance .

joru

[peterlaursen]

First you should understand the difference between replication and synchronization:

http://www.webyog.com/faq/11_60_en.html

Next we have an extensive article on the SQLyog DATA synchronization here:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

And last let me try to answer your question:

What you are trying to do is not really possible with the SQLyog DATA SYNC tool. You write this:

Quote:

but when i'm editing the target table – my changes are discard during sinchronisation process

… and if you read the article you will understand that this is exactly how the tool works. This simply is what 'source' and 'target' means here. Same with rows deleted in target.

You write: i have last_edit field.. Is it a TIMESTAMP (or a DATETIME)? Sounds so. The data Sync tool does not use such TIME information. You can define a PK that uses this field but it won't solve the problem.

You cannot UPDATE on both servers between syncs and sync changes with the SQLyog DATA sync tool. You can INSERT on both in you choose an approiate PK. And two-way sync wil always copy rows that exist on one server only. So rows deleted on one server only will be restored with a two-way sync.

The solution in your situation would be to write a program (a PHP script, Visual Basic – anything that can connect to MySQL) that connects to both databases, compares this TIME for each row of data and writes data from newer >> older when this TIME information differs.

But there are some issues, that you must solve:

* If a row exists on one server only, how to tell if it was inserted on one server (and should be copied to the other) or it was deleted on one server (and should be deleted on the other as well). No program can tell. You must define the 'rule' to apply!

* Now if the same row has been updated on both servers what to copy then? Always the LAST one. So simple? or another 'rule' to apply?

* How to avoid a PK-conflict when rows are inserted on both servers? (tip: don't use auto-increments!)

Added:

In the article I wrote this:

I have an idea that it would be useful to have an ability to use a symbolic addressing using “source” and “target” with the SJA as some sort of “reserved words” like “source.tablename.columnname” and “target.tablename.columnname” with the SQL_WHERE . That would allow for SQL_WHERE expressions like

source.tablename.columnname >= target.tablename.columnname

That actually is what you request I think. But it is not available. And will only solve situations where identical rows exist on both servers!

And it won't solve the issue with DELETEd rows. But there will be solutions to this as well. One idea: Don't physically delete, but mark-up for delete using a special column for that (an ENUM yes/no for instance). An option to DELETE instead of INSERT when this is 'yes' could be added to the tool. And BTW: you can use this trick with your own script!

[joru]

ok , thank you for your comprehensive answer .

i've read both two links you posted and what i've understood is that SJA doesn't much my needs.

and yes , what i need is really a replicator.

i'll be grateful if you can help me with some links for known MySQL replicators if you have suchs.

thanks again.

joru.

[peterlaursen]

I don't think that there are 'MySQL replicators'

there is MySQL replication. It is built-in the MySQL server. Just must be enabled in configration. However it lets you replicate SERVERS, not DATABASES or TABLES. So you must have two servers that you don't share with others. And the concept of replication implies that the servers should be able to communicate (though of course if the slave crashes it will 'pick up' what it missed when it gets connected again)

And I think that even two-way replication between two servers is not secure (two servers being master and slave for one and another at the same time has some issues). You can create a 'polygon' (three or more) of replicating servers a secure way – but not only two.

If the only thing you need is

1) compare two TIMESTAMPs and copy row from the server with the most recent to the other

2) check for a to-delete 'mark-up' and delete on both servers if this 'mark'up' is positive on either

.. then a program for it is pretty simply. At least if there are not huge amounts of data to be written and 'peak' performance is not an issue.

But also notice that a TIMESTAMP has an 'accuracy' of one second. So if same row is updated one the two servers within the same second you won't be able to track it and the sync will fail (and if system clocks get 'out of tune' you are pretty much lost!). MySQL replication uses the Binary Logs of the servers. SQLyog synchronization uses DATA THEMSELVES. If you respect their limitations none of them will ever fail.

[Author]

Posts