Master/Slave Replication Question

Hi,

I'm a big fan of mySQL's multi-master replication, but I've run into gotchas over the years. Off the top of my head, I can think of:

- auto_increment complications,
- if you have a->b->c->a, it's not exactly graceful to insert a "d" server for a->b->c->d->a
- if you have a->b->c->a and b fails, it's tricky to change your config to a->c->a
- no one authoritative data set
- problems with certain types of stored procedures/functions

I think I may have asked this question before, but I don't recall, so I thought I'd ask here:

Assuming all your grant tables are replicated and identical, wouldn't it be possible for a read-only mySQL slave to pass update queries to its master server, and then return the response from the master to the client?

I think this approach has several neat advantages:

- your client software doesn't have to know anything about replication (like to connect to a different server for updates)
- problems with stored functions and procedures go away
- no auto_increment problems - the master would maintain auto_increment consistency
- rebuilding a failed slave in this arrangement is worry-free
- one failed slave doesn't interrupt the replication of all the other servers
- you can do multi-level replication, where you have a->b, b->c, and then send an update query to c, which would send it to b, which would send it to a, which would process the query, return the result to b, which would then return the result to c (if "c" couldn't run the update query on "b", or if "b" couldn't run the update query on "a", an SQL error could be returned to the client)

I can see a few retorts right off the bat:

- this complicates the replication protocol
- not everyone would want to do it like this
- updates might take a bit longer since they have to be sent to the master

But I think this arrangement could be very useful in certain situations, and it seems to me that this is something that could totally be implemented as a configurable option, something like a simple my.cnf setting that says "slave_pass_upadtes_to_master" or something.

Has anyone suggested anything like this before? Any thoughts/comments/flames?

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Master/Slave Replication Question

> Another thought would be at the application layer, sending
> all the updates (insert,delete, update, etc) to server A,
> and the selects to the local slave servers.

This has been suggested before, and I'm totally against it. Applications like PHPBB, Drupal, WordPress, etc can't be easily configured to do this, and I'd really like to use those applications in a more robust and redundant environment.

Pushing the work of this sort of master/slave relationship to the application level means that most applications will not support it. Replication is a database server problem, not an application problem.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Master/Slave Replication Question

