MySQL High Availability Using HP Serviceguard

Introduction to HP Serviceguard and MySQL Toolkit

HP Serviceguard is specialized software for protecting mission-critical applications from a wide variety of hardware and software failures.

The MySQL toolkit for HP Serviceguard consists of a set of shell scripts used by a package control script to start, stop, and monitor the MySQL server package. The toolkit simplifies the effort of making MySQL highly available by integrating MySQL with HP Serviceguard for Linux (SG/LX).

In an SG/LX environment, MySQL must have the same configuration on all cluster nodes that are configured to run the package. The node currently running the package is called the primary node. All other nodes are called standby nodes. In the event of a failure on the primary node, the package fails over to a standby node and the database continues to function. To ensure that the database can failover properly, all data must be stored on shared storage, and this storage must be accessible to all nodes configured to run the package.

When the package fails over from one node to another, the following actions occur:

On the primary node:
- The package is halted on the node where it is currently running. As a result, all package resources are halted.
- The relocatable IP address is removed from this node.
- The file systems are unmounted and all volume groups assigned to this package are deactivated.
- The systems are going to halt when the storage or networks fail.
On the standby node:
- The volume groups are activated and file systems are mounted.
- The relocatable IP address is moved to the new node.
- All resources are started up, and the database is displayed.
- The clients connect through the same relocatable IP address.

Testing Assumptions

The MySQL toolkit README file describes how to test the High Availability with the MySQL Toolkit in an SG/LX environment. The file is located in the /opt/cmcluster/mysqltoolkit/ directory. Make sure that you are already familiar with the HP Serviceguard configuration and with MySQL server concepts. Additionally, verify that the following installation and configuration procedures have been completed:

You have set up two or more Linux systems, all connected to the appropriate external hardware for a shared data drive. These systems act as the MySQL Serviceguard cluster used in the following test example. For example, these may be two or more HP ProLiant or Integrity servers.
You have set up an additional server to test the MySQL Serviceguard cluster. This server has the MySQL database installed, as well as the MySQL bench testing tool, also called sql-bench.
You have set up a shared storage system, such as an HP StorageWorks MSA 1000, MSA 1500, EVA3000, or EVA 4000 system, and this shared storage is located in the /dev/sdx directory on each server. For this example, the directory /dev/sdb is used on both servers.

You have set up the lock logical unit number (LUN) and logical volumes (LV) on the external shared storage using the following specifications:

Cluster_Lock_LUN:
  DEVICE Name             /dev/sdx1                          
  Size                    10MB

Logical Volumes:
  PV Name                 /dev/sdx2
  LV Name                 /dev/vg/lv
  VG Name                 /dev/vg
  LV Size                 50.00 GB
  Mount Point             /mysql
  Filesystem Type         ext3

For more information about Lock LUN, see Managing HP Serviceguard for Linux located at:

http://docs.hp.com/en/B9903-90046/index.html

You have installed SG/LX on all the nodes that are included in the cluster. For more information on this installation, see Managing HP Serviceguard for Linux at:
http://docs.hp.com/en/B9903-90046/index.html
You have installed the Serviceguard MySQL toolkit on all target nodes.
The Serviceguard MySQL toolkit is installed in the /usr/local/cmcluster/mysqltoolkit directory. For more information about this installation, see the MySQL toolkit documentation at:
http://docs.hp.com/en/T2377-90001/index.html
You have installed the MySQL database and configured it on the shared storage.
For more information, see the file /opt/cmcluster/mysqltoolkit/README.

You have configured the MySQL Serviceguard Cluster package with the following values:

MySQL Cluster:
PACKAGE Name              	MySQL_PKG
Relocatable IP address     10.101.0.251

Testing the MySQL Serviceguard Cluster

Testing the Cluster Connection

Perform the following steps from the external test system as described in the testing assumptions in “Testing Assumptions”:

