MySQL-HA-Cluster-on-CentOS7-using-DRBD

mysql-ha.txt

https://www.lisenet.com/2016/activepassive-mysql-high-availability-pacemaker-cluster-with-drbd-on-centos-7/

http://clusterlabs.org/doc/it-IT/Pacemaker/1.1/html/Clusters_from_Scratch/ch07.html

http://elrepo.org/tiki/tiki-index.php

http://qiita.com/sion_cojp/items/a6a329df0415a843fb12

https://blog.3ware.co.jp/drbd-users-guide-8.3/s-configure-split-brain-behavior.html

==============================================================================

ホスト名設定
[db1]# hostnamectl set-hostname db1
[db2]# hostnamectl set-hostname db2

==============================================================================

# disable ipv6
[ALL]# echo "net.ipv6.conf.all.disable_ipv6 = 1" > /etc/sysctl.d/disable-ipv6.conf
[ALL]# echo "net.ipv6.conf.default.disable_ipv6 = 1" >> /etc/sysctl.d/disable-ipv6.conf
[ALL]# sysctl -p

==============================================================================

[ALL]# systemctl start firewalld
[ALL]# systemctl enable firewalld

==============================================================================

[ALL]# firewall-cmd --add-service high-availability
[ALL]# firewall-cmd --add-service high-availability --permanent

==============================================================================

[ALL]# yum install -y pcs
# pcs のインストールで
# pacemaker, corosync, resource-agents もインストールされる

==============================================================================

[ALL]# echo passwd | passwd hacluster --stdin

==============================================================================

[ALL]# systemctl start pcsd
[ALL]# systemctl enable pcsd

==============================================================================

[db1]# pcs cluster auth db1 db2 -u hacluster -p passwd
# /var/lib/pcsd/tokens に token が保存される

==============================================================================

[db1]# pcs cluster setup --name mysql_cluster db1 db2

```
[root@db1 ~]# pcs cluster setup --name mysql_cluster db1 db2
Destroying cluster on nodes: db1, db2...
db1: Stopping Cluster (pacemaker)...
db2: Stopping Cluster (pacemaker)...
db1: Successfully destroyed cluster
db2: Successfully destroyed cluster

Sending cluster config files to the nodes...
db1: Succeeded
db2: Succeeded

Synchronizing pcsd certificates on nodes db1, db2...
db1: Success
db2: Success

Restarting pcsd on the nodes in order to reload the certificates...
db1: Success
db2: Success
```

```
[root@db1 ~]# cat /etc/corosync/corosync.conf
totem {
    version: 2
    secauth: off
    cluster_name: mysql_cluster
    transport: udpu
}

nodelist {
    node {
        ring0_addr: db1
        nodeid: 1
    }

    node {
        ring0_addr: db2
        nodeid: 2
    }
}

quorum {
    provider: corosync_votequorum
    two_node: 1
}

logging {
    to_logfile: yes
    logfile: /var/log/cluster/corosync.log
    to_syslog: yes
}
```

```
[root@db1 ~]# pcs status corosync

Membership information
----------------------
    Nodeid      Votes Name
         1          1 db1 (local)
         2          1 db2
```

```
[root@db1 ~]# pcs status
Cluster name: mysql_cluster
WARNING: no stonith devices and stonith-enabled is not false
Stack: corosync
Current DC: db2 (version 1.1.15-11.el7_3.4-e174ec8) - partition with quorum
Last updated: Sun Jun 11 15:29:22 2017		Last change: Sun Jun 11 15:25:58 2017 by hacluster via crmd on db2

2 nodes and 0 resources configured

Online: [ db1 db2 ]

No resources


Daemon Status:
  corosync: active/disabled
  pacemaker: active/disabled
  pcsd: active/enabled
```

==============================================================================

[db1]# pcs cluster start --all

```
[root@db1 ~]# pcs cluster start --all
db1: Starting Cluster...
db2: Starting Cluster...
```

