Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Linux-HA: Pacemaker

MySQL-Cluster with Pacemaker

  

 
Linux-HA pacemaker RSS feed   Index | Next | Previous | View Threaded


AStallmann at CONET

Jan 30, 2012, 1:46 AM

Post #1 of 8 (930 views)
Permalink
MySQL-Cluster with Pacemaker
Hi!

I’m on the lookout for alternatives to our current MySQL “cluster”, which is an Active/Standby solution with MySQL on DRBD.
With increasing customer demand for a faster failover, we want an Active/Passive or even an Active/Active cluster.

Currently we run a Tomcat application, which works in Active/Passive-Mode. The applications on the active node communicate it’s status via a MySQL database to the passive node. In our current setup, both nodes run the tomcat application but only one holds the database (due to the setup with DRBD). For faster failovers, we’d rather want the database to be active on both nodes. It’s not necessary, that it can written on both nodes, but read access would be desirable. The thought is, that switching the “master status” of a database might be quicker than switching DRBD’s master status, unmounting and mounting the file system and stopping and starting the database.

A MySQL/Pacemaker cluster with replication as described in http://www.mysqlperformanceblog.com/2011/11/29/percona-replication-manager-a-solution-for-mysql-high-availability-with-replication-using-pacemaker/ thus looked very promising, but it seems to be not yet mature enough for a production environment. Please do correct men if I’m wrong there, I’m really interested in your experience with this solution in a real world scenario.

Are there perhaps other howtos describing Pacemaker and MySQL replication?

The second idea was using the native MySQL NDB clustering with Pacemaker. http://www.mysqlperformanceblog.com/2010/05/19/pacemaker-please-meet-ndb-cluster-or-using-pacemakerheartbeat-to-start-a-ndb-cluster/ (from 2010, uses heartbeat and pacemaker) and http://www.howtoforge.com/loadbalanced_mysql_cluster_debian (from 2008, uses pure heartbeat). Are there any more recent “howtos” on pacemaker and MySQL NDB 7.x describe this. Can you provide me with your opinions and field reports on these setups?

Looking forward to your upcoming mails,

Andreas








----------------------------
CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
Geschäftsführer/Managing Director: Anke Höfer

----------------------------


andreas at hastexo

Feb 1, 2012, 2:54 PM

Post #2 of 8 (885 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
Hello,

On 01/30/2012 10:46 AM, Stallmann, Andreas wrote:
> Hi!
>
>
>
> Im on the lookout for alternatives to our current MySQL cluster,
> which is an Active/Standby solution with MySQL on DRBD.
>
> With increasing customer demand for a faster failover, we want an
> Active/Passive or even an Active/Active cluster.

Already had a look at this presentation my colleague Florian held at
Percona Live UK 2011 in London?

http://goo.gl/5mDFR

Regards,
Andreas

--
Need help with Pacemaker?
http://www.hastexo.com/now

>
>
>
> Currently we run a Tomcat application, which works in
> Active/Passive-Mode. The applications on the active node communicate
> its status via a MySQL database to the passive node. In our current
> setup, both nodes run the tomcat application but only one holds the
> database (due to the setup with DRBD). For faster failovers, wed rather
> want the database to be active on both nodes. Its not necessary, that
> it can written on both nodes, but read access would be desirable. The
> thought is, that switching the master status of a database might be
> quicker than switching DRBDs master status, unmounting and mounting the
> file system and stopping and starting the database.
>
>
>
> A MySQL/Pacemaker cluster with replication as described in
> http://www.mysqlperformanceblog.com/2011/11/29/percona-replication-manager-a-solution-for-mysql-high-availability-with-replication-using-pacemaker/
> thus looked very promising, but it seems to be not yet mature enough
> for a production environment. Please do correct men if Im wrong there,
> Im really interested in your experience with this solution in a real
> world scenario.
>
>
>
> Are there perhaps other howtos describing Pacemaker and MySQL replication?
>
>
>
> The second idea was using the native MySQL NDB clustering with
> Pacemaker.
> http://www.mysqlperformanceblog.com/2010/05/19/pacemaker-please-meet-ndb-cluster-or-using-pacemakerheartbeat-to-start-a-ndb-cluster/
> (from 2010, uses heartbeat and pacemaker) and
> http://www.howtoforge.com/loadbalanced_mysql_cluster_debian (from 2008,
> uses pure heartbeat). Are there any more recent howtos on pacemaker
> and MySQL NDB 7.x describe this. Can you provide me with your opinions
> and field reports on these setups?
>
>
>
> Looking forward to your upcoming mails,
>
>
>
> Andreas
>
>
>
>
>
>
>
>
>
>
>
>
>
> ----------------------------
> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
> Geschftsfhrer/Managing Director: Anke Hfer
>
> ----------------------------
>
>
>
>
>
> _______________________________________________
> Pacemaker mailing list: Pacemaker [at] oss
> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>
> Project Home: http://www.clusterlabs.org
> Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
> Bugs: http://bugs.clusterlabs.org
Attachments: signature.asc (0.22 KB)