WW91J2QgYmUgc3VycHJpc2VkIGhvdyBtYW55IHBsYWNlcyB1c2VzIHRoaXMg YXMgYW4gYWN0dWFs
IHNvbHV0aW9uLiBBbGwgYXJndW1lbnRzIGFzaWRlIGFib3V0IHdoYXQgbGV2 ZWwgb2YgdGhlIGFy
Y2hpdGVjdHVyZSBzaG91bGQgYmUgZG9pbmcgd2hhdCwgaXQgc2ltcGx5IHdv cmtzIHdlbGwuICBN
b3Jlb3ZlciwgaXQgd29ya3MgdG9kYXkgYXMgb3Bwb3NlZCB0byB3YWl0aW5n IHVudGlsIHRoZSBl
bmQgb2YgdGltZSBmb3IgdGhlIGRhdGFiYXNlIGRldmVsb3BlcnMgdG8gYWRk IGZlYXR1cmVzIGxp
a2UgdGhhdCAod2hpY2ggbXlzcWwgY2x1c3RlciBpcyBhbHJlYWR5IGEgZGlz dHJpYnV0ZWQgZGF0
YWJhc2UsIGFuZCB0aGUgZGV2cyBoYXZlIHNhaWQgdGhleSdyZSBub3QgaW50 ZXJlc3RlZCBpbiB0
cnlpbmcgdG8gdHVybiB0aGUgcmVndWxhciBteXNxbCBpbnRvIGEgZGlzdHJp YnV0ZWQgcHJvZHVj
dCwgaW5zdGVhZCB0aGV5IHdhbnQgdG8gZm9jdXMgb24gd2hhdCBpdCBkb2Vz IGJlc3QpDQoNCkkg
d291bGQgbG92ZSB0byBzZWUgYSBkcm9wLWluIHNvbHV0aW9uIHRoYXQgcmVx dWlyZXMgbm8gYXBw
bGljYXRpb24gY2hhbmdlcywgYW5kIGRvZXNuJ3QgaW50cm9kdWNlIGFueSBh ZGRpdGlvbmFsIGNv
bXBsaWNhdGlvbnMgc3VjaCBhcyBhZGRpbmcgZXhjZXNzaXZlIG92ZXJoZWFk LCBvciBoYXZlIHll
dCBhbm90aGVyIHNpbmdsZSBwb2ludCBvZiBmYWlsdXJlLCBidXQgbm9uZSAo b3IgYXQgbGVhc3Qg
bm8gZ29vZCBvbmVzKSBleGlzdC4gIFRoaXMgaXMgYmVjYXVzZSBvcHRpbWl6 YXRpb24gaXMgYSB2
ZXJ5IHNwZWNpZmljIHByb2Nlc3MuDQoNCkFwcGxpY2F0aW9ucyBsaWtlIFBI UEJCLCBEcnVwYWwg
YW5kIFdvcmRQcmVzcyBzaG91bGQgaGF2ZSB0aGVpciBkYXRhYmFzZSBhY2Nl c3MgZW5jYXBzdWxh
dGVkIHdlbGwgZW5vdWdoIHRoYXQgbWFraW5nIHRoZSBjaGFuZ2VzIHRvIHNw bGl0IHJlYWRzIGFu
ZCB3cml0ZXMgaXMgdHJpdmlhbC4gIElmIG5vdCwgdGhlbiBpdCdzIGJhZCBk ZXNpZ24gb24gdGhl
aXIgcGFydC4gU2NhbGFiaWxpdHkgaXMgdmVyeSBtdWNoIGEgcGFydCBvZiBh cHBsaWNhdGlvbiBk
ZXNpZ24gYXMgd2VsbCwgYW5kIGp1c3QgYmVjYXVzZSBsb3RzIG9mIHBlb3Bs ZSB1c2UgdGhvc2Ug
YXBwcywgZG9lc24ndCBtZWFuIHRoZXkgd2VyZSBkZXNpZ25lZCB0byBzY2Fs ZSB3ZWxsLg0KDQpS
ZWdhcmRzLA0KR2F2aW4gVG93ZXkNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdl LS0tLS0NCkZyb206
IFRpbSBHdXN0YWZzb24gW21haWx0bzp0amdAc29lLnVjc2MuZWR1XQ0KU2Vu dDogRnJpZGF5LCBT
ZXB0ZW1iZXIgMjUsIDIwMDkgMjo0NCBQTQ0KVG86IG15c3FsQGxpc3RzLm15 c3FsLmNvbQ0KU3Vi
amVjdDogUmU6IE1hc3Rlci9TbGF2ZSBSZXBsaWNhdGlvbiBRdWVzdGlvbg0K DQo+IEFub3RoZXIg
dGhvdWdodCB3b3VsZCBiZSBhdCB0aGUgYXBwbGljYXRpb24gbGF5ZXIsIHNl bmRpbmcNCj4gYWxs
IHRoZSB1cGRhdGVzIChpbnNlcnQsZGVsZXRlLCB1cGRhdGUsIGV0YykgdG8g c2VydmVyIEEsDQo+
IGFuZCB0aGUgc2VsZWN0cyB0byB0aGUgbG9jYWwgc2xhdmUgc2VydmVycy4N Cg0KVGhpcyBoYXMg
YmVlbiBzdWdnZXN0ZWQgYmVmb3JlLCBhbmQgSSdtIHRvdGFsbHkgYWdhaW5z dCBpdC4gIEFwcGxp
Y2F0aW9ucyBsaWtlIFBIUEJCLCBEcnVwYWwsIFdvcmRQcmVzcywgZXRjIGNh bid0IGJlIGVhc2ls
eSBjb25maWd1cmVkIHRvIGRvIHRoaXMsIGFuZCBJJ2QgcmVhbGx5IGxpa2Ug dG8gdXNlIHRob3Nl
IGFwcGxpY2F0aW9ucyBpbiBhIG1vcmUgcm9idXN0IGFuZCByZWR1bmRhbnQg ZW52aXJvbm1lbnQu
DQoNClB1c2hpbmcgdGhlIHdvcmsgb2YgdGhpcyBzb3J0IG9mIG1hc3Rlci9z bGF2ZSByZWxhdGlv
bnNoaXAgdG8gdGhlIGFwcGxpY2F0aW9uIGxldmVsIG1lYW5zIHRoYXQgbW9z dCBhcHBsaWNhdGlv
bnMgd2lsbCBub3Qgc3VwcG9ydCBpdC4gIFJlcGxpY2F0aW9uIGlzIGEgZGF0 YWJhc2Ugc2VydmVy
IHByb2JsZW0sIG5vdCBhbiBhcHBsaWNhdGlvbiBwcm9ibGVtLg0KDQpUaW0g R3VzdGFmc29uDQpC
YXNraW4gU2Nob29sIG9mIEVuZ2luZWVyaW5nDQpVQyBTYW50YSBDcnV6DQp0 amdAc29lLnVjc2Mu
ZWR1DQo4MzEtNDU5LTUzNTQNCg0KLS0NCk15U1FMIEdlbmVyYWwgTWFpbGlu ZyBMaXN0DQpGb3Ig
bGlzdCBhcmNoaXZlczogaHR0cDovL2xpc3RzLm15c3FsLmNvbS9teXNxbA0K VG8gdW5zdWJzY3Jp
YmU6ICAgIGh0dHA6Ly9saXN0cy5teXNxbC5jb20vbXlzcWw/dW5zdWI9Z3Rv d2V5QGZmbi5jb20N
Cg0KDQpUaGUgaW5mb3JtYXRpb24gY29udGFpbmVkIGluIHRoaXMgdHJhbnNt aXNzaW9uIG1heSBj
b250YWluIHByaXZpbGVnZWQgYW5kIGNvbmZpZGVudGlhbCBpbmZvcm1hdGlv bi4gSXQgaXMgaW50
ZW5kZWQgb25seSBmb3IgdGhlIHVzZSBvZiB0aGUgcGVyc29uKHMpIG5hbWVk IGFib3ZlLiBJZiB5
b3UgYXJlIG5vdCB0aGUgaW50ZW5kZWQgcmVjaXBpZW50LCB5b3UgYXJlIGhl cmVieSBub3RpZmll
ZCB0aGF0IGFueSByZXZpZXcsIGRpc3NlbWluYXRpb24sIGRpc3RyaWJ1dGlv biBvciBkdXBsaWNh
dGlvbiBvZiB0aGlzIGNvbW11bmljYXRpb24gaXMgc3RyaWN0bHkgcHJvaGli aXRlZC4gSWYgeW91
IGFyZSBub3QgdGhlIGludGVuZGVkIHJlY2lwaWVudCwgcGxlYXNlIGNvbnRh Y3QgdGhlIHNlbmRl
ciBieSByZXBseSBlbWFpbCBhbmQgZGVzdHJveSBhbGwgY29waWVzIG9mIHRo ZSBvcmlnaW5hbCBt
ZXNzYWdlLg0K

