FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > CentOS > CentOS

 
 
LinkBack Thread Tools
 
Old 12-11-2007, 04:18 PM
Steve Campbell
 
Default Clustering MySQL

I'm just beginning to consider using the Clustering available with
CentOS. We are going to spec out some new hardware, and after reading
most of the Clustering manuals, I have a small question about MySQL.


I would like to run High Availability MySQL, in other words, similar to
how you can run HA HTTPD and the like. The catch seems to be if I run
MySQL on an individual server, with common MySQL replication to another
server, how do failovers work? I see a real problem with table locking
and the like. Is there a way to run multiple MySQL servers that get
removed from the cluster as opposed to failing over when using the newer
MySQL versions (I am running 3.23 now, so a little behind)?


Thanks for any insights.

Steve Campbell



_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 04:32 PM
"Matt Shields"
 
Default Clustering MySQL

On Dec 11, 2007 12:18 PM, Steve Campbell <campbell@cnpapers.com> wrote:
> I'm just beginning to consider using the Clustering available with
> CentOS. We are going to spec out some new hardware, and after reading
> most of the Clustering manuals, I have a small question about MySQL.
>
> I would like to run High Availability MySQL, in other words, similar to
> how you can run HA HTTPD and the like. The catch seems to be if I run
> MySQL on an individual server, with common MySQL replication to another
> server, how do failovers work? I see a real problem with table locking
> and the like. Is there a way to run multiple MySQL servers that get
> removed from the cluster as opposed to failing over when using the newer
> MySQL versions (I am running 3.23 now, so a little behind)?
>
> Thanks for any insights.

There are a number of ways to do it. We currently have 1 master mysql
server, and multiple replicas. We then load balance the reads only
from the replicas and writes go to the master database (all handled in
the code). But I saw a presentation at the Boston MySQL Meetup.com
group about how to do master-master in mysql 5. We're about to
implement this in the next few weeks. If it's done this way both
reads and writes to the db can be load balanced. We use
linuxvirtualserver.org (heartbeat, ipvsadm and ldirectord) for load
balancing.

You might be able to contact the Meetup organizer, Sherri
http://mysql.meetup.com/137/, she usually posts the presentations
online.

--
-matt
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 04:42 PM
Karanbir Singh
 
Default Clustering MySQL

Matt Shields wrote:

the code). But I saw a presentation at the Boston MySQL Meetup.com
group about how to do master-master in mysql 5. We're about to
implement this in the next few weeks. If it's done this way both


that is imho, a mysql-5.1 only feature, where you can have rbr and
multimaster setups that actually work. and 5.1 isnt quite ready for
release as yet


--
Karanbir Singh : http://www.karan.org/ : 2522219@icq
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 05:07 PM
Ryan Ordway
 
Default Clustering MySQL

On Dec 11, 2007, at 9:42 AM, Karanbir Singh wrote:


Matt Shields wrote:

the code). But I saw a presentation at the Boston MySQL Meetup.com
group about how to do master-master in mysql 5. We're about to
implement this in the next few weeks. If it's done this way both


that is imho, a mysql-5.1 only feature, where you can have rbr and
multimaster setups that actually work. and 5.1 isnt quite ready for
release as yet


I'm running a multi-master setup with 5.0 in production with a
moderate amount of success. I did try 5.1 a few months ago and it died
a horrible, fiery death.


You will definitely need auto_increment_increment and
auto_increment_offset and replicate-same-server-id set to 0.


FYI, I recently took a MySQL High Availability class, and multi-master
is definitely not a standard configuration. It was only briefly
touched on, and only one other person there had it running in
production. But, while it's not officially supported they do their
best to make it work.


Specifically, what makes you say it is a 5.1 only feature? What does
5.1 give you that makes it easier than 5.0?



Ryan

--
Ryan Ordway E-mail: rordway@oregonstate.edu
Unix Systems Administrator rordway@library.oregonstate.edu
OSU Libraries, Corvallis, OR 97331 Office: Valley Library #4657









_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 05:39 PM
"J. Potter"
 
Default Clustering MySQL

... But I saw a presentation at the Boston MySQL Meetup.com
group about how to do master-master in mysql 5. We're about to
implement this in the next few weeks. ...


I've run into issues with crash recovery in master-master mode:

- master A is at position X
- master B, replicating from A, gets to position X
- master A syncs to its filesystem that it's at position X

- master A receives some inserts, and is now at position Y
- master B, replicating from A, gets to position Y
- master A crashes before the position gets synced to filesystem
- master A gets rebooted, recovers from innodb log, but has itself
only marked at position X
- master B requests position Y from master A, but that position
doesn't exist yet, so replication breaks.


Perhaps someone here knows the proper recovery procedure at this point?

best,
Jeff
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 06:29 PM
"Matt Shields"
 
Default Clustering MySQL

