2 ways Replication in MySQL
Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? Thanks in advance for you advice! Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004 13:59:21: Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? What you are attempting to do is inherently difficult, and I don't think any DBMS has solved it. What do you expect to happen if the links between cities are down, and the *same* row in the database is updated differently by different users? Even when the link is up, you have the possibility of a race condition if users in different places update records within a narrow window. The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 ways Replication in MySQL
I worked on a military system that went further than this, but again required a proprietary application to perform the updates. Databases were either slave, master, of standalone. Any update transaction was logged to a file. If the master was available then If we are the master we apply the update and sent a replication update to all other databases. If we are the slave we send the transaction to the master and wait for its response. If the master was unavailable then we store the transaction until the master becomes available. At this point we apply any pending updates from the master. We then send our updates to the master an wait for a response. If the master detects a conflict between slave updates and pre applied updates then it refuses the update and sends a conflict message back to the originating slave, where it is up to the user to resolve manually. Standalone is a special version of master. It applies updates locally but remembers what the last update it received from the master was. When it is reconnected to the master the user must manually resolve conflicts and determine which, if any, of the updates should be applied to the master. When I left 14 months into the project we had the basic replication engine working but none of the conflict resolution stuff. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec .com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Thanks Alec, how is a bank system implemented? do they have just one master sever and all the other servers are slaves? you solution is not bad and I should think more about it. it's close to one of my solutions: I should convert the database to InnoDB. when a user in location A needs to update or add a new record, the program in background should lock the record in all 3 locations. if it is successful, then user in A can change the data and program should update all 3 locations. if updating has any problem, send rollback command to the other locations. I should have a commands waiting list. if update command could get through but rollback could not, after connection backed, send it to the server. then I should think about how to solve dead lock (if location A send lock to B, and C and before release the lock, connection gone. then B and C should not be locked forever : anyways, it's complicated. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:34 AM Subject: Re: 2 ways Replication in MySQL Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004 13:59:21: Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? What you are attempting to do is inherently difficult, and I don't think any DBMS has solved it. What do you expect to happen if the links between cities are down, and the *same* row in the database is updated differently by different users? Even when the link is up, you have the possibility of a race condition if users in different places update records within a narrow window. The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Thanks Kevin! that's a good method. but in this method we should not have any AUTO_INCREMENT field in tables and a master table should take care of giving a unique key. because at first we should add a record to a table then mysql gives the key. if it's a slave the master is off, then we will be in trouble (two slaves needs to add to the same table when master is off). in this case slaves cannot add any record if they don't have connection. unless I change the key and add the location to the key also (that's a big change and program should be changed also). on the other hand usually primary key is not a field that is visible for the user. then program can change the primary key if there are duplicated in master table and fix all relational tables. anyways, I am wondering how ORACLE can handle this kind of situtaion. - Original Message - From: Kevin Cowley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:56 AM Subject: RE: 2 ways Replication in MySQL I worked on a military system that went further than this, but again required a proprietary application to perform the updates. Databases were either slave, master, of standalone. Any update transaction was logged to a file. If the master was available then If we are the master we apply the update and sent a replication update to all other databases. If we are the slave we send the transaction to the master and wait for its response. If the master was unavailable then we store the transaction until the master becomes available. At this point we apply any pending updates from the master. We then send our updates to the master an wait for a response. If the master detects a conflict between slave updates and pre applied updates then it refuses the update and sends a conflict message back to the originating slave, where it is up to the user to resolve manually. Standalone is a special version of master. It applies updates locally but remembers what the last update it received from the master was. When it is reconnected to the master the user must manually resolve conflicts and determine which, if any, of the updates should be applied to the master. When I left 14 months into the project we had the basic replication engine working but none of the conflict resolution stuff. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec .com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
