HA MySQL Cluster with DRBD and Redhat Cluster Add-on

I am creating a fault-tolerant mysql cluster using filesystem replication provided by DRBD and controlled by the Redhat Cluster Add-on. This will provide 99.999% uptime according to Oracle.

Starting with a base install of Centos 6.4 x86_64 with iptables turned off and selinux disabled. For networking, I am using two subnets with bonding. One for data connections and one for replication.

VIP: 192.168.21.175

server1:
bond0: 192.168.21.176
bond1: 172.16.2.1

server2:
bond0: 192.168.21.177
bond1: 172.16.2.2

To start, install and configure NTP on both servers. Time synchronization is extremely important.

yum install ntp
chkconfig ntpd on
service ntpd start

Download and install the Elrepo rpm for access to their repositories. This is where we will be getting DRBD.

rpm -Uvh http://elrepo.org/elrepo-release-6-5.el6.elrepo.noarch.rpm

Once you have installed the repo on both of your machines, edit the .repo and disable it. We do not want to install any other packages from their repository by accident. Edit /etc/yum.repos.d/elrepo.repo and ensure that all are enabled=0.

Now install DRBD.

yum --enablerepo install drbd84-utils kmod-drbd84

(For me this installed a kernel update which required a reboot.)

Create and edit a resource file for our mysql resource, /etc/drbd.d/mysql.res

resource mysql {
	protocol C;
 
	startup {
		degr-wfc-timeout 120;    # 2 minutes.
		outdated-wfc-timeout 2;  # 2 seconds.
	}
 
        disk {
        	on-io-error detach;
	}
 
	net {
		cram-hmac-alg "sha1";
		shared-secret "clusterdb";
		after-sb-0pri disconnect;
		after-sb-1pri disconnect;
		after-sb-2pri disconnect;
		rr-conflict disconnect;
	}
 
	syncer {
		rate 20M;
		al-extents 257;
		on-no-data-accessible io-error;
	}
 
	device /dev/drbd0;
        disk /dev/sdb1;
        meta-disk internal;
 
        on server1 {
        	address 172.16.2.1:7790;
	}
 
	on server2 {
        	address 172.16.2.2:7790;
	}
}

Once finished, copy it to your other server.

If you have not already done so, you will need to provision your underlying storage. I will leave that to you to decide, but for this excersize I have a single raid1+0 that is /dev/sdb1. Simply partition your device, but do not create a filesystem.

Now enable your resource on both servers.

drbdadmin create-md mysql

If you receive an error like the following there is more than likely an error in your resource file.

'mysql' not defined in your config (for this host).

Also, if your storage volume was used previously then DRDB will refuse to enable it with the following error.

[root@server1 drbd.d]# drbdadm create-md mysql
md_offset 2399729086464
al_offset 2399729053696
bm_offset 2399655817216
 
Found ext3 filesystem
  2343485440 kB data area apparently used
  2343413884 kB left usable by current configuration
 
Device size would be truncated, which
would corrupt data and result in
'access beyond end of device' errors.
You need to either
   * use external meta data (recommended)
   * shrink that filesystem first
   * zero out the device (destroy the filesystem)
Operation refused.
 
Command 'drbdmeta 0 v08 /dev/sdb1 internal create-md' terminated with exit code 40

You can simply zero out the device with the following.

[root@server1 drbd.d]# dd if=/dev/zero of=/dev/sdb1 bs=1M count=128

Then enable it.

[root@server1 drbd.d]# drbdadm create-md mysql
Writing meta data...
initializing activity log
NOT initializing bitmap
New drbd meta data block successfully created.
success

Now start the DRBD service on both nodes. Once started on the first node, it will wait a certain amount of time for the second node to start. I have configured this one to wait two minutes.

[root@server1 drbd.d]# service drbd start
Starting DRBD resources: [
     create res: mysql
   prepare disk: mysql
    adjust disk: mysql
     adjust net: mysql
]
.....