Re: Master/Slave Replication Question

> Moreover, it works today as opposed to waiting until the end
> of time for the database developers to add features like that
> (which mysql cluster is already a distributed database, and
> the devs have said they're not interested in trying to turn
> the regular mysql into a distributed product, instead they
> want to focus on what it does best)

With all due respect to the mySQL cluster people, setting up a mySQL cluster just isn't in the cards for lots of organizations. It's just too much. There's a huge implementation gap between a single mySQL server and a mySQL Cluster. I've also heard from people who have tried to implement mySQL clustering that wide-area cluster replication is hard or impossible (I can't remember which), so the ability to provide geographic redundancy (one of my requirements here) isn't workable.

I think saying that I'd have to wait until the end of time is a bit harsh. Sure, it's not going to happen tomorrow, but I wasn't expecting that anyhow.

I'm not sure if you've looked at the database integration for things like Drupal, but there will probably never be a way for Drupal to use an "updates go to this server, reads go to this server" configuration, as there are thousands of Drupal modules and almost all of them use the database directly, and each would have to be re-coded to work with the read/write split configuration.

And anyhow, I think that suggestion is missing the point:

If each application handles this sort of thing differently, then when I run all these applications on my server (and I do - we host about 175 web sites altogether) I have to configure each application separately, and I have to instruct all my users (many of them inexperienced grad students) to remember that "writes go here, reads go there" when they write their own PHP code.

And, of course, handling this sort of thing at the application level means that some applications will never support it, and therefore never be able to be geographically redundant.

So yeah, maybe lots of custom-written software handles the read/write split configuration well, but there's lots more that doesn't. I don't know of a single open source application that does.

So again, I go back to my original statement: replication is a database server problem, not an application problem. :)

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Master/Slave Replication Question

