1. Proxmox5(drbd8.9+corosync2.4+pacemaker1.1)でHA MySQL構築をやってみた

proxmox5ではcorosyncが最初から入っている。
drbd8とpacemakerも簡単にインストール出来た。
[drbd9にしようとする](https://qiita.com/tukiyo3/items/72311b29995bec456ab6)とkernelモジュール作成の必要があり時間がかかるのと、drbdmanageがうまく動かないところで詰まった。

```sh:fdisk
fdisk /dev/vdb
n p enter enter enter
t 8e
w
```

```sh:drbd用にボリューム用意
pvcreate /dev/vdb1
vgcreate vg1 /dev/vdb1
lvcreate --name mysql-drbd --size 3.99g vg1
```

```rb:/etc/hosts
192.168.100.55 proxmox55
192.168.100.56 proxmox56
```

```
apt update; 
apt-get install -y \
 drbd-utils pacemaker crmsh
```

```ruby:/etc/drbd.d/mysqldrbd.res
resource mysqldrbd {
        protocol C;

        handlers {
           pri-on-incon-degr "/usr/lib/drbd/notify-pri-on-incon-degr.sh; /usr/lib/drbd/notify-emergency-reboot.sh; echo b > /proc/sysrq-trigger; reboot -f";
           pri-lost-after-sb "/usr/lib/drbd/notify-pri-lost-after-sb.sh; /usr/lib/drbd/notify-emergency-reboot.sh; echo b > /proc/sysrq-trigger; reboot -f";
           local-io-error "/usr/lib/drbd/notify-io-error.sh; /usr/lib/drbd/notify-emergency-shutdown.sh; echo o > /proc/sysrq-trigger; halt -f";
           fence-peer "/usr/lib/drbd/crm-fence-peer.sh";
        }

        startup {
           degr-wfc-timeout 120; # 2 minutes.
           outdated-wfc-timeout 2; #2 seconds
        }

        disk {
           on-io-error detach;
        }

        net {
           cram-hmac-alg "sha1";
           shared-secret "mysqldrbd";
           after-sb-0pri disconnect;
           after-sb-1pri disconnect;
           after-sb-2pri disconnect;
           rr-conflict disconnect;
        }

        syncer {
           rate 50M;
           al-extents 257;
           on-no-data-accessible io-error;
        }

        on proxmox55 {
           device      /dev/drbd0;
           disk        /dev/vg1/mysql-drbd;
           address     192.168.100.55:7788;
           meta-disk   internal;
        }

        on proxmox56 {
           device      /dev/drbd0;
           disk        /dev/vg1/mysql-drbd;
           address     192.168.100.56:7788;
           meta-disk   internal;
        }
}
```

* OS一度再起動して以下実施

```sh:
modprobe drbd

drbdadm create-md mysqldrbd

# (両方のサーバーでほぼ同時に実行しないとコマンドが完了しない)
service drbd start

# (一台で実施)primaryに指定
drbdadm -- --overwrite-data-of-peer primary all
```

```sh:同期状況の確認(UpToDate/UpToDateになれば同期完了)
drbd-overview 
 0:mysqldrbd/0  SyncTarget Secondary/Primary Inconsistent/UpToDate 
	[=>..................] sync'ed: 12.0% (3688284/4185948)K
```

```
mkfs.ext4 /dev/drbd0
mkdir /var/lib/mysql
mount /dev/drbd0 /var/lib/mysql/

# primaryをsecondaryに切り替える
umount /var/lib/mysql
drbdadm secondary mysqldrbd

# standby側で実施
drbdadm primary mysqldrbd
mkdir /var/lib/mysql
mount /dev/drbd0 /var/lib/mysql/

# もとに戻す
umount /var/lib/mysql
drbdadm secondary mysqldrbd

drbdadm primary mysqldrbd
```

## corosync pacemaker

```sh:
corosync-keygen -l

scp /etc/corosync/authkey root@proxmox56:/etc/corosync/
```

```rb:/etc/corosync/corosync.conf
totem {
    version: 2
 
    # How long before declaring a token lost (ms)
    token: 3000
 
    # How many token retransmits before forming a new configuration
    token_retransmits_before_loss_const: 10
 
    # How long to wait for join messages in the membership protocol (ms)
    join: 60
 
    # How long to wait for consensus to be achieved before starting a new round of membership configuration (ms)
    consensus: 3600
 
    # Turn off the virtual synchrony filter
    vsftype: none
 
    # Number of messages that may be sent by one processor on receipt of the token
    max_messages: 20
 
    # Limit generated nodeids to 31-bits (positive signed integers)
    clear_node_high_bit: yes
 
    # Disable encryption
    #secauth: off
    secauth: on
 
    # How many threads to use for encryption/decryption
    threads: 0
 
    # Optionally assign a fixed node id (integer)
    # nodeid: 1234
 
    # This specifies the mode of redundant ring, which may be none, active, or passive.
    rrp_mode: none
 
    interface {
        # The following values need to be set based on your environment
        ringnumber: 0
        bindnetaddr: 192.168.100.0
        #bindnetaddr: 127.0.0.1
        mcastaddr: 226.94.1.1
        mcastport: 5405
    }
}
 
amf {
    mode: disabled
}
 
quorum {
    # Quorum for the Pacemaker Cluster Resource Manager
    provider: corosync_votequorum
    expected_votes: 1
}
 
aisexec {
    user:   root
    group:  root
}
 
logging {
    fileline: off
    to_stderr: yes
    to_logfile: no
    to_syslog: yes
    syslog_facility: daemon
    debug: off
    timestamp: on
    logger_subsys {
        subsys: AMF
        debug: off
        tags: enter|leave|trace1|trace2|trace3|trace4|trace6
    }
}
```

```
scp /etc/corosync/corosync.conf root@proxmox56:/etc/corosync/
```

```sh:/etc/default/corosync
START=yes
```

```
scp /etc/default/corosync root@proxmox56:/etc/default/
```

```
mkdir /etc/corosync/service.d
```

```sh:/etc/corosync/service.d/pcmk
service {
    # Load the Pacemaker Cluster Resource Manager
    name: pacemaker
    ver: 1
}
```

```
scp -r /etc/corosync/service.d/ root@proxmox56:/etc/corosync/
```

## crm


```sh:
crm status
```

## mysql

```
wget https://github.com/tukiyo/mysql4.0php5.2/releases/download/debian9/opt-mysql4_4.0.30-1_amd64.deb

dpkg -i opt-mysql4_4.0.30-1_amd64.deb
```

```ruby:/etc/my.cnf
[mysqld]
socket=/var/run/mysqld/mysqld.sock2
datadir=/opt/mysql/var/

default-character-set=sjis
max_allowed_packet=512M # for insert big data

log=/tmp/query.log
log-long-format

log_slow_queries=/tmp/mysql-slow.log
long_query_time = 1
#log-queries-not-using-indexes

query_cache_limit=16M
query_cache_size=256M

basedir=/opt/mysql

[client]
socket=/var/run/mysqld/mysqld.sock2
```

```
scp /etc/my.cnf root@proxmox56:/etc/
```

```sh:mysqlアカウント作成
addgroup --system mysql

adduser --system \
 --group \
 --no-create-home \
 --home /opt/mysql \
 --shell /sbin/nologin \
 --disabled-password \
 --disabled-login \
 mysql 
```

```sh:
chown -R mysql:mysql /var/lib/mysql/

mkdir -p /var/run/mysqld/
chown -R mysql:mysql /var/run/mysqld/

# 以下片方だけで実施
drbdadm primary mysqldrbd
mount /dev/drbd0 /var/lib/mysql

mkdir -p /var/lib/mysql/data
chown -R mysql:mysql /var/lib/mysql/data
chmod 700 /var/lib/mysql/data

# mysql_install_dbが/usr/bin/hostnameを参照しているため
ln -s /bin/hostname /usr/bin/hostname
/opt/mysql/bin/mysql_install_db --no-defaults --datadir=/var/lib/mysql/data --user=mysql
```

## crmshで設定


```sh:設定前のためエラー出力を確認
crm_verify -L -V
   error: unpack_resources:	Resource start-up disabled since no STONITH resources have been defined
   error: unpack_resources:	Either configure some or disable STONITH with the stonith-enabled option
   error: unpack_resources:	NOTE: Clusters with shared data need STONITH to ensure data integrity
Errors found during check: config not valid

```

```sh:片方のノードだけで実行
crm configure node proxmox55 attributes standby=off
crm configure node proxmox56 attributes standby=off

crm configure property stonith-enabled=false
crm configure property no-quorum-policy=ignore

crm configure rsc_defaults resource-stickiness=100


crm configure primitive p_drbd_mysqldrbd ocf:linbit:drbd params drbd_resource="mysqldrbd" op monitor interval="15s"
crm configure ms ms_drbd_mysqldrbd p_drbd_mysqldrbd meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

crm configure primitive p_fs_mysqldrbd ocf:heartbeat:Filesystem params device="/dev/drbd0" directory="/var/lib/mysql" fstype="ext4"
crm configure primitive p_ip_mysqldrbd ocf:heartbeat:IPaddr2 params ip="192.168.100.57" cidr_netmask="24" nic="vmbr0" iflabel="0"
crm configure primitive p_mysqldrbd ocf:heartbeat:mysql params binary="/opt/mysql/bin/mysqld_safe" client_binary="/opt/mysql/bin/mysql" config="/etc/my.cnf" datadir="/var/lib/mysql/data" pid="/var/run/mysqld/mysql.pid" socket="/var/run/mysqld/mysqld.sock2" user="mysql" group="mysql" op monitor interval="60s" meta target-role="Started"

crm configure group g_mysqldrbd p_fs_mysqldrbd p_ip_mysqldrbd p_mysqldrbd
crm configure colocation c_mysql_on_drbd inf: g_mysqldrbd ms_drbd_mysqldrbd:Master
crm configure order o_drbd_before_mysql inf: ms_drbd_mysqldrbd:promote g_mysqldrbd:start
```

```rb:
sh crm.sh
WARNING: p_drbd_mysqldrbd: default timeout 20s for start is smaller than the advised 240
WARNING: p_drbd_mysqldrbd: default timeout 20s for stop is smaller than the advised 100
WARNING: p_drbd_mysqldrbd: action 'monitor' not found in Resource Agent meta-data
WARNING: p_fs_mysqldrbd: default timeout 20s for start is smaller than the advised 60
WARNING: p_fs_mysqldrbd: default timeout 20s for stop is smaller than the advised 60
```


```sh:出力が何もないことを確認。
crm_verify -L -V
```

* OS再起動。

## 状態確認

![crm.png](https://qiita-image-store.s3.amazonaws.com/0/25728/618fbec9-6dbe-9d6c-359e-0641f8a61a6f.png)

初回のみdrbd-overviewで同期完了を待つ(1時間はかかる。)
![スクリーンショット_2017-12-18_10-46-36.png](https://qiita-image-store.s3.amazonaws.com/0/25728/8e2855c6-74e9-d4ea-da0d-91a365afcbb0.png)


```sh:切り替えテスト
# 通常Masterのproxmox55をslaveにして、proxmox56をMasterにする
/usr/sbin/crm node standby proxmox55

# slaveにしたproxmox55をonlineにする
/usr/sbin/crm node online proxmox55
```