Re: [CentOS] Clustering MySQL
On Dec 12, 2007 4:46 PM, Karanbir Singh [EMAIL PROTECTED] 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/ : [EMAIL PROTECTED] 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. -- -matt ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
On Dec 11, 2007 12:42 PM, Karanbir Singh [EMAIL PROTECTED] 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/ : [EMAIL PROTECTED] 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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering 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 -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
[CentOS] 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
Re: [CentOS] Clustering MySQL
On Dec 11, 2007 12:18 PM, Steve Campbell [EMAIL PROTECTED] 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
Re: [CentOS] 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 :D -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] 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 :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: [EMAIL PROTECTED] Unix Systems Administrator [EMAIL PROTECTED] OSU Libraries, Corvallis, OR 97331Office: Valley Library #4657 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] 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
Re: [CentOS] Clustering MySQL
On Dec 11, 2007, at 11:29 AM, Matt Shields wrote: On Dec 11, 2007 1:39 PM, J. Potter [EMAIL PROTECTED] 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: [EMAIL PROTECTED] Unix Systems Administrator [EMAIL PROTECTED] OSU Libraries, Corvallis, OR 97331Office: Valley Library #4657 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] 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/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] 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: [EMAIL PROTECTED] Unix Systems Administrator [EMAIL PROTECTED] OSU Libraries, Corvallis, OR 97331Office: Valley Library #4657 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
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. -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
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. -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
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. -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] Clustering MySQL
On Dec 11, 2007 6:10 PM, Karanbir Singh [EMAIL PROTECTED] 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. :) -- -matt ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