Q2x1c3RlciBpcyBub3Qgc3VwcG9zZWQgdG8gYmUgYSB1bml2ZXJzYWwgc29s dXRpb24sIGZvciBh
IHJlYXNvbi4gIFVuaXZlcnNhbCBzb2x1dGlvbnMgdGVuZCBub3QgdG8gYmUg dmVyeSBwZXJmb3Jt
YW50Lg0KDQoiSWYgZWFjaCBhcHBsaWNhdGlvbiBoYW5kbGVzIHRoaXMgc29y dCBvZiB0aGluZyBk
aWZmZXJlbnRseSwgdGhlbiB3aGVuIEkgcnVuIGFsbCB0aGVzZSBhcHBsaWNh dGlvbnMgb24gbXkg
c2VydmVyIChhbmQgSSBkbyAtIHdlIGhvc3QgYWJvdXQgMTc1IHdlYiBzaXRl cyBhbHRvZ2V0aGVy
KSBJIGhhdmUgdG8gY29uZmlndXJlIGVhY2ggYXBwbGljYXRpb24gc2VwYXJh dGVseSwgYW5kIEkg
aGF2ZSB0byBpbnN0cnVjdCBhbGwgbXkgdXNlcnMgKG1hbnkgb2YgdGhlbSBp bmV4cGVyaWVuY2Vk
IGdyYWQgc3R1ZGVudHMpIHRvIHJlbWVtYmVyIHRoYXQgIndyaXRlcyBnbyBo ZXJlLCByZWFkcyBn
byB0aGVyZSIgd2hlbiB0aGV5IHdyaXRlIHRoZWlyIG93biBQSFAgY29kZS4i DQoNCkRvIHlvdSB3
YW50IGdlb2dyYXBoaWMgcmVkdW5kYW5jeSBvciBkbyB5b3Ugd2FudCB0byBz Y2FsZSByZWFkcz8g
IEluIHRoaXMgY2FzZSB5b3UncmUgdGFsa2luZyBhYm91dCBzY2FsaW5nIHJl YWRzIGZvciBhIGJ1
bmNoIG9mIGFwcHMgYWxsIHJ1bm5pbmcgdG9nZXRoZXIuICBJZiB5b3Ugd2Fu dCBwZXJmb3JtYW5j
ZSBpbiB0aGF0IGNhc2UsIHRoZW4gZmlyc3QgeW91J2Qgd2FudCB0byBpc29s YXRlIHRoZSBhcHBz
IGZyb20gZWFjaCBvdGhlci4NCg0KDQoiQW5kLCBvZiBjb3Vyc2UsIGhhbmRs aW5nIHRoaXMgc29y
dCBvZiB0aGluZyBhdCB0aGUgYXBwbGljYXRpb24gbGV2ZWwgbWVhbnMgdGhh dCBzb21lIGFwcGxp
Y2F0aW9ucyB3aWxsIG5ldmVyIHN1cHBvcnQgaXQsIGFuZCB0aGVyZWZvcmUg bmV2ZXIgYmUgYWJs
ZSB0byBiZSBnZW9ncmFwaGljYWxseSByZWR1bmRhbnQuIg0KDQpHZW9ncmFw aGljYWwgcmVkdW5k
YW5jeSBpcyBkaWZmZXJlbnQ6ICBhIGRucyByZWNvcmQgd2l0aCBhIHplcm8g dHRsLCB3aXRoIGEg
bWFzdGVyLT5zbGF2ZSByZXBsaWNhdGlvbiBzZXR1cC4gUG9pbnQgdGhlIHJl Y29yZCBhIHRoZSBt
YXN0ZXIgYW5kIGlmIGl0IGZhaWxzLCBjaGFuZ2UgdGhlIGRucyBlbnRyeSB0 byBwb2ludCB0byB0
aGUgc2xhdmUuICBZb3VyIGFwcGxpY2F0aW9ucyBuZXZlciBuZWVkIHRvIGtu b3cgYWJvdXQgcmVw
bGljYXRpb24uDQoNClRoYXTigJlzIGV2ZW4gaWYgeW91IGRvbid0IHdhbnQg dG8gZ28gd2l0aCB0
aGUgbW9yZSBjb21wbGV4IExpbnV4IEhBIG9yIGhhcmR3YXJlIGJhc2VkIGlw IHRha2VvdmVyIHNv
bHV0aW9ucy4gIFRoZXJlIGFyZSBtYW55IHdheXMgeW91IGNvdWxkIGFkZCBy ZWR1bmRhbmN5IHdp
dGhvdXQgbW9kaWZ5aW5nIHRoZSBhcHBzLg0KDQpUaGF0J3MgdGhlIGdyZWF0 IHRoaW5nIGFib3V0
IG9wZW4gc291cmNlIHNvZnR3YXJlIGFuZCB0ZWNobmlxdWVzLiBUaGV5J3Jl IGxpa2UgYnVpbGRp
bmcgYmxvY2tzLCBhbmQgeW91IGNhbiBwdXQgdGhlbSB0b2dldGhlciBob3dl dmVyIHlvdSB3YW50
LiAgSSBmaW5kIHRoaXMgbXVjaCBtb3JlIHByZWZlcmFibGUgdG8gdGhlIGFs bC1pbi1vbmUgYmxh
Y2stYm94IHNvbHV0aW9uLg0KDQpSZWdhcmRzLA0KR2F2aW4gVG93ZXkNCg0K LS0tLS1PcmlnaW5h
bCBNZXNzYWdlLS0tLS0NCkZyb206IFRpbSBHdXN0YWZzb24gW21haWx0bzp0 amdAc29lLnVjc2Mu
ZWR1XQ0KU2VudDogRnJpZGF5LCBTZXB0ZW1iZXIgMjUsIDIwMDkgNDoxOCBQ TQ0KVG86IEdhdmlu
IFRvd2V5DQpDYzogbXlzcWxAbGlzdHMubXlzcWwuY29tDQpTdWJqZWN0OiBS ZTogTWFzdGVyL1Ns
YXZlIFJlcGxpY2F0aW9uIFF1ZXN0aW9uDQoNCj4gTW9yZW92ZXIsIGl0IHdv cmtzIHRvZGF5IGFz
IG9wcG9zZWQgdG8gd2FpdGluZyB1bnRpbCB0aGUgZW5kDQo+IG9mIHRpbWUg Zm9yIHRoZSBkYXRh
YmFzZSBkZXZlbG9wZXJzIHRvIGFkZCBmZWF0dXJlcyBsaWtlIHRoYXQNCj4g KHdoaWNoIG15c3Fs
IGNsdXN0ZXIgaXMgYWxyZWFkeSBhIGRpc3RyaWJ1dGVkIGRhdGFiYXNlLCBh bmQNCj4gdGhlIGRl
dnMgaGF2ZSBzYWlkIHRoZXkncmUgbm90IGludGVyZXN0ZWQgaW4gdHJ5aW5n IHRvIHR1cm4NCj4g
dGhlIHJlZ3VsYXIgbXlzcWwgaW50byBhIGRpc3RyaWJ1dGVkIHByb2R1Y3Qs IGluc3RlYWQgdGhl
eQ0KPiB3YW50IHRvIGZvY3VzIG9uIHdoYXQgaXQgZG9lcyBiZXN0KQ0KDQpX aXRoIGFsbCBkdWUg
cmVzcGVjdCB0byB0aGUgbXlTUUwgY2x1c3RlciBwZW9wbGUsIHNldHRpbmcg dXAgYSBteVNRTCBj
bHVzdGVyIGp1c3QgaXNuJ3QgaW4gdGhlIGNhcmRzIGZvciBsb3RzIG9mIG9y Z2FuaXphdGlvbnMu
ICBJdCdzIGp1c3QgdG9vIG11Y2guICBUaGVyZSdzIGEgaHVnZSBpbXBsZW1l bnRhdGlvbiBnYXAg
YmV0d2VlbiBhIHNpbmdsZSBteVNRTCBzZXJ2ZXIgYW5kIGEgbXlTUUwgQ2x1 c3Rlci4gIEkndmUg
YWxzbyBoZWFyZCBmcm9tIHBlb3BsZSB3aG8gaGF2ZSB0cmllZCB0byBpbXBs ZW1lbnQgbXlTUUwg
Y2x1c3RlcmluZyB0aGF0IHdpZGUtYXJlYSBjbHVzdGVyIHJlcGxpY2F0aW9u IGlzIGhhcmQgb3Ig
aW1wb3NzaWJsZSAoSSBjYW4ndCByZW1lbWJlciB3aGljaCksIHNvIHRoZSBh YmlsaXR5IHRvIHBy
b3ZpZGUgZ2VvZ3JhcGhpYyByZWR1bmRhbmN5IChvbmUgb2YgbXkgcmVxdWly ZW1lbnRzIGhlcmUp
IGlzbid0IHdvcmthYmxlLg0KDQpJIHRoaW5rIHNheWluZyB0aGF0IEknZCBo YXZlIHRvIHdhaXQg
dW50aWwgdGhlIGVuZCBvZiB0aW1lIGlzIGEgYml0IGhhcnNoLiAgU3VyZSwg aXQncyBub3QgZ29p
bmcgdG8gaGFwcGVuIHRvbW9ycm93LCBidXQgSSB3YXNuJ3QgZXhwZWN0aW5n IHRoYXQgYW55aG93
Lg0KDQpJJ20gbm90IHN1cmUgaWYgeW91J3ZlIGxvb2tlZCBhdCB0aGUgZGF0 YWJhc2UgaW50ZWdy
YXRpb24gZm9yIHRoaW5ncyBsaWtlIERydXBhbCwgYnV0IHRoZXJlIHdpbGwg cHJvYmFibHkgbmV2
ZXIgYmUgYSB3YXkgZm9yIERydXBhbCB0byB1c2UgYW4gInVwZGF0ZXMgZ28g dG8gdGhpcyBzZXJ2
ZXIsIHJlYWRzIGdvIHRvIHRoaXMgc2VydmVyIiBjb25maWd1cmF0aW9uLCBh cyB0aGVyZSBhcmUg
dGhvdXNhbmRzIG9mIERydXBhbCBtb2R1bGVzIGFuZCBhbG1vc3QgYWxsIG9m IHRoZW0gdXNlIHRo
ZSBkYXRhYmFzZSBkaXJlY3RseSwgYW5kIGVhY2ggd291bGQgaGF2ZSB0byBi ZSByZS1jb2RlZCB0
byB3b3JrIHdpdGggdGhlIHJlYWQvd3JpdGUgc3BsaXQgY29uZmlndXJhdGlv bi4NCg0KQW5kIGFu
eWhvdywgSSB0aGluayB0aGF0IHN1Z2dlc3Rpb24gaXMgbWlzc2luZyB0aGUg cG9pbnQ6DQoNCklm
IGVhY2ggYXBwbGljYXRpb24gaGFuZGxlcyB0aGlzIHNvcnQgb2YgdGhpbmcg ZGlmZmVyZW50bHks
IHRoZW4gd2hlbiBJIHJ1biBhbGwgdGhlc2UgYXBwbGljYXRpb25zIG9uIG15 IHNlcnZlciAoYW5k
IEkgZG8gLSB3ZSBob3N0IGFib3V0IDE3NSB3ZWIgc2l0ZXMgYWx0b2dldGhl cikgSSBoYXZlIHRv
IGNvbmZpZ3VyZSBlYWNoIGFwcGxpY2F0aW9uIHNlcGFyYXRlbHksIGFuZCBJ IGhhdmUgdG8gaW5z
dHJ1Y3QgYWxsIG15IHVzZXJzIChtYW55IG9mIHRoZW0gaW5leHBlcmllbmNl ZCBncmFkIHN0dWRl
bnRzKSB0byByZW1lbWJlciB0aGF0ICJ3cml0ZXMgZ28gaGVyZSwgcmVhZHMg Z28gdGhlcmUiIHdo
ZW4gdGhleSB3cml0ZSB0aGVpciBvd24gUEhQIGNvZGUuDQoNCkFuZCwgb2Yg Y291cnNlLCBoYW5k
bGluZyB0aGlzIHNvcnQgb2YgdGhpbmcgYXQgdGhlIGFwcGxpY2F0aW9uIGxl dmVsIG1lYW5zIHRo
YXQgc29tZSBhcHBsaWNhdGlvbnMgd2lsbCBuZXZlciBzdXBwb3J0IGl0LCBh bmQgdGhlcmVmb3Jl
IG5ldmVyIGJlIGFibGUgdG8gYmUgZ2VvZ3JhcGhpY2FsbHkgcmVkdW5kYW50 Lg0KDQpTbyB5ZWFo
LCBtYXliZSBsb3RzIG9mIGN1c3RvbS13cml0dGVuIHNvZnR3YXJlIGhhbmRs ZXMgdGhlIHJlYWQv
d3JpdGUgc3BsaXQgY29uZmlndXJhdGlvbiB3ZWxsLCBidXQgdGhlcmUncyBs b3RzIG1vcmUgdGhh
dCBkb2Vzbid0LiAgSSBkb24ndCBrbm93IG9mIGEgc2luZ2xlIG9wZW4gc291 cmNlIGFwcGxpY2F0
aW9uIHRoYXQgZG9lcy4NCg0KU28gYWdhaW4sIEkgZ28gYmFjayB0byBteSBv cmlnaW5hbCBzdGF0
ZW1lbnQ6IHJlcGxpY2F0aW9uIGlzIGEgZGF0YWJhc2Ugc2VydmVyIHByb2Js ZW0sIG5vdCBhbiBh
cHBsaWNhdGlvbiBwcm9ibGVtLiAgOikNCg0KVGltIEd1c3RhZnNvbg0KQmFz a2luIFNjaG9vbCBv
ZiBFbmdpbmVlcmluZw0KVUMgU2FudGEgQ3J1eg0KdGpnQHNvZS51Y3NjLmVk dQ0KODMxLTQ1OS01
MzU0DQoNClRoZSBpbmZvcm1hdGlvbiBjb250YWluZWQgaW4gdGhpcyB0cmFu c21pc3Npb24gbWF5
IGNvbnRhaW4gcHJpdmlsZWdlZCBhbmQgY29uZmlkZW50aWFsIGluZm9ybWF0 aW9uLiBJdCBpcyBp
bnRlbmRlZCBvbmx5IGZvciB0aGUgdXNlIG9mIHRoZSBwZXJzb24ocykgbmFt ZWQgYWJvdmUuIElm
IHlvdSBhcmUgbm90IHRoZSBpbnRlbmRlZCByZWNpcGllbnQsIHlvdSBhcmUg aGVyZWJ5IG5vdGlm
aWVkIHRoYXQgYW55IHJldmlldywgZGlzc2VtaW5hdGlvbiwgZGlzdHJpYnV0 aW9uIG9yIGR1cGxp
Y2F0aW9uIG9mIHRoaXMgY29tbXVuaWNhdGlvbiBpcyBzdHJpY3RseSBwcm9o aWJpdGVkLiBJZiB5
b3UgYXJlIG5vdCB0aGUgaW50ZW5kZWQgcmVjaXBpZW50LCBwbGVhc2UgY29u dGFjdCB0aGUgc2Vu
ZGVyIGJ5IHJlcGx5IGVtYWlsIGFuZCBkZXN0cm95IGFsbCBjb3BpZXMgb2Yg dGhlIG9yaWdpbmFs
IG1lc3NhZ2UuDQo=

