From owner-freebsd-isp@FreeBSD.ORG Fri Mar 24 21:23:53 2006 Return-Path: X-Original-To: freebsd-isp@freebsd.org Delivered-To: freebsd-isp@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5A6C516A401 for ; Fri, 24 Mar 2006 21:23:53 +0000 (UTC) (envelope-from usmanbsd@yahoo.com) Received: from web38709.mail.mud.yahoo.com (web38709.mail.mud.yahoo.com [209.191.125.85]) by mx1.FreeBSD.org (Postfix) with SMTP id EEFDD43D45 for ; Fri, 24 Mar 2006 21:23:52 +0000 (GMT) (envelope-from usmanbsd@yahoo.com) Received: (qmail 13813 invoked by uid 60001); 24 Mar 2006 21:23:52 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=H3DuuhWbeWFyn9ClnOSRUMaLg0thFxUaFMW4t1ieOQqhbWnfifPona43o62Q4rCh410FQQ79htFSiD4D0sl8Dibi7HIxHElyLNj+KwY1EV9ZhRI8NKIl542hDL80jayJTVzErU7qoH1cWL1dJTk4GM8yzSyrHrfs/OEop5m2xes= ; Message-ID: <20060324212352.13811.qmail@web38709.mail.mud.yahoo.com> Received: from [202.59.80.55] by web38709.mail.mud.yahoo.com via HTTP; Fri, 24 Mar 2006 13:23:52 PST Date: Fri, 24 Mar 2006 13:23:52 -0800 (PST) From: muhammad usman To: lannygodsey@yahoo.com, freebsd-isp@freebsd.org In-Reply-To: <20060322123541.55063.qmail@web33313.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Cc: Subject: Re: MySQL Clustering X-BeenThere: freebsd-isp@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Internet Services Providers List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 24 Mar 2006 21:23:53 -0000 There is a very usefull book of O-Reilly on MySQL load balancing. And according to it you can have only on writeable SQL server, and load balancing only works for read-only queries. but as Mr.Cody Baker suggested its better if you configure your load balancer to make persistent binding. regards usman --- "L. Jason Godsey" wrote: > > The following solves replication breaking over the > same auto int race > condition. This can happen because of fast inserts > from multiple hosts > connected to multiple back end servers (8 in my > case), or because of > disconnected operation. > > The way I solved this in the past was using > composite keys. Instead of > just the auto integer field, I also used a server_id > field. > > Prior to inserting anything into this database, > issue the query > set @server_id=@@server_id;, reuse the db connection > for the insert. > > Sometimes you can use something like: > > $query = "set @server_id=@@server_id; insert > into....."; > > Don't skip the seemingly redundant, @server_id=@@ > bit and go with > insert into VALUES (@@server_id)... then each server > doing the > replication will use it's own server_id which isn't > what you want. > > create table users ( > id int auto_increment, > server_id int, > login varchar(32), > pass varchar(32), > primary key (id,server_id) > ); > create unique index _i_users_login on users (login); > > mysql> set @server_id=@@server_id; > Query OK, 0 rows affected (0.00 sec) > > mysql> insert into users (server_id, login, pass) > values (@server_id, > 'lanny', 'examples'); > Query OK, 1 row affected (0.00 sec) > > mysql> select * from users; > +----+-----------+-------+----------+ > | id | server_id | login | pass | > +----+-----------+-------+----------+ > | 1 | 2 | lanny | examples | > +----+-----------+-------+----------+ > 1 row in set (0.00 sec) > > mysql> set @server_id=32; insert into users > (server_id, login, pass) > values (@server_id, 'lanny3', 'examples'); > Query OK, 0 rows affected (0.00 sec) > > Query OK, 1 row affected (0.00 sec) > > mysql> select * from users; > +----+-----------+--------+----------+ > | id | server_id | login | pass | > +----+-----------+--------+----------+ > | 1 | 2 | lanny | examples | > | 2 | 2 | lanny2 | examples | > | 3 | 32 | lanny3 | examples | > +----+-----------+--------+----------+ > > > --- Cody Baker wrote: > > > If you're database is simply read only then the > load balanced > > situation > > should work fine. If your database is read/write > then your load > > balancer could cause you problems under certain > circumstances. If > > your > > updates are not time critical then it should be > fine. If, however, > > your > > databases are used for a time critical updates > (ex. session data for > > a > > web page) then it's important that your users > always hit the same > > database server because the replication can become > delayed at times. > > If > > the user has a random chance of hitting any of > your database servers > > then for example your user may add something to > their cart, and > > refresh > > the page connecting to another server, and find > the item missing from > > their cart. Other no-nos for replicated databases > especially on mysql > > < > > 5, are auto-incrementing fields in tables. 5.x > has a solution for > > this > > ( > > > http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html > > ). > > > > Thank You, > > > > Cody Baker > > cody@wilkshire.net > > http://www.wilkshire.net > > Jon Simola wrote: > > > On 3/15/06, James Ryan > wrote: > > > > > >> Not sure if this is the right list for this (I > apologize if its > > not), > > >> but has anybody ran a MySQL 2+ node cluster > under FreeBSD 5.x > > behind a > > >> load balancer; and if so, could you offer any > tips or warnings? > > >> > > > > > > If you're talking about the actual MySQL > clustering server setup, > > I've > > > never had a chance to try it as our dataset is > too large. > > > > > > I've run a pair of MySQL servers in a > round-robin master setup (A > > > slaves from B, B slaves from A) and that worked > rather well, > > > replication was impressively quick. > > > > > > -- > > > Jon Simola > > > Systems Administrator > > > ABC Communications > > > _______________________________________________ > > > freebsd-isp@freebsd.org mailing list > > > > http://lists.freebsd.org/mailman/listinfo/freebsd-isp > > > To unsubscribe, send any mail to > > "freebsd-isp-unsubscribe@freebsd.org" > > > > > > > _______________________________________________ > > freebsd-isp@freebsd.org mailing list > > > http://lists.freebsd.org/mailman/listinfo/freebsd-isp > > To unsubscribe, send any mail to > > "freebsd-isp-unsubscribe@freebsd.org" > > > > _______________________________________________ > freebsd-isp@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-isp > To unsubscribe, send any mail to > "freebsd-isp-unsubscribe@freebsd.org" > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com