==============================================================================

( [ALL]# yum install -y epel-release )

==============================================================================

[ALL]# rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org
[ALL]# rpm -Uvh http://www.elrepo.org/elrepo-release-7.0-2.el7.elrepo.noarch.rpm
[ALL]# yum install -y kmod-drbd84 drbd84-utils

==============================================================================

[ALL]# yum install -y policycoreutils-python
[ALL]# semanage permissive -a drbd_t

==============================================================================

[ALL]# lvcreate -T -V 1G -n drbd0 cl/pool00

==============================================================================

[db1]# firewall-cmd --add-rich-rule='rule family=ipv4 source address=192.168.122.12 port port=7789 protocol=tcp accept'
[db1]# firewall-cmd --add-rich-rule='rule family=ipv4 source address=192.168.122.12 port port=7789 protocol=tcp accept' --permanent

[db2]# firewall-cmd --add-rich-rule='rule family=ipv4 source address=192.168.122.11 port port=7789 protocol=tcp accept'
[db2]# firewall-cmd --add-rich-rule='rule family=ipv4 source address=192.168.122.11 port port=7789 protocol=tcp accept' --permanent

==============================================================================

[ALL]# cat <<EOF > /etc/drbd.d/mysql.res
resource mysql {
  protocol C;
  meta-disk internal;
  device /dev/drbd0;
  disk /dev/cl/drbd0;
  handlers {
    split-brain "/usr/lib/drbd/notify-split-brain.sh root";
  }
  net {
    allow-two-primaries no;
    after-sb-0pri discard-zero-changes;
    after-sb-1pri discard-secondary;
    after-sb-2pri disconnect;
    rr-conflict disconnect;
  }
  disk {
    on-io-error detach;
  }
  syncer {
    verify-alg sha1;
  }
  on db1 {
    address 192.168.122.11:7789;
  }
  on db2 {
    address 192.168.122.12:7789;
  }
}
EOF

https://blog.3ware.co.jp/drbd-users-guide-8.3/s-configure-split-brain-behavior.html

==============================================================================

[ALL]# drbdadm create-md mysql

mysql.res の on XXX が `hostname` と一致する必要がある

```
[root@db1 ~]# drbdadm create-md mysql
[ 9270.237905] Request for unknown module key 'The ELRepo Project (http://elrepo.org): ELRepo.org Secure Boot Key: f365ad3481a7b20e3427b61b2a26635b83fe427b' err -11
[ 9270.240696] drbd: loading out-of-tree module taints kernel.
[ 9270.241865] drbd: module verification failed: signature and/or required key missing - tainting kernel
[ 9270.256491] drbd: initialized. Version: 8.4.9-1 (api:1/proto:86-101)
[ 9270.257797] drbd: GIT-hash: 9976da086367a2476503ef7f6b13d4567327a280 build by akemi@Build64R7, 2016-12-04 01:08:48
[ 9270.260611] drbd: registered as block device major 147
initializing activity log
NOT initializing bitmap
Writing meta data...
New drbd meta data block successfully created.
success
```

==============================================================================

[ALL]# drbdadm up mysql

==============================================================================

[db1]# drbdadm primary --force mysql

```
[root@db1 ~]# drbdadm primary --force mysql
[ 9511.568863] block drbd0: role( Secondary -> Primary ) disk( Inconsistent -> UpToDate ) 
[ 9511.578612] block drbd0: Forced to consider local data as UpToDate!
[ 9511.582295] block drbd0: new current UUID 14ECF8124105E71D:0000000000000004:0000000000000000:0000000000000000
[root@db1 ~]# [ 9511.637403] block drbd0: drbd_sync_handshake:
[ 9511.639456] block drbd0: self 14ECF8124105E71D:0000000000000004:0000000000000000:0000000000000000 bits:262127 flags:0
[ 9511.644818] block drbd0: peer 0000000000000004:0000000000000000:0000000000000000:0000000000000000 bits:262127 flags:0
[ 9511.650265] block drbd0: uuid_compare()=2 by rule 30
[ 9511.652122] block drbd0: Becoming sync source due to disk states.
[ 9511.654249] block drbd0: Writing the whole bitmap, full sync required after drbd_sync_handshake.
[ 9511.663728] block drbd0: 1024 MB (262127 bits) marked out-of-sync by on disk bit-map.
[ 9511.672009] block drbd0: conn( Connected -> WFBitMapS ) 
[ 9511.674362] block drbd0: send bitmap stats [Bytes(packets)]: plain 0(0), RLE 21(1), total 21; compression: 100.0%
[ 9511.691066] block drbd0: receive bitmap stats [Bytes(packets)]: plain 0(0), RLE 21(1), total 21; compression: 100.0%
[ 9511.695874] block drbd0: helper command: /sbin/drbdadm before-resync-source minor-0
[ 9511.703847] block drbd0: helper command: /sbin/drbdadm before-resync-source minor-0 exit code 0 (0x0)
[ 9511.709108] block drbd0: conn( WFBitMapS -> SyncSource ) 
[ 9511.711175] block drbd0: Began resync as SyncSource (will sync 1048508 KB [262127 bits set]).
[ 9511.714794] block drbd0: updated sync UUID 14ECF8124105E71D:BD677C4D24725CA1:0000000000000004:0000000000000000
```

```
[root@db1 ~]# drbd-overview
 0:mysql/0  SyncSource Primary/Secondary UpToDate/Inconsistent 
	[>....................] sync'ed:  2.8% (1022252/1048508)K           
```

```
[root@db1 ~]# drbd-overview
 0:mysql/0  SyncSource Primary/Secondary UpToDate/Inconsistent 
	[=>..................] sync'ed: 11.8% (929188/1048508)K           
```

```
[root@db1 ~]# drbd-overview
 0:mysql/0  Connected Primary/Secondary UpToDate/UpToDate
```

==============================================================================

[db1]# mkfs.xfs /dev/drbd0

==============================================================================

[db1]# mkdir /var/lib/mysql
[db1]# mount /dev/drbd0 /mnt
セットアップ用に一時的に /mnt にマウント

==============================================================================

[ALL]# yum install -y mariadb-server mariadb
[ALL]# systemctl disable mariadb.service

==============================================================================

[db1]# systemctl start mariadb
[db1]# mysql_install_db --datadir=/mnt --user=mysql
[db1]# mysql_secure_installation

==============================================================================

[db1]# semanage fcontext -a -t mysqld_db_t "/mnt(/.*)?"
[db1]# restorecon -Rv /mnt

==============================================================================

[db1]# umount /mnt
[db1]# systemctl stop mariadb

==============================================================================

[ALL]# cat << EOL > /etc/my.cnf
[mysqld]
symbolic-links=0
bind_address            = 0.0.0.0
datadir                 = /var/lib/mysql
pid_file                = /var/run/mariadb/mysqld.pid
socket                  = /var/run/mariadb/mysqld.sock

[mysqld_safe]
bind_address            = 0.0.0.0
datadir                 = /var/lib/mysql
pid_file                = /var/run/mariadb/mysqld.pid
socket                  = /var/run/mariadb/mysqld.sock

!includedir /etc/my.cnf.d
EOL

==============================================================================

Configure Pacemaker Cluster

Start:
  mysql_fs01 -> mysql_service01 -> mysql_VIP01
Stop:
  mysql_VIP01 -> mysql_service01 -> mysql_fs01

==============================================================================

[db1]# pcs cluster cib clust_cfg

stonith の無効化
[db1]# pcs -f clust_cfg property set stonith-enabled=false

quorum の無効化
[db1]# pcs -f clust_cfg property set no-quorum-policy=ignore

failback を抑制する
[db1]# pcs -f clust_cfg resource defaults resource-stickiness=200

==============================================================================

[db1]# pcs -f clust_cfg resource create mysql_data ocf:linbit:drbd \
  drbd_resource=mysql \
  op monitor interval=30s

==============================================================================

[db1]# pcs -f clust_cfg resource master MySQLClone mysql_data \
  master-max=1 master-node-max=1 \
  clone-max=2 clone-node-max=1 \
  notify=true

master-max:
  how many copies of the resource can be promoted to master status,

master-node-max:
  how many copies of the resource can be promoted to master status
  on a single node,

clone-max:
  how many copies of the resource to start. Defaults to the number
  of nodes in the cluster,

clone-node-max:
  how many copies of the resource can be started on a single node,

notify:
  when stopping or starting a copy of the clone, tell all the other
  copies beforehand and when the action was successful.

==============================================================================

[db1]# pcs -f clust_cfg resource create mysql_fs Filesystem \
  device="/dev/drbd0" \
  directory="/var/lib/mysql" \
  fstype="xfs"

==============================================================================

[db1]# pcs -f clust_cfg constraint colocation add mysql_fs with MySQLClone \
  INFINITY with-rsc-role=Master

==============================================================================

[db1]# pcs -f clust_cfg constraint order promote MySQLClone then start mysql_fs

==============================================================================

[db1]# pcs -f clust_cfg resource create mysql_service ocf:heartbeat:mysql \
  binary="/usr/bin/mysqld_safe" \
  config="/etc/my.cnf" \
  datadir="/var/lib/mysql" \
  pid="/var/lib/mysql/mysql.pid" \
  socket="/var/lib/mysql/mysql.sock" \
  additional_parameters="--bind-address=0.0.0.0" \
  op start timeout=60s \
  op stop timeout=60s \
  op monitor interval=20s timeout=30s

==============================================================================

[db1]# pcs -f clust_cfg constraint colocation add mysql_service with mysql_fs INFINITY

==============================================================================

[db1]# pcs -f clust_cfg constraint order mysql_fs then mysql_service

==============================================================================

[db1]# pcs -f clust_cfg resource create mysql_VIP ocf:heartbeat:IPaddr2 \
 ip=192.168.122.10 cidr_netmask=32 \
 op monitor interval=30s

IPaddr – manages virtual IPv4 addresses (portable version),
IPaddr2 – manages virtual IPv4 addresses (Linux specific version).

==============================================================================

[db1]# pcs -f clust_cfg constraint colocation add mysql_VIP with mysql_service INFINITY

==============================================================================

[db1]# pcs -f clust_cfg constraint order mysql_service then mysql_VIP

==============================================================================

[db1]# pcs -f clust_cfg constraint

```
[root@db1 ~]# pcs -f clust_cfg constraint
Location Constraints:
Ordering Constraints:
  promote MySQLClone then start mysql_fs (kind:Mandatory)
  start mysql_fs then start mysql_service (kind:Mandatory)
  start mysql_service then start mysql_VIP (kind:Mandatory)
Colocation Constraints:
  mysql_fs with MySQLClone (score:INFINITY) (with-rsc-role:Master)
  mysql_service with mysql_fs (score:INFINITY)
  mysql_VIP with mysql_service (score:INFINITY)
Ticket Constraints:
```

==============================================================================

[db1]# pcs -f clust_cfg resource show

```
[root@db1 ~]# pcs -f clust_cfg resource show
 Master/Slave Set: MySQLClone [mysql_data]
     Stopped: [ db1 db2 ]
 mysql_fs	(ocf::heartbeat:Filesystem):	Stopped
 mysql_service	(ocf::heartbeat:mysql):	Stopped
 mysql_VIP	(ocf::heartbeat:IPaddr2):	Stopped
```

==============================================================================

We can commit changes now and check cluster status

[db1]# pcs cluster cib-push clust_cfg

==============================================================================

[db1]# pcs status

==============================================================================