Re: Master/Slave Replication Question

> Do you want geographic redundancy or do you want to scale reads?
> In this case you're talking about scaling reads for a bunch of
> apps all running together. If you want performance in that case,
> then first you'd want to isolate the apps from each other.

Geographic redundancy is my primary goal. Geographic redundancy also by its very nature helps boost performance of reads because you have more than one server from which to read. Each web site hosted on this group of servers has an "A" record that points to all three servers - so, foo.com has A records for 1.2.3.4, 2.3.4.5 and 3.4.5.6. Using round-robin DNS, the connections for clients are distributed across all three geographically redundant servers using poor-man's load balancing.

> Geographical redundancy is different: a dns record with a zero
> ttl, with a master->slave replication setup. Point the record a
> the master and if it fails, change the dns entry to point to the
> slave. Your applications never need to know about replication.

Except I'm not just making my database geographically redundant, but my web server and applications as well. Each server runs mySQL, Apache and PHP. PHP connects to "localhost" on each server. Using your method (which by the way requires human intervention to activate), only the mySQL would be redundant. And your solution does not balance reads across all mySQL servers.

> That's the great thing about open source software and
> techniques. They're like building blocks, and you can put them
> together however you want. I find this much more preferable to
> the all-in-one black-box solution.

You find it preferable to handle your database redundancy with something other than your database server? Any mySQL is black-box? I was pretty sure mySQL was open source.

