Deploying a highly available MySQL 5.6 cluster with DRBD on Compute Engine

Stay organized with collections Save and categorize content based on your preferences.

Getting set up

In this section, you set up a service account, create environment variables, and reserve IP addresses.

Set up a service account for the cluster instances

1. Open Cloud Shell:

   OPEN Cloud Shell

2. Create the service account:

   gcloudiamservice-accountscreatemysql-instance\--display-name"mysql-instance"

3. Attach the roles needed for this tutorial to the service account:

   gcloudprojectsadd-iam-policy-binding${DEVSHELL_PROJECT_ID}\--member=serviceAccount:mysql-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com\--role=roles/compute.instanceAdmin.v1gcloudprojectsadd-iam-policy-binding${DEVSHELL_PROJECT_ID}\--member=serviceAccount:mysql-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com\--role=roles/compute.viewergcloudprojectsadd-iam-policy-binding${DEVSHELL_PROJECT_ID}\--member=serviceAccount:mysql-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com\--role=roles/iam.serviceAccountUser

Create Cloud Shell environment variables

1. Create a file with the required environment variables for this tutorial:

   cat<<EOF > ~/.mysqldrbdrc # Cluster instance namesDATABASE1_INSTANCE_NAME=database1 DATABASE2_INSTANCE_NAME=database2 QUORUM_INSTANCE_NAME=qdevice CLIENT_INSTANCE_NAME=mysql-client # Cluster IP addressesDATABASE1_INSTANCE_IP="10.140.0.2"DATABASE2_INSTANCE_IP="10.140.0.3"QUORUM_INSTANCE_IP="10.140.0.4"ILB_IP="10.140.0.6"# Cluster zones and regionDATABASE1_INSTANCE_ZONE="asia-east1-a"DATABASE2_INSTANCE_ZONE="asia-east1-b"QUORUM_INSTANCE_ZONE="asia-east1-c"CLIENT_INSTANCE_ZONE="asia-east1-c"CLUSTER_REGION="asia-east1" EOF 

2. Load the environment variables in the current session and set Cloud Shell to automatically load the variables on future sign-ins:

   source~/.mysqldrbdrc grep-q-F"source ~/.mysqldrbdrc"~/.bashrc||echo"source ~/.mysqldrbdrc" >> ~/.bashrc 

Reserve IP addresses

• In Cloud Shell, reserve an internal IP address for each of the three cluster nodes:

  gcloudcomputeaddressescreate${DATABASE1_INSTANCE_NAME}${DATABASE2_INSTANCE_NAME}${QUORUM_INSTANCE_NAME}\--region=${CLUSTER_REGION}\--addresses"${DATABASE1_INSTANCE_IP},${DATABASE2_INSTANCE_IP},${QUORUM_INSTANCE_IP}"\--subnet=default

Creating the Compute Engine instances

In the following steps, the cluster instances use Debian 9 and the client instances use Ubuntu 16.

1. In Cloud Shell, create a MySQL instance named database1 in zone asia-east1-a:

   gcloudcomputeinstancescreate${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--machine-type=n1-standard-2\--network-tier=PREMIUM\--maintenance-policy=MIGRATE\--image-family=debian-9\--image-project=debian-cloud\--boot-disk-size=50GB\--boot-disk-type=pd-standard\--boot-disk-device-name=${DATABASE1_INSTANCE_NAME}\--create-disk=mode=rw,size=300,type=pd-standard,name=disk-1\--private-network-ip=${DATABASE1_INSTANCE_NAME}\--tags=mysql--service-account=mysql-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com\--scopes="https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly"\--metadata="DATABASE1_INSTANCE_IP=${DATABASE1_INSTANCE_IP},DATABASE2_INSTANCE_IP=${DATABASE2_INSTANCE_IP},DATABASE1_INSTANCE_NAME=${DATABASE1_INSTANCE_NAME},DATABASE2_INSTANCE_NAME=${DATABASE2_INSTANCE_NAME},QUORUM_INSTANCE_NAME=${QUORUM_INSTANCE_NAME},DATABASE1_INSTANCE_ZONE=${DATABASE1_INSTANCE_ZONE},DATABASE2_INSTANCE_ZONE=${DATABASE2_INSTANCE_ZONE}"

2. Create a MySQL instance named database2 in zone asia-east1-b:

   gcloudcomputeinstancescreate${DATABASE2_INSTANCE_NAME}\--zone=${DATABASE2_INSTANCE_ZONE}\--machine-type=n1-standard-2\--network-tier=PREMIUM\--maintenance-policy=MIGRATE\--image-family=debian-9\--image-project=debian-cloud\--boot-disk-size=50GB\--boot-disk-type=pd-standard\--boot-disk-device-name=${DATABASE2_INSTANCE_NAME}\--create-disk=mode=rw,size=300,type=pd-standard,name=disk-2\--private-network-ip=${DATABASE2_INSTANCE_NAME}\--tags=mysql\--service-account=mysql-instance@${DEVSHELL_PROJECT_ID}.iam.gserviceaccount.com\--scopes="https://www.googleapis.com/auth/compute,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly"\--metadata="DATABASE1_INSTANCE_IP=${DATABASE1_INSTANCE_IP},DATABASE2_INSTANCE_IP=${DATABASE2_INSTANCE_IP},DATABASE1_INSTANCE_NAME=${DATABASE1_INSTANCE_NAME},DATABASE2_INSTANCE_NAME=${DATABASE2_INSTANCE_NAME},QUORUM_INSTANCE_NAME=${QUORUM_INSTANCE_NAME},DATABASE1_INSTANCE_ZONE=${DATABASE1_INSTANCE_ZONE},DATABASE2_INSTANCE_ZONE=${DATABASE2_INSTANCE_ZONE}"

3. Create a quorum node for use by Pacemaker in zone asia-east1-c:

   gcloudcomputeinstancescreate${QUORUM_INSTANCE_NAME}\--zone=${QUORUM_INSTANCE_ZONE}\--machine-type=n1-standard-1\--network-tier=PREMIUM\--maintenance-policy=MIGRATE\--image-family=debian-9\--image-project=debian-cloud\--boot-disk-size=10GB\--boot-disk-type=pd-standard\--boot-disk-device-name=${QUORUM_INSTANCE_NAME}\--private-network-ip=${QUORUM_INSTANCE_NAME}

4. Create a MySQL client instance:

   gcloudcomputeinstancescreate${CLIENT_INSTANCE_NAME}\--image-family=ubuntu-1604-lts\--image-project=ubuntu-os-cloud\--tags=mysql-client\--zone=${CLIENT_INSTANCE_ZONE}\--boot-disk-size=10GB\--metadata="ILB_IP=${ILB_IP}"

Installing and configuring DRBD

In this section, you install and configure the DRBD packages on the database1 and database2 instances, and then initiate DRBD replication from database1 to database2.

Configure DRBD on database1

1. In the Google Cloud console, go to the VM instances page:

   VM INSTANCES PAGE

2. In the database1 instance row, click SSH to connect to the instance.

3. Create a file to retrieve and store instance metadata in environment variables:

   sudobash-ccat<<EOF > ~/.varsrc DATABASE1_INSTANCE_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_IP"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_IP"-H"Metadata-Flavor: Google")DATABASE1_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_NAME"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_NAME"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_ZONE=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_ZONE"-H"Metadata-Flavor: Google")DATABASE1_INSTANCE_ZONE=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_ZONE"-H"Metadata-Flavor: Google")QUORUM_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/QUORUM_INSTANCE_NAME"-H"Metadata-Flavor: Google") EOF 

