Re: Master/Master Asynchronous replication
Excellent are there any doc pointers describing how to set this up? /Joe Jeremy Zawodny wrote: On Wed, Jul 23, 2003 at 04:47:35PM -0400, Joe Gainey wrote: Currently we have a web based application that is mostly reads (4:1 r/w). It is using a single MySQL database server. Is there any way to have two database servers in a master/master configuration such that writes to either database server are replicated to eachother. Basically even though we have a 4:1 ration of read/write the writes happen often enought that when the database goes down the app stops working. I know how to get this working in Oracle (insert big laugh here) but Oracle is cost prohibitive. Any pointers? Any suggestions? If this is available in the latest version that would be great. You can do it, yes. But beware that MySQL has no provisions for conflict resolution. So using auto-increment fields with primary keys (for example) can be a problem because of the inherent race condition. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
On Mon, 28 Jul 2003 22:23:06 -0700, Jeremy Zawodny used a few recycled electrons to form: | On Wed, Jul 23, 2003 at 04:47:35PM -0400, Joe Gainey wrote: | | Currently we have a web based application that is mostly reads (4:1 | r/w). It is using a single MySQL database server. Is there any way to | have two database servers in a master/master configuration such that | writes to either database server are replicated to eachother. Basically | even though we have a 4:1 ration of read/write the writes happen often | enought that when the database goes down the app stops working. I know | how to get this working in Oracle (insert big laugh here) but Oracle is | cost prohibitive. Any pointers? Any suggestions? If this is available | in the latest version that would be great. | | You can do it, yes. | | But beware that MySQL has no provisions for conflict resolution. So | using auto-increment fields with primary keys (for example) can be a | problem because of the inherent race condition. What about using a joined primary key, such that PRIMARY KEY (server_id,auto_increment_column) Of course you might be able to set the server_id differently in the create table defaults on the different servers. Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
Hi, Joe, I have exactly the same scenario. Did you get any valuable response you could share with me? I haven't seen any on the list. In fact, my case is slightly more complicated; I have N computers, all having their local databases, and have an additional computer, which I call the central one, having a central copy of the database. I'd like to sync all the N+1 databases continuously, so they are identical. A---C---B | D That is, if I change anything on any PC's DB (e.g. A), it replicates itself to the central DB (C), and then migrates to B and D. This means that the A-C connection is such that A is a master and C is a slave, and the C-B is such that C is a master and B is a slave. On the other hand, if I change something on another local DB than A, e.g. B or C itself, I'd like this to migrate to A, i.e. this case the A-C connection is such that A is the slave. Altogether, I'd need a continuous master-master replication between all DBs and C. Similar to a RAID-1 array, just in the world of databases, and over TCP. Maybe there is a problem with my concept, and this solution of pushing for reliability will eventually cause chaos. The Mysql manual does not mention master-master replication: Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
Well, the bad news is that I did not get any positive responses (that I can recall) from the list. But I did a little digging. suppossedly in the latest release you can do A-B-C-D-A. Something to do with the server name encoded in the blog. I'm not much of a dba but in todays ID sweatshops they are trying to squeeze me for all I got. My official answer to the higher ups was I know this can be done in Oracle but at thier licensing costs there's no way. If I get anything concrete going I'll drop a note to the list. /Joe Gaspar Bakos wrote: Hi, Joe, I have exactly the same scenario. Did you get any valuable response you could share with me? I haven't seen any on the list. In fact, my case is slightly more complicated; I have N computers, all having their local databases, and have an additional computer, which I call the central one, having a central copy of the database. I'd like to sync all the N+1 databases continuously, so they are identical. A---C---B | D That is, if I change anything on any PC's DB (e.g. A), it replicates itself to the central DB (C), and then migrates to B and D. This means that the A-C connection is such that A is a master and C is a slave, and the C-B is such that C is a master and B is a slave. On the other hand, if I change something on another local DB than A, e.g. B or C itself, I'd like this to migrate to A, i.e. this case the A-C connection is such that A is the slave. Altogether, I'd need a continuous master-master replication between all DBs and C. Similar to a RAID-1 array, just in the world of databases, and over TCP. Maybe there is a problem with my concept, and this solution of pushing for reliability will eventually cause chaos. The Mysql manual does not mention master-master replication: Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
On Wed, Jul 23, 2003 at 04:47:35PM -0400, Joe Gainey wrote: Currently we have a web based application that is mostly reads (4:1 r/w). It is using a single MySQL database server. Is there any way to have two database servers in a master/master configuration such that writes to either database server are replicated to eachother. Basically even though we have a 4:1 ration of read/write the writes happen often enought that when the database goes down the app stops working. I know how to get this working in Oracle (insert big laugh here) but Oracle is cost prohibitive. Any pointers? Any suggestions? If this is available in the latest version that would be great. You can do it, yes. But beware that MySQL has no provisions for conflict resolution. So using auto-increment fields with primary keys (for example) can be a problem because of the inherent race condition. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 575,245,290 queries (346/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master/Master Asynchronous replication
Currently we have a web based application that is mostly reads (4:1 r/w). It is using a single MySQL database server. Is there any way to have two database servers in a master/master configuration such that writes to either database server are replicated to eachother. Basically even though we have a 4:1 ration of read/write the writes happen often enought that when the database goes down the app stops working. I know how to get this working in Oracle (insert big laugh here) but Oracle is cost prohibitive. Any pointers? Any suggestions? If this is available in the latest version that would be great. Thanks /Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]