By luojiac on Aug 20, 2007
MySQL data service running on a clustered-server model provide orderly startup, shutdown, fault monitoring, and high availability(failover) mechanism compared to the single server model. It can be free donwloaded, and Sun also released Open HA cluster, derived from the Sun Cluster 3.2 agents including HA MySQL data service. The open HA cluster also provide build tools necessary to develop new features, build and use the code. In addition, Sun cluster 3.2 Data service for MySQL has the advantages including:
It is easy to install and configure with straightforward GUI and command line interfaces.
Expanded support for Solaris Containers(Solaris zones)
Expanded support for SMF
ZFS is fully supported as a local highly available fileystem
The following is the OS and HW setup for two-node cluster:
Solaris 10 11/06 installed on the two nodes
Each node has two network interfaces to be used as point-to-point private interconnects, and one network interface connect to the public network interface.
Two storage device(SE6120) connected to the two nodes
Figure 1:MySQL Replication Clustered Model
In the above MySQL clustered-server configuration, logical host name is set as the failover IP address within the same subnet. When the MySQL resource online, the failover IP is plumbed on the node where the MySQL resource is running. If a failover happen, the IP address moves along with the MySQL resource to the failover node. In this example, the configuration is:
For the private network interfaces as cluster interconnection, Sun cluster installation will configure the network assigning the private network addresses. Note: Do NOT configure the private network interfaces before installing Sun cluster, otherwise, you will fail to install Sun cluster and get the error message as:
Adapter “ce0” is already in use as a public network adapter
After the HW and network is setup as the above, the general task flow of MySQL data service installation and configuration is:
Figure 2:MySQL Data Service Installation & Configuration Flow chart
Step 1: Plan Installation
Sun cluster requires to set aside a special file system named as: /globaldevices on one of the local disks for use in managing global devices on the two nodes.This file system is later mounted as a cluster file system as:
(optional) Setup cluster environment
On both nodes, update the /etc/inet/ipnodes file with all public hostnames for the cluster
Sunfire-x64-241-02# vi /etc/inet/ipnodes 127.0.0.1 localhost 10.6.241.208 Sunfire-x64-241-02 loghost 10.6.241.209 Sunfire-x64-241-03 Sunfire-x64-241-03# vi /etc/inet/ipnodes 127.0.0.1 localhost 10.6.241.209 Sunfire-x64-241-03 loghost 10.6.241.208 Sunfire-x64-241-02
On both nodes, add the following entry in the /etc/system file
set ce:ce_taskq_disable=1 exclude:lofs
The first entry supports ce adapters for the private interconnect, and the second entry disable the loopback file system(LOFS)
5. local-mac-address? variable must set as true for Ethenet adapters. On Solaris x86, use the command: #eeprom local-mac-address?=true; On Solaris SPARC, change the local-mac-address? Variable to be ture from OBP OK> prompt
Step 2: Sun Cluster and MySQL data service Installation
Solaris cluster(sun cluster, Sun cluster Geographic Edition and Sun cluster agents) 3.2 is downloadable at:
On both nodes, run the installer command to bring up the installer GUI . The installation of Sun cluster and Sun cluster for HA MySQL is straightforward to follow the instructions on the screen. Choose Sun cluster Core 3.2 core software, Sun cluster HA for MySQL to install, and choose Configure later before the installation.
Step 3: Configure Sun Cluster Software
Perform this procedure from one node of the cluster to configure Sun Cluster software on all nodes of the cluster:
1. # /usr/cluster/bin/scinstall
2. From the main menu, pick the “Option 1” to “Create a new cluster or add a cluster node”
3. From the new cluster and Cluster Node menu, pick the “Option 1” to “Create a new cluster”
4. From the Typical or Custom Mode, pick the “Option 1” for typical mode
5. From the Cluster Nodes menu, type the node name: “Sunfire-x64-241-02”, and “Sunfire-x64-241-03”
6. Provide the first and the second private adapter name: ce0, bge1
7. Type “no” for “ disable automatic quorum device selection”
8. Type “yes” for “ create the new cluster”
9. Type “no” for “ Interrupt cluster creation for sccheck errors”
At this point, the scinstall utility configures all cluster nodes and reboot the cluster. The cluster is established when all nodes have successfully booted into the cluster. Sun Cluster installation output is logged in /var/cluster/logs/install/scinstall.log.N file.
10. Verify cluster setup:
Sunfire-x64-241-03# clquorum list Sunfire-x64-241-03 Sunfire-x64-241-02 Sunfire-x64-241-03# clnode status Cluster Nodes === --- Node Status --- Node Name Status --------- ------ Sunfire-x64-241-03 Online Sunfire-x64-241-02 Online
When the scinstall utility finishes, The cluster is now ready to configure the components you will use to support highly available MySQL, including device groups, and file systems.
Step 4: Configure Volume Manager and File System
In a shared disk set configuration in this example, two hosts are physically connected to the same set of disks. When one node fails, another node has exclusive access to the disks. Each node can control a shared disk set, but only one host can control it at a time.
From one node: Sunfire-x64-241-02, create one disk set for MySQL data service, so that Sunfire-x64-241-02 is make as the primary node:
Sunfire-x64-241-02# metaset -s mysqlset -a -h Sunfire-x64-241-02 Sunfire-x64-241-03
Verify that the configuration of the disk sets is correct and visible to both nodes.
Sunfire-x64-241-02# metaset Set name = mysqlset, Set number = 1 Host Owner Sunfire-x64-241-02 Yes
From the primary node: Sunfire-x64-241-02, list the DID mappings:
Sunfire-x64-241-02# cldevice show |grep Device
=== DID Device Instances ===
... DID Device Name: /dev/did/rdsk/d5 Full Device Path: Sunfire-x64-241-02:/dev/rdsk/c3t60003BACCC90200046264D58000A22E3d0 Full Device Path: Sunfire-x64-241-03:/dev/rdsk/c5t60003BACCC90200046264D58000A22E3d0
Add /dev/did/rdsk/d5 to the MySQL disk setup
Sunfire-x64-241-02# metaset -s mysqlset -a /dev/did/rdsk/d5
Verify that the configuration of the disk set is correct
Sunfire-x64-241-02# metaset -s mysqlset
On both nodes, create the /etc/lvm/md.tab file with the following entries
mysqlset1/d0 -m mysqlset1/d10 mysqlset1/d10 1 1 /dev/did/rdsk/d5s0 mysqlset1/d1 -p mysqlset1/d0 50G
mysqlset1/d2 -p mysqlset1/d0 50G
mysqlset1/d3 -p mysqlset1/d0 50G
7. From Sunfire-x64-241-02, take ownership for the mysql disk set and activate the volume
Sunfire-x64-241-02# cldevicegroup switch -n Sunfire-x64-241-02 mysqlset Sunfire-x64-241-02# metainit -s mysqlset -a
8. Verify the status of the volume for the disk setup
Sunfire-x64-241-02# metastat ... Status: Okay ...
9. Create the cluster file system for use Sun Cluster HA for MySQL. From Sunfire-x64-241-02,create te file systems: Sunfire-x64-241-02# newfs /dev/md/mysqlset1/rdsk/d1 Sunfire-x64-241-02# newfs /dev/md/mysqlset1/rdsk/d2 Sunfire-x64-241-02# newfs /dev/md/mysqlset1/rdsk/d3
10. On both node, create the mount-point directory for the file systems
# mkdir -p /global/mysql - mysql master and slave servers
# mkdir -p /global/mysql-data1 – data directory for mysql master server # mkdir -p /global/mysql-data2 – data directory for mysql slave server
11. On both node, add entries to the /etc/vfstab file for the above mount points
12. On both nodes, mount the file systems, and verify that the file systems are mounted
# mount /global/mysql on /dev/md/mysqlset/dsk/d1 read/write/setuid/devices/intr/largefiles/logging/noquota/global/xattr/nodfratime/onerror=panic/dev=1544001 on Tue Aug 14 17:12:45 2007
/global/mysql-data1 on /dev/md/mysqlset/dsk/d2 read/write/setuid/devices/intr/forcedirectio/largefiles/logging/noquota/global/xattr/nodfratime/onerror=panic/dev=1544002 on Tue Aug 14 17:12:50 2007
/global/mysql-data2 on /dev/md/mysqlset/dsk/d3 read/write/setuid/devices/intr/forcedirectio/largefiles/logging/noquota/global/xattr/nodfratime/onerror=panic/dev=1544003 on Tue Aug 14 17:12:56 2007
13. Verify the Sun cluster installation & configuration before registering and configuring Sun cluster HA for MySQL
Sunfire-x64-241-02# cluster check
Step 5: Configure Sun Cluster Resource for MySQL
Register SUNW.gds, SUNW.HAStoragePlus resource type
Sunfire-x64-241-02# scrgadm -a -t SUNW.gds
Sunfire-x64-241-02# scrgadm -a -t SUNW.HAStoragePlus
Create MySQL resource group named MySQL-failover-resource-group
Sunfire-x64-241-02# scrgadm -a -g MySQL-failover-resource-group
Create the HAStoragePlus resource named MySQL-has-resource in the MySQL-failover-resource-group resource group for MySQl disk storage
Sunfire-x64-241-02# scrgadm -a -j MySQL-has-resource -g MySQL-failover-resource-group -t SUNW.HAStoragePlus -x FilesystemMountPoints=/global/mysql-data1,/global/mysql-data2
Create a logical hostname resource named MySQL-lh-resource
Sunfire-x64-241-02# scrgadm -a -L -j MySQL-lh-resource -g MySQL-failover-resource-group -l 10.6.241.210
To verify the logical hostname resource is online, you can run “ifconfig -a” to see if the virtual IP address is configured on the network interface.
Enable the failover resource group including the MySQL disk storage and logical hostname resources.
Sunfire-x64-241-02# scswitch -Z -g MySQL-failover-resource-group
Step 6: Install and Configure MySQL
On both nodes, install MySQL under /usr/local/mysql, which is symbol link to /global/mysql
Sunfire-x64-241-02# mysql_install_db –datadir=/global/mysql-data1
Sunfire-x64-241-02# chown -R root .
Sunfire-x64-241-02# chown -R mysql /global/mysql-data1
Sunfire-x64-241-02# chgrp -R mysql .
Copy the sample “my.cnf_sample_master” and “my.cnf_sample_slave” under “/opt/SUNWscmys/etc” to the MySQl data directory(mysql-data1, mysql-data2) of the MySQL master and slave machine.
Modify the sample my.cnf file to point to the right directories for the data and log files. “bind-address” must be set with the logical hostname as “10.6.241.210” in this example. Please note: it need to set bind-address = ip number, because of the unfixed MySQL bug on Solaris amd64 OS.
Step 7: Modify MySQL Configuration Files
Go to the directory /opt/SUNWscmys, add cluster's information in the mysql_config file.
Sunfire-x64-241-02# vi mysql_config ... MYSQL_USER=root MYSQL_PASSWD=password MYSQL_HOST=10.6.241.210 ->Logical hostname(IP) FMUSER=fmuser FMPASS=fmuser MYSQL_SOCK=/tmp/10.6.241.212.sock MYSQL_NIC_HOSTNAME="Sunfire-x64-241-02 Sunfire-x64-241-03" ->Physical hostname
Add cluster's information in the ha_mysql_config file
Sunfire-x64-241-02# vi ha_mysql_config ... RS=MySQL-failover-resource-group RG=MySQL-failover-resource-group PORT=3306 LH=10.6.241.210 HAS_RS=mysql-has-resource
... BASEDIR=/usr/local/mysql DATADIR=/global/mysql-data1 MYSQLUSER=mysql MYSQLHOST=Sunfire-x64-241-02 FMUSER=fmuser FMPASS=fmuser LOGDIR=/global/mysql-data1/logs CHECK=YES
Step 8: Enable Sun cluster HA For MySQL
Start MySQL server with “–skip-grant-table” option
Sunfire-x64-241-02# mysqld_safe –defaults-file=/global/mysql-data1/my.cnf –datadir=/global/mysql-data1 –skip-grant-table –user=mysql &
Change the password for root as “password”
Sunfire-x64-241-02# mysql -S /tmp/10.6.241.210.sock -u root mysql>UPDATE mysql.user set Password=PASSWORD('password') -> where User='root'; mysql>FLUSH PRIVILEGES;
Shutdown and restart the MySQL servers without the “–skip-grant-table” option on the node where the resource group is online(check it with the “scstat -g” command)
Prepare the Sun cluster specific test database.
Sunfire-x64-241-02# cd /opt/SUNWscmys Sunfire-x64-241-02# ./mysql_register -f /opt/SUNWscmys/util/mysql_config sourcing /opt/SUNWscmys/util/mysql_config and create a working copy under /opt/SUNWscmys/util/mysql_config.work
MySQL version 5 detected on 5.10/SC3.2 Add faulmonitor user (fmuser) with password (fmuser) with Process-,Select-, Reload- and Shutdown-privileges to user table for mysql database for host Sunfire-x64-241-02 Add SUPER privilege for fmuser@Sunfire-x64-241-02 Add faulmonitor user (fmuser) with password (fmuser) with Process-,Select-, Reload- and Shutdown-privileges to user table for mysql database for host Sunfire-x64-241-03 Add SUPER privilege for fmuser@Sunfire-x64-241-03 Create test-database sc3_test_database Grant all privileges to sc3_test_database for faultmonitor-user fmuser for host Sunfire-x64-241-02 Grant all privileges to sc3_test_database for faultmonitor-user fmuser for host Sunfire-x64-241-03 Flush all privileges Mysql configuration for HA is done
Shutdown the mysql server
Sunfire-x64-241-02# mysqladmin -S /tmp/10.6.241.210.sock shutdown -p
Sunfire-x64-241-02# cd /opt/SUNWscmys Sunfire-x64-241-02# ./ha_mysql_register -f /opt/SUNWscmys/util/ha_mysql_config
Enable each MySQL resource
scswitch -e -j MySQL-has-resource scswitch -e -j MySQL-lh-resource
Step 9: Verify Sun cluster HA for MySQL configuration
Once all the MySQL resources are created and configured, and online(check with the “scstat -g” command), you should go ahead to see if the MySQL database can successfully fail over to each node configured in the resource group(MySQL-failover-resource-group). This can be verified by running with “scswitch” to switch MySQL resource group to another node to fail the resouce group to:
#scswitch -z -g MySQL-failover-resource-group -h Sunfire-x64-241-03
If you can successfully migrate the database to each node in the cluster, you now have highly available MySQL database.
At this point, by following the above nine steps, I have completed the basics of deploying highly available MySQL database. To deploy the Sun Cluster MySQL data service to achieve the maximum availability into the production environment, I would highly recommend you read through the Sun cluster documents and MySQL data service guides and verify everything in a test environment in advance.