4. Load the metadata variables from file:

   source~/.varsrc 

5. Format the data disk:

   sudobash-c"mkfs.ext4 -m 0 -F -E \lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb"

   For a detailed description of mkfs.ext4 options, see the mkfs.ext4 manpage.

6. Install DRBD:

   sudoapt-yinstalldrbd8-utils 

7. Create the DRBD configuration file:

   sudobash-c'cat <<EOF > /etc/drbd.d/global_common.confglobal { usage-count no;}common { protocol C;}EOF'

8. Create a DRBD resource file:

   sudobash-c"cat <<EOF > /etc/drbd.d/r0.resresource r0 { meta-disk internal; device /dev/drbd0; net { allow-two-primaries no; after-sb-0pri discard-zero-changes; after-sb-1pri discard-secondary; after-sb-2pri disconnect; rr-conflict disconnect; } on database1 { disk /dev/sdb; address 10.140.0.2:7789; } on database2 { disk /dev/sdb; address 10.140.0.3:7789; }}EOF"

9. Load the DRBD kernel module:

   sudomodprobedrbd 

10. Clear the contents of the /dev/sdb disk:

    sudoddif=/dev/zeroof=/dev/sdbbs=1kcount=1024

11. Create the DRBD resource r0:

    sudodrbdadmcreate-mdr0 

12. Bring up DRBD:

    sudodrbdadmupr0 

13. Disable DRBD when the system starts, letting the cluster resource management software bring up all necessary services in order:

    sudoupdate-rc.ddrbddisable 

Configure DRBD on database2

You now install and configure the DRBD packages on the database2 instance.

1. Connect to the database2 instance through SSH.

2. Create a .varsrc file to retrieve and store instance metadata in environment variables:

   sudobash-ccat<<EOF > ~/.varsrc DATABASE1_INSTANCE_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_IP"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_IP"-H"Metadata-Flavor: Google")DATABASE1_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_NAME"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_NAME"-H"Metadata-Flavor: Google")DATABASE2_INSTANCE_ZONE=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE2_INSTANCE_ZONE"-H"Metadata-Flavor: Google")DATABASE1_INSTANCE_ZONE=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/DATABASE1_INSTANCE_ZONE"-H"Metadata-Flavor: Google")QUORUM_INSTANCE_NAME=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/QUORUM_INSTANCE_NAME"-H"Metadata-Flavor: Google") EOF 

3. Load the metadata variables from the file:

   source~/.varsrc 

4. Format the data disk:

   sudobash-c"mkfs.ext4 -m 0 -F -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb"

5. Install the DRBD packages:

   sudoapt-yinstalldrbd8-utils 

6. Create the DRBD configuration file:

   sudobash-c'cat <<EOF > /etc/drbd.d/global_common.confglobal { usage-count no;}common { protocol C;}EOF'

7. Create a DRBD resource file:

   sudobash-c"cat <<EOF > /etc/drbd.d/r0.resresource r0 { meta-disk internal; device /dev/drbd0; net { allow-two-primaries no; after-sb-0pri discard-zero-changes; after-sb-1pri discard-secondary; after-sb-2pri disconnect; rr-conflict disconnect; } on ${DATABASE1_INSTANCE_NAME} { disk /dev/sdb; address ${DATABASE1_INSTANCE_IP}:7789; } on ${DATABASE2_INSTANCE_NAME} { disk /dev/sdb; address ${DATABASE2_INSTANCE_IP}:7789; }}EOF"

8. Load the DRBD kernel module:

   sudomodprobedrbd 

9. Clear out the /dev/sdb disk:

   sudoddif=/dev/zeroof=/dev/sdbbs=1kcount=1024

10. Create the DRBD resource r0:

    sudodrbdadmcreate-mdr0 

11. Bring up DRBD:

    sudodrbdadmupr0 

12. Disable DRBD when the system starts, letting the cluster resource management software bring up all necessary services in order:

    sudoupdate-rc.ddrbddisable 

