Wednesday, November 9, 2011

Clustering MySQL instances with Oracle Clusterware 11gR2

I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.

One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.

Requirements I had:

  • Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances are moved to any other surviving node (least loaded)
  • Different MySQL instances may run different MySQL RDBMS software versions
  • Each MySQL instance is listening to its own dedicated and clusterware managed IP address (so MySQL can always respond on default 3306 port and can move independently around nodes without any dependencies to other MySQL instances)
  • Clusterware monitors each MySQL instance and in case of trouble automatically moves IP and starts instance on another cluster node

For this task I wrote some Bash scripts to help managing MySQL instances in Oracle Clusterware environment. All these scripts are available here: Google Code project page, also with documentation of the whole setup and how to use scripts: Documentation

All comments welcome!

5 comments:

  1. Is it possible to also do load balancing with this setup. I am new to the mysql cluster idea, but need to setup a load balanced highly available cluster. Thanks.

    - Tim

    ReplyDelete
  2. For each MySQL instance you can set LOAD attribute and when starting up MySQL resource clusterware can place it on the least loaded node.
    http://docs.oracle.com/cd/E11882_01/rac.112/e16794/resatt.htm#CHDIEHCH

    ReplyDelete
  3. Great stuff, just one question:
    In the installation guide you are saying "Download MySQL RDBMS software tar-release" but I can only find rpm's (or deb's), only the source is available as tar, but I'm sure if you're going all the way to get Clusterware/Grid running you wouldn't want to compile your own MySQL version in the end...
    Can the rpm's be installed into /instance/instance-name somhow? (rpm --root?)

    ReplyDelete
  4. If you choose "Linux - Generic" as platform, then there is "Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive"

    ReplyDelete