Go to main content

Oracle® Solaris Cluster Data Service for MySQL Guide

Exit Print View

Updated: October 2019
 
 

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

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.

  1. On a node in the cluster that hosts MySQL, become an administrator that provides solaris.cluster.admin authorization.
  2. 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 &
  3. Configure the administrator password for the administrative user. 
    # MySQL-Database-directory/bin/mysqladmin \
-S /tmp/logical-host.sock password 'admin-password'
  4. Add the administrative user for locally accessing 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 mysqld --initialize creates two administrative users, one belonging to localhost and one belonging to the node on which mysqld --initialize was executed.

    Add an administrative user for every global-cluster node 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'@'clusterix2' IDENTIFIED BY 'rootpasswd';
mysql> GRANT ALL ON *.* TO 'root'@'clusterix1' IDENTIFIED BY 'rootpasswd';
mysql> exit;
    Note -  You must manually add Grant_priv to the administrative users. See the MySQL administration documentation.

    The following is an example for a MySQL 4.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='clusterix1';
mysql> UPDATE user SET Grant_priv='Y' WHERE User='root' AND Host='clusterix2';
mysql> exit;

    The following is an example for a MySQL 5.x instance.

    # mysql -S /tmp/hahostix1.sock -uroot -padmin-password
mysql> use mysql;
mysql> GRANT ALL ON *.* TO 'root'@'clusterix2' IDENTIFIED BY 'rootpasswd';
mysql> GRANT ALL ON *.* TO 'root'@'clusterix1' IDENTIFIED BY 'rootpasswd';
mysql> exit;

    The following is an example for a MySQL 8.x instance.

    # mysql -S /tmp/hahostix1.sock -uroot -padmin-password
mysql> use mysql;
mysql> CREATE USER 'root'@'clusterix2' IDENTIFIED BY 'rootpasswd';
mysql> CREATE USER 'root'@'clusterix1' IDENTIFIED BY 'rootpasswd';
mysql> GRANT ALL ON *.* TO 'root'@'clusterix2' IDENTIFIED BY 'rootpasswd';
mysql> GRANT ALL ON *.* TO 'root'@'clusterix1' IDENTIFIED BY 'rootpasswd';
mysql> UPDATE user SET Grant_priv='Y' WHERE User='root' AND Host='clusterix1';
mysql> UPDATE user SET Grant_priv='Y' WHERE User='root' AND Host='clusterix2';
mysql> exit;
    Note -  If you experience any problems here, refer to the MySQL administration documentation.
  5. Copy the MySQL configuration file to your home directory. 
    # cp /opt/SUNWscmys/util/mysql_config /home-dir
  6. Create a faultmonitor-user and a test-database for the MySQL instance. 
    # cd home-dir

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

#
# 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 passowrd 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 logical hostname 
# belongs to for every node in the cluster this resource group can be 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=

    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="clusterix1 clusterix2"
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
  7. Stop the MySQL Server instance manually. 
    # kill -TERM `cat MySQL-Database-directry/mysqld.pid
  8. Create and register MySQL as a failover data service.
    1. Assume the root role on one node.
    2. Encrypt the password of the fault-monitoring database user. 
      # clpstring create -t resource -b MySQL-resource MySQL-resource
    3. Register the MySQL resource. 
      # clresource create -d -g MySQL-failover-resource-group -t ORCL.mysql \
-p Mysql_basedir=MySQL-base-directory \
-p Mysql_datadir=MySQL-databse-directory \
-p Mysql_user=MySQL-OS-User \
-p Mysql_host=MySQL-logical-host \
-p Mysql_fmuser=MySQL-database-faul-monitoring-user \
-p Mysql_logdir=MySQL-database-log-directory \
-p Resource_dependencies=MySQL-logical-host-resource \
-p Resource_dependencies_offline_restart=MySQL-storage-resource \
MySQL-resource
  9. Enable each MySQL resource.

    Repeat this step for each MySQL instance, if multiple instances were created.

    # clresource status
# clresource enable MySQL-resource
Example 2  Creating a Failover HA for MySQL Resource

The following example encrypts a password for the MySQL-resource resource, then creates and registers the failover resource.

# clpstring create -t resource -b MySQL-resource MySQL-resource

# clresource create -d -g MySQL-failover-resource-group \
-t ORCL.mysql \
-p Mysql_basedir=MySQL-base-directory \
-p Mysql_datadir=MySQL-database-directory \
-p Mysql_user=MySQL-OS-user \
-p Mysql_host=MySQL-logical-host \
-p Mysql_fmuser=MySQL-database-fault-monitoring-user \
-p Mysql_logdir=MySQL-database-log-directory \
-p Resource_dependencies=MySQL-logical-host-resource \
-p Resource_dependencies_offline_restart=MySQL-storage-resource \
MySQL-resource
Copyright © 2000, 2019, Oracle and/or its affiliates.  
Previous
Next