Sun Cluster 3.1 Data Service for MySQL Guide

Planning the Sun Cluster HA for MySQL Installation and Configuration

This section contains the information you need to plan your Sun Cluster HA for MySQL installation and configuration.

Configuration Restrictions

Caution –

Your data service configuration might not be supported if you do not observe these restrictions.

Consider the restrictions in this section to plan the installation and configuration of Sun Cluster HA for MySQL. This section provides a list of software and hardware configuration restrictions that apply to Sun Cluster HA for MySQL only.

For restrictions that apply to all data services, see the Sun Cluster Release Notes.

The Sun Cluster for MySQL can only be configured as a HA and not a Scalable.
The MySQL configuration file (my.cnf) should only be placed in the MySQL Database directory. If my.cnf has to be placed in local filesystem then create a symlink from the MySQL Database directory. No my.cnf should be in /etc (Global file) because it could override command line options
Supported configurations – Currently, only the following configurations are supported with the Sun Cluster HA for MySQL data service.
- Single/Multiple MySQL instances in master configuration
- Single/Multiple MySQL instances in slave configuration
  
  Note –
  The master and slave instances shouldn't be on the same physical node.

Installing MySQL onto Cluster File Systems — Regardless of which MySQL delivery method you have chosen, ie from http://www.mysql.com or from other source, the following restrictions apply.

Each MySQL instance must have a unique Database directory. This Database directory can be mounted as either a Failover File System or Global File System.

Note –

It is considered best practice when mounting Global File Systems to mount them with the /global prefix and to mount Failover File Systems with the /local prefix. However, be aware that this is simply viewed as best practice.

Example 1–1 MySQL instances with Global File Systems

The following example shows MySQL installed onto a Global File System with two MySQL instances (mysql-data-1and mysql-data-2) . The final output shows a subset of the /etc/vfstab entries for MySQL deployed using Veritas Volume Manager.

# ls -l /usr/local
total 4
drwxrwxrwx   2 root     other        512 Oct  1 16:44 bin
lrwxrwxrwx   1 root     other         13 Oct 11 11:20 mysql -> /global/mysql
#
# ls -l /global/mysql
total 10432
drwxr-xr-x  13 mysql    mysql        512 Mar 16 00:03 .
drwxrwxrwx   7 root     other       2048 Apr 11 09:53 ..
-rw-r--r--   1 mysql    mysql      19106 Mar 15 23:29 COPYING
-rw-r--r--   1 mysql    mysql      28003 Mar 15 23:29 COPYING.LIB
-rw-r--r--   1 mysql    mysql     126466 Mar 15 16:47 ChangeLog
-rw-r--r--   1 mysql    mysql       6811 Mar 15 23:29 INSTALL-BINARY
-rw-r--r--   1 mysql    mysql       1937 Mar 15 16:47 README
drwxr-xr-x   2 mysql    mysql       1536 Mar 16 00:03 bin
-rwxr-xr-x   1 mysql    mysql        773 Mar 16 00:03 configure
drwxr-x---   4 mysql    mysql        512 Mar 16 00:03 data
drwxr-xr-x   2 mysql    mysql       1024 Mar 16 00:03 include
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 lib
drwxr-xr-x   3 mysql    mysql        512 Mar 16 00:03 man
-rw-r--r--   1 mysql    mysql    2676944 Mar 15 23:23 manual.html
-rw-r--r--   1 mysql    mysql    2329252 Mar 15 23:23 manual.txt
-rw-r--r--   1 mysql    mysql      98233 Mar 15 23:23 manual_toc.html
drwxr-xr-x   6 mysql    mysql        512 Mar 16 00:03 mysql-test
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 scripts
drwxr-xr-x   3 mysql    mysql        512 Mar 16 00:03 share
drwxr-xr-x   7 mysql    mysql       1024 Mar 16 00:03 sql-bench
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 support-files
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 tests
# ls -l /global/mysql-data-1
Total 30
drwxrwxrwx   9 mysql    mysql        512 Apr 15 12:06 .
drwxrwxrwx  20 root     root        1024 Apr 10 12:41 ..
drwxr-xr-x   2 mysql    mysql        512 Apr 15 12:00 BDB
drwxrwxrwx   2 mysql    mysql        512 Apr 15 11:59 innodb
drwxrwxrwx   2 mysql    mysql       2048 Apr 15 14:47 logs
-rw-r--r--   1 mysql    mysql       1432 Apr 15 11:58 my.cnf
drwx------   2 mysql    mysql        512 Apr 15 11:59 mysql
-rw-rw----   1 mysql    mysql          5 Apr 15 14:47 mysqld.pid
drwx------   2 mysql    mysql        512 Apr 15 14:53 sc3_test_database
drwx------   2 mysql    mysql        512 Apr 15 11:58 test
drwx------   2 mysql    mysql        512 Apr 15 12:00 testdb
#

