1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Database Synchronization

Discussion in 'MySQL' started by dylanj, Dec 17, 2009.

0
  1. #1
    Hi

    Usually, there would not be much point in doing what I am trying to do, but let me explain.

    I have 2 game servers which run off a MySQL database. At the moment, both servers are using server A to do all the MySQL queries. The problem is, server B is in Germany, and server A is in California, so whenever a MySQL query is run on server B, there is a short lag, which is noticeable when in-game.

    What I want to do, is run the main MySQL server on server A, and a sort of "clone" on server B, so whenever I execute any commands on server B, they will instantly take effect on server A, too. So, I need both databases to be completely identical, so that server B can use its local database, while server A submits any changes to the database to server B, and vice versa. Thus, removing the lag from in-game, and transferring it to a separate process.

    This is how it is at the moment:

    Player 1 joins server A, and logs in. The query is local, and very fast.
    Player 2 joins server B, and server B fetches login data from server A on-the-spot, creating a lag.

    I want it to be like this:

    Player 1 joins server A, and logs in. The query is local, but if any changes are made to server A's database, they are submitted to server B.
    Player 2 joins server B, and logs in. The query is local, but if any changes are made to server B's database, they are submitted to server A.
    The databases are now identical.

    Any help would be appreciated.

    Thanks
     
    dylanj, Dec 17, 2009 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    You should go for MySQL master-master replication.
     
    mwasif, Dec 18, 2009 IP
  3. basia

    basia Well-Known Member

    Messages:
    263
    Likes Received:
    25
    Best Answers:
    0
    Trophy Points:
    155
    #3
    You don't want master-master replication because you will run into all sorts of problems with conflicts (master-master replication isn't even supported on commercial versions of Mysql). The way I would set this up is use master-slave replication. All writes from both websites are done to the master (server A, there may be a lag but only during setup). Then they are all replicated to the slave. The select queries are done on the local database server. One thing you will have to resolve is race conditions. It will take some time to write to the master from Germany and the propagate the changes back to Germany. You can't read the data before it's replicated.
     
    basia, Jan 6, 2010 IP