How to Setup MariaDB High Availability with Heartbeat and DRBD on Ubuntu 16.04 LTS
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 identical authorization keys on both nodes. You can do this by /etc/ha.d/authkeys file on each Node:
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:0
Install 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 [email protected]:/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.%' IDENTIFIED BY 'password';
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)