Initiate DRBD replication from database1 to database2

1. Connect to the database1 instance through SSH.

2. Overwrite all r0 resources on the primary node:

   sudodrbdadm----overwrite-data-of-peerprimaryr0 sudomkfs.ext4-m0-F-Elazy_itable_init=0,lazy_journal_init=0,discard/dev/drbd0 

3. Verify the status of DRBD:

   sudocat/proc/drbd|grep============

   The output looks like this:

    [===================>] sync'ed:100.0% (208/307188)M 

4. Mount /dev/drbd to /srv:

   sudomount-odiscard,defaults/dev/drbd0/srv 

Installing MySQL and Pacemaker

In this section, you install MySQL and Pacemaker on each instance.

Install MySQL on database1

1. Connect to the database1 instance through SSH.

2. Update the APT repository with the MySQL 5.6 package definitions:

   sudobash-c'cat <<EOF > /etc/apt/sources.list.d/mysql.listdeb http://repo.mysql.com/apt/debian/ stretch mysql-5.6\ndeb-src http://repo.mysql.com/apt/debian/ stretch mysql-5.6EOF'

3. Add the GPG keys to the APT repository.srv file:

   wget-O/tmp/RPM-GPG-KEY-mysqlhttps://repo.mysql.com/RPM-GPG-KEY-mysql sudoapt-keyadd/tmp/RPM-GPG-KEY-mysql 

4. Update the package list:

   sudoaptupdate 

5. Install the MySQL server:

   sudoapt-yinstallmysql-server 

   When prompted for a password, enter DRBDha2.

6. Stop the MySQL server:

   sudo/etc/init.d/mysqlstop 

7. Create the MySQL configuration file:

   sudobash-c'cat <<EOF > /etc/mysql/mysql.conf.d/my.cnf[mysqld]bind-address = 0.0.0.0 # You may want to listen at localhost at the beginningdatadir = /var/lib/mysqltmpdir = /srv/tmpuser = mysqlEOF'

8. Create a temporary directory for the MySQL server (configured in mysql.conf):

   sudomkdir/srv/tmp sudochmod1777/srv/tmp 

9. Move all MySQL data into the DRBD directory /srv/mysql:

   sudomv/var/lib/mysql/srv/mysql 

10. Link /var/lib/mysql to /srv/mysql under the DRBD replicated storage volume:

    sudoln-s/srv/mysql/var/lib/mysql 

11. Change the /srv/mysql owner to a mysql process:

    sudochown-Rmysql:mysql/srv/mysql 

12. Remove InnoDB initial data to make sure the disk is as clean as possible:

    sudobash-c"cd /srv/mysql && rm ibdata1 && rm ib_logfile*"

    InnoDB is a storage engine for the MySQL database management system.

13. Start MySQL:

    sudo/etc/init.d/mysqlstart 

14. Grant access to root user for remote connections in order to test the deployment later:

    mysql-uroot-pDRBDha2-e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'DRBDha2' WITH GRANT OPTION;"

15. Disable automatic MySQL startup, which cluster resource management takes care of:

    sudoupdate-rc.d-fmysqldisable 

Install Pacemaker on database1

1. Load the metadata variables from the .varsrc file that you created earlier:

   source~/.varsrc 

2. Stop the MySQL server:

   sudo/etc/init.d/mysqlstop 

3. Install Pacemaker:

   sudoapt-yinstallpcs 

4. Enable pcsd, corosync, and pacemaker at system start on the primary instance:

   sudoupdate-rc.d-fpcsdenable sudoupdate-rc.d-fcorosyncenable sudoupdate-rc.d-fpacemakerenable

5. Configure corosync to start before pacemaker:

   sudoupdate-rc.dcorosyncdefaults2020 sudoupdate-rc.dpacemakerdefaults3010

6. Set the cluster user password to haCLUSTER3 for authentication:

   sudobash-c"echo hacluster:haCLUSTER3 | chpasswd"

7. Run the corosync-keygen script to generate a 128-bit cluster authorization key and write it into/etc/corosync/authkey:

   sudocorosync-keygen-l 

8. Copy authkey to the database2 instance. When prompted for a passphrase, press Enter:

   sudochmod444/etc/corosync/authkey gcloudbetacomputescp/etc/corosync/authkey${DATABASE2_INSTANCE_NAME}:~/authkey--zone=${DATABASE2_INSTANCE_ZONE}--internal-ip sudochmod400/etc/corosync/authkey 

9. Create a Corosync cluster configuration file:

   sudobash-c"cat <<EOF > /etc/corosync/corosync.conftotem { version: 2 cluster_name: mysql_cluster transport: udpu interface { ringnumber: 0 Bindnetaddr: ${DATABASE1_INSTANCE_IP} broadcast: yes mcastport: 5405 }}quorum { provider: corosync_votequorumtwo_node: 1}nodelist { node { ring0_addr: ${DATABASE1_INSTANCE_NAME} name: ${DATABASE1_INSTANCE_NAME} nodeid: 1 } node { ring0_addr: ${DATABASE2_INSTANCE_NAME} name: ${DATABASE2_INSTANCE_NAME} nodeid: 2 }}logging { to_logfile: yes logfile: /var/log/corosync/corosync.log timestamp: on}EOF"

   The totem section configures the Totem protocol for reliable communication. Corosync uses this communication to control cluster membership, and it specifies how the cluster members should communicate with each other.

   The important settings in the setup are as follows:

   • transport: Specifies unicast mode (udpu).
   • Bindnetaddr: Specifies the network address to which Corosync binds.
   • nodelist: Defines the nodes in the cluster, and how they can be reached—in this case, the database1 and database2 nodes.
   • quorum/two_node: By default, in a two-node cluster, no node will acquire a quorum. You can override this by specifying the value "1" for two_node in the quorum section.

   This setup lets you configure the cluster and prepare it for later when you add a third node that will be a quorum device.

