MySQL for Drupal

MySQL active/passive cluster using Oracle CRS

Oracle clusterware CRS can be used to cluster for high availability (HA) non-Oracle resources. This wiki page describes the steps to set up HA mysql databases in a failover configuration using Oracle 10gR2 CRS.

Oracle CRS 10gR2 allows to define generic cluster resources. An implementation for HA mysql is described here. The macro steps are:

• a 2-node cluster with shared storage (NFS mounted NAS) is used - 2-nodes running in virtualized environment
• oracle CRS and mysql are installed on both nodes
• 2 cluster resource profiles are defined: VIP, mysql application
• the resources are created with dependencies, so that CRS will take care of running them on only 1 node at a time
• mysql startup and shutdown will be handled by the clusterware: crs_start drupal-v drupal_mysql
• monitor with crs_stat -t (or crsstat.sh)

Implementation steps

Software Installation

• a 2-node cluster is allocated with shared storage.
• filesystems for mysql is created on shared storage:
  • /ORA/dbs02/DRUPAL/mysql/ - for binary logs
  • /ORA/dbs03/DRUPAL/mysql/ - for mysql data files
• mysql is installed locally on both nodes
  • run /sbin/chkconfig mysql off
  • make sure that mysql is shut down before starting this configuration

Service Configuration

• run the following commands as root (some of those can be run from an upriviledged user if needed)
• clean up the services configured by default

srvctl stop nodeapps -n <nodename>
crs_unregister ora.<nodename>.ons
crs_unregister ora.<nodename>.gsd
crs_unregister ora.<nodename>.vip

• configure the VIP
  • create the VIP for mysql1 + register it + set it to run as root + allow oracle to start it + startit

    crs_profile -create drupal-v -t  application -a /ORA/dbs01/oracle/product/crs/bin/usrvip -o oi=eth0,ov=10.18.2.147,on=255.255.0.0
    crs_register drupal-v
    crs_setperm drupal-v  -o root
    crs_setperm drupal-v -u user:mysql:r-x
    crs_setperm drupal-v -u user:oracle:r-x
    crs_start drupal-v
    

  • copy the contents of $ORA_CRS_HOME/crs/public and $ORA_CRS_HOME/crs/profile to the other cluster node
• write action script for mysql instances
  • see: action_mysqlserver.scr: action script for Oracle CRS - mysql instance handler
• create profile and register action script for mysql

crs_profile -create drupal_mysql -t application -d "Mysql Instance 1" -r "drupal-v" -a /ORA/dbs01/oracle/product/crs/crs/public/action_mysqlserver.scr -o ci=10,ra=5
crs_register drupal_mysql
crs_setperm drupal_mysql -o root
crs_setperm drupal_mysql -u user:mysql:r-x
crs_setperm drupal_mysql -u user:oracle:r-x

• • copy the contents of $ORA_CRS_HOME/crs/public and $ORA_CRS_HOME/crs/profile to the other cluster node
• stop and start the db with
  • crs_stop drupal-v drupal_mysql
  • crs_start drupal-v drupal_mysql

NOTE: when shutting down services, stop commands can fail. The corresponding resources will then go in UNKNOWN state. Take special care in resolving these issues manually before restarting the services.

-- DawidWojcik - 23-May-2011