Heartbeat and DRBD can be used effectively to maintain high availability for MySQL databases on Ubuntu 16.04. Heartbeat is a network-oriented tool for maintaining high availability and managing failover. With heartbeat, you can make sure that a shared IP address, is active on one and only one server at a time in your cluster. DRBD (Distributed Replicated Block Device) synchronizes data between two nodes by writing the data to the primary server first and then writing to the secondary server. When used to provide high availability for MySQL databases, heartbeat and DRBD allow for a seamless transition from the primary to the secondary server when the primary server fails. This guide will take you through the steps necessary to create and initiate such a setup.<\/p>\n
In order to follow this guide you will need to have the following in place: Throughout the tutorial, ALL commands must be done on both servers unless otherwise specified.<\/p>\n IP Assignment<\/strong><\/p>\n You will need to assign public and private IP addresses to the servers, a floating IP address through which your MySQL databases will be accessed, and IP addresses for DRBD traffic. In this guide, we assume that the IP range for your public network is 173.209.49.66\/29 and the IP range for your private network (LAN) is 10.119.0.0\/24. We also assume you have a third network card on both servers with a cross-connect between servers for DRBD traffic. The LAN subnet for this cross-connect presumably covers the IP range 192.168.5.0\/24.<\/p>\n The networking required for such a setup may be configured in a number of ways. We chose to use the easiest to understand. In the setup described below, the first LAN will be used for floating IP’s and heartbeat while the third network will be reserved for DRBD.<\/p>\n We have assigned the following IP addresses to the components we will use in this tutorial. <\/p>\n Server eth0 (wan) eth1 (lan) eth1 (drbd) Always makes sure your system is up to date before you install any software.<\/p>\n Disable Ubuntu’s firewall (if it is installed) before you assign the above IP addresses. <\/p>\n Configure the hostnames for your servers. To do so run the following commands on <\/p>\n On DB1:<\/p>\n On DB2: Add the hostnames you have created as entries in the host file:<\/p>\n Next, add the following entries on both servers. This will bind hostnames to the DRBD IP’s in the 192.168.5.0\/24 range:<\/p>\n Your network is now set up.<\/p>\n Install and Configure DRBD and Heartbeat<\/strong><\/p>\n With your network configuration in place, install DRBD and heartbeat. Configure your nodes so that both DRDB and heartbeat start when the servers boot:<\/p>\n You will need to set up a DRDB device for each server. Check that your second unpartitioned drive is available to use as your DRBD device:<\/p>\n Below, we can see that we have a 16GB \/dev\/sdb drive in our setup. We will use this drive for DRBD.<\/p>\n The second drive can easily be partitioned using the simple formula below. This formula will create a single partition that occupies the whole disk.<\/p>\n With the DRBD devices in place, it is time to configure DRBD on both nodes. First, create the DRBD configuration file on each node:<\/p>\n Add the following into both DRBD configuration files:<\/p>\n We will now edit the various files used to configure our servers for high availability. Remember to edit the files on both servers in exactly the same way except where we indicate there need to be differences.<\/p>\n The first file we will edit is the configuration file \/etc\/ha.d\/ha.cf on both nodes. You may open the file like so:<\/p>\n Enter the following parameters in the file. The bcast parameter line is of critical importance. In our case, we have 3 network interfaces. We need to put eth2 on this line. If you are using an existing network setup that is designed differently than our simple example you may need to enter a different value here:<\/p>\n The next file to edit is the resources file \/etc\/ha.d\/haresources<\/p>\n We will enter only one line in this file. Inspect this entry carefully. It should include the hostname of your main active node (db1), the floating IP (10.119.0.10), the device (\/dev\/drbd0) and its mount point (\/var\/lib\/mysql):<\/p>\n To secure your high availability setup you will need to define and store identical authorization keys on both nodes. To do so, open and edit \/etc\/ha.d\/authkeys<\/p>\n This file should only contain the two lines below. Use the same password on both nodes. The password is the text immediately after the “sha1” statement.<\/p>\n Using the partitions created above, create the DRBD disks. Start by entering the following command on DB1:<\/p>\n If you get an error message along the following lines, “The file \/dev\/drbd0 does not exist and no size was specified,\u201d check that your hostnames have been set properly.<\/p>\n Once the DRBD disk is created on DB1, you may create the DRBD disk on DB2:<\/p>\n With both disks in place, you may now verify that the DRBD disk is connected and is properly syncing<\/p>\n The above command should yield the below output. In this output, you should see Primary\/Secondary. This output indicates that the DB1 node is the master while the other node is the slave. It also shows that everything is syncing as expected.<\/p>\n With properly configured synchronization in place between the primary and secondary nodes, it is time to enable the failover portion of our setup. To do so, we will simply start heartbeat on both nodes<\/p>\n The DRBD partition should be mounted on DB1 only. Verify that this is so:<\/p>\n Once you verify that the floating IP is only bound to DB1 with the following command, you are ready to install your MySQL-type database service.<\/p>\n Install MariaDB Database Service<\/strong><\/p>\n Now is the time to install our database service on both servers. There are several variants of MySQL that were created as forks of the original MySQL source code. In the following, we have opted to install MariaDB because of its performance and good track record.<\/p>\n Percona DB and MySQL are two other options you might choose.<\/p>\n The database service on a given node should only start if that node is designated as the primary node at the time. To assure you don’t end up with MySQL running on both nodes simultaneously, disable auto-start for the MariaDB service on both nodes<\/p>\n By design, DB1 is primary when our setup is initiated. No database service should be running on DB2. So long as DB1 is primary, databases on DB2 should be created and populated through synchronization with DB1. Therefore, on DB2, you need to stop the MariaDB service and empty \/var\/lib\/mysql. Perform this command ON DB2 ONLY:<\/p>\n Before you proceed further, configure the root password on DB1. To do so, simply run the wizard. Set a new root password and allow all other options to retain their default values for now:<\/p>\n Copy the MySQL Maintenance configuration file from DB1 to DB2<\/p>\n Now we will create a root user for remote management of and access to the databases on the highly available MySQL instance. We will make use of wildcards to do so.<\/p>\n Our cluster architecture is set up so that all other servers on our LAN can reach the database at the floating IP 10.119.0.10. If you wish to enable users outside your LAN to access your highly available database you may bind a public IP in \/etc\/ha.d\/haresources for database access as well, following the pattern set above in editing that file.<\/p>\n In our case, we have set up our high availability database servers to be accessible from other servers on the LAN that share the IP range 10.119.0.0\/24.<\/p>\n Enter the following commands to create the root user. Replace \u201cmy-password\u201d with the MySQL root password you wish to assign to the remote access user:<\/p>\n Set the bind address for MySQL on both servers<\/p>\n Initiate Heartbeat for MySQL Service<\/strong><\/p>\n Add support for the MySQL service in our heartbeat instances on both servers. First, open the configuration file:<\/p>\n Then, simply add mysql at the end of the line and save the file<\/p>\n Once heartbeat is configured we need to restart it on both servers. heartbeat must be started on the primary server (DB1) first. Once heartbeat has started on DB1 allow at least 20 seconds before you restart heartbeat on DB2.<\/p>\n The command sequence is as follows. ON DB1 enter:<\/p>\n Wait 20 seconds or more and then enter the following on DB2:<\/p>\n The delay between initiating the heartbeat stack on DB1 and DB2 will prevent heartbeat from inadvertently initiating failover to DB2 upon startup.<\/p>\n Testing redundancy of our setup<\/strong><\/p>\n Our goal throughout this tutorial has been to tie together our servers such that MySQL service will not be interrupted if the active server fails. Now that our setup is complete, we will 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. We will also verify that DRBD is properly syncing the data between the two servers.<\/p>\n Tests to Perform on DB1<\/strong><\/p>\n First, we will verify that DB1 is the primary drbd node<\/p>\n Next, we will verify that the DRBD disk is mounted<\/p>\n The floating IP must be bound correctly for the setup to function properly<\/p>\n Check to make sure that MariaDB is running<\/p>\n Use the remote access root user to test the MySQL connection directly on the floating (failover) IP and create a test database.<\/p>\n Restart heartbeat on DB1.<\/p>\n Heartbeat will interpret this restart as a failure of DB1 and should trigger failover to make DB2 the primary server. Ensure that DRBD is now treating DB1 as the secondary server:<\/p>\n Having triggered the failover, we will now test that our setup is fully functional with DB2 acting as the primary server.<\/p>\n Tests to perform on DB2<\/strong><\/p>\n Verify that DB2 is now the primary drbd node<\/p>\n Verify that the DRBD disk is mounted on DB2<\/p>\n Verify the floating IP is now bound to DB2 correctly<\/p>\n Check to make sure that MariaDB is running on DB2<\/p>\n Use the remote access user to connect to the MySQL instance at the floating (failover) IP. If your setup is working properly, the following commands should enable you to view the test database we created earlier while DB1 was the primary server.<\/p>\n Restart Heartbeat.<\/p>\n As it did before, this restart should trigger failover. To check that it has done so, ensure that DRBD is now secondary on DB2<\/p>\n Provided your tests yielded the expected results, your high availability MySQL setup should now be fully operational.<\/p>\n Congratulations, the high-availability MySQL setup you have created on your Ubuntu 16.04 servers will make your databases more reliable for mission critical functions. This frees you to create content while your users get nearly continuous data access. <\/p>\n","protected":false},"excerpt":{"rendered":" How to Achieve High Availability with Heartbeat & DRBD on Ubuntu 16 Heartbeat and DRBD can be used effectively to maintain high availability for MySQL databases on Ubuntu 16.04. Heartbeat is a network-oriented tool for maintaining high availability and managing failover. With heartbeat, you can make sure that a shared IP address, is active on<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75],"tags":[],"class_list":["post-3163","post","type-post","status-publish","format-standard","hentry","category-ha-clustering","operating_system-ubuntu-16-04"],"yoast_head":"\n
\n\u2022\tTwo nodes (Cloud Server<\/a> or Dedicated Server<\/a>) on a single LAN with Ubuntu 16.04 installed. We will call these servers DB1 and DB2 below
\n\u2022\tThree network cards on each server to allow for the establishment of all required IP addresses
\n\u2022\tA second unpartitioned drive on each server to serve as the DRDB device
\n\u2022\tRoot access to the nodes<\/p>\nTutorial<\/h2>\n
\nDB1 173.209.49.66 10.119.0.1 192.168.5.1
\nDB2 173.209.49.67 10.119.0.2 192.168.5.2
\nFloating None 10.119.0.10 None<\/p>\napt-get update
\napt-get -y upgrade<\/code><\/p>\nufw disable<\/code><\/p>\necho \"db1.mydomain.com\" > \/etc\/hostname
\nhostname db1.mydomain.com<\/code><\/p>\n
\necho \"db2.mydomain.com\" > \/etc\/hostname
\nhostname db2.mydomain.com<\/code><\/p>\nnano \/etc\/hosts<\/code><\/p>\n192.168.5.1 db1.mydomain.com
\n192.168.5.2 db2.mydomain.com<\/code><\/p>\napt-get -y install drbd8-utils heartbeat
\nsystemctl enable drbd
\nsystemctl enable heartbeat<\/code><\/p>\nfdisk -l<\/code><\/p>\nDisk \/dev\/sda: 16 GiB, 17179869184 bytes, 33554432 sectors
\nUnits: sectors of 1 * 512 = 512 bytes
\nSector size (logical\/physical): 512 bytes \/ 512 bytes
\nI\/O size (minimum\/optimal): 512 bytes \/ 512 bytes
\nDisklabel type: dos
\nDisk identifier: 0xcda6f1d3
\nDevice Boot Start End Sectors Size Id Type
\n\/dev\/sda1 * 2048 976895 974848 476M 83 Linux
\n\/dev\/sda2 976896 4976639 3999744 1.9G 82 Linux swap \/ Solaris
\n\/dev\/sda3 4976640 33552383 28575744 13.6G 83 Linux
\nDisk \/dev\/sdb: 16 GiB, 17179869184 bytes, 33554432 sectors
\nUnits: sectors of 1 * 512 = 512 bytes
\nSector size (logical\/physical): 512 bytes \/ 512 bytes
\nI\/O size (minimum\/optimal): 512 bytes \/ 512 bytes<\/code><\/p>\necho -e 'n\\np\\n1\\n\\n\\nw' | fdisk \/dev\/sdb<\/code><\/p>\necho \"\" > \/etc\/drbd.d\/global_common.conf
\nnano \/etc\/drbd.d\/r0.res<\/code><\/p>\nglobal {
\n usage-count no;
\n}
\nresource r0 {
\n protocol C;
\n startup {
\n degr-wfc-timeout 60;
\n }
\n disk {
\n on-io-error detach;
\n }
\n syncer {
\n rate 100M;
\n }
\n net {
\n cram-hmac-alg sha1;
\n shared-secret \"wXE8MqVa\";
\n }
\n on db1.mydomain.com {
\n device \/dev\/drbd0;
\n disk \/dev\/sdb1;
\n address 192.168.5.1:7789;
\n meta-disk internal;
\n }
\n on db2.mydomain.com {
\n device \/dev\/drbd0;
\n disk \/dev\/sdb1;
\n address 192.168.5.2:7789;
\n meta-disk internal;
\n }
\n}<\/code><\/p>\nnano \/etc\/ha.d\/ha.cf<\/code><\/p>\n# Check Interval
\nkeepalive 1
\n# Time before server declared dead
\ndeadtime 10
\n# Secondary wait delay at boot
\ninitdead 60
\n# Auto-failback
\nauto_failback off
\n# Heartbeat Interface
\nbcast eth2
\n# Nodes to monitor
\nnode db1.mydomain.com
\nnode db2.mydomain.com<\/code><\/p>\nnano \/etc\/ha.d\/haresources<\/code><\/p>\ndb1.mydomain.com 10.119.0.10\/24 drbddisk::r0 Filesystem::\/dev\/drbd0::\/var\/lib\/mysql::ext4::noatime<\/code><\/p>\nnano \/etc\/ha.d\/authkeys<\/code><\/p>\nauth1
\n1 sha1 e86b38f5075de0318548edad9566436423ada422<\/code><\/p>\ndrbdadm create-md r0
\nsystemctl restart drbd
\ndrbdadm outdate r0
\ndrbdadm -- --overwrite-data-of-peer primary all
\ndrbdadm primary r0
\nmkfs.ext4 \/dev\/drbd0
\nchmod 600 \/etc\/ha.d\/authkeys
\nmkdir \/var\/lib\/mysql<\/code><\/p>\ndrbdadm create-md r0
\nsystemctl restart drbd
\nchmod 600 \/etc\/ha.d\/authkeys
\nmkdir \/var\/lib\/mysql<\/code><\/p>\ncat \/proc\/drbd<\/code><\/p>\nroot@db1:~# cat \/proc\/drbd
\nversion: 8.4.5 (api:1\/proto:86-101)
\nsrcversion: D496E56BBEBA8B1339BB34A
\n 0: cs:SyncSource ro:Primary\/Secondary ds:UpToDate\/Inconsistent C r-----
\n ns:550488 nr:0 dw:135424 dr:416200 al:35 bm:0 lo:0 pe:11 ua:0 ap:0 ep:1 wo:f oos:16233752
\n [>....................] sync'ed: 3.3% (15852\/16380)M
\n finish: 0:16:45 speed: 16,136 (16,932) K\/sec<\/code><\/p>\nsystemctl start heartbeat<\/code><\/p>\nroot@db1:\/etc\/ha.d# mount | grep drbd
\n\/dev\/drbd0 on \/var\/lib\/mysql type ext4 (rw,noatime,data=ordered)<\/code><\/p>\nroot@db1:\/etc\/ha.d# ip addr show | grep 10.119.0.10
\n inet 10.119.0.10\/24 brd 10.119.0.255 scope global secondary eth1:0<\/code><\/p>\napt-get install mariadb-server<\/code><\/p>\nsystemctl disable mysql<\/code><\/p>\nsystemctl stop mysql
\nrm -rfv \/var\/lib\/mysql\/*<\/code><\/p>\nroot@db1:\/var\/lib# mysql_secure_installation
\nNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
\n SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
\nIn order to log into MariaDB to secure it, we'll need the current
\npassword for the root user. If you've just installed MariaDB, and
\nyou haven't set the root password yet, the password will be blank,
\nso you should just press enter here.
\nEnter current password for root (enter for none):
\nOK, successfully used password, moving on...
\nSetting the root password ensures that nobody can log into the MariaDB
\nroot user without the proper authorisation.
\nSet root password? [Y\/n] y
\nNew password:
\nRe-enter new password:
\nPassword updated successfully!
\nReloading privilege tables..
\n ... Success!
\nBy default, a MariaDB installation has an anonymous user, allowing anyone
\nto log into MariaDB without having to have a user account created for
\nthem. This is intended only for testing, and to make the installation
\ngo a bit smoother. You should remove them before moving into a
\nproduction environment.
\nRemove anonymous users? [Y\/n] Y
\n ... Success!
\nNormally, root should only be allowed to connect from 'localhost'. This
\nensures that someone cannot guess at the root password from the network.
\nDisallow root login remotely? [Y\/n] Y
\n ... Success!
\nBy default, MariaDB comes with a database named 'test' that anyone can
\naccess. This is also intended only for testing, and should be removed
\nbefore moving into a production environment.
\nRemove test database and access to it? [Y\/n] Y
\n - Dropping test database...
\n ... Success!
\n - Removing privileges on test database...
\n ... Success!
\nReloading the privilege tables will ensure that all changes made so far
\nwill take effect immediately.
\nReload privilege tables now? [Y\/n] Y
\n ... Success!
\nCleaning up...
\nAll done! If you've completed all of the above steps, your MariaDB
\ninstallation should now be secure.
\nThanks for using MariaDB!<\/code><\/p>\nrsync -av \/etc\/mysql\/debian.cnf root@192.168.5.2:\/etc\/mysql\/debian.cnf<\/code><\/p>\nmysql -u root -p<\/code><\/p>\nMariaDB [(none)]> CREATE USER 'root'@'10.119.0.%' IDENTIFIED BY 'my-password';
\nMariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.119.0.%' WITH GRANT OPTION;
\nMariaDB [(none)]> FLUSH PRIVILEGES;
\nMariaDB [(none)]> QUIT;<\/code><\/p>\nsed -i 's\/127.0.0.1\/0.0.0.0\/g' \/etc\/mysql\/mariadb.conf.d\/*.cnf<\/code><\/p>\nnano \/etc\/ha.d\/haresources<\/code><\/p>\ndb1.mydomain.com 10.119.0.10\/24 drbddisk::r0 Filesystem::\/dev\/drbd0::\/var\/lib\/mysql::ext4::noatime mysql<\/code><\/p>\nsystemctl restart heartbeat<\/code><\/p>\nsystemctl restart heartbeat<\/code><\/p>\nroot@db1:~# cat \/proc\/drbd<\/code>
\nversion: 8.4.5 (api:1\/proto:86-101)
\nsrcversion: D496E56BBEBA8B1339BB34A
\n 0: cs:Connected ro:Primary\/Secondary ds:UpToDate\/UpToDate C r-----
\n 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<\/code><\/p>\nroot@db1:~# mount | grep drbd<\/code>
\n\/dev\/drbd0 on \/var\/lib\/mysql type ext4 (rw,noatime,data=ordered)<\/code><\/p>\nroot@db1:~# ip addr show | grep 10.119.0.10<\/code>
\n inet 10.119.0.10\/24 brd 10.119.0.255 scope global secondary eth1:0<\/code><\/p>\nroot@db1:~# ps -ef | grep mysqld<\/code>
\nroot 7472 1 0 05:52 ? 00:00:00 \/bin\/bash \/usr\/bin\/mysqld_safe
\nmysql 7617 7472 0 05:52 ? 00:00:00 \/usr\/sbin\/mysqld --basedir=\/usr --datadir=\/var\/lib\/mysql --plugin-dir=\/usr\/lib\/mysql\/plugin --user=mysql --skip-log-error --pid-file=\/var\/run\/mysqld\/mysqld.pid --socket=\/var\/run\/mysqld\/mysqld.sock --port=3306
\nroot 7618 7472 0 05:52 ? 00:00:00 logger -t mysqld -p daemon error<\/code><\/p>\nmysql -h 10.119.0.10 -u root -p<\/code>
\nMariaDB [(none)]> create database failtest;
\nMariaDB [(none)]> quit<\/code><\/p>\nsystemctl restart heartbeat<\/code><\/p>\nroot@db1:~# cat \/proc\/drbd<\/code>
\nversion: 8.4.5 (api:1\/proto:86-101)
\nsrcversion: D496E56BBEBA8B1339BB34A
\n 0: cs:Connected ro:Secondary\/Primary ds:UpToDate\/UpToDate C r-----
\n 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<\/code><\/p>\nroot@db2:~# cat \/proc\/drbd<\/code>
\nversion: 8.4.5 (api:1\/proto:86-101)
\nsrcversion: D496E56BBEBA8B1339BB34A
\n 0: cs:Connected ro:Primary\/Secondary ds:UpToDate\/UpToDate C r-----
\n 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<\/code><\/p>\nroot@db2:~# mount | grep drbd<\/code>
\n\/dev\/drbd0 on \/var\/lib\/mysql type ext4 (rw,noatime,data=ordered)<\/code><\/p>\nroot@db2:~# ip addr show | grep 10.119.0.10<\/code>
\n inet 10.119.0.10\/24 brd 10.119.0.255 scope global secondary eth1:0<\/code><\/p>\nroot@db2:~# ps -ef | grep mysqld<\/code>
\nroot 7568 1 0 06:13 ? 00:00:00 \/bin\/bash \/usr\/bin\/mysqld_safe
\nmysql 7713 7568 0 06:13 ? 00:00:00 \/usr\/sbin\/mysqld --basedir=\/usr --datadir=\/var\/lib\/mysql --plugin-dir=\/usr\/lib\/mysql\/plugin --user=mysql --skip-log-error --pid-file=\/var\/run\/mysqld\/mysqld.pid --socket=\/var\/run\/mysqld\/mysqld.sock --port=3306
\nroot 7714 7568 0 06:13 ? 00:00:00 logger -t mysqld -p daemon error<\/code><\/p>\nmysql -u 10.119.0.10 -u root -p<\/code>
\nMariaDB [(none)]> show databases;
\n+--------------------+
\n| Database |
\n+--------------------+
\n| failtest |
\n| information_schema |
\n| lost+found |
\n| mysql |
\n| performance_schema |
\n+--------------------+
\n5 rows in set (0.04 sec)
\nMariaDB [(none)]> exit<\/code><\/p>\nsystemctl restart heartbeat<\/code><\/p>\nroot@db2:~# cat \/proc\/drbd<\/code>
\nversion: 8.4.5 (api:1\/proto:86-101)
\nsrcversion: D496E56BBEBA8B1339BB34A
\n 0: cs:Connected ro:Secondary\/Primary ds:UpToDate\/UpToDate C r-----
\n ns:508 nr:20881012 dw:20881520 dr:11475 al:7 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0<\/code><\/p>\nConclusion<\/h2>\n