AStallmann at CONET

Feb 2, 2012, 1:42 AM

Post #3 of 8 (880 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
Well, yes, I read the presentation a while ago, but it didn't present me with any new information.

What Florian does is replicate a MySQL-Master over two nodes with DRBD and have several MySQL-Slaves replicate their data from the current Master via the built-in mechanism.

That's firstly quite nice, if you have a scenario, where there is the need for multiple nodes to be read from and where one single node for writing is sufficient.

Secondly, this scenario works only with 2+(n>1) nodes, whereas we have (and want) a classical 2 node scenario.

Thirdly (and that's the main issue with MySQL on DRBD), the failover process still takes quite long, because of the amount of events due to happen: Stop mysql on node 1, unmount the filesystem, switch DRBD master/slave, mount filesystem on node 2, start mysql (and perhaps repair tables).

Still, thanks for the suggestion.

Cheers,

Andreas



-----------------------------
CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
Geschftsfhrer/Managing Directors: Anke Hfer
-----------------------------

-----Ursprngliche Nachricht-----
Von: Andreas Kurz [mailto:andreas [at] hastexo]
Gesendet: Mittwoch, 1. Februar 2012 23:55
An: pacemaker [at] oss
Betreff: Re: [Pacemaker] MySQL-Cluster with Pacemaker

Hello,

On 01/30/2012 10:46 AM, Stallmann, Andreas wrote:
> Hi!
>
>
>
> I'm on the lookout for alternatives to our current MySQL "cluster",
> which is an Active/Standby solution with MySQL on DRBD.
>
> With increasing customer demand for a faster failover, we want an
> Active/Passive or even an Active/Active cluster.

Already had a look at this presentation my colleague Florian held at Percona Live UK 2011 in London?

http://goo.gl/5mDFR

Regards,
Andreas

--
Need help with Pacemaker?
http://www.hastexo.com/now

>
>
>
> Currently we run a Tomcat application, which works in
> Active/Passive-Mode. The applications on the active node communicate
> it's status via a MySQL database to the passive node. In our current
> setup, both nodes run the tomcat application but only one holds the
> database (due to the setup with DRBD). For faster failovers, we'd
> rather want the database to be active on both nodes. It's not
> necessary, that it can written on both nodes, but read access would be
> desirable. The thought is, that switching the "master status" of a
> database might be quicker than switching DRBD's master status,
> unmounting and mounting the file system and stopping and starting the database.
>
>
>
> A MySQL/Pacemaker cluster with replication as described in
> http://www.mysqlperformanceblog.com/2011/11/29/percona-replication-man
> ager-a-solution-for-mysql-high-availability-with-replication-using-pac
> emaker/ thus looked very promising, but it seems to be not yet mature
> enough for a production environment. Please do correct men if I'm
> wrong there, I'm really interested in your experience with this
> solution in a real world scenario.
>
>
>
> Are there perhaps other howtos describing Pacemaker and MySQL replication?
>
>
>
> The second idea was using the native MySQL NDB clustering with
> Pacemaker.
> http://www.mysqlperformanceblog.com/2010/05/19/pacemaker-please-meet-n
> db-cluster-or-using-pacemakerheartbeat-to-start-a-ndb-cluster/
> (from 2010, uses heartbeat and pacemaker) and
> http://www.howtoforge.com/loadbalanced_mysql_cluster_debian (from
> 2008, uses pure heartbeat). Are there any more recent "howtos" on
> pacemaker and MySQL NDB 7.x describe this. Can you provide me with
> your opinions and field reports on these setups?
>
>
>
> Looking forward to your upcoming mails,
>
>
>
> Andreas
>
>
>
>
>
>
>
>
>
>
>
>
>
> ----------------------------
> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr.
> 9136) Geschftsfhrer/Managing Director: Anke Hfer
>
> ----------------------------
>
>
>
>
>
> _______________________________________________
> Pacemaker mailing list: Pacemaker [at] oss
> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>
> Project Home: http://www.clusterlabs.org Getting started:
> http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
> Bugs: http://bugs.clusterlabs.org



