Re: Master-master setup
There're an article about master to master replication at my blog. On Thu, Oct 9, 2008 at 3:45 PM, Simon J Mudd [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Carl) writes: I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? You need to set 2 variables to ensure you don't have problems. # when you have 2 master servers auto_increment_increment = 2 # each server has a different offset (values in this case 1,2) auto_increment_offset= 1 This way each master will generate unique ids Note: doing this means that you will get gaps in your ids as each server uses its own value to generate new ids and these increment by auto_increment_increment every time. Be aware that if the updates to the tables are very frequent it's quite possible that replication delay may mean that the data on both servers is not the same. The only way to ensure that this is avoided is to use explicit WHERE id IN (1,3,43,5,...,nn) clauses so that you are absolutely certain that the changes applied on one master will be produced on the other one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Master-master setup
[EMAIL PROTECTED] (Carl) writes: I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? You need to set 2 variables to ensure you don't have problems. # when you have 2 master servers auto_increment_increment = 2 # each server has a different offset (values in this case 1,2) auto_increment_offset= 1 This way each master will generate unique ids Note: doing this means that you will get gaps in your ids as each server uses its own value to generate new ids and these increment by auto_increment_increment every time. Be aware that if the updates to the tables are very frequent it's quite possible that replication delay may mean that the data on both servers is not the same. The only way to ensure that this is avoided is to use explicit WHERE id IN (1,3,43,5,...,nn) clauses so that you are absolutely certain that the changes applied on one master will be produced on the other one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master-master setup
I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? TIA, Carl
