RandD:MasterMySQL
From Oddcast Wiki
Contents[hide] |
Overview
Following the guidelines in the MySQL Replication FAQ and using HeartBeat to provide High Availability cluster of two MySQL servers with automatic designation of the master and slave. The management of the master / slave status between the two clustered servers is via the MasterMySQL script and MasterMySQL Perl package. The script relies on the fact that state transitions in heartbeat cause certain resources to be transitioned based upon entries in the /etc/ha.d/haresources file. By creating a new resource "MasterMySQL" and giving it it's own unique IP address, we can access the master of the cluster by that IP address and control starting and stopping of the master via the MasterMySQL Perl script which is stored in /etc/ha.d/resource.d.
Hardware Configuration
Two servers will act as the two node cluster of Master servers. Master servers are responsible for read/write access in an active/passive mode. The passive node can also participate in the read only cluster of slave servers. Additional slave servers can connect to the active master to allow read scalability.
The two read/write nodes must run heartbeat v2.x and be configured as an active/passive cluster. The two master nodes should have a serial null modem connection. All nodes should have a connection to a "public" LAN, where client servers will connect from, and a separate dedicated connection to a "private" LAN, which will be used for all replication and communication between nodes.
Sample Scenario
The following image illustrates a sample scenario. Note that the choice of IP addresses within two Class B networks is intended to simplify the numbering only.
The domain name will be "example.com".
- There will be two networks:
- a "public" network which is 10.0.0.0/16
- a "private" network which is 10.1.0.0/16
- There are two master nodes (A and B), which are generally reachable via the IP addresses 10.0.0.1 (masterA.example.com) and 10.0.0.2 (masterB.example.com) respectively.
- The cluster will appear on the "public" network as 10.0.2.1.
- There are N total slaves (1 to N), which are generally reachable via the IP addresses 10.0.1.1 (slave1.example.com) up to 10.0.1.N (slaveN.example.com).
MySQL Specific Configuration
The MySQL Replication FAQ mentions that each server in a replicated group must have a unique server-id set in /etc/my.cnf. I have also found it neccessary to increase the maximum allowed packet size as my slave kept crashing and complaining that I needed to increase the value for max_allowed_packet. And of course binary logging (log-bin) must be turned on for replication.
/etc/my.cnf
log-bin server-id = 1 set-variable = max_allowed_packet=128M
Heartbeat Resource Script
The resource script uses three targets: start, stop and status. All of the real work is done in the start section. start implies that the server running this resource script will be the master of the MySQL replicated pair. The script then attempts to go through the steps described in the MySQL replication FAQ. It also makes every best effort to inform all slaves that it knows of that it is the new master.
The only responsibility of the script in stop mode is to make sure that mysql is actually running on the server. The master will need to contact it when it is ready to issue commands to set up the slave and if mysql is not running, this would not be possible. NOTE: This is problematic. If MasterMySQL finds that mysql is not behaving as expected (possibly down), then the cluster should really be considered unstable and shouldn't allow this server to become a member of the cluster.
database connectivity
All database commands are made via the perl module DBI and because this is a mysql database, I also needed DBD::mysql installed. I am also using the Sys::Syslog module for logging. Logging at the highest level is pretty excessive. For reference, you need to run
perl -MCPAN -eshell install DBI force install DBD::mysql
"force" only being neccessary if you don't want to bother with loading the correct connection information in to CPAN before you do your install. Also, using CentOS 4 and the RHEL mysql packages from dev.mysql.com, I was able to add the Perl-DBI package rather than using CPAN.
intracluster communication
The names of the two master cluster members are pulled from the ha.cf file as fully qualified hostnames (e.g. masterA.example.com and masterB.example.com) and "atomized" (i.e. turned in to not-fully qualified domain names). These atomic names are used in the /etc/hosts file on each server to identify a path to the other cluster member via the crossover ethernet cable installed as part of the heartbeat configuration. This allows references to the server via the FQDN (e.g. masterA.example.com) to use a public DNS resolvable IP address (10.0.0.1) while the atomic name (e.g. masterA) to be used for private intra-cluster traffic (10.1.0.1). This difference is exploited by the script. This would imply the following entries in the two relevant files:
/etc/ha.d/ha.cf
node masterA.example.com node masterB.example.com
/etc/hosts
10.1.0.1 masterA 10.1.0.2 masterB
download and install
The files can be downloaded from http://www.workboy.com/charles/mysql-heartbeat/new.tar
Edit the Makefile to provide the mysql root password you will use for your cluster. Then type "make" and "make install". The READ_ME file in the distribution contains the most updated instructions.
I welcome any feedback etc. to move the development of this along
This is currently running on MySQL 4.1.x / 5.1.x with heartbeat 2.0.x and CentOS 4.5