Grant the required privileges to the sample database user to allow access to the MySQL database service from the third machine. This procedure is described in “Configuring MySQL Server Replication”. For example, enter the grant command as follows:
grant all on osmsdb.* to 'osmsusr'@'%' identified by 'osmspass';
On the test machine, verify the MySQL connection by entering the following command:
# mysqladmin -h relocatable_ip_address version
For example:
# mysqladmin -h 10.101.0.251 version
The output looks similar to the following:
Server version 5.0.40-enterprise-gpl-log Protocol version 10 Connection 10.101.0.251 via TCP/IP TCP port 3306 Uptime: 57 sec Threads: 1 Questions: 14 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 6 Queries per second avg: 0.246
This output demonstrates that the MySQL client running on the test machine is able to connect to the primary node of the MySQL Serviceguard cluster using the relocatable IP address.

Testing the Cluster Stability

The MySQL-bench tool is useful for performing general comparisons of servers and configurations. Use this tool to perform the stress tests described in this blueprint. In the following test, a sample database is created and populated with benchmark data. The tests in the suite can be run separately or together.

For more information about using the MySQL-bench tool, see the web page for the MySQL benchmark suite at:

http://dev.mysql.com/doc/refman/5.0/en/mysql-benchmarks.html

The following test uses an x86_64 system running a fully installed version of the RHEL5 distribution. The RHEL5 distribution contains embedded mysql-bench test suite.

To enable and use the mysql-bench test suite to verify the stability of the MySQL Serviceguard cluster, use the following steps:

Verify that the mysql-bench test suite is installed, by entering the following command:
# rpm -qa | grep mysql-bench
If the test suite is installed, the following message is shown:
mysql-bench-5.0.22-2.1
If the test suite is not installed, you can retrieve the package (mysql-bench-5.0.22-2.1.x86_64.rpm) from the RHEL5 distribution. Once you have the file, install it by entering the following command:
# rpm -ivh mysql-bench-5.0.22-2.1.x86_64.rpm
Next, verify that mysql client, mysql server, the Perl DBI module and MySQL DBD drivers are installed by entering the following commands. :
# rpm -qa | grep perl-DBD-MySQL
# rpm -qa | grep perl-DBI
# rpm -qa | grep mysql-5.0.22-2.1
# rpm -qa | grep mysql-server-5.0.22-2.1
Run the test suite by entering the following commands.
# cd $SQL_BENCH_DIR
# ./run-all-tests --host= relocatable_ip_address
where:
SQL_BENCH_DIR is the MySQL-bench installation directory.
For example, enter the following commands:
# cd /usr/share/sql-bench
# ./run-all-tests --host= 10.101.0.251
NOTE: These tests can take a long time to complete.
To make the results easier to read, repeat the commands with the options --log --use-old-result.
To create a file based on the results you obtained from the $SQL_BENCH_DIR output directory, enter the following command:
# ./run-all-tests --host= 10.101.0.251 --comment= \
"HP SG/LX with MySQL 5.0.40 enterprise gpl log" --log --use-old-results
Check the results located in output/RUN-mysql-Linux_2.6.18_8.el5_x86_64. If the test passed, the following message is shown:
All 9 test executed successfully

Testing Cluster Failover

You can simulate some of the following cases, which cause primary-node failure, to verify the MySQL Serviceguard cluster failover function.




	CAUTION: You should perform the following tests with sample data before trying the tests with critical production data. In the event of a serious failure or the wrong configuration, data corruption can result.

End the primary node's MySQL processes.
Pull the power cable out on the primary node to cause a power failure.
Pull the network cable out on the primary node to cause the loss of a network.
Issue the power off and reboot commands on the primary node.
End all SG/LX processes on the primary node.

In the event of a failure on the primary node, the package automatically fails over to the standby node and the database continues to function. Thus, the database fails over successfully.

You can verify that the database continues to function by accessing the MySQL database service when the primary node fails. For more information on how to test the MySQL Serviceguard cluster connection, see “Testing the Cluster Connection ”.