Setup MariaDB High Availability with Heartbeat-Arnold

ab520025520025

　　Heartbeat and DRBD both are used for a cluster solution for any application using two servers. Both servers are work in active and passive mode, one server will work at the same time and another server as a backup server. DRBD (Distributed Replicated Block Device) is a kernel-level service that synchronizes data between two servers in real-time. Heartbeat is an open source program that allows a primary and a backup Linux server to determine if the other is "alive" and if the primary isn't, failover resources to the backup. It will also manage the IP high availability and other services in your servers.
　　In this tutorial, we will learn how to achieve high availability of MariaDB using Heartbeat and DRBD on Ubuntu 16.04 server.
Requirements

• 　　Two nodes with Ubuntu 16.04 server installed.
  
• 　　Two network cards installed on each node.
  
• 　　Additional unpartitioned hard drive installed on each node.
  
• 　　Non root user with sudo privileges setup on each node.
  

Getting Started
　　Before starting, you will need to setup IP address on each node. Use the following IP address on each node:
　　Node1 :
　　172.16.0.1 on eth0 and 192.168.0.101 on eth1
　　Node2 :
　　172.16.0.2 on eth0 and 192.168.0.102 on eth1
　　IP 192.168.0.103 will be the high availability IP.
　　Next, you will also need to setup hostname and hostname resolution on each node. So each node can communicate with each other. On the first Node, open the /etc/hosts file and /etc/hostname file:
　　sudo nano /etc/hosts
　　Add the following lines at the end of the file:
172.16.0.1  Node1　　
172.16.0.2  Node2
　　sudo nano /etc/hostname
　　Change the file as shown below:
Node1　　Save and close the file when you are finished.
　　On the second node, open the /etc/hosts file and /etc/hostname file:
　　sudo nano /etc/hosts
　　Add the following lines at the end of the file:
172.16.0.1  Node1　　
172.16.0.2  Node2
　　sudo nano /etc/hostname
　　Change the file as shown below:
Node2　　Save and close the file when you are finished.
　　Next, update each node with the latest version with the following command:
　　sudo apt-get update -y
　　sudo apt-get upgrade -y
　　Once your system is updated, restart the system to apply these changes.
Install DRBD and Heartbeat
　　Next, you will need to install DRBD and Heartbeat on both nodes. By default, both are available in Ubuntu 16.04 default repository. You can install them by just running the following command on both Nodes:
　　sudo apt-get install drbd8-utils heartbeat -y
　　Next, start DRBD and Heartbeat service and enable them to start on boot time:
　　sudo systemctl start drbd
　　sudo systemctl start heartbeat
　　systemctl enable drbd
　　systemctl enable heartbeat
Configure DRBD and Heartbeat
　　Next, you will need to setup DRBD device on each Node. Create a single partition on second unpartitioned drive /dev/sdb on each Node.
　　You can do this by just running the following command on each Node:
　　sudo echo -e 'n\np\n1\n\n\nw' | fdisk /dev/sdb
　　Next, you will need to configure DRBD on both nodes. You can do this by creating /etc/drbd.d/r0.res file on each Node.
　　sudo nano /etc/drbd.d/r0.res
　　Add the following lines:
global {　　
usage-count no;
　　
}
　　
resource r0 {
　　
protocol C;
　　
startup {
　　
degr-wfc-timeout 60;
　　
}
　　
disk {
　　
}
　　
syncer {
　　
rate 100M;
　　
}
　　
net {
　　
cram-hmac-alg sha1;
　　
shared-secret "aBcDeF";
　　
}
　　
on Node1 {
　　
device /dev/drbd0;
　　
disk /dev/sdb1;
　　
address 172.16.0.1:7789;
　　
meta-disk internal;
　　
}
　　
on Node2 {
　　
device /dev/drbd0;
　　
disk /dev/sdb1;
　　
address 172.16.0.2:7789;
　　
meta-disk internal;
　　
}
　　
}
　　Save and close the file when you are finished, then open another configuration file on each Node:
　　sudo nano /etc/ha.d/ha.cf
　　Add the following lines:
# Check Interval　　
keepalive 1
　　
# Time before server declared dead
　　
deadtime 10
　　
# Secondary wait delay at boot
　　
initdead 60
　　
# Auto-failback
　　
auto_failback off
　　
# Heartbeat Interface
　　
bcast eth1
　　
# Nodes to monitor
　　
node Node1
　　
node Node2
　　Save and close the file.
　　Next, open the resource file /etc/ha.d/haresources on each Node:
　　sudo nano /etc/ha.d/haresources
　　Add the following lines:
Node1 192.168.0.103/24 drbddisk::r0 Filesystem::/dev/drbd0::/var/lib/mysql::ext4::noatime　　Here, Node1 is the hostname of your main active node, 192.168.0.103 is the floating point IP address, /var/lib/mysql is a mount point and /dev/drbd0 is DRBD device.