10. Create the service directory for corosync:

    sudomkdir-p/etc/corosync/service.d 

11. Configure corosync to be aware of Pacemaker:

    sudobash-c'cat <<EOF > /etc/corosync/service.d/pcmkservice { name: pacemaker ver: 1}EOF'

12. Enable the corosync service by default:

    sudobash-c'cat <<EOF > /etc/default/corosync# Path to corosync.confCOROSYNC_MAIN_CONFIG_FILE=/etc/corosync/corosync.conf# Path to authfileCOROSYNC_TOTEM_AUTHKEY_FILE=/etc/corosync/authkey# Enable service by defaultSTART=yesEOF'

13. Restart the corosync and pacemaker services:

    sudoservicecorosyncrestart sudoservicepacemakerrestart 

14. Install the Corosync quorum device package:

    sudoapt-yinstallcorosync-qdevice 

15. Install a shell script to handle DRBD failure events:

    sudobash-c'cat << 'EOF' > /var/lib/pacemaker/drbd_cleanup.sh#!/bin/shif [ -z \$CRM_alert_version ]; then echo "\$0 must be run by Pacemaker version 1.1.15 or later" exit 0fitstamp="\$CRM_alert_timestamp: "case \$CRM_alert_kind in resource) if [ \${CRM_alert_interval} = "0" ]; then CRM_alert_interval="" else CRM_alert_interval=" (\${CRM_alert_interval})" fi if [ \${CRM_alert_target_rc} = "0" ]; then CRM_alert_target_rc="" else CRM_alert_target_rc=" (target: \${CRM_alert_target_rc})" fi case \${CRM_alert_desc} in Cancelled) ;; *) echo "\${tstamp}Resource operation "\${CRM_alert_task}\${CRM_alert_interval}" for "\${CRM_alert_rsc}" on "\${CRM_alert_node}": \${CRM_alert_desc}\${CRM_alert_target_rc}" >> "\${CRM_alert_recipient}" if [ "\${CRM_alert_task}" = "stop" ] && [ "\${CRM_alert_desc}" = "Timed Out" ]; then echo "Executing recovering..." >> "\${CRM_alert_recipient}" pcs resource cleanup \${CRM_alert_rsc} fi ;; esac ;; *) echo "\${tstamp}Unhandled \$CRM_alert_kind alert" >> "\${CRM_alert_recipient}" env | grep CRM_alert >> "\${CRM_alert_recipient}" ;;esacEOF' sudochmod0755/var/lib/pacemaker/drbd_cleanup.sh sudotouch/var/log/pacemaker_drbd_file.log sudochownhacluster:haclient/var/log/pacemaker_drbd_file.log 

Install MySQL on database2

1. Connect to the database2 instance through SSH.

2. Update the APT repository with the MySQL 5.6 package:

   sudobash-c'cat <<EOF > /etc/apt/sources.list.d/mysql.listdeb http://repo.mysql.com/apt/debian/ stretch mysql-5.6\ndeb-src http://repo.mysql.com/apt/debian/ stretch mysql-5.6EOF'

3. Add the GPG keys to the APT repository:

   wget-O/tmp/RPM-GPG-KEY-mysqlhttps://repo.mysql.com/RPM-GPG-KEY-mysql sudoapt-keyadd/tmp/RPM-GPG-KEY-mysql 

4. Update the package list:

   sudoaptupdate 

5. Install the MySQL server:

   sudoapt-yinstallmysql-server 

   When prompted for a password, enter DRBDha2.

6. Stop the MySQL server:

   sudo/etc/init.d/mysqlstop 

7. Create the MySQL configuration file:

   sudobash-c'cat <<EOF > /etc/mysql/mysql.conf.d/my.cnf[mysqld]bind-address = 0.0.0.0 # You may want to listen at localhost at the beginningdatadir = /var/lib/mysqltmpdir = /srv/tmpuser = mysqlEOF'

8. Remove data under /var/lib/mysql and add a symbolic link to the mount point target for the replicated DRBD volume. The DRBD volume (/dev/drbd0) will be mounted at /srv only when a failover occurs.

   sudorm-rf/var/lib/mysql sudoln-s/srv/mysql/var/lib/mysql 

9. Disable automatic MySQL startup, which cluster resource management takes care of:

   sudoupdate-rc.d-fmysqldisable 

Install Pacemaker on database2

1. Load the metadata variables from the .varsrc file:

   source~/.varsrc 

2. Install Pacemaker:

   sudoapt-yinstallpcs 

3. Move the Corosync authkey file that you copied before to /etc/corosync/:

   sudomv~/authkey/etc/corosync/ sudochownroot:/etc/corosync/authkey sudochmod400/etc/corosync/authkey 

4. Enable pcsd, corosync, and pacemaker at system start on the standby instance:

   sudoupdate-rc.d-fpcsdenable sudoupdate-rc.d-fcorosyncenable sudoupdate-rc.d-fpacemakerenable

5. Configure corosync to start before pacemaker:

   sudoupdate-rc.dcorosyncdefaults2020 sudoupdate-rc.dpacemakerdefaults3010

6. Set the cluster user password for authentication. The password is the same one (haCLUSTER3) you used for the database1 instance.

   sudobash-c"echo hacluster:haCLUSTER3 | chpasswd"

7. Create the corosync configuration file:

   sudobash-c"cat <<EOF > /etc/corosync/corosync.conftotem { version: 2 cluster_name: mysql_cluster transport: udpu interface { ringnumber: 0 Bindnetaddr: ${DATABASE2_INSTANCE_IP} broadcast: yes mcastport: 5405 }}quorum { provider: corosync_votequorum two_node: 1}nodelist { node { ring0_addr: ${DATABASE1_INSTANCE_NAME} name: ${DATABASE1_INSTANCE_NAME} nodeid: 1 } node { ring0_addr: ${DATABASE2_INSTANCE_NAME} name: ${DATABASE2_INSTANCE_NAME} nodeid: 2 }}logging { to_logfile: yes logfile: /var/log/corosync/corosync.logtimestamp: on}EOF"

