Go to main content

Oracle® Solaris Cluster Data Service for MySQL Guide

Exit Print View

Updated: June 2017
 
 

Planning the HA for MySQL Installation and Configuration

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

MySQL and Oracle Solaris Zones

HA for MySQL is supported in Oracle Solaris Zones. Oracle Solaris Cluster offers the following concepts for Oracle Solaris Zones.

  • Zone clusters are containers that are running after a reboot of the node. These containers form virtual clusters with their own node names.

  • High-availability zones (HA Zones), also called failover zones, are managed by the Oracle Solaris Cluster HA for Oracle Solaris Zones data service, and are represented by a resource of a resource group.

Configuration Restrictions

This section provides a list of software and hardware configuration restrictions that apply to HA for MySQL only.

For restrictions that apply to all data services, see the Oracle Solaris Cluster 4.3 Release Notes.

Caution

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

Restriction for the HA for MySQL Data Service Configuration

A single MySQL Server is to be configured as a failover resource only. A MySQL server is configured within a MySQL cluster, can be configured as a failover, scalable or a multiple-master resource. Scalable or multiple-master data services are typically deployed when you configure the MySQL server for MySQL Cluster.

Restrictions on the MySQL Configuration File

The MySQL configuration file, my.cnf, should be placed only in the MySQL Database directory. If my.cnf has to be placed in local file system, and then create a symbolic link from the MySQL Database directory. The configuration file my.cnf should not be placed in the /etc directory because it will override command line options.

Restrictions for the MySQL Configurations

The following configurations are supported with the HA for MySQL data service.

  • Single/Multiple MySQL instances in master configuration

  • Single/Multiple MySQL instances in slave configuration

Restrictions on the MySQL Database Directory

Regardless of which MySQL delivery method you have chosen, whether from http://www.mysql.com or from other source, each MySQL instance must have a unique Database directory. You can mount this Database directory as either a highly available local file system or cluster file system.

Note -  It is always a good practice to mount the cluster file systems with the /global prefix and to mount the highly available local file systems with the /local prefix.

Restriction for the MySQL smf Service Name in an HA Zone

The MySQL configuration in an HA Zone uses the smf component of Oracle Solaris Cluster HA for Oracle Solaris Zones. The registration of the MySQL data service in an HA Zone defines an smf service to control the MySQL database. The name of this smf service is generated in this naming scheme: svc:/application/sczone-agents:resource-name. No other smf service with exactly this name can exist.

The associated smf manifest is automatically created during the registration process in this location and naming scheme: /var/svc/manifest/application/sczone-agents/resource-name.xml. No other manifest can coexist with this name.

Examples for the File Systems Layout

Example 1  MySQL Instances with Cluster File Systems

The following example shows MySQL installed onto a cluster file system with two MySQL instances (mysql-data-1 and mysql-data-2). The final output shows a subset of the /etc/vfstab entries for a MySQL deployment.

# 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 2  MySQL Instances with Highly Available Local File Systems

The following example shows MySQL installed on local file systems and two MySQL instances, mysql-data-1 and mysql-data-2, on highly available local file systems. The final output shows a subset of the /etc/vfstab entries for a MySQL deployment.

# 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

The requirements in this section apply to HA for MySQL only. You must meet these requirements before you proceed with your HA for MySQL installation and configuration.

Caution

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

Components and Their Dependencies for HA for MySQL

MySQL components and their dependencies – You can configure HA for MySQL data service to protect a MySQL instance and its respective components. The components, and their dependencies between each other, are briefly described below.

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.
Table 3  Dependencies Between HA for MySQL Components in Failover Configurations
Component
Dependency
MySQL resource in a global or non-global zones.
SUNW.HAStoragePlus – This dependency is required only if the configuration uses a failover file system or file systems in a non-global zones. It is a resource_offline_restart_dependency.
SUNW.LogicalHostName
SUNW.SharedAddress
MySQL resource in an HA Zone
HA for Oracle Solaris Zones boot resource.
SUNW.HAStoragePlus – This dependency is a resource_offline_restart_dependency.
SUNW.LogicalHostName – This dependency is required only if the zone's boot resource does not manage the zone's IP address.

Registration and Configuration File for HA for MySQL

The MySQL component has two configuration and registration files in /opt/SUNWscmys/util. These files enable you to register the MySQL component with Oracle Solaris Cluster and prepare a MySQL instance to be registered.