Now we are going to sync the two servers. I know that I am starting with two fresh disks with nothing of value on them, so I tell server1 to overwrite server2.

[root@server1 drbd.d]# drbdadm primary --force mysql

You can then check the status of the sync with the following commands. Depending on the size of your storage, it may take a while for the sync to complete, but it should not be necessary to wait for it to complete for the next few steps.

[root@server1 drbd.d]# drbd-overview 
  0:mysql/0  SyncSource Primary/Secondary UpToDate/Inconsistent C r---n- 
	[>....................] sync'ed:  0.1% (2288100/2288488)M
 
[root@server1 drbd.d]# service drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by dag@Build64R6, 2012-09-06 08:16:10
m:res    cs          ro                 ds                     p  mounted  fstype
0:mysql  SyncSource  Primary/Secondary  UpToDate/Inconsistent  C
...      sync'ed:    10.1%              (2059556/2288488)M

Now we will format the filesystem. Remember that server1 is still the primary node and has the device, so we will format from there.

[root@server1 ~]# mkfs.ext4 /dev/drbd0
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=64 blocks, Stripe width=512 blocks
146464768 inodes, 585852959 blocks
29292647 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
17879 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968, 
	102400000, 214990848, 512000000, 550731776
 
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
 
This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

I am installing MySQL from the RPM provided by Oracle, but you can use the version from the package manager. Install the server and client onto both servers.

[root@server1 ~]# rpm -ivh --force MySQL-server-5.6.12-1.el6.x86_64.rpm 
[root@server1 ~]# yum install MySQL-client-5.6.12-1.el6.x86_64.rpm

Also, ensure that the mysql service is NOT set to start on boot. The cluster service will control where the mysql process runs.

[root@server1 ~]# chkconfig mysql off

On both servers, make the directory that will be the mount point for the DRBD device and change the ownership to the mysql user.

[root@server1 ~]# mkdir /var/lib/mysql_drbd
[root@server1 ~]# chown mysql.mysql /var/lib/mysql_drbd/

From server1, which at this point should be the primary, mount the drbd device at the mysql mount point.

[root@server1 ~]# mount /dev/drbd0 /var/lib/mysql_drbd/

We will be keeping the mysql config file and the mysql data directory on the drbd device so that the servers can properly share them. Create the directory and copy the my.cnf to the mount point and ensure the proper file ownership.

[root@server1 ~]# mkdir /var/lib/mysql_drbd/data
[root@server1 ~]# cp /etc/my.cnf /var/lib/mysql_drbd/my.cnf
[root@server1 ~]# chown -R mysql.mysql /var/lib/mysql_drbd/

Edit the edit the /var/lib/mysql_drbd/my.cnf file and set datadir=/var/lib/mysql_drbd/data in the [mysqld] section. Also confirm that the socket is configured to /var/lib/mysql/mysql.sock and the pid file to /var/lib/mysql/mysql.pid.

Now install the database at the new location.