8. Create the Corosync service directory:

   sudomkdir/etc/corosync/service.d 

9. Configure corosync to be aware of Pacemaker:

   sudobash-c'cat <<EOF > /etc/corosync/service.d/pcmkservice {name: pacemakerver: 1}EOF'

10. Enable the corosync service by default:

    sudobash-c'cat <<EOF > /etc/default/corosync# Path to corosync.confCOROSYNC_MAIN_CONFIG_FILE=/etc/corosync/corosync.conf# Path to authfileCOROSYNC_TOTEM_AUTHKEY_FILE=/etc/corosync/authkey# Enable service by defaultSTART=yesEOF'

11. Restart the corosync and pacemaker services:

    sudoservicecorosyncrestart sudoservicepacemakerrestart 

12. Install the Corosync quorum device package:

    sudoapt-yinstallcorosync-qdevice 

13. Install a shell script to handle DRBD failure events:

    sudobash-c'cat << 'EOF' > /var/lib/pacemaker/drbd_cleanup.sh#!/bin/shif [ -z \$CRM_alert_version ]; then echo "\$0 must be run by Pacemaker version 1.1.15 or later" exit 0fitstamp="\$CRM_alert_timestamp: "case \$CRM_alert_kind in resource) if [ \${CRM_alert_interval} = "0" ]; then CRM_alert_interval="" else CRM_alert_interval=" (\${CRM_alert_interval})" fi if [ \${CRM_alert_target_rc} = "0" ]; then CRM_alert_target_rc="" else CRM_alert_target_rc=" (target: \${CRM_alert_target_rc})" fi case \${CRM_alert_desc} in Cancelled) ;; *) echo "\${tstamp}Resource operation "\${CRM_alert_task}\${CRM_alert_interval}" for "\${CRM_alert_rsc}" on "\${CRM_alert_node}": \${CRM_alert_desc}\${CRM_alert_target_rc}" >> "\${CRM_alert_recipient}" if [ "\${CRM_alert_task}" = "stop" ] && [ "\${CRM_alert_desc}" = "Timed Out" ]; then echo "Executing recovering..." >> "\${CRM_alert_recipient}" pcs resource cleanup \${CRM_alert_rsc} fi ;; esac ;; *) echo "\${tstamp}Unhandled \$CRM_alert_kind alert" >> "\${CRM_alert_recipient}" env | grep CRM_alert >> "\${CRM_alert_recipient}" ;;esacEOF' sudochmod0755/var/lib/pacemaker/drbd_cleanup.sh sudotouch/var/log/pacemaker_drbd_file.log sudochownhacluster:haclient/var/log/pacemaker_drbd_file.log 

14. Check the Corosync cluster status:

    sudocorosync-cmapctl|grep"members...ip"

    The output looks like this:

     runtime.totem.pg.mrp.srp.members.1.ip (str) = r(0) ip(10.140.0.2) runtime.totem.pg.mrp.srp.members.2.ip (str) = r(0) ip(10.140.0.3) 

Starting the cluster

1. Connect to the database2 instance through SSH.

2. Load the metadata variables from the .varsrc file:

   source~/.varsrc 

3. Authenticate against the cluster nodes:

   sudopcsclusterauth--namemysql_cluster${DATABASE1_INSTANCE_NAME}${DATABASE2_INSTANCE_NAME}-uhacluster-phaCLUSTER3 

4. Start the cluster:

   sudopcsclusterstart--all 

5. Verify the cluster status:

   sudopcsstatus 

   The output looks like this:

    Cluster name: mysql_cluster WARNING: no stonith devices and stonith-enabled is not false Stack: corosync Current DC: database2 (version 1.1.16-94ff4df) - partition with quorum Last updated: Sat Nov 3 07:24:53 2018 Last change: Sat Nov 3 07:17:17 2018 by hacluster via crmd on database2 2 nodes configured 0 resources configured Online: [ database1 database2 ] No resources Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

Configuring Pacemaker to manage cluster resources

Next, you configure Pacemaker with the DRBD, disk, MySQL, and quorum resources.

1. Connect to the database1 instance through SSH.

2. Use the Pacemaker pcs utility to queue several changes into a file and later push those changes to the Cluster Information Base (CIB) atomically:

   sudopcsclustercibclust_cfg 

3. Disable STONITH, because you'll deploy the quorum device later:

   sudopcs-fclust_cfgpropertysetstonith-enabled=false

4. Disable the quorum-related settings. You'll set up the quorum device node later.

   sudopcs-fclust_cfgpropertysetno-quorum-policy=stop 

5. Prevent Pacemaker from moving back resources after a recovery:

   sudopcs-fclust_cfgresourcedefaultsresource-stickiness=200

6. Create the DRBD resource in the cluster:

   sudopcs-fclust_cfgresourcecreatemysql_drbdocf:linbit:drbd\drbd_resource=r0\opmonitorrole=Masterinterval=110timeout=30\opmonitorrole=Slaveinterval=120timeout=30\opstarttimeout=120\opstoptimeout=60

7. Make sure that only one primary role is assigned to the DRBD resource:

   sudopcs-fclust_cfgresourcemasterprimary_mysqlmysql_drbd\master-max=1master-node-max=1\clone-max=2clone-node-max=1\notify=true

8. Create the file system resource to mount the DRBD disk:

   sudopcs-fclust_cfgresourcecreatemystore_FSFilesystem\device="/dev/drbd0"\directory="/srv"\fstype="ext4"