Within these files, the appropriate dependencies have been applied.

Example 3  The ha_mysql_config File 
# cd /opt/SUNWscmys
# more util/*config
::::::::::::::
ha_mysql_config
::::::::::::::
#
# CDDL HEADER START
#
# The contents of this file are subject to the terms of the
# Common Development and Distribution License (the License).
# You may not use this file except in compliance with the License.
#
# You can obtain a copy of the license at usr/src/CDDL.txt
# or http://www.opensolaris.org/os/licensing.
# See the License for the specific language governing permissions
# and limitations under the License.
#
# When distributing Covered Code, include this CDDL HEADER in each
# file and include the License file at usr/src/CDDL.txt.
# If applicable, add the following below this CDDL HEADER, with the
# fields enclosed by brackets [] replaced with your own identifying
# information: Portions Copyright [yyyy] [name of copyright owner]
#
# CDDL HEADER END
#

#
# Copyright (c) 2006, 2012,  Oracle and/or its affiliates.  All rights reserved.

#

#ident  "ha_mysql_config.ksh   1.9    12/08/20""

# 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
#  SCALABLE - Flag to indicate a scalable resource creation.
#             The default is no, so any entry here triggers a scalable resource.
# LB_POLICY - Set the loadbalancing policy for a scalable MySQL service.
#             Use the values defined for the standard resource property
#             Load_balancing_policy. If you do not specify it, the defaults are 
#             used.
#   RS_PROP - Additional resource properties in the format for clresource create.
              Example: "-p start_timeout=600".
#        To have the MySQL agent local zone aware, 4 variables are needed:
#      ZONE - The zone name where the MySQL Database should run 
#             Optional
#    ZONEBT - The resource name which controls the zone. 
#             Optional
#   PROJECT - A project in the zone that will be used for this service.
#             Specify it if you have an su - in the start stop or probe,
#             or to define the smf credentials. If the variable is not set,
#             it will be translated as :default for the sm and default 
#             for the zsh component.
#             Optional
#     ZUSER - A user in the zone which is used for the smf method 
#             credentials. Your smf service will run under this user.
#             Optional
#
# MySQL specific variables
#
#   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
# MYSQLHOST - Name of the host in /etc/hosts. For scalable/multiple-master \
resources, leave it empty. 
#    FMUSER - Name of the MySQL fault monitor user
#    FMPASS - Name of the MySQL fault monitor user password
#             If you do not want to store the password as readable in the file, \
#             leave the FMPASS variable empty.
#    LOGDIR - Name of the directory where mysqld should store its logfile.
#    CHECK  - Should HA-MySQL check MyISAM index files before start YES/NO.
#    HAS_RS - Name of the MySQL HAStoragePlus SC resource
# NDB_CHECK - Is MySQL Cluster installed?
#             Any entry here triggers the ndb engine check. If no MySQL \
cluster should be checked, leave it empty.
#
#       The following examples illustrate sample parameters
#       for MySQL
#
#       BASEDIR=/usr/local/mysql
#       DATADIR=/global/mysqldata
#       MYSQLUSER=mysql
#       LH=mysqllh
#       RS_PROP=
#       SCALABLE=
#       LB_POLICY=
#       MYSQLHOST=mysqllh
#       FMUSER=fmuser
#       FMPASS=fmuser
#       LOGDIR=/global/mysqldata/logs
#       CHECK=YES
#       NDB_CHECK=
#

RS=
RG=
PORT=
LH=
SCALABLE=
LB_POLICY=
RS_PROP=
HAS_RS=

# local zone specific options

ZONE=
ZONE_BT=
PROJECT=

# mysql specifications

BASEDIR=
DATADIR=
MYSQLUSER=
MYSQLHOST=
FMUSER=
FMPASS=
LOGDIR=
CHECK=
NDB_CHECK=
Example 4  The mysql_config File 
cd /opt/SUNWscmys
# more util/*config
::::::::::::::
mysql_config
::::::::::::::
#
# CDDL HEADER START
#
# The contents of this file are subject to the terms of the
# Common Development and Distribution License (the License).
# You may not use this file except in compliance with the License.
#
# You can obtain a copy of the license at usr/src/CDDL.txt
# or http://www.opensolaris.org/os/licensing.
# See the License for the specific language governing permissions
# and limitations under the License.
#
# When distributing Covered Code, include this CDDL HEADER in each
# file and include the License file at usr/src/CDDL.txt.
# If applicable, add the following below this CDDL HEADER, with the
# fields enclosed by brackets [] replaced with your own identifying
# information: Portions Copyright [yyyy] [name of copyright owner]
#
# CDDL HEADER END
#