# ls -l /global/mysql-data-2
total 32
drwxrwxrwx   9 mysql    mysql        512 Apr 15 07:49 .
drwxrwxrwx  20 root     root        1024 Apr 10 12:41 ..
drwxr-xr-x   2 mysql    mysql        512 Apr 14 11:16 BDB
drwxr-xr-x   2 mysql    mysql        512 Apr 14 11:14 innodb
drwxr-xr-x   2 mysql    mysql       2560 Apr 15 10:15 logs
-rw-r--r--   1 mysql    mysql       1459 Apr 14 11:13 my.cnf
drwx------   2 mysql    mysql        512 Apr 14 11:14 mysql
-rw-rw----   1 mysql    mysql          5 Apr 15 10:10 mysqld.pid
drwx------   2 mysql    mysql        512 Apr 15 10:10 sc3_test_database
drwx------   2 mysql    mysql        512 Apr 14 11:14 test
drwx------   2 mysql    mysql        512 Apr 14 11:16 testdb

# more /etc/vfstab (Subset of the output)
/dev/vx/dsk/dg1/vol01   /dev/vx/rdsk/dg1/vol01  /global/mysql    ufs     2
yes global,logging
/dev/vx/dsk/dg2/vol01   /dev/vx/rdsk/dg2/vol01  /global/mysql-data-1    ufs     2
yes global,logging
/dev/vx/dsk/dg2/vol01   /dev/vx/rdsk/dg2/vol01  /global/mysql-data-2    ufs     2
yes global,logging

#

Note –

In the above example the Database directory for the MySQL instance 1 is /global/mysql-data-1, whereas the Database directory for the MySQL instance 2 is /global/mysql-data-2..

Example 1–2 MySQL instances with Failover File Systems

The following example shows MySQL installed on Local File Systems and two MySQL instances (mysql-data-1 and mysql-data-2) on Failover File Systems. The final output shows a subset of the /etc/vfstab entries for MySQL deployed using Veritas Volume Manager.

# ls -l /usr/local/mysql
total 10432
drwxr-xr-x  13 mysql    mysql        512 Mar 16 00:03 .
drwxrwxrwx   7 root     other       2048 Apr 11 09:53 ..
-rw-r--r--   1 mysql    mysql      19106 Mar 15 23:29 COPYING
-rw-r--r--   1 mysql    mysql      28003 Mar 15 23:29 COPYING.LIB
-rw-r--r--   1 mysql    mysql     126466 Mar 15 16:47 ChangeLog
-rw-r--r--   1 mysql    mysql       6811 Mar 15 23:29 INSTALL-BINARY
-rw-r--r--   1 mysql    mysql       1937 Mar 15 16:47 README
drwxr-xr-x   2 mysql    mysql       1536 Mar 16 00:03 bin
-rwxr-xr-x   1 mysql    mysql        773 Mar 16 00:03 configure
drwxr-x---   4 mysql    mysql        512 Mar 16 00:03 data
drwxr-xr-x   2 mysql    mysql       1024 Mar 16 00:03 include
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 lib
drwxr-xr-x   3 mysql    mysql        512 Mar 16 00:03 man
-rw-r--r--   1 mysql    mysql    2676944 Mar 15 23:23 manual.html
-rw-r--r--   1 mysql    mysql    2329252 Mar 15 23:23 manual.txt
-rw-r--r--   1 mysql    mysql      98233 Mar 15 23:23 manual_toc.html
drwxr-xr-x   6 mysql    mysql        512 Mar 16 00:03 mysql-test
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 scripts
drwxr-xr-x   3 mysql    mysql        512 Mar 16 00:03 share
drwxr-xr-x   7 mysql    mysql       1024 Mar 16 00:03 sql-bench
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 support-files
drwxr-xr-x   2 mysql    mysql        512 Mar 16 00:03 tests
# ls -l /local/mysql-data-1
Total 30
drwxrwxrwx   9 mysql    mysql        512 Apr 15 12:06 .
drwxrwxrwx  20 root     root        1024 Apr 10 12:41 ..
drwxr-xr-x   2 mysql    mysql        512 Apr 15 12:00 BDB
drwxrwxrwx   2 mysql    mysql        512 Apr 15 11:59 innodb
drwxrwxrwx   2 mysql    mysql       2048 Apr 15 14:47 logs
-rw-r--r--   1 mysql    mysql       1432 Apr 15 11:58 my.cnf
drwx------   2 mysql    mysql        512 Apr 15 11:59 mysql
-rw-rw----   1 mysql    mysql          5 Apr 15 14:47 mysqld.pid
drwx------   2 mysql    mysql        512 Apr 15 14:53 sc3_test_database
drwx------   2 mysql    mysql        512 Apr 15 11:58 test
drwx------   2 mysql    mysql        512 Apr 15 12:00 testdb
#

