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
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 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 :D
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 :D
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
... 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
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.
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
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
Ryan Ordway wrote:
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.
I wonder if someone has spent the time to write such a tool, that would workout what this diff/delta was, and then be able to bring both machines upto speed, so after a period of time ( hopefully short ), they would both be in the same state.
Matt Shields wrote:
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.
has a live lvm-snapshot ever worked for you as a real move-data-around policy ? you would, at the very least, need to flush in memory data, and have a system wide write lock in place while the snapshot is created.
its been a tempting idea, but so far of the few people I know having tried this lvm snapshoting, have never actually managed to get it working right for mysql dumps. So, would be good to hear from someone who has it working.
On Dec 11, 2007 6:10 PM, Karanbir Singh mail-lists@karan.org wrote:
Matt Shields wrote:
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.
has a live lvm-snapshot ever worked for you as a real move-data-around policy ? you would, at the very least, need to flush in memory data, and have a system wide write lock in place while the snapshot is created.
its been a tempting idea, but so far of the few people I know having tried this lvm snapshoting, have never actually managed to get it working right for mysql dumps. So, would be good to hear from someone who has it working.
I didn't put all the details, we have a custom script that we run which locks the tables, does a flush, starts a lvm snapshot. We can then copy the mysql data, when the copy is done we've got a script to release the snapshot.
The thing you need to remember when you image the server is to make sure you leave some unused diskspace on your partition. So for example if you have a 100GB drive, put it all into the pv and lv, but only create a 80GB vg. That gives you 20GB for the snapshot. Of course when calculating how much extra space you need, you need to think about how fast your data grows and how much time you need to do a copy of the data. If your snapshot is too small and you outgrow the snapshot before you've finished copying your data, then the snapshot will expire. Works great for us in our master -> multiple slave environment. Some of our slaves even have slaves. :)
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.
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
Ryan Ordway 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. ;-)
reality is what it is, I have zero problems with working through the issues with them if they choose to do so.
Ryan Ordway wrote:
( 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. ;-)
you mean the folks who scoffed at the idea transactions were important, or the folks that treat 'foreign key' as a comment ?
:-D
On Dec 11, 2007 12:42 PM, Karanbir Singh mail-lists@karan.org 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 :D
-- Karanbir Singh : http://www.karan.org/ : 2522219@icq
I just got my master-master servers setup and we're running mysql-server-5.0.48-1.el4.centos. I should also mention that Meetup presentation was given by Patrick Galbraith who used to work for MySQL and was responsible for adding replication to MySQL.
Matt Shields wrote:
I just got my master-master servers setup and we're running mysql-server-5.0.48-1.el4.centos. I should also mention that Meetup presentation was given by Patrick Galbraith who used to work for MySQL and was responsible for adding replication to MySQL.
sounds good, will you do a howto for the centos wiki ?
- KB
On Dec 12, 2007 4:46 PM, Karanbir Singh mail-lists@karan.org wrote:
Matt Shields wrote:
I just got my master-master servers setup and we're running mysql-server-5.0.48-1.el4.centos. I should also mention that Meetup presentation was given by Patrick Galbraith who used to work for MySQL and was responsible for adding replication to MySQL.
sounds good, will you do a howto for the centos wiki ?
- KB
-- Karanbir Singh : http://www.karan.org/ : 2522219@icq
I'll see what I can do. I'm so backlogged with work and I've promised quite a few docs to people, but I'll try.
On 11/12/2007 17:18, Steve Campbell 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.
Steve Campbell
After all the discussions regarding MySQL-style clustering (multi-master etc), what about a "classic" HA cluster for MySQL? Since the OP mentioned high availability, wouldn't the simplest solution be failover clustering (ie. single master with failover, shared storage, fenced nodes etc) via Centos CS?
As I haven't done this myself I can't really comment further, but does anyone else on the list have experience engineering a Centos Cluster Suit failover cluster for MySQL?
cheers Luke
Luke Dudney wrote:
After all the discussions regarding MySQL-style clustering (multi-master etc), what about a "classic" HA cluster for MySQL? Since the OP mentioned high availability, wouldn't the simplest solution be failover clustering (ie. single master with failover, shared storage, fenced nodes etc) via Centos CS?
You could achieve something like this just using shared storage, a clustered filesystem, and mysql-proxy balancing a bunch of mysql instances.
As I haven't done this myself I can't really comment further, but does anyone else on the list have experience engineering a Centos Cluster Suit failover cluster for MySQL?
not me no :D not the whole boatload anyway.
After all the discussions regarding MySQL-style clustering (multi- master etc), what about a "classic" HA cluster for MySQL? Since the OP mentioned high availability, wouldn't the simplest solution be failover clustering (ie. single master with failover, shared storage, fenced nodes etc) via Centos CS?
We have this running in one setup, and it's been working (mostly) fine:
- master-master setup - heartbeat creating a virtual IP - all mysql clients use the virtual IP
So, effectively, it's a master-master setup where only 1 master is ever receiving traffic, and if that master fails, it'll automatically fail-over to the standby master.
The benefit of doing master-master in this scenario is that there's no real recovery process needed for restoring redundancy -- when the failed master comes back online, it catches up with the current master. (Make sure auto-fallback is off in heartbeat.)
The only problem I've seen is that a crashed node may not be able to replicate correctly, if its on-disk log position gets out of sync with what the other node has. It seems if this happens one has to do a real sync (lock tables, lvm snapshot, unlock tables, if you're willing to give up the storage needed for the lvm snapshot; or rsync, shutdown and re-rsync, startup).
best, Jeff
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)?
After all the discussions regarding MySQL-style clustering (multi-master etc), what about a "classic" HA cluster for MySQL? Since the OP mentioned high availability, wouldn't the simplest solution be failover clustering (ie. single master with failover, shared storage, fenced nodes etc) via Centos CS?
As I haven't done this myself I can't really comment further, but does anyone else on the list have experience engineering a Centos Cluster Suit failover cluster for MySQL?
cheers Luke
I use a MySQL high-availability setup in a 800-1000 concurrent connection environment. We use DRBD and Heartbeat and it's bulletproof. See http://marksitblog.blogspot.com/2007/07/mysql-5-high-availability-with-drbd-... for an example configuration.
-
J Potter -
J. Potter
-
John R Pierce -
Karanbir Singh -
Ken Price -
Luke Dudney -
Matt Shields -
Ryan Ordway -
Steve Campbell