　　Next, you will need to need to define and store>　　sudo nano /etc/ha.d/authkeys
　　Add the following lines:
auth1　　
1 sha1 your-secure-password
　　Here, your-secure-password is your secure password. Use the same password on both nodes.
　　Next, create and start DRBD by running the following command on Node1:
　　sudo drbdadm create-md r0
　　sudo systemctl restart drbd
　　sudo drbdadm outdate r0
　　sudo drbdadm -- --overwrite-data-of-peer primary all
　　sudo drbdadm primary r0
　　sudo mkfs.ext4 /dev/drbd0
　　sudo chmod 600 /etc/ha.d/authkeys
　　sudo mkdir /var/lib/mysql
　　Once the DRBD disk is created on Node1, create the DRBD disk on Node2 with the following command:
　　sudo drbdadm create-md r0
　　sudo systemctl restart drbd
　　sudo chmod 600 /etc/ha.d/authkeys
　　sudo mkdir /var/lib/mysql
　　Now, you can verify the DRBD disk is connected and is properly syncing by running the following command:
　　sudo cat /proc/drbd
　　If everything is fine, you should see the following output:
version: 8.4.5 (api:1/proto:86-101)　　
srcversion: F446E16BFEBS8B115AJB14H
　　
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
　　
ns:210413 nr:0 dw:126413 dr:815311 al:35 bm:0 lo:0 pe:11 ua:0 ap:0 ep:1 wo:f oos:16233752
　　
[>....................] sync'ed: 3.3% (14752/14350)M
　　
finish: 0:12:23 speed: 12,156 (16,932) K/sec
　　Next, start heartbeat on both Nodes to enable the failover portion of your setup.
　　sudo systemctl start heartbeat
　　Next, verify the mounted DRBD partition with the following command on Node1:
　　sudo mount | grep drbd
　　You should see the following output:
/dev/drbd0 on /var/lib/mysql type ext4 (rw,noatime,data=ordered)　　Next, verify the floating IP is only bound to Node1 with the following command:
　　sudo ip addr show | grep 192.168.0.103
　　You should see the following output:
inet 192.168.0.103/24 brd 192.168.0.255 scope global secondary eth1:0Install and Configure MariaDB
　　Once everything is configured properly on both Nodes, it's time to install MariaDB server on both Nodes.
　　Run the following command on both Nodes to install MariaDB server:
　　sudo apt-get install mariadb-server -y
　　Next, you will need to disable MariaDB service on both Nodes:
　　sudo systemctl disable mysql
　　Here, we will use Node1 as primary and databases on Node2 should be created and populated through synchronization with Node1. So you will need to stop MariaDB service and remove content inside /var/lib/mysql on Node2. You can do this with the following command:
　　sudo systemctl stop mysql
　　sudo rm -rf /var/lib/mysql/*
　　Next, you will need to copy the MySQL Maintenance configuration file from Node1 to Node2. You can do this by running the following command:
　　sudo scp /etc/mysql/debian.cnf root@172.16.0.2:/etc/mysql/debian.cnf
　　Next, you will need to create a root user for remote management of and access to the databases on the highly available MySQL instance.
　　You can do this by running the following command on Node1:
　　mysql -u root -p
　　Enter your root password, then create a root user with the following command:

　　MariaDB [(none)]> CREATE USER 'root'@'192.168.0.%'>　　MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0..%' WITH GRANT OPTION;
　　MariaDB [(none)]> FLUSH PRIVILEGES;
　　MariaDB [(none)]> QUIT;
　　Next, Set the bind address for MySQL on both Nodes with the following command:
　　sudo sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mariadb.conf.d/*.cnf
Initiate Heartbeat for MariaDB Service
　　Next, you will need to add the MariaDB service in your heartbeat instances on both Nodes. You can do this by editing /etc/ha.d/haresources file:
　　sudo nano /etc/ha.d/haresources
　　Modify the following lines:
Node1 192.168.0.103/24 drbddisk::r0 Filesystem::/dev/drbd0::/var/lib/mysql::ext4::noatime mysql　　Save and close the file, when you are finished.
　　Once the heartbeat is configured, you will need to restart it on both Nodes.
　　First, restart heartbea on Node1:
　　sudo systemctl restart heartbeat
　　Next, wait for 50 seconds, then restart heartbeat service on Node2:
　　sudo systemctl restart heartbeat
Test Heartbeat and DRBD
　　Now, everything is configured properly, it's time to perform a series of tests to verify that heartbeat will actually trigger a transfer from the active server to the passive server when the active server fails in some way.
　　First, verify that Node1 is the primary drbd node with the following command on Node1:
　　sudo cat /proc/drbd
　　You should see the following output:
version: 8.4.5 (api:1/proto:86-101)　　
srcversion: F446E16BFEBS8B115AJB14H
　　
O cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
　　
ns:22764644 nr:256 dw:529232 dr:22248299 al:111 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
　　Next, we will verify that the DRBD disk is mounted with the following command:
　　sudo mount | grep drbd
/dev/drbd0 on /var/lib/mysql type ext4 (rw,noatime,data=ordered)　　Next, verify the MariaDB service with the following command:
　　sudo systemctl status mysql
　　Next, access MariaDB server from the remote machine using floating IP and create a test database:
　　mysql -h 192.168.0.103 -u root -p
　　MariaDB [(none)]> create database test;
　　MariaDB [(none)]> quit
　　Next, restart heartbeat on Node1:
　　sudo systemctl restart heartbeat
　　Now, heartbeat will interpret this restart as a failure of MariaDB on Node1 and should trigger failover to make Node2 the primary server.
　　You can check that DRBD is now treating Node1 as the secondary server with the following command on Node1:
　　sudo cat /proc/drbd
　　You should see the following output:
version: 8.4.5 (api:1/proto:86-101)　　
srcversion: F446E16BFEBS8B115AJB14H
　　
0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----
　　
ns:22764856 nr:388 dw:529576 dr:22248303 al:112 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
　　Now, verify that Node2 is the primary drbd node by running the following command on Node2:
　　sudo cat /proc/drbd
　　You should see the following output:
version: 8.4.5 (api:1/proto:86-101)　　
srcversion: F446E16BFEBS8B115AJB14H
　　
0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
　　
ns:412 nr:20880892 dw:20881304 dr:11463 al:7 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
　　Next, Check to make sure that MariaDB is running on Node2:
　　sudo systemctl status mysql
　　Now, connect to MariaDB server using floating IP on Node2 from remote user.
　　mysql -h 192.168.0.103 -u root -p
　　Next, view the test database we created earlier while Node1 was the primary server.
　　MariaDB [(none)]> show databases;
　　You should see the following output:
+--------------------+　　
| Database |
　　
+--------------------+
　　
| test |
　　
| information_schema |
　　
| lost+found |
　　
| mysql |
　　
| performance_schema |
　　
+--------------------+
　　
5 rows in set (0.04 sec)