9. Configure the cluster to colocate the DRBD resource with the disk resource on the same VM:

   sudopcs-fclust_cfgconstraintcolocationaddmystore_FSwithprimary_mysqlINFINITYwith-rsc-role=Master 

10. Configure the cluster to bring up the disk resource only after the DRBD primary is promoted:

    sudopcs-fclust_cfgconstraintorderpromoteprimary_mysqlthenstartmystore_FS 

11. Create a MySQL service:

    sudopcs-fclust_cfgresourcecreatemysql_serviceocf:heartbeat:mysql\binary="/usr/bin/mysqld_safe"\config="/etc/mysql/my.cnf"\datadir="/var/lib/mysql"\pid="/var/run/mysqld/mysql.pid"\socket="/var/run/mysqld/mysql.sock"\additional_parameters="--bind-address=0.0.0.0"\opstarttimeout=60s\opstoptimeout=60s\opmonitorinterval=20stimeout=30s 

12. Configure the cluster to colocate the MySQL resource with the disk resource on the same VM:

    sudopcs-fclust_cfgconstraintcolocationaddmysql_servicewithmystore_FSINFINITY 

13. Ensure that the DRBD file system precedes the MySQL service in the startup order:

    sudopcs-fclust_cfgconstraintordermystore_FSthenmysql_service 

14. Create the alert agent, and add the patch to the log file as its recipient:

    sudopcs-fclust_cfgalertcreateid=drbd_cleanup_filedescription="Monitor DRBD events and perform post cleanup"path=/var/lib/pacemaker/drbd_cleanup.sh sudopcs-fclust_cfgalertrecipientadddrbd_cleanup_fileid=logfilevalue=/var/log/pacemaker_drbd_file.log 

15. Commit the changes to the cluster:

    sudopcsclustercib-pushclust_cfg 

