How to Register and Configure HA for MySQL as a Failover Service in a Zone Configuration

Language:

This procedure assumes that you installed the data service packages during your initial Oracle Solaris Cluster installation.

If you did not install the HA for MySQL packages as part of your initial Oracle Solaris Cluster installation, go to Installing the HA for MySQL Package.

Log in to the zone on the node that hosts yourMySQLresource-group.
On the node's zone in the cluster that hosts MySQL, become an administrator that provides solaris.cluster.admin authorization.

Start the MySQL server instance manually.

# cd MySQL-base-directory

# ./bin/mysqld --defaults-file=MySQL-Database-directory/my.cnf \
--basedir=MySQL-Base-directory \
--datadir=MySQL-Database-directory \
--user=mysql \
--pid-file=MySQL-Database-directory/mysqld.pid &

Configure the administrator password for the administrative user.

# MySQL-Database-directory/bin/mysqladmin \
-S /tmp/logical-host.sock password 'admin-password'

Add the administrative user for accessing locally a MySQL instance with a MySQL logical-host-ip-name.
Note - If you want to access the MySQL instance only through the socket (localhost), omit this step.

When bootstrapping MySQL the command mysql_install_db creates two administrative users, one belonging to localhost and one belonging to the node on which mysql_install_db was executed.

Add an administrative user for every physical node in the cluster that runs this MySQL instance.

Note - If the node name and the hostname for the physical interface are different, use the hostname for the physical interface.

The following is an example for a MySQL instance.
```
# mysql -S /tmp/hahostix1.sock -uroot -padmin-password
mysql> use mysql;
mysql> GRANT ALL ON *.* TO 'root'@'zone1' IDENTIFIED BY 'rootpasswd';
mysql> GRANT ALL ON *.* TO 'root'@'zone2' IDENTIFIED BY 'rootpasswd';
mysql> exit;
```
Note - You have to manually add Grant_priv to the administrative users. See the MySQL administration documentation.

The following is an example for a MySQL 4.x or 5.0.x instance.
```
# mysql -S /tmp/hahostix1.sock -uroot -padmin-password
mysql> use mysql;
mysql> UPDATE user SET Grant_priv='Y' WHERE User='root' AND Host='zone1';
mysql> UPDATE user SET Grant_priv='Y' WHERE User='root' AND Host='zone2';
mysql> exit;
```
Note - If you experience any problems here, refer to the MySQL administration documentation.
Copy the MySQL configuration file to your home directory.
```
# cp /opt/SUNWscmys/util/mysql_config /home-dir
```

Create a fault-monitor user and a test database for the MySQL instance.

# cd home-dir

Edit the mysql_config file and follow the comments within that file.

# 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
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
# logical hostname

# belongs to for every node in the cluster this resource group can be
# 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-hme0 and
# for hme3 on phys-2 it is phys-2-hme3.
# IE: MYSQL_NIC_HOSTNAME="phys-1-hme0 phys-2-hme3"

MYSQL_NIC_HOSTNAME=

# Where are your databases installed? (location of my.cnf)

MYSQL_DATADIR=

The following is an example for a MySQL instance.

MYSQL_BASE=/global/mysql
MYSQL_USER=root
MYSQL_PASSWD=
MYSQL_HOST=hahostix1
FMUSER=fmuser
FMPASS=
MYSQL_SOCK=/tmp/hahostix1.sock
MYSQL_NIC_HOSTNAME="zone1 zone2"
MYSQL_DATADIR=/global/mysql-data

After editing mysql_config, you must run the mysql_register script.

# /opt/SUNWscmys/util/mysql_register -f home-dir/mysql_config

Stop the MySQL server instance manually.

# kill -TERM `cat MySQL-Database-directory/mysqld.pid

Copy the MySQL configuration file to your home directory.
```
# cp /opt/SUNWscmys/util/ha_mysql_config /home-dir
```

Create and register MySQL as a failover data service.

Navigate to the home-dir directory.
```
# cd home-dir
```

Edit the ha_mysql_config file and follow the comments within that file.

#
# 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
#

#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 sclable resource creation.
# The default is no, so any enty 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 in
# 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 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 a file, leave the FMPASS variable
# empty.
# 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
# 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 is an example for a MySQL instance.

RS=mysql-res
RG=mysql-rg
BASEDIR=/global/mysql
DATADIR=/global/mysql-data
MYSQLUSER=mysql
LH=hahostix1
MYSQLHOST=hahostix1
FMUSER=fmuser
FMPASS=
LOGDIR=/global/mysql-data/logs
CHECK=YES 
HAS_RS=mysql-has-res
ZONE=
ZONE_BT=
PROJECT=

(Optional) If you did not specify the FMPASS variable, call the following script in one zone that can host the MySQL database.
```
# /opt/SUNWscmys/util/ha_mysql_register -f home-dir/ha_mysql_config -e
```

Register the MySQL resource.

# /opt/SUNWscmys/util/ha_mysql_register -f home-dir/ha_mysql_config

Enable each MySQL resource.
Repeat this step for each MySQL instance, if multiple instances were created.
```
# clresource status
# clresource enable MySQL-resource
```