Anyhow, you have actually proven my point: mySQL has already provided the building blocks of master/slave replication. Now I'd like to see an addition to that protocol (upstream updates) and bam, we've got a whole new replication methodology that fills the needs of a set of people whose needs were not met by multi-master replication or mySQL cluster.

At any rate, this part of the discussion is getting very off-topic. My original suggestion pertained to a new way of doing replication that I'd like to see in mySQL. If you don't like my idea, don't use the feature if and when it ever comes into being.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Master/Slave Replication Question

> from what i've read and seen geographical load balancer
> works as: multiple DNS A records routes to multiple
> Apache Servers(mod_php tucks in as a module under Apache)
> each web servers would connect to MySQL on their own
> localhost 1.2.3.4-WebServer would communicate directly to
> 1.2.3.4-MySQL (localhost) master-slave configurations
> imply A1-MySql master replicate to A2-MySql slave
> how should this new replication be handled?

You are correct about the DNS load balancing and connecting to localhost.

Here is the actual set-up I'm working with:

We have three LAMP servers: www-01, www-02 and www-03. Currently, mySQL on www-01 replicates to www-02, which replicates to www-03, which replicates back to www-01.

What I'd like to do is have www-01 be the authoritative server, and have www-02 and www-03 both be replication slaves to www-01. When a user connect to www-01 and makes an update, nothing special - the update gets recorded, written to the binary log and replicated to www-02 and www-03.

