Search code examples
phpcakephp-2.0mysqlmysql-error-1064

Mysql database replication when data with same id is present

Is it possible to replicate database when data with same id are present on the same table ?

Ie when the databsae on my local system(xampp mysql) goes through some changes when the internet goes down and also in the live database new data will be inserted. How can I sync both the database without losing the data(when records with same primary key are inserted in both the database).

What I am trying to do is replicating two database. One on xampp localhost and other on my live server. The local PC may not be connected to internet all the time as internet connection goes down sometime at that place. So what I need to do is syn both the database when the internet comes back. Till then the client can make changes on the local database and the customers of that particular clients can make changes on the live database.

So, can I use any MYSQL functionality to do this?

Can I do it with php? If yes Is there any way we can compare and execute Update, Insert and Delete in both the database ?

Solution

  • No, this can't be done and is a classic problem in a "split-brain" cluster which is what you effectively have (the whole point of the id column is to uniquely identify a row).

    If the column is an auto_increment one though then mysql has a work-around for this kind of multi-master setup:

    1. In the config file (/etc/mysql/my.cnf on linux) set auto_increment_offset on both servers to 2
    2. Then find the largest current id value
    3. Set auto_increment_offset on one server to something bigger than this and on the other server set it to that value + 1
    4. Restart both databases

    After this the autoincrement id values on the two databases will "leapfrog" over each other so there's no chance of a clash when they replicate.

    See http://dev.mysql.com/doc/refman/5.1/ja/replication-auto-increment.html