16. Verify that all the resources are online:

    sudopcsstatus 

    The output looks like this:

     Online: [ database1 database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Slaves: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 

Configuring a quorum device

1. Connect to the qdevice instance through SSH.

2. Install pcs and corosync-qnetd:

   sudoaptupdate && sudoapt-yinstallpcscorosync-qnetd 

3. Start the Pacemaker/Corosync configuration system daemon (pcsd) service and enable it on system start:

   sudoservicepcsdstart sudoupdate-rc.dpcsdenable

4. Set the cluster user password (haCLUSTER3) for authentication:

   sudobash-c"echo hacluster:haCLUSTER3 | chpasswd"

5. Check the quorum device status:

   sudopcsqdevicestatusnet--full 

   The output looks like this:

    QNetd address: *:5403 TLS: Supported (client certificate required) Connected clients: 0 Connected clusters: 0 Maximum send/receive size: 32768/32768 bytes 

Configure the quorum device settings on database1

1. Connect to the database1 node through SSH.

2. Load the metadata variables from the .varsrc file:

   source~/.varsrc 

3. Authenticate the quorum device node for the cluster:

   sudopcsclusterauth--namemysql_cluster${QUORUM_INSTANCE_NAME}-uhacluster-phaCLUSTER3 

4. Add the quorum device to the cluster. Use the ffsplit algorithm, which guarantees that the active node will be decided based on 50% of the votes or more:

   sudopcsquorumdeviceaddmodelnethost=${QUORUM_INSTANCE_NAME}algorithm=ffsplit 

5. Add the quorum setting to corosync.conf:

   sudobash-c"cat <<EOF > /etc/corosync/corosync.conftotem { version: 2 cluster_name: mysql_cluster transport: udpu interface { ringnumber: 0 Bindnetaddr: ${DATABASE1_INSTANCE_IP} broadcast: yes mcastport: 5405 }}quorum { provider: corosync_votequorum device { votes: 1 model: net net { tls: on host: ${QUORUM_INSTANCE_NAME} algorithm: ffsplit } }}nodelist { node { ring0_addr: ${DATABASE1_INSTANCE_NAME} name: ${DATABASE1_INSTANCE_NAME} nodeid: 1 } node { ring0_addr: ${DATABASE2_INSTANCE_NAME} name: ${DATABASE2_INSTANCE_NAME} nodeid: 2 }}logging { to_logfile: yes logfile: /var/log/corosync/corosync.log timestamp: on}EOF"

6. Restart the corosync service to reload the new quorum device setting:

   sudoservicecorosyncrestart 

7. Start the corosync quorum device daemon and bring it up at system start:

   sudoservicecorosync-qdevicestart sudoupdate-rc.dcorosync-qdevicedefaults 

Configure the quorum device settings on database2

1. Connect to the database2 node through SSH.

2. Load the metadata variables from the .varsrc file:

   source~/.varsrc 

3. Add a quorum setting to corosync.conf:

   sudobash-c"cat <<EOF > /etc/corosync/corosync.conftotem { version: 2 cluster_name: mysql_cluster transport: udpu interface { ringnumber: 0 Bindnetaddr: ${DATABASE2_INSTANCE_IP} broadcast: yes mcastport: 5405 }}quorum { provider: corosync_votequorum device { votes: 1 model: net net { tls: on host: ${QUORUM_INSTANCE_NAME} algorithm: ffsplit } }}nodelist { node { ring0_addr: ${DATABASE1_INSTANCE_NAME} name: ${DATABASE1_INSTANCE_NAME} nodeid: 1 } node { ring0_addr: ${DATABASE2_INSTANCE_NAME} name: ${DATABASE2_INSTANCE_NAME} nodeid: 2 }}logging { to_logfile: yes logfile: /var/log/corosync/corosync.log timestamp: on}EOF"

4. Restart the corosync service to reload the new quorum device setting:

   sudoservicecorosyncrestart 

5. Start the Corosync quorum device daemon and configure it to bring it up at system start:

   sudoservicecorosync-qdevicestart sudoupdate-rc.dcorosync-qdevicedefaults 

Verifying the cluster status

The next step is to verify that the cluster resources are online.

1. Connect to the database1 instance through SSH.

2. Verify the cluster status:

   sudopcsstatus 

   The output looks like this:

    Cluster name: mysql_cluster Stack: corosync Current DC: database1 (version 1.1.16-94ff4df) - partition with quorum Last updated: Sun Nov 4 01:49:18 2018 Last change: Sat Nov 3 15:48:21 2018 by root via cibadmin on database1 2 nodes configured 4 resources configured Online: [ database1 database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Slaves: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

3. Show the quorum status:

   sudopcsquorumstatus 

   The output looks like this:

    Quorum information ------------------ Date: Sun Nov 4 01:48:25 2018 Quorum provider: corosync_votequorum Nodes: 2 Node ID: 1 Ring ID: 1/24 Quorate: Yes Votequorum information ---------------------- Expected votes: 3 Highest expected: 3 Total votes: 3 Quorum: 2 Flags: Quorate Qdevice Membership information ---------------------- Nodeid Votes Qdevice Name 1 1 A,V,NMW database1 (local) 2 1 A,V,NMW database2 0 1 Qdevice 

4. Show the quorum device status:

   sudopcsquorumdevicestatus 

   The output looks like this:

    Qdevice information ------------------- Model: Net Node ID: 1 Configured node list: 0 Node ID = 1 1 Node ID = 2 Membership node list: 1, 2 Qdevice-net information ---------------------- Cluster name: mysql_cluster QNetd host: qdevice:5403 Algorithm: Fifty-Fifty split Tie-breaker: Node with lowest node ID State: Connected 

Configuring an internal load balancer as the cluster IP

1. Open Cloud Shell:

   OPEN Cloud Shell

2. Create an unmanaged instance group and add the database1 instance to it:

   gcloudcomputeinstance-groupsunmanagedcreate${DATABASE1_INSTANCE_NAME}-instance-group\--zone=${DATABASE1_INSTANCE_ZONE}\--description="${DATABASE1_INSTANCE_NAME} unmanaged instance group"gcloudcomputeinstance-groupsunmanagedadd-instances${DATABASE1_INSTANCE_NAME}-instance-group\--zone=${DATABASE1_INSTANCE_ZONE}\--instances=${DATABASE1_INSTANCE_NAME}

3. Create an unmanaged instance group and add the database2 instance to it:

   gcloudcomputeinstance-groupsunmanagedcreate${DATABASE2_INSTANCE_NAME}-instance-group\--zone=${DATABASE2_INSTANCE_ZONE}\--description="${DATABASE2_INSTANCE_NAME} unmanaged instance group"gcloudcomputeinstance-groupsunmanagedadd-instances${DATABASE2_INSTANCE_NAME}-instance-group\--zone=${DATABASE2_INSTANCE_ZONE}\--instances=${DATABASE2_INSTANCE_NAME}

4. Create a health check for port 3306:

   gcloudcomputehealth-checkscreatetcpmysql-backend-healthcheck\--port3306

5. Create a regional internal backend service:

   gcloudcomputebackend-servicescreatemysql-ilb\--load-balancing-schemeinternal\--region${CLUSTER_REGION}\--health-checksmysql-backend-healthcheck\--protocoltcp

6. Add the two instance groups as backends to the backend service:

   gcloudcomputebackend-servicesadd-backendmysql-ilb\--instance-group${DATABASE1_INSTANCE_NAME}-instance-group\--instance-group-zone${DATABASE1_INSTANCE_ZONE}\--region${CLUSTER_REGION}gcloudcomputebackend-servicesadd-backendmysql-ilb\--instance-group${DATABASE2_INSTANCE_NAME}-instance-group\--instance-group-zone${DATABASE2_INSTANCE_ZONE}\--region${CLUSTER_REGION}

7. Create a forwarding rule for the load balancer:

   gcloudcomputeforwarding-rulescreatemysql-ilb-forwarding-rule\--load-balancing-schemeinternal\--ports3306\--networkdefault\--subnetdefault\--region${CLUSTER_REGION}\--address${ILB_IP}\--backend-servicemysql-ilb

8. Create a firewall rule to allow an internal load balancer health check:

   gcloudcomputefirewall-rulescreateallow-ilb-healthcheck\--direction=INGRESS--network=default\--action=ALLOW--rules=tcp:3306\--source-ranges=130.211.0.0/22,35.191.0.0/16--target-tags=mysql

9. To check the status of your load balancer, go to the Load balancing page in the Google Cloud console.

   OPEN THE LOAD BALANCING PAGE

10. Click mysql-ilb:

    

    Because the cluster allows only one instance to run MySQL at any given time, only one instance is healthy from the internal load balancer's perspective.

    

Connecting to the cluster from the MySQL client

1. Connect to the mysql-client instance through SSH.

2. Update the package definitions:

   sudoapt-getupdate 

3. Install the MySQL client:

   sudoapt-getinstall-ymysql-client 

4. Create a script file that creates and populates a table with sample data:

   cat<<EOF > db_creation.sql CREATEDATABASEsource_db; usesource_db; CREATETABLEsource_table (idBIGINTNOTNULLAUTO_INCREMENT, timestamptimestampNOTNULLDEFAULTCURRENT_TIMESTAMP, event_datafloatDEFAULTNULL, PRIMARYKEY(id)); DELIMITER$$ CREATEPROCEDUREsimulate_data() BEGIN DECLAREiINTDEFAULT0; WHILEi < 100DO INSERTINTOsource_table(event_data)VALUES(ROUND(RAND()*15000,2));SETi=i+1; ENDWHILE; END$$ DELIMITER; CALLsimulate_data() EOF 

5. Create the table:

   ILB_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/ILB_IP"-H"Metadata-Flavor: Google") mysql-uroot-pDRBDha2"-h${ILB_IP}" < db_creation.sql 

Testing the cluster

To test the HA capability of the deployed cluster, you can perform the following tests:

• Shut down the database1 instance to test whether the master database can fail over to the database2 instance.
• Start the database1 instance to see if database1 can successfully rejoin the cluster.
• Shut down the database2 instance to test whether the master database can fail over to the database1 instance.
• Start the database2 instance to see whether database2 can successfully rejoin the cluster and whether database1 instance still keeps the master role.
• Create a network partition between database1 and database2 to simulate a split-brain issue.

1. Open Cloud Shell:

   OPEN Cloud Shell

2. Stop the database1 instance:

   gcloudcomputeinstancesstop${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}

3. Check the status of the cluster:

   gcloudcomputessh${DATABASE2_INSTANCE_NAME}\--zone=${DATABASE2_INSTANCE_ZONE}\--command="sudo pcs status"

   The output looks like the following. Verify that the configuration changes you made took place:

    2 nodes configured 4 resources configured Online: [ database2 ] OFFLINE: [ database1 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database2 ] Stopped: [ database1 ] mystore_FS (ocf::heartbeat:Filesystem): Started database2 mysql_service (ocf::heartbeat:mysql): Started database2 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

4. Start the database1 instance:

   gcloudcomputeinstancesstart${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}

5. Check the status of the cluster:

   gcloudcomputessh${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--command="sudo pcs status"

   The output looks like this:

    2 nodes configured 4 resources configured Online: [ database1 database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database2 ] Slaves: [ database1 ] mystore_FS (ocf::heartbeat:Filesystem): Started database2 mysql_service (ocf::heartbeat:mysql): Started database2 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

6. Stop the database2 instance:

   gcloudcomputeinstancesstop${DATABASE2_INSTANCE_NAME}\--zone=${DATABASE2_INSTANCE_ZONE}

7. Check the status of the cluster:

   gcloudcomputessh${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--command="sudo pcs status"

   The output looks like this:

    2 nodes configured 4 resources configured Online: [ database1 ] OFFLINE: [ database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Stopped: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

8. Start the database2 instance:

   gcloudcomputeinstancesstart${DATABASE2_INSTANCE_NAME}\--zone=${DATABASE2_INSTANCE_ZONE}

9. Check the status of the cluster:

   gcloudcomputessh${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--command="sudo pcs status"

   The output looks like this:

    2 nodes configured 4 resources configured Online: [ database1 database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Slaves: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled 

10. Create a network partition between database1 and database2:

    gcloudcomputefirewall-rulescreateblock-comms\--description="no MySQL communications"\--action=DENY\--rules=all\--source-tags=mysql\--target-tags=mysql\--priority=800

11. After a couple of minutes, check the status of the cluster. Note how database1 keeps its primary role, because the quorum policy is the lowest ID node first under network partition situation. In the meantime, the database2 MySQL service is stopped. This quorum mechanism avoids the split-brain issue when the network partition occurs.

    gcloudcomputessh${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--command="sudo pcs status"

    The output looks like this:

     2 nodes configured 4 resources configured Online: [ database1 ] OFFLINE: [ database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Stopped: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 

12. Delete the network firewall rule to remove the network partition. (Press Y when prompted.)

    gcloudcomputefirewall-rulesdeleteblock-comms

13. Verify that the cluster status is back to normal:

    gcloudcomputessh${DATABASE1_INSTANCE_NAME}\--zone=${DATABASE1_INSTANCE_ZONE}\--command="sudo pcs status"

    The output looks like this:

     2 nodes configured 4 resources configured Online: [ database1 database2 ] Full list of resources: Master/Slave Set: primary_mysql [mysql_drbd] Masters: [ database1 ] Slaves: [ database2 ] mystore_FS (ocf::heartbeat:Filesystem): Started database1 mysql_service (ocf::heartbeat:mysql): Started database1 

14. Connect to the mysql-client instance through SSH.

15. In your shell, query the table you created previously:

    ILB_IP=$(curl-s"http://metadata.google.internal/computeMetadata/v1/instance/attributes/ILB_IP"-H"Metadata-Flavor: Google") mysql-uroot"-h${ILB_IP}"-pDRBDha2-e"select * from source_db.source_table LIMIT 10"

    The output should list 10 records of the following form, verifying the data consistency in the cluster:

     +----+---------------------+------------+ | id | timestamp | event_data | +----+---------------------+------------+ | 1 | 2018-11-27 21:00:09 | 1279.06 | | 2 | 2018-11-27 21:00:09 | 4292.64 | | 3 | 2018-11-27 21:00:09 | 2626.01 | | 4 | 2018-11-27 21:00:09 | 252.13 | | 5 | 2018-11-27 21:00:09 | 8382.64 | | 6 | 2018-11-27 21:00:09 | 11156.8 | | 7 | 2018-11-27 21:00:09 | 636.1 | | 8 | 2018-11-27 21:00:09 | 14710.1 | | 9 | 2018-11-27 21:00:09 | 11642.1 | | 10 | 2018-11-27 21:00:09 | 14080.3 | +----+---------------------+------------+ 

Failover sequence

If the primary node in the cluster goes down, the failover sequence looks like this:

1. Both the quorum device and the standby node lose connectivity with the primary node.
2. The quorum device votes for the standby node, and the standby node votes for itself.
3. Quorum is acquired by the standby node.
4. The standby node is promoted to primary.
5. The new primary node does the following:
   1. Promotes DRBD to primary
   2. Mounts the MySQL data disk from DRBD
   3. Starts MySQL
   4. Becomes healthy for the load balancer
6. The load balancer starts sending traffic to the new primary node.