On Dec 11, 2007 1:39 PM, J. Potter <jpotter-centos@codepuppy.com> wrote:
>
> > ... But I saw a presentation at the Boston MySQL Meetup.com
> > group about how to do master-master in mysql 5. We're about to
> > implement this in the next few weeks. ...
>
> I've run into issues with crash recovery in master-master mode:
>
> - master A is at position X
> - master B, replicating from A, gets to position X
> - master A syncs to its filesystem that it's at position X
>
> - master A receives some inserts, and is now at position Y
> - master B, replicating from A, gets to position Y
> - master A crashes before the position gets synced to filesystem
> - master A gets rebooted, recovers from innodb log, but has itself
> only marked at position X
> - master B requests position Y from master A, but that position
> doesn't exist yet, so replication breaks.
>
> Perhaps someone here knows the proper recovery procedure at this point?

If this were master-slave, I'd probably do an LVM Snapshot and get a
fresh copy of the master db. The same could be done for
master-master.

--
-matt
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 07:28 PM
"J. Potter"
 
Default Clustering MySQL

- master A is at position X
- master B, replicating from A, gets to position X
- master A syncs to its filesystem that it's at position X

- master A receives some inserts, and is now at position Y
- master B, replicating from A, gets to position Y
- master A crashes before the position gets synced to filesystem
- master A gets rebooted, recovers from innodb log, but has itself
only marked at position X
- master B requests position Y from master A, but that position
doesn't exist yet, so replication breaks.

Perhaps someone here knows the proper recovery procedure at this
point?


If this were master-slave, I'd probably do an LVM Snapshot and get a
fresh copy of the master db. The same could be done for
master-master.


I'm not sure this would work, since some data will have been inserted
in on master B as well. I.e., with master-master, a one-way sync won't
work. The only recovery option that I can see is to destroy Master A,
and copy Master B -- either via an LVM snapshot or shutdown, sync,
startup -- to create a new Master A. Maybe this is what you're
suggesting?


Is there a better way?

best,
Jeff
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 07:31 PM
Ryan Ordway
 
Default Clustering MySQL

On Dec 11, 2007, at 11:29 AM, Matt Shields wrote:

On Dec 11, 2007 1:39 PM, J. Potter <jpotter-centos@codepuppy.com>
wrote:



... But I saw a presentation at the Boston MySQL Meetup.com
group about how to do master-master in mysql 5. We're about to
implement this in the next few weeks. ...


I've run into issues with crash recovery in master-master mode:

- master A is at position X
- master B, replicating from A, gets to position X
- master A syncs to its filesystem that it's at position X

- master A receives some inserts, and is now at position Y
- master B, replicating from A, gets to position Y
- master A crashes before the position gets synced to filesystem
- master A gets rebooted, recovers from innodb log, but has itself
only marked at position X
- master B requests position Y from master A, but that position
doesn't exist yet, so replication breaks.

Perhaps someone here knows the proper recovery procedure at this
point?


If this were master-slave, I'd probably do an LVM Snapshot and get a
fresh copy of the master db. The same could be done for
master-master.



The problem is you'll have some inconsistency between your master A's
view of the database and the master B's view. You lose any changes to
the data on master B. It would be nice to be able to merge any changes
from B that hadn't made their way to master A yet. At that point
you're examining binlogs.


Ryan

--
Ryan Ordway E-mail: rordway@oregonstate.edu
Unix Systems Administrator rordway@library.oregonstate.edu
OSU Libraries, Corvallis, OR 97331 Office: Valley Library #4657









_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 09:44 PM
Karanbir Singh
 
Default Clustering MySQL

Ryan Ordway wrote:
> Specifically, what makes you say it is a 5.1 only feature? What does 5.1
> give you that makes it easier than 5.0?

specifically - rbr

we've had load of issues with mysql-5.0 recently ( i think were just
tryign to use mysql like too much of a real database, while we seem to
have clearly outgrown its capabilities )

issues like cascading deletes not working as yet in replicas, and quite
a few issues with stored proc's etc.

--
Karanbir Singh : http://www.karan.org/ : 2522219@icq
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 12-11-2007, 09:52 PM
Ryan Ordway
 
Default Clustering MySQL

On Dec 11, 2007, at 2:44 PM, Karanbir Singh wrote:


Ryan Ordway wrote:
Specifically, what makes you say it is a 5.1 only feature? What
does 5.1

give you that makes it easier than 5.0?


specifically - rbr


Ahh, true.

( i think were just tryign to use mysql like too much of a real
database, while we seem to have clearly outgrown its capabilities )


I think the MySQL AB folks would object to that statement. ;-)

Ryan

--
Ryan Ordway E-mail: rordway@oregonstate.edu
Unix Systems Administrator rordway@library.oregonstate.edu
OSU Libraries, Corvallis, OR 97331 Office: Valley Library #4657









_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 

Thread Tools




All times are GMT. The time now is 05:11 AM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright 2007 - 2008, www.linux-archive.org