Date: Fri, 24 Mar 2006 13:23:52 -0800 (PST) From: muhammad usman <usmanbsd@yahoo.com> To: lannygodsey@yahoo.com, freebsd-isp@freebsd.org Subject: Re: MySQL Clustering Message-ID: <20060324212352.13811.qmail@web38709.mail.mud.yahoo.com> In-Reply-To: <20060322123541.55063.qmail@web33313.mail.mud.yahoo.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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" <lannygodsey@yahoo.com> 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 <cody@wilkshire.net> 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
> <james@infinityprosports.com> 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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20060324212352.13811.qmail>