When a users attempts to update/insert/delete a record on www-02, I want www-02 to:

1. open a connection to www-01 (or have one connection that's already open for this purpose). if the connection to www-01 fails because of a TCP problem, www-02 should return an error along the lines of "replication master is unavailable; query not executed" to the client

2. authenticate against www-01 as the user the is currently authenticated on www-02. again, if this fails, return an error along the lines of "could not authenticate on replication master; query not executed"

3. pass the exact same query that www-02 received upstream to www-01

4. wait for www-01 to respond "ok" or "query error"

5. pass the response from www-01 back to the client connected to www-02

Is that clear? Normal replication would then pass the update from www-01 back to www-02 and www-03 so that all three servers are in sync. Since the update happened on www-01, auto_increments would not need to use the auto_increment_increment and auto_increment_offset, stored functions and stored procedures would not have to worry about their respective replication problems (certain types of functions/procedures aren't safe to use with normal mySQL replication), and locking would work (because lock statements would also be passed up the chain to the master server).

Yes, this means that updates wouldn't be as fast as multi-master replication, but I think dealing with almost all the other problems with replication would become much more manageable.

This set-up makes the most sense in read-heavy environments. As I mentioned before, my main goal here is geographic redundancy, load balancing and the ability to use applications that aren't really designed to be used with master/slave replication (Drupal, WordPress, etc) without having to modify those applications.

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@soe.ucsc.edu
831-459-5354

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org