#ls -l /local/mysql-data-2
total 32
drwxrwxrwx   9 mysql    mysql        512 Apr 15 07:49 .
drwxrwxrwx  20 root     root        1024 Apr 10 12:41 ..
drwxr-xr-x   2 mysql    mysql        512 Apr 14 11:16 BDB
drwxr-xr-x   2 mysql    mysql        512 Apr 14 11:14 innodb
drwxr-xr-x   2 mysql    mysql       2560 Apr 15 10:15 logs
-rw-r--r--   1 mysql    mysql       1459 Apr 14 11:13 my.cnf
drwx------   2 mysql    mysql        512 Apr 14 11:14 mysql
-rw-rw----   1 mysql    mysql          5 Apr 15 10:10 mysqld.pid
drwx------   2 mysql    mysql        512 Apr 15 10:10 sc3_test_database
drwx------   2 mysql    mysql        512 Apr 14 11:14 test
drwx------   2 mysql    mysql        512 Apr 14 11:16 testdb

# more /etc/vfstab (Subset of the output)
/dev/vx/dsk/dg2/vol01   /dev/vx/rdsk/dg2/vol01  /local/mysql-data-1    ufs     2
yes logging
/dev/vx/dsk/dg2/vol01   /dev/vx/rdsk/dg2/vol01  /local/mysql-data-2    ufs     2
yes logging
#

Note –

In the above example the Database directory for the MySQL instance 1 is /local/mysql-data-1, whereas the Database directory for the MySQL instance 2 is /local/mysql-data-2.

Configuration Requirements

Caution –

Your data service configuration might not be supported if you do not adhere to these requirements.

Use the requirements in this section to plan the installation and configuration of Sun Cluster HA for MySQL. These requirements apply to Sun Cluster HA for MySQL only. You must meet these requirements before you proceed with your Sun Cluster HA for MySQL installation and configuration.

MySQL components and their dependencies —The Sun Cluster HA for MySQL data service can be configured to protect a MySQL instance and its respective components. These components, and their dependencies between each other, are briefly described below.

Table 1–3 MySQL components and their dependencies (via -> symbol)


Componet	Description
MySQL Server	-> SUNW.HAStoragePlus resource The SUNW.HAStoragePlus resource manages the MySQL File System Mount points and ensures that MySQL is not started until these are mounted.

The MySQL component has two configuration and registration files under /opt/SUNWscmys/util. These files allow you to register the MySQL component with Sun Cluster and prepare a MySQL instance to be registered

Within these files, the appropriate dependencies have already been applied.

Example 1–3 MySQL configuration and registration file for Sun Cluster

