Knowledge base
How to build a highly available MySQL database cluster using DRBD, corosync and pacemaker?
Posted by Pomoc Oktawave on 28.10.2014 22:25

In order to ensure high-availability of database we will use a highly available cluster, consisting of two nodes. Data replication between nodes will be held at the block level using DRBD software. To increase the reliability of the proposed solution, we can create nodes in different sub-regions to work in physically separate pools of resources. To learn more about the subregions, click here.

 

1. Assumptions

We will use template with Ubuntu Server, for each of the two instances created OVS is connected, of the same size, which will store the database files. We will use a MySQL database available in the default repository.

Both instances are connected to OPN, wherein using corosync and pacemaker packets communication is done between the nodes in the cluster. In this example we will use the subnet 10.0.0.0/24 with statically configured addresses.

db01: 10.0.0.1 
db02: 10.0.0.2 
db: 10.0.0.10 


10.0.0.10 is the address, after which we will access the database.

 

2. Configure the environment

Necessary condition for the proper operation of the software cluster is to have the correct entries in the /etc/hosts file on all nodes.

10.0.0.1 db01 
10.0.0.2 db02 


Also change the hostname, for example.

 

hostname db01
echo 'db01' > /etc/hostname

 

On each database instance we are updating information about package repositories.

 

apt-get update
apt-get install drbd8-utils pacemaker fence-agents mysql-server

 

To ensure free communication within the OPN, we denote the corresponding interface as trusted for /etc/firewall.conf.

 

TRUSTED_INTERFACES='lo eth1'

 

Lets restart local firewall to confirm the new setting.

 

service firewall restart

 

2.1 DRBD

In our solution two nodes will exist operating in active-passive mode. Each of them must be configured according to the following guidelines, unless otherwise specified. DRBD configuration file is located in the /etc/drbd.conf, however, the default contents of this file points to other files, in which the configuration is saved.

 

include "drbd.d/global_common.conf";
# Ustawienia globalne dla wszystkich udziaƂów
include "drbd.d/*.res";
# Shares settings configured by us


In /etc/drbd.d/global_common.conf lets take care of the use of synchronous replication to ensure data consistency between nodes, by an entry in clause net - it should have the following form.

 
net {
protocol C;
}

Then we need to configure DRBD resource, here are a proposal to the proper configuration.

 

# cat /etc/drbd.d/mysql.res 
resource mysql {
 device		/dev/drbd1; 
 disk		/dev/sdb; 
 meta-disk	internal; 
 syncer {
 rate 100M; 
 }
 net { 
 after-sb-0pri discard-zero-changes; 
 after-sb-1pri discard-secondary; 
 } 
 on db01 { 
 address	10.0.0.1:7789; 
 } 
 on db02 { 
 address	10.0.0.2:7789; 
 } 
}

 

The syncer clause declares the bandwidth available for replication of resources between nodes, the value specified in the example in OPN is sufficient. The net clause define the behavior of the individual nodes in the event of a split-brain occurrence, that is, when nodes lose contact with each other and are not able to inform each other about their condition.

  • When the time of detection of split-Brain part is not in the active state at any of the nodes, and no changes in the data on one of them, the state is rejected. If there is a conflict, the connection between the nodes is broken and they work independently, while the conflict must be resolved manually by the administrator.
  • Once upon detection of a split-Brain participation is in the active state on one of the nodes, the state of passive nodes is discarded.

Next, initialize the newly created resource.

 

drbdadm create-md mysql
drbdadm up mysql

 

Then, on one of the instances we run the following command.

drbdadm -- --overwrite-data-of-peer primary mysql

 

Choosing an instance where we want to run it is important only if you already have some data on the resource you want to replicate on the remaining node. In this case, we create a new resource, so the choice of a node does not matter. We can track the status of replication using the command service drbd status and drbd-overview.

The next step is to create a file system on the newly created resource, for example. Ext4. This step is also performed only on the active node.

 

mkfs.ext4 / dev / drbd1

 

2.2 MySQL

We stop the MySQL server service.

 

 service mysql stop

 

Temporarily mount the DRBD resource on the active node to copy to it the contents of /var/lib/mysql, where it will mounted by the cluster.

 

mkdir /mnt/tmp
mount /dev/drbd1 /mnt/tmp
cp -ar /var/lib/mysql/* /mnt/tmp/
umount /mnt/tmp

 

Then, the contents of the directory on system disks, can be removed from the instance.

rm -rf /var/lib/mysql/*

 

All that remains is to change the configuration of the MySQL server so that it will bind to the address of the cluster. To do this, set the bind-address = 10.0.0.10 in /etc/mysql/my.cnf.

2.3 Cluster

To implement the functionality of the cluster we will use corosync and pacemaker packages. Corosync is responsible for communication between nodes in the cluster, and the pacemaker for management.

In the file /etc/default/corosync set START=yes, while /etc/corosync/corosync.conf file should be set out in interface clause, set bindnetaddr addressed to the node in the OPN and increase expected_votes in quorum clause to 2 (essentially the value should be 50% of the number of nodes in the cluster + 1).

Since DRBD and MySQL services on all nodes will be managed by the cluster, we need to ensure that it does not run at startup using its standard init scripts.

 

update-rc.d -f drbd remove
update-rc.d -f mysql remove
echo "manual" > /etc/init/mysql.override
update-rc.d -f pacemaker remove
update-rc.d pacemaker start 50 1 2 3 4 5 . stop 01 0 6 .

 

After initial setup of corosync we can turn on the cluster services.

 

service corosync start
service pacemaker start

 

We can now proceed to configure the cluster on one node using crm tool.

crm
configure
property stonith-enabled=false
property no-quorum-policy=ignore
primitive DRBD_MYSQL ocf:linbit:drbd params drbd_resource="mysql" op monitor interval="9s" role="Master" op monitor interval="11s" role="Slave"
ms MS_DRBD_MYSQL DRBD_MYSQL meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"
primitive MYSQL_FS ocf:heartbeat:Filesystem params device="/dev/drbd1" directory="/var/lib/mysql" fstype="ext4"
primitive MYSQL_IP ocf:heartbeat:IPaddr2 params ip=10.0.0.10 cidr_netmask=24 op monitor interval=10s
primitive MYSQL_SERVER lsb:mysql
group MYSQL MYSQL_FS MYSQL_IP MYSQL_SERVER
colocation MYSQL_ON_DRBD inf: MYSQL MS_DRBD_MYSQL:Master
order MYSQL_AFTER_DRBD inf: MS_DRBD_MYSQL:promote MYSQL:start
exit


When you're asked about the Submit changes, confirm. This example configuration includes DRBD resource, the file system on it, an IP address through which we connect with the database, and the database server service. Configuration provides the correct order of starting services and the relationships between them. we can check the current configuration of the cluster, by issue of the following command.

 

crm configure show

 

The state of the cluster and managed services for its help we can check with the following commands:

crm_mon
crm status 
(0 vote(s))
This article was helpful
This article was not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.