_______________________________________________
Pacemaker mailing list: Pacemaker [at] oss
http://oss.clusterlabs.org/mailman/listinfo/pacemaker

Project Home: http://www.clusterlabs.org
Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
Bugs: http://bugs.clusterlabs.org


andreas at hastexo

Feb 2, 2012, 2:25 AM

Post #4 of 8 (882 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
On 02/02/2012 10:42 AM, Stallmann, Andreas wrote:
> Well, yes, I read the presentation a while ago, but it didn't present me with any new information.
>
> What Florian does is replicate a MySQL-Master over two nodes with DRBD and have several MySQL-Slaves replicate their data from the current Master via the built-in mechanism.
>
> That's firstly quite nice, if you have a scenario, where there is the need for multiple nodes to be read from and where one single node for writing is sufficient.
>
> Secondly, this scenario works only with 2+(n>1) nodes, whereas we have (and want) a classical 2 node scenario.

If I understand you correct, you'd like to have a multi-master MySQL
setup with circular replication?

Regards,
Andreas

--
Need help with Pacemaker?
http://www.hastexo.com/services/remote

>
> Thirdly (and that's the main issue with MySQL on DRBD), the failover process still takes quite long, because of the amount of events due to happen: Stop mysql on node 1, unmount the filesystem, switch DRBD master/slave, mount filesystem on node 2, start mysql (and perhaps repair tables).
>
> Still, thanks for the suggestion.
>
> Cheers,
>
> Andreas
>
>
>
> -----------------------------
> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
> Geschftsfhrer/Managing Directors: Anke Hfer
> -----------------------------
>
> -----Ursprngliche Nachricht-----
> Von: Andreas Kurz [mailto:andreas [at] hastexo]
> Gesendet: Mittwoch, 1. Februar 2012 23:55
> An: pacemaker [at] oss
> Betreff: Re: [Pacemaker] MySQL-Cluster with Pacemaker
>
> Hello,
>
> On 01/30/2012 10:46 AM, Stallmann, Andreas wrote:
>> Hi!
>>
>>
>>
>> I'm on the lookout for alternatives to our current MySQL "cluster",
>> which is an Active/Standby solution with MySQL on DRBD.
>>
>> With increasing customer demand for a faster failover, we want an
>> Active/Passive or even an Active/Active cluster.
>
> Already had a look at this presentation my colleague Florian held at Percona Live UK 2011 in London?
>
> http://goo.gl/5mDFR
>
> Regards,
> Andreas
>
> --
> Need help with Pacemaker?
> http://www.hastexo.com/now
>
>>
>>
>>
>> Currently we run a Tomcat application, which works in
>> Active/Passive-Mode. The applications on the active node communicate
>> it's status via a MySQL database to the passive node. In our current
>> setup, both nodes run the tomcat application but only one holds the
>> database (due to the setup with DRBD). For faster failovers, we'd
>> rather want the database to be active on both nodes. It's not
>> necessary, that it can written on both nodes, but read access would be
>> desirable. The thought is, that switching the "master status" of a
>> database might be quicker than switching DRBD's master status,
>> unmounting and mounting the file system and stopping and starting the database.
>>
>>
>>
>> A MySQL/Pacemaker cluster with replication as described in
>> http://www.mysqlperformanceblog.com/2011/11/29/percona-replication-man
>> ager-a-solution-for-mysql-high-availability-with-replication-using-pac
>> emaker/ thus looked very promising, but it seems to be not yet mature
>> enough for a production environment. Please do correct men if I'm
>> wrong there, I'm really interested in your experience with this
>> solution in a real world scenario.
>>
>>
>>
>> Are there perhaps other howtos describing Pacemaker and MySQL replication?
>>
>>
>>
>> The second idea was using the native MySQL NDB clustering with
>> Pacemaker.
>> http://www.mysqlperformanceblog.com/2010/05/19/pacemaker-please-meet-n
>> db-cluster-or-using-pacemakerheartbeat-to-start-a-ndb-cluster/
>> (from 2010, uses heartbeat and pacemaker) and
>> http://www.howtoforge.com/loadbalanced_mysql_cluster_debian (from
>> 2008, uses pure heartbeat). Are there any more recent "howtos" on
>> pacemaker and MySQL NDB 7.x describe this. Can you provide me with
>> your opinions and field reports on these setups?
>>
>>
>>
>> Looking forward to your upcoming mails,
>>
>>
>>
>> Andreas
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ----------------------------
>> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
>> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr.
>> 9136) Geschftsfhrer/Managing Director: Anke Hfer
>>
>> ----------------------------
>>
>>
>>
>>
>>
>> _______________________________________________
>> Pacemaker mailing list: Pacemaker [at] oss
>> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>>
>> Project Home: http://www.clusterlabs.org Getting started:
>> http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
>> Bugs: http://bugs.clusterlabs.org
>
>
>
> _______________________________________________
> Pacemaker mailing list: Pacemaker [at] oss
> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>
> Project Home: http://www.clusterlabs.org
> Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
> Bugs: http://bugs.clusterlabs.org
Attachments: signature.asc (0.22 KB)