# cd /opt/SUNWscmys
#
# ls -l util
-rwxr-xr-x   1 root     other       1208 Mar 25 13:49 ha_mysql_config
-rwxr-xr-x   1 root     other        820 Mar 25 13:55 ha_mysql_register
-rwxr-xr-x   1 root     other       1105 Apr 16 07:48 mysql_config
-rwxr-xr-x   1 root     other       2993 Apr 11 10:57 mysql_register
#
# more util/*config
::::::::::::::
util/mysql_config
::::::::::::::
#
# Copyright 2003 Sun Microsystems, Inc.  All rights reserved.
# Use is subject to license terms.
# 
# This file will be sourced in by mysql_register and the parameters
# listed below will be used.
#

# Where is mysql installed (BASEDIR)
MYSQL_BASE=

# Mysql admin-user for localhost
MYSQL_USER=

# Password for mysql admin user
MYSQL_PASSWD=

# Configured logicalhost
MYSQL_HOST=

# Specify a username for a faultmonitor user
FMUSER=

# Pick a password for that faultmonitor user
FMPASS=

# Socket name for mysqld ( Should be /tmp/<logical host>.sock )
MYSQL_SOCK=/tmp/<logical host>.sock

# FOR SC3.1 ONLY, Specify the physical hostname for the
# physical NIC that this logicalhostname belongs to for every node in the
# cluster this Resourcegroup can located on.
# IE: The logicalhost lh1 belongs to hme1 for physical-node phys-1 and
# hme3 for  physical-node phys-2. The hostname for hme1 is phys-1-hme1 and
# for hme3 on phys-2 it is phys-2-hme3.
# IE: MYSQL_NIC_HOSTNAME="phys-1-hme1 phys-2-hme3"
MYSQL_NIC_HOSTNAME=""
::::::::::::::
util/ha_mysql_config
::::::::::::::
#
# Copyright 2003 Sun Microsystems, Inc.  All rights reserved.
# Use is subject to license terms.
# 
# This file will be sourced in by ha_mysql_register and the parameters
# listed below will be used.
#
# These parameters can be customized in (key=value) form
#
#        RS - name of the resource for the application
#        RG - name of the resource group containing RS
#   BASEDIR - name of the Mysql bin directory
#   DATADIR - name of the Mysql Data directory
# MYSQLUSER - name of the user Mysql should be started of
#        LH - name of the LogicalHostname SC resource
#    FMUSER - name of the Mysql fault monitor user
#    FMPASS - name of the Mysql fault monitor user password
#    LOGDIR - name of the directory mysqld should store it's logfile.
#    CHECK  - should HA-MySQL check MyISAM index files before start YES/NO.
#    HAS_RS - name of the mysql HAStoragePlus SC resource
#
#       The following examples illustrate sample parameters
#       for Mysql
#
#       BASEDIR=/usr/local/mysql
#       DATADIR=/global/mysqldata
#       MYSQLUSER=mysql
#       LH=mysqllh
#       FMUSER=fmuser
#       FMPASS=fmuser
#       LOGDIR=/global/mysqldata/logs
#       CHECK=YES
#

RS=
RG=
BASEDIR=
DATADIR=
MYSQLUSER=
LH=
FMUSER=
FMPASS=
LOGDIR=
CHECK=
HAS_RS=
#

The my.cnf file — The Sun Cluster HA for MySQL data service provides two sample my.cnf files, one sample file for a master configuration and one for a slave configuration, however you should ensure that at least the following parameters are set.

Note –
The my.cnf file is a important file within MySQL. Therefore you are recommended to refer to the MySQL Documentation for complete configuration information on the parameters that follow.
- MySQL my.cnffile in [mysqld]section in a master configuration
  - bind address has to be set to the defined logical host.
  - socket should be defined as /tmp/<logical host>.sock.
  - binlog-ignore-db should contain sc3_test_database if log-bin option is being used.
- MySQL my.cnffile in [mysqld]section in a slave configuration
  - bind address has to be set to the defined logical host.
  - socket should be defined as /tmp/<logical host>.sock.
  - binlog-ignore-db should contain sc3_test_database if log-bin option is being used.
  - master—host is the hostname where the master instance resides.
  - master—user is the username the slave will use for indentification to the master.
  - master—password is the password the slave will use for indentification to the master.
  - master-info-file is the location of the file that remembers where we left off on the master during the replication process. This file should be placed on a GFS/FFS