Mysql remote synch

views:

answers:

Mysql remote synch

Hi,

We currently have an application located on a remote server, and our call center uses this application to perform customer transactions.

We plan to setup asterisk on a local server to help us with all the call routing and recording, for asterisk to work smoothly we have to move our application from the remote server to the local.

Its will be easy to mover all data to the local server and do transactions locally, but there is an option for users to do transactions online too which will hit the remote server database.

The reason we still have the remote application because of the reliable infrastructure and backup solution provided by rackspace.

If we move application to local server i am looking at a reliable solution for syncing remote and local databases so that we can handle local as well as online transactions.

+1 A:

Why not use mysql master-master replication and hold definitive data at both ends? (Note you'll have to do some reading on on auto_increment_increment and auto_increment_offset)

symcbean 2010-10-08 15:34:22

Server1: xxx.xxx.xxx.xxxserver-id=1binlog-do-db=samplemaster-host = xxx.xxx.xxx.xxx master-user = someusermaster-password = somepasswordmaster-connect-retry = 60 relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index Server 2: xxx.xxx.xxx.xxxserver-id=2 binlog-do-db = sample master-host = xxx.xxx.xxx.xxx master-user = someusermaster-password = somepasswordmaster-connect-retry = 60 relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index

2010-10-21 14:35:11

I have setup the master master replication configuration on 2 test servers, i have also opened ports for both the mysql 3306 on serversi am facing a problem with slave connection I keep getting the following errors101021 19:12:23 [ERROR] Slave I/O: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400, Error_code: 1045

2010-10-21 14:36:25

Could be lots of things - rollback the config and debug by connection from one using 'mysql' command line client to dbms running on other (its probably a permissions problem) and vice versa

symcbean 2010-10-22 08:20:06

symcbean's answer is basically correct. I'd add this article as a good starting place to understand master-master replication. I'd further recommend High Performance MySQL as a good reference for a deeper understanding of the techniques and issues.

There are some issues that you will have to face doing writes to two non-colocated MySQL servers. You'll have replication lag to deal with, so the databases won't necessarily be completely in sync, but will only be "eventually consistent". Also, if you have both sides doing updates on content, you can end up with data integrity issues. If your system leans towards INSERTs more then UPDATES for the write operations, it is less likely that you'll run into issues. Also, if the subset of data that is likely to be modified tends to be localized around one or the other of the servers, you'll run into fewer issues.

Otherwise, you'll probably want to roll your own solution that is designed towards the specific use cases of your application.

Tim 2010-10-08 15:46:49

ansaurus

tags:

views:

answers:

Mysql remote synch

related questions