AStallmann at CONET

Feb 2, 2012, 2:54 AM

Post #5 of 8 (881 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
> If I understand you correct, you'd like to have a multi-master MySQL setup with circular replication?
No, not really. :-) I just want to avoid the overhead of DRBD for replication. We have to get faster than that. :-)

As I already wrote. I'm evaluating two different approaches:

- MySQL replication, with Master/Slave Status and shared writer IP controlled by Pacemaker (as described by Yves Trudeau)
- MySQL NBD Cluster with Pacemaker controlling a shared IP and maybe the MySQL Cluster Manager (as described by Yves, too and - with an additional loadbalancer - by Falko Timme).

You'll find the links in my first post. What I was asking for is the opinion and experience the audience of this mailing list has with the three solutions mentioned above.

Cheers,

Andreas


-----------------------------
CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
Gesch?ftsf?hrer/Managing Directors: Anke H?fer
-----------------------------


_______________________________________________
Pacemaker mailing list: Pacemaker [at] oss
http://oss.clusterlabs.org/mailman/listinfo/pacemaker

Project Home: http://www.clusterlabs.org
Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
Bugs: http://bugs.clusterlabs.org


y.trudeau at videotron

Feb 8, 2012, 10:35 AM

Post #6 of 8 (857 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
Hi Andreas,
NDB requires a minimum of 4 nodes for HA... 2x SQL/mgmnt nodes + 2x
data nodes. The SQL/mgmnt nodes could be your tomcat servers without
problem but the data nodes must be other physical servers.

Regards,

Yves

On 12-01-30 04:46 AM, Stallmann, Andreas wrote:
>
> Hi!
>
> I’m on the lookout for alternatives to our current MySQL “cluster”,
> which is an Active/Standby solution with MySQL on DRBD.
>
> With increasing customer demand for a faster failover, we want an
> Active/Passive or even an Active/Active cluster.
>
> Currently we run a Tomcat application, which works in
> Active/Passive-Mode. The applications on the active node communicate
> it’s status via a MySQL database to the passive node. In our current
> setup, both nodes run the tomcat application but only one holds the
> database (due to the setup with DRBD). For faster failovers, we’d
> rather want the database to be active on both nodes. It’s not
> necessary, that it can written on both nodes, but read access would be
> desirable. The thought is, that switching the “master status” of a
> database might be quicker than switching DRBD’s master status,
> unmounting and mounting the file system and stopping and starting the
> database.
>
> A MySQL/Pacemaker cluster with replication as described in
> http://www.mysqlperformanceblog.com/2011/11/29/percona-replication-manager-a-solution-for-mysql-high-availability-with-replication-using-pacemaker/
> thus looked very promising, but it seems to be not yet mature enough
> for a production environment. Please do correct men if I’m wrong
> there, I’m really interested in your experience with this solution in
> a real world scenario.
>
> Are there perhaps other howtos describing Pacemaker and MySQL replication?
>
> The second idea was using the native MySQL NDB clustering with
> Pacemaker.
> http://www.mysqlperformanceblog.com/2010/05/19/pacemaker-please-meet-ndb-cluster-or-using-pacemakerheartbeat-to-start-a-ndb-cluster/
> (from 2010, uses heartbeat and pacemaker) and
> http://www.howtoforge.com/loadbalanced_mysql_cluster_debian (from
> 2008, uses pure heartbeat). Are there any more recent “howtos” on
> pacemaker and MySQL NDB 7.x describe this. Can you provide me with
> your opinions and field reports on these setups?
>
> Looking forward to your upcoming mails,
>
> Andreas
>
> ----------------------------
> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
> Geschäftsführer/Managing Director: Anke Höfer
>
> ----------------------------
>
>
> _______________________________________________
> Pacemaker mailing list: Pacemaker [at] oss
> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>
> Project Home: http://www.clusterlabs.org
> Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
> Bugs: http://bugs.clusterlabs.org