[root@server1 ~]# mysql_install_db --datadir=/var/lib/mysql_drbd/data --user=mysql
Installing MySQL system tables...2013-07-23 18:02:02 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-07-23 18:02:02 8511 [Note] InnoDB: The InnoDB memory heap is disabled
2013-07-23 18:02:02 8511 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-07-23 18:02:02 8511 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-07-23 18:02:02 8511 [Note] InnoDB: Using Linux native AIO
2013-07-23 18:02:02 8511 [Note] InnoDB: Using CPU crc32 instructions
2013-07-23 18:02:02 8511 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-07-23 18:02:02 8511 [Note] InnoDB: Completed initialization of buffer pool
2013-07-23 18:02:02 8511 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2013-07-23 18:02:02 8511 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2013-07-23 18:02:02 8511 [Note] InnoDB: Database physically writes the file full: wait...
2013-07-23 18:02:02 8511 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2013-07-23 18:02:02 8511 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2013-07-23 18:02:03 8511 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-07-23 18:02:03 8511 [Warning] InnoDB: New log files created, LSN=45781
2013-07-23 18:02:03 8511 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-07-23 18:02:03 8511 [Note] InnoDB: Doublewrite buffer created
2013-07-23 18:02:03 8511 [Note] InnoDB: 128 rollback segment(s) are active.
2013-07-23 18:02:03 8511 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-07-23 18:02:03 8511 [Note] InnoDB: Foreign key constraint system tables created
2013-07-23 18:02:03 8511 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-07-23 18:02:03 8511 [Note] InnoDB: Tablespace and datafile system tables created.
2013-07-23 18:02:03 8511 [Note] InnoDB: Waiting for purge to start
2013-07-23 18:02:03 8511 [Note] InnoDB: 5.6.12 started; log sequence number 0
2013-07-23 18:02:03 8511 [Note] Binlog end
2013-07-23 18:02:03 8511 [Note] InnoDB: FTS optimize thread exiting.
2013-07-23 18:02:03 8511 [Note] InnoDB: Starting shutdown...
2013-07-23 18:02:05 8511 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
 
Filling help tables...2013-07-23 18:02:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-07-23 18:02:05 8534 [Note] InnoDB: The InnoDB memory heap is disabled
2013-07-23 18:02:05 8534 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-07-23 18:02:05 8534 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-07-23 18:02:05 8534 [Note] InnoDB: Using Linux native AIO
2013-07-23 18:02:05 8534 [Note] InnoDB: Using CPU crc32 instructions
2013-07-23 18:02:05 8534 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-07-23 18:02:05 8534 [Note] InnoDB: Completed initialization of buffer pool
2013-07-23 18:02:05 8534 [Note] InnoDB: Highest supported file format is Barracuda.
2013-07-23 18:02:05 8534 [Note] InnoDB: 128 rollback segment(s) are active.
2013-07-23 18:02:05 8534 [Note] InnoDB: Waiting for purge to start
2013-07-23 18:02:05 8534 [Note] InnoDB: 5.6.12 started; log sequence number 1625977
2013-07-23 18:02:05 8534 [Note] Binlog end
2013-07-23 18:02:05 8534 [Note] InnoDB: FTS optimize thread exiting.
2013-07-23 18:02:05 8534 [Note] InnoDB: Starting shutdown...
2013-07-23 18:02:07 8534 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
 
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
 
  /usr/bin/mysqladmin -u root password 'new-password'
  /usr/bin/mysqladmin -u root -h server1 password 'new-password'
 
Alternatively you can run:
 
  /usr/bin/mysql_secure_installation
 
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
 
See the manual for more instructions.
 
You can start the MySQL daemon with:
 
  cd /usr ; /usr/bin/mysqld_safe &
 
You can test the MySQL daemon with mysql-test-run.pl
 
  cd mysql-test ; perl mysql-test-run.pl
 
Please report any problems with the /usr/bin/mysqlbug script!
 
The latest information about MySQL is available on the web at
 
  http://www.mysql.com
 
Support MySQL by buying support/licenses at http://shop.mysql.com
 
WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.
 
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

Now unmount the drbd device and demote the server to secondary as the cluster service will control which server is the primary and mounts the filesystem.

[root@server1 ~]# umount /var/lib/mysql_drbd/
[root@server1 ~]# drbdadmin secondary mysql

Now to configure the Redhat Cluster Add-on to manage our drbd device and the mysql service.

Install the clustering services.

yum groupinstall "High Availability"

Ensure the correct services are set to start on boot.

chkconfig cman on
chkconfig rgmanager on
chkconfig modclusterd on
chkconfig ricci on

Create a password for the ricci service user.

passwd ricci

Create our basic cluster config file at /etc/cluster/cluster.conf. One this to note, this configuration does not include fencing. Device fencing is a requirement for Redhat clustering. If you do not have it, your cluster will not operate properly.