#
# Copyright (c) 2006, 2012, Oracle and/or its affiliates. All rights reserved.
#
#

#ident  "@(#)mysql_config.ksh   1.11    12/08/20"

# 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 (Default is root)
MYSQL_USER=

# Password for mysql admin user, if you do not want to disclose this password in
# a file, leave the MYSQL_PASSWD variable empty, or take it out.
MYSQL_PASSWD=

# Configured logicalhost. For scalable or multiple master resources leave it empty.
MYSQL_HOST=

# Specify a username for a faultmonitor user
FMUSER=

# Pick a password for that faultmonitor user, if you do not want to disclose this 
# password in a file, leave the FMPASS variable empty, or take it out.
FMPASS=

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

# Specify the physical hostname for the physical NIC that this logicalhostname 
# belongs to for every node in the cluster this Resource group can get located on.
# If you use the mysql_geocontrol features to implement the MySQL replication as 
# the replication protocol in Oracle Solaris Cluster geographic edition, specify all
# physical nodes of all clusters, specify at least all the nodes on both sites
# where the mysql databases can be hosted.
# 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-hme0 and
# for hme3 on phys-2 it is phys-2-hme3.
# IE: MYSQL_NIC_HOSTNAME="phys-1-hme0 phys-2-hme3"
# IE: If two clusters are tied together by the mysql_geocontrol features, assuming the
# mysql database on cluster one belongs to cl1-phys1-hme0 and cl1-phys2-hme3, the
# mysql database on cluster two belongs to cl2-phys1-hme2 and cl2-phys2-hme4. Then the  
# MYSQL_NIC_HOSTNAME variable needs to be set to:
# MYSQL_NIC_HOSTNAME="cl1-phys1-hme0 cl1-phys2-hme3 cl2-phys1-hme2 cl2-phys2-hme4"

MYSQL_NIC_HOSTNAME=

# Where are your databases installed, (location of my.cnf)
MYSQL_DATADIR=

# Is MySQL Cluster installed?
# Any entry here triggers the ndb engine check preparation. If no MySQL cluster should be
# checked, leave it empty.
NDB_CHECK=

Explanation of the my.cnf File

The my.cnf file – The 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, ensure that at least the following parameters are set.

Note -  The my.cnf file is a important file within MySQL. Refer to the MySQL documentation for complete configuration information on the parameters that follow.

The MySQL my.cnf file in [mysqld]section in a master configuration:

  • The bind address parameter must be set to the defined logical host's IP name.

    Note -  Some of the MySQL parameters do not work with bind address if the name of the logical host is set. In these cases do not set the bind address parameter, or use the absolute address of the logical hostname.

  • The socket parameter is defined as /tmp/logical-host-ip-name.sock in case of a failover deployment. If you deploy a scalable or multiple-master configuration, specify /tmp/physical-hostname.sock.

  • The binlog-ignore-db parameter contains sc3_test_database, if log-bin option is being used.

The MySQL my.cnf file in [mysqld]section in a slave configuration:

  • The bind address parameter must be set to the defined logical host's IP name.

    Note -  Some MySQL builds do not work with names in the bind address parameter. In these cases do not set the bind address parameter, or use the absolute IP address of the logical hostname.

  • The socket parameter is defined as /tmp/logical-host-IP name.sock.

  • The binlog-ignore-db parameter contains sc3_test_database if log-bin option is being used.

  • The master—host parameter is the hostname where the master instance resides.

  • The master—user parameter is the username the slave will use for identification to the master.

  • The master—password parameter is the password the slave will use for identification to the master.

    Note -  Beginning in MySQL server 5.0, the use of master-host, master-user and master-password in the my.cnf file is deprecated. Configure this information in the MySQL client with the change_master statement.

  • The master-info-file parameter is the location of the file that remembers where MySQL left off on the master during the replication process. This file must be placed on a global file system or a failover file system.

Copyright © 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
Previous
Next