AStallmann at CONET

Feb 9, 2012, 12:21 AM

Post #7 of 8 (845 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
Hi Yves,


Ø NDB requires a minimum of 4 nodes for HA...
I understood that in NDB-Cluster the concept of „nodes” has nothing to do with physical (or virtual) hosts but with running instances / processes. That’s at least, what can be derived from http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-mysql5.1 and from http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-overview.html.

Too me it looks as if NDB-Cluster is supposed to be set up as a (n>1)+1 cluster*, where the n>1 hosts can carry mysqld and ndb and the +1 host is supposed to carry the mysql cluster manager. Please correct me, if I’m wrong of if this setup is possible, but (for some reason) is unwise / unstable / unsuitable for productive usage.

Thanks in advance,

Andreas
*The first links speaks of an loadbalanced mysql cluster and thus needs two further nodes, which carry the load balancing software.



----------------------------
CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
Geschäftsführer/Managing Director: Anke Höfer

----------------------------


y.trudeau at videotron

Feb 9, 2012, 6:34 AM

Post #8 of 8 (849 views)
Permalink
Re: MySQL-Cluster with Pacemaker [In reply to]
Hi Andreas,
with NDB, you have 3 types of node: SQL which is mysqld, Management
running ndb_mgmd and data running ndbmtd. SQL and Management can be on
the same physical host (cohosting) but you ideally need 2 for HA, the
ndb_mgmd process is very small and low on resources. Data nodes, for HA
you need mandatory 2 hosts and they can't be on the same host as the
Management node. The Data nodes need a lot of memory since in most
application, you'll want to use only in memory data (with async persistence)

So, the classic setup is:

2 servers, low memory, SQL + Mgmnt
2 servers, high memory, data

Regards,

Yves


On 12-02-09 03:21 AM, Stallmann, Andreas wrote:
> Hi Yves,
>
> ØNDB requires a minimum of 4 nodes for HA...
>
> I understood that in NDB-Cluster the concept of „nodes” has nothing to
> do with physical (or virtual) hosts but with running instances /
> processes. That’s at least, what can be derived from
> http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-mysql5.1
> and from http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-overview.html.
>
> Too me it looks as if NDB-Cluster is supposed to be set up as a (n>1)+1
> cluster*, where the n>1 hosts can carry mysqld and ndb and the +1 host
> is supposed to carry the mysql cluster manager. Please correct me, if
> I’m wrong of if this setup is possible, but (for some reason) is unwise
> / unstable / unsuitable for productive usage.
>
> Thanks in advance,
>
> Andreas
>
> *The first links speaks of an loadbalanced mysql cluster and thus needs
> two further nodes, which carry the load balancing software.
>
> ----------------------------
> CONET Solutions GmbH, Theodor-Heuss-Allee 19, 53773 Hennef.
> Registergericht/Registration Court: Amtsgericht Siegburg (HRB Nr. 9136)
> Geschäftsführer/Managing Director: Anke Höfer
>
> ----------------------------
>
>
>
> _______________________________________________
> Pacemaker mailing list: Pacemaker [at] oss
> http://oss.clusterlabs.org/mailman/listinfo/pacemaker
>
> Project Home: http://www.clusterlabs.org
> Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
> Bugs: http://bugs.clusterlabs.org


_______________________________________________
Pacemaker mailing list: Pacemaker [at] oss
http://oss.clusterlabs.org/mailman/listinfo/pacemaker

Project Home: http://www.clusterlabs.org
Getting started: http://www.clusterlabs.org/doc/Cluster_from_Scratch.pdf
Bugs: http://bugs.clusterlabs.org

Linux-HA pacemaker RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.