<?xml version="1.0"?>
<cluster config_version="18" name="mysql-cluster">
	<fence_daemon clean_start="0" post_join_delay="30"/>
	<clusternodes>
		<clusternode name="server01" nodeid="1">
			<fence>
				<method name="fence_ilo">
					<device name="ipmi_176"/>
				</method>
			</fence>
		</clusternode>
		<clusternode name="server02" nodeid="2">
			<fence>
				<method name="fence_ilo">
					<device name="ipmi_177"/>
				</method>
			</fence>
		</clusternode>
	</clusternodes>
	<cman expected_votes="1" transport="udpu" two_node="1"/>
	<fencedevices>
		<fencedevice agent="fence_ipmilan" auth="password" ipaddr="192.168.100.176" lanplus="on" login="hpilo" name="ipmi_176" passwd="yourpassword"/>
		<fencedevice agent="fence_ipmilan" auth="password" ipaddr="192.168.100.177" lanplus="on" login="hpilo" name="ipmi_177" passwd="yourpassword"/>
	</fencedevices>
	<rm>
		<failoverdomains>
			<failoverdomain name="mysql_pri" nofailback="0" ordered="1" restricted="0">
				<failoverdomainnode name="server01" priority="1"/>
				<failoverdomainnode name="server02" priority="2"/>
			</failoverdomain>
		</failoverdomains>
		<resources/>
		<service autostart="1" name="mysql" recovery="relocate">
			<drbd name="drbd-mysql" resource="mysql">
				<fs device="/dev/drbd0" fstype="ext4" mountpoint="/var/lib/mysql_drbd" name="mysql" options="noatime">
					<ip address="192.168.21.175" monitor_link="1">
						<mysql config_file="/var/lib/mysql_drbd/my.cnf" listen_address="192.168.21.175" name="mysqld"/>
					</ip>
				</fs>
			</drbd>
		</service>
	</rm>
</cluster>

Now copy the cluster.conf to server2.

scp /etc/cluster/cluster.conf server2:/etc/cluster/cluster.conf

Now start all services.

service cman start &amp;&amp; service rgmanager start &amp;&amp; service modclusterd start &amp;&amp; service ricci start

Now increment the version number in your config file and push it out. The command will ask you to authenticate the ricci service for both nodes. Once completed, your nodes should be aware of each other

[root@server1 cluster]# cman_tool version -r
You have not authenticated to the ricci daemon on server2
Password: 
You have not authenticated to the ricci daemon on server1
Password: 
 
[root@server1 cluster]# clustat 
Cluster Status for mysql-cluster @ Wed Jul 24 16:55:16 2013
Member Status: Quorate
 
 Member Name                                                  ID   Status
 ------ ----                                                  ---- ------
 server1                                                  1 Online, Local, rgmanager
 server2                                                  2 Online, rgmanager
 
 Service Name                                        Owner (Last)                                        State         
 ------- ----                                        ----- ------                                        -----         
 service:mysql                                       server1                                     started

Now lets secure our mysql installation. As you can see from the output of clustat, the service is running on server1. So from that server, lets execute:

[root@server1 mysql_drbd]# mysql_secure_installation 
 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
 
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
 
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
 
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
 
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
 
Remove anonymous users? [Y/n] y
 ... Success!
 
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? [Y/n] y
 ... Success!
 
By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
 
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
 
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
 
Reload privilege tables now? [Y/n] y
 ... Success!
 
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
 
Thanks for using MySQL!
Cleaning up...

You can now connect to your database! You will need to create user’s on it with permissions to connect remotely and then upload some data.

One thought on “HA MySQL Cluster with DRBD and Redhat Cluster Add-on

  1. This is excellent article. Sir i have some requirements i need to configure the web and PostgreSQL as group service. So we need to create the volume group and lvm.. then how we can set up the with redhat cluster ?

Leave a Reply

Your email address will not be published. Required fields are marked *