When we cannot setup mysql cluster, but we need data consistency important, then "Mysql with drbd" is another option.
While mysql replication does not guarantee consistency, drbd with mysql can provide better(almost perfect with exception such as network error or power failure) one. Split brain could occur in such case.
1. Setup labs.(using virtualbox and vagrant)
#
whatsup@whatsup-To-be-filled-by-O-E-M ~ $ mkdir md
whatsup@whatsup-To-be-filled-by-O-E-M ~ $ cd md
whatsup@whatsup-To-be-filled-by-O-E-M ~/md $ vi Vagrantfile
# -*- mode: ruby -*-
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
config.vm.define "host1" do |host1|
host1.vm.box = "host1"
host1.vm.box_url = "file:///home/whatsup/vg/ubuntu14.box"
host1.vm.provision "shell", inline: "echo now time to executing shell"
host1.vm.provision "shell", inline: "echo timezone config; echo 'Asia/Seoul' > /etc/timezone && dpkg-reconfigure --frontend noninteractive tzdata"
host1.vm.provision "shell", inline: "apt-get update && apt-get upgrade"
host1.vm.network "private_network",ip:"10.0.0.25"
host1.vm.host_name = "host1"
disk=['/home/whatsup/vg/host1_disk1.vdi','/home/whatsup/vg/host1_disk2.vdi','/home/whatsup/vg/host1_disk3.vdi']
disk_count=disk.size
host1.vm.provider :virtualbox do |vb|
for i in (0..(disk_count-1))
unless File.exist?(disk[i])
vb.customize ["createhd",'--filename',disk[i],'--size',10240]
end
vb.customize ['storageattach', :id,'--storagectl','SATA', '--port',i+1,'--device',0,'--type','hdd','--medium',disk[i]]
end
vb.customize ["modifyvm", :id,"--memory","1024"]
vb.cpus = 1
end
end
config.vm.define "host2" do |host2|
host2.vm.box = "host2"
host2.vm.box_url = "file:///home/whatsup/vg/ubuntu14.box"
host2.vm.provision "shell", inline: "echo now time to executing shell"
host2.vm.provision "shell", inline: "echo timezone config; echo 'Asia/Seoul' > /etc/timezone && dpkg-reconfigure --frontend noninteractive tzdata"
host2.vm.provision "shell", inline: "apt-get update && apt-get upgrade"
host2.vm.network "private_network",ip:"10.0.0.26"
host2.vm.host_name = "host2"
disk=['/home/whatsup/vg/host2_disk1.vdi','/home/whatsup/vg/host2_disk2.vdi','/home/whatsup/vg/host2_disk3.vdi']
disk_count=disk.size
host2.vm.provider :virtualbox do |vb|
for i in (0..(disk_count-1))
unless File.exist?(disk[i])
vb.customize ["createhd",'--filename',disk[i],'--size',10240]
end
vb.customize ['storageattach', :id,'--storagectl','SATA', '--port',i+1,'--device',0,'--type','hdd','--medium',disk[i]]
end
vb.customize ["modifyvm", :id,"--memory","1024"]
vb.cpus = 1
end
end
end
whatsup@#vagrant up host1 host2
whatsup@whatsup-To-be-filled-by-O-E-M ~/md $ vagrant status
Current machine states:
host1 running (virtualbox)
host2 running (virtualbox)
#Lvm setup
root@host1:~# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x47c0a9fa.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1):
Using default value 1
First sector (2048-20971519, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-20971519, default 20971519):
Using default value 20971519
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
root@host1:~# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x5d72ef97.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
Using default value 1
First sector (2048-20971519, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-20971519, default 20971519):
Using default value 20971519
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
root@host1:~# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xb2701e38.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
Using default value 1
First sector (2048-20971519, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-20971519, default 20971519):
Using default value 20971519
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
root@host1:~# pvcreate /dev/sdb1 /dev/sdc1 /dev/sdd1
Physical volume "/dev/sdb1" successfully created
Physical volume "/dev/sdc1" successfully created
Physical volume "/dev/sdd1" successfully created
root@host1:~#
root@host1:~# vgcreate host1_vg /dev/sdb1 /dev/sdc1 /dev/sdd1
Volume group "host1_vg" successfully created
root@host1:~# vgdisplay
--- Volume group ---
VG Name host1_vg
System ID
Format lvm2
Metadata Areas 3
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 3
Act PV 3
VG Size 29.99 GiB
PE Size 4.00 MiB
Total PE 7677
Alloc PE / Size 0 / 0
Free PE / Size 7677 / 29.99 GiB
VG UUID inTGbG-UBXA-vXZa-e2of-xohq-pUu2-wG0Epk
root@host1:~# lvcreate --name mysql-drbd --size 20G host1_vg
Logical volume "mysql-drbd" created
root@host1:~#
On host2 , also create mysql-drbd lvm.
#result
root@host1:~# lvs
LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert
mysql-drbd host1_vg -wi-a---- 20.00g
root@host2:~# lvs
LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert
mysql-drbd host2_vg -wi-a---- 20.00g
=============================================
#Name resolving via /etc/hosts
root@host1:~# vi /etc/hosts
root@host1:~# cat /etc/hosts
127.0.0.1 localhost
127.0.1.1 host1 host1
10.0.0.25 host1
10.0.0.26 host2
root@host2:~# vi /etc/hosts
127.0.0.1 localhost
127.0.1.1 host1 host1
10.0.0.25 host1
10.0.0.26 host2
#drbd install
On ubuntu,
root@host1:~# sudo apt-get install drbd8-utils
root@host2:~# sudo apt-get install drbd8-utils
root@host1:~# vi /etc/drbd.conf
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 10M;
al-extents 257;
on-no-data-accessible io-error;
}
on host1{
device /dev/drbd0;
disk /dev/host1_vg/mysql-drbd;
address 10.0.0.25:7788;
meta-disk internal;
}
on host2{
device /dev/drbd0;
disk /dev/host2_vg/mysql-drbd;
address 10.0.0.26:7788;
meta-disk internal;
}
}
Copy to host2 /etc/drbd.conf
root@host2:~# vi /etc/drbd.conf
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 10M;
al-extents 257;
on-no-data-accessible io-error;
}
on host1{
device /dev/drbd0;
disk /dev/host1_vg/mysql-drbd;
address 10.0.0.25:7788;
meta-disk internal;
}
on host2{
device /dev/drbd0;
disk /dev/host2_vg/mysql-drbd;
address 10.0.0.26:7788;
meta-disk internal;
}
}
#Creating drbd device on each host
root@host1:~# drbdadm create-md mysqldrbd
root@host1:~# drbdadm create-md mysqldrbd
Writing meta data...
initializing activity log
NOT initializing bitmap
New drbd meta data block successfully created.
--== Creating metadata ==--
As with nodes, we count the total number of devices mirrored by DRBD
at http://usage.drbd.org.
The counter works anonymously. It creates a random number to identify
the device and sends that random number, along with the kernel and
DRBD version, to usage.drbd.org.
http://usage.drbd.org/cgi-bin/insert_usage.pl?nu=10578279484940011345&ru=12472199146912316707&rs=21474836480
* If you wish to opt out entirely, simply enter 'no'.
* To continue, just press [RETURN]
success
root@host2:~# drbdadm create-md mysqldrbd
Writing meta data...
initializing activity log
NOT initializing bitmap
New drbd meta data block successfully created.
--== Creating metadata ==--
As with nodes, we count the total number of devices mirrored by DRBD
at http://usage.drbd.org.
The counter works anonymously. It creates a random number to identify
the device and sends that random number, along with the kernel and
DRBD version, to usage.drbd.org.
http://usage.drbd.org/cgi-bin/insert_usage.pl?nu=2108185229951619448&ru=17615320328886740552&rs=21474836480
* If you wish to opt out entirely, simply enter 'no'.
* To continue, just press [RETURN]
success
root@host1:~# service drbd start
Just press [RETURN] to continue:
[
create res: mysqldrbd
prepare disk: mysqldrbd
adjust disk: mysqldrbd
adjust net: mysqldrbd
]
...... [ OK
root@host2:~# service drbd start
Just press [RETURN] to continue:
[
create res: mysqldrbd
prepare disk: mysqldrbd
adjust disk: mysqldrbd
adjust net: mysqldrbd
]
...... [ OK
root@host1:~# drbdadm -- --overwrite-data-of-peer primary all
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
... sync'ed: 5.4% (19384/20476)Mfinish: 0:03:14 101,932 (101,932) K/sec
0:mysqldrbd SyncSource Primary/Secondary UpToDate/Inconsistent C
root@host2:~# drbdadm secondary mysqldrbd
root@host2:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
... sync'ed: 15.5% (17324/20476)Mfinish: 0:02:51 103,320 (100,960) want: 102,400 K/sec
0:mysqldrbd SyncTarget Secondary/Primary Inconsistent/UpToDate C
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
... sync'ed: 99.9% (32/20476)Mfinish: 0:00:00 102,828 (102,124) K/sec
0:mysqldrbd SyncSource Primary/Secondary UpToDate/Inconsistent C
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd Connected Primary/Secondary UpToDate/UpToDate C
root@host2:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd Connected Secondary/Primary UpToDate/UpToDate C
#100% synced.
#Let's do some test.
root@host1:~# mkfs.ext4 /dev/drbd0
root@host1:~# mkdir /mnt/mysql
root@host1:~# mount /dev/drbd0 /mnt/mysql/
root@host1:~# vi /mnt/mysql/test
I'm from host1
root@host1:~# umount /mnt/mysql
root@host1:~# drbdadm secondary mysqldrbd
root@host1:~#
#On host 2
root@host2:~# drbdadm primary mysqldrbd
root@host2:~# mkdir /mnt/mysql
root@host2:~# mount /dev/drbd0 /mnt/mysql/
root@host2:~# cat /mnt/mysql/test
I'm from host1
#Well, usually drbd works as active and standby and we can connect to directly only active server. And standby only synchonize on block-level(not file level such as gluster-www.gluster.org)
#We can change active and standby each other.
root@host2:~# umount /mnt/mysql
root@host2:~# drbdadm secondary mysqldrbd
root@host1:~# drbdadm primary mysqldrbd
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd Connected Primary/Secondary UpToDate/UpToDate C
#corosync and pacemaker setup
#VIP(virtual ip) is 10.0.0.55
root@host1:~# apt-get install corosync pacemaker
root@host2:~# apt-get install corosync pacemaker
root@host1:~# corosync-keygen
Corosync Cluster Engine Authentication key generator.
Gathering 1024 bits for key from /dev/random.
Press keys on your keyboard to generate entropy.
Press keys on your keyboard to generate entropy (bits = 880).
Press keys on your keyboard to generate entropy (bits = 928).
Writing corosync key to /etc/corosync/authkey.
#Because I use ssh (vagrant ssh host1), I had to use another terminal to ssh host1 and then, using wget command download anything such kernel-file..or web files. ex) wget http://kernel.org/recent-kernel.tar.gz. Thie will give entropy to generate authkey of corosync. Copy authkey to host2 /etc/corosync/
#Here is simple tip for ssh newbie to copy auth key to host2. Though, usually do not generate root ssh key(this is a simple local virtual lab environment)
copy the key with mouse,
root@host2:~# mkdir ~/.ssh; chmod 700 ~/.ssh; vi ~/.ssh/authorized_keys
root@host2:~# chmod 600 ~/.ssh/authorized_keys
#Ok now do scp
root@host1:~# scp /etc/corosync/authkey root@10.0.0.26:/etc/corosync/
The authenticity of host '10.0.0.26 (10.0.0.26)' can't be established.
ECDSA key fingerprint is 05:b6:b4:d5:f5:b5:42:1c:c3:84:1f:1f:81:d9:53:5f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.0.0.26' (ECDSA) to the list of known hosts.
authkey 100% 128 0.1KB/s 00:00
#Corosync.conf
root@host1:~#vi /etc/corosync/corosync.conf
totem {
version: 2
token: 3000
token_retransmits_before_loss_const: 10
join: 60
consensus: 3600
vsftype: none
max_messages: 20
clear_node_high_bit: yes
secauth: on
threads: 0
rrp_mode: none
interface {
# The following values need to be set based on your environment
ringnumber: 0
bindnetaddr: 10.0.0.0
mcastaddr: 226.94.1.1
mcastport: 5405
}
}
amf {
mode: disabled
}
quorum {
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 corosync.conf to host2(10.0.0.26)
root@host1:~# scp /etc/corosync/corosync.conf root@10.0.0.26:/etc/corosync/
corosync.conf 100% 855 0.8KB/s 00:00
root@host1:~#
root@host1:/etc/corosync# cat /etc/default/corosync
# start corosync at boot [yes|no]
START=no
root@host11:/etc/corosync# sed -i 's/no/yes/g' /etc/default/corosync
root@host1:/etc/corosync# cat /etc/default/corosync
# start corosync at boot [yes|yes]
START=yes
root@host2:~/.ssh# sed -i 's/no/yes/g' /etc/default/corosync
root@host2:~/.ssh# cat /etc/default/corosync
# start corosync at boot [yes|yes]
START=yes
root@host1:~# vi /etc/corosync/service.d/pcmk
service {
# Load the Pacemaker Cluster Resource Manager
name: pacemaker
ver: 1
}
root@host1:~# scp /etc/corosync/service.d/pcmk root@10.0.0.26:/etc/corosync/service.d/
pcmk 100% 81 0.1KB/s 00:00
root@host1:~#
#Restart corosync pacemaker and crm_mon validation.
#corosync first(the order is important to work smoothly)
root@host2:/etc/corosync# service corosync restart; service pacemaker restart;
root@host1:~# service corosync restart; service pacemaker restart
~
root@host1:~# crm status
Last updated: Wed May 6 07:15:21 2015
Last change: Wed May 6 07:06:33 2015 via crmd on host1
Stack: corosync
Current DC: host1 (167772185) - partition with quorum
Version: 1.1.10-42f2063
2 Nodes configured
0 Resources configured
Online: [ host1 host2 ]
root@host2:/etc/corosync# crm status
Last updated: Wed May 6 07:19:55 2015
Last change: Wed May 6 07:06:33 2015 via crmd on host1
Stack: corosync
Current DC: host1 (167772185) - partition with quorum
Version: 1.1.10-42f2063
2 Nodes configured
0 Resources configured
Online: [ host1 host2 ]
root@host2:/etc/corosync#
#Well it is very ok..until now
#MySQL installation.
#On centos like,disable selinux,on debian like ubuntu, disable apparmor on mysql service.
On each host:
root@host1:~# apt-get install mysql-server-5.6
root@host1:# apt-get install apparmor-utils
root@host1:/mnt/mysql# aa-disable /etc/apparmor.d/usr.sbin.mysqld
Disabling /etc/apparmor.d/usr.sbin.mysqld.
root@host1:/mnt/mysql#
root@host2:~# apt-get install mysql-server-5.6
root@host1:# apt-get install apparmor-utils
root@host2:~# aa-disable /etc/apparmor.d/usr.sbin.mysqld
Disabling /etc/apparmor.d/usr.sbin.mysqld.
#host1(drbd active)
#Cause mysql_install_db looks for my-default.cnf on ubuntu14.04. Not a big hitch.
root@host1:~# cp /etc/mysql/my.cnf /usr/share/mysql/my-default.cnf
root@host1:~# chown -R mysql:mysql /mnt/mysql/
#host2
root@host2:~# chown -R mysql:mysql /mnt/mysql/
#host1(drbd active)
root@host1:/mnt/mysql# mount /dev/drbd0 /mnt/mysql/
root@host1:~# mkdir /mnt/mysql/data
root@host1:~# chown -R mysql:mysql /mnt/mysql/data/
root@host1:~# chmod 700 /mnt/mysql/data
root@host1:/mnt/mysql# mysql_install_db -no-defaults --datadir=/mnt/mysql/data --user=mysql
Tip)There should be no error, if it is, check apparmor. again.
Finally)
It is time to configure crm. Before that, drbd and mysql will be run by pacemaker.
root@host1:~# service drbd stop
* Stopping all DRBD resources
root@host2:~# service drbd stop
* Stopping all DRBD resources
root@host2:~# service mysql stop
root@host1:~# service mysql stop
I made line-based simple command line to file to be useful later.
root@host1:~# vi crc.sh
#!/usr/bin/env bash
crm configure rsc_defaults resource-stickiness=100
crm configure property stonith-enabled=false
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="/mnt/mysql" fstype="ext4"
crm configure primitive p_ip_mysqldrbd ocf:heartbeat:IPaddr2 params ip="10.0.0.55" cidr_netmask="24" nic="eth1"
crm configure primitive p_mysqldrbd ocf:heartbeat:mysql params binary="/usr/sbin/mysqld" config="/etc/mysql/my.cnf" datadir="/mnt/mysql/data" pid="/var/mysql/mysqld/mysql.pid" socket="/var/run/mysqld/mysql.sock" user="mysql" group="mysql" additional_parameters="--bind-address=10.0.0.55 --user=mysql" op start timeout=120s op stop timeout=120s op monitor interval=20s timeout=30s
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
root@host1:~# bash crc.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 advertised in meta-data, it may not be supported by the RA
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
#virtual ip check.
root@host1:~# ping 10.0.0.55
PING 10.0.0.55 (10.0.0.55) 56(84) bytes of data.
64 bytes from 10.0.0.55: icmp_seq=1 ttl=64 time=0.026 ms
...
root@host1:~# crm status
Last updated: Wed May 6 08:37:03 2015
Last change: Wed May 6 08:33:52 2015 via cibadmin on host1
Stack: corosync
Current DC: host1 (167772185) - partition with quorum
Version: 1.1.10-42f2063
2 Nodes configured
5 Resources configured
Online: [ host1 host2 ]
Master/Slave Set: ms_drbd_mysqldrbd [p_drbd_mysqldrbd]
Masters: [ host1 ]
Slaves: [ host2 ]
Resource Group: g_mysqldrbd
p_fs_mysqldrbd (ocf::heartbeat:Filesystem): Started host1
p_ip_mysqldrbd (ocf::heartbeat:IPaddr2): Started host1
p_mysqldrbd (ocf::heartbeat:mysql): Started host1
root@host1:~# vi crc2.sh
crm configure primitive p_ping ocf:pacemaker:ping params name="ping" multiplier="1000" host_list="10.0.0.1" op monitor interval="15s" timeout="60s" start timeout="60s"
crm configure clone cl_ping p_ping meta interleave="true"
crm configure location l_drbd_master_on_ping ms_drbd_mysqldrbd rule role="Master" -inf: not_defined ping or ping number:lte 0
root@host1:~# bash crc2.sh
WARNING: p_ping: default timeout 20s for start is smaller than the advised 60
root@host1:~# crm status
Last updated: Wed May 6 09:17:28 2015
Last change: Wed May 6 09:01:21 2015 via cibadmin on host1
Stack: corosync
Current DC: host2 (167772186) - partition with quorum
Version: 1.1.10-42f2063
2 Nodes configured
7 Resources configured
#Restart corosync and pacemaker.
#root#crm status
Online: [ host1 host2 ]
Master/Slave Set: ms_drbd_mysqldrbd [p_drbd_mysqldrbd]
Masters: [ host2 ]
Slaves: [ host1 ]
Resource Group: g_mysqldrbd
p_fs_mysqldrbd (ocf::heartbeat:Filesystem): Started host2
p_ip_mysqldrbd (ocf::heartbeat:IPaddr2): Started host2
p_mysqldrbd (ocf::heartbeat:mysql): Started host2
Clone Set: cl_ping [p_ping]
Started: [ host1 host2 ]
#Now disable mysql and drbd for system boot.
root@host1:~# vi /etc/init/mysql.conf
description "MySQL 5.6 Server"
author "Mario Limonciello <superm1@ubuntu.com>"
start on runlevel [345]
stop on starting rc RUNLEVEL=[0216]
#ubuntu mysql boot is done by /etc/init/mysql.conf,not by init script in /etc/init.d/mysql
#runlevel 2 is usually being used on debian like distro,ubuntu
#Disable drbd system boot
root@host1:~# update-rc.d -f drbd disable
update-rc.d: warning: start runlevel arguments (none) do not match drbd Default-Start values (2 3 4 5)
update-rc.d: warning: stop runlevel arguments (none) do not match drbd Default-Stop values (0 1 6)
Disabling system startup links for /etc/init.d/drbd ...
Removing any system startup links for /etc/init.d/drbd ...
/etc/rc0.d/K20drbd
/etc/rc1.d/K20drbd
/etc/rc2.d/S20drbd
/etc/rc3.d/S20drbd
/etc/rc4.d/S20drbd
/etc/rc5.d/S20drbd
/etc/rc6.d/K20drbd
Adding system startup for /etc/init.d/drbd ...
/etc/rc0.d/K20drbd -> ../init.d/drbd
/etc/rc1.d/K20drbd -> ../init.d/drbd
/etc/rc6.d/K20drbd -> ../init.d/drbd
/etc/rc2.d/K80drbd -> ../init.d/drbd
/etc/rc3.d/K80drbd -> ../init.d/drbd
/etc/rc4.d/K80drbd -> ../init.d/drbd
/etc/rc5.d/K80drbd -> ../init.d/drbd
Do above on host2 too.
#Mysql root password and allow from 10.0.0.25(host1)
From terminal
root@host1:~# mysqld_safe --skip-grant-tables --datadir=/mnt/mysql/data&
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> insert into user(user,host,password)values('root','10.0.0.25','test');
Query OK, 1 row affected, 3 warnings (0.01 sec)
mysql> update user set password=password('yourpass') where user='root' and host='10.0.0.25';
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *232BEE719F1B45FF4193133EE37DDA54dddddED4A2F47 |
| host1 | root | *232BEE719F1B45FF4193133EE37DDA5DDDAAA4ED4A2F47 |
| 127.0.0.1 | root | *232BEE719F1B45FF4193133EE37DD9999**A54ED4A2F47 |
| ::1 | root | *232BEE719F1B45FF4193133EE37D******DA54ED4A2F47 |
| localhost | | |
| host1 | | |
+-----------+------+-------------------------------------------+
6 rows in set (0.01 sec)
mysql> delete from user where user=' ';
Query OK, 2 rows affected (0.01 sec)
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *232B44444EE719F1B4555FF4193133EE37DDA54ED4A2F47 |
| host1 | root | *232BEE4444719F1B45FF4193133EE37DDA54ED4A2F47 |
| 127.0.0.1 | root | *232BE4444E719F1B445545FF4193133EE37DDA54ED4A2F47 |
| ::1 | root | *232BEE71444449F1B44445FF4193133EE37DDA54ED4A2F47 |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#When split brains occurs.
#^^; I accidently create split-brain because I configured mysql root password later. Now most recently update mysql data is in host2. Drbd status is like belows.
Drbd mount is done on host2. I updated password when host2 mount mysql drbd block device like belows.
root@host2:~# df /mnt/mysql/
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/drbd0 20510680 157832 19287924 1% /mnt/mysql
#See below's Secondary/Unknown.
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd StandAlone Secondary/Unknown UpToDate/DUnknown r-----
root@host2:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd StandAlone Primary/Unknown UpToDate/DUnknown r----- ext4
#So, now host2 /dev/drbd0 has recently update data.
#discard host1 data like belows.
root@host1:~# drbdadm secondary mysqldrbd
root@host1:~# drbdadm -- --discard-my-data connect mysqldrbd
#connect mysqldrbd drbd service on host2.
root@host2:~# drbdadm connect mysqldrbd
#Confirm if drbd state updated or not.
root@host2:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd Connected Primary/Secondary UpToDate/UpToDate C /mnt/mysql ext4
root@host1:~# service drbd status
drbd driver loaded OK; device status:
version: 8.4.3 (api:1/proto:86-101)
srcversion: F97798065516C94BE0F27DC
m:res cs ro ds p mounted fstype
0:mysqldrbd Connected Secondary/Primary UpToDate/UpToDate C
#Connecting mysql using virtual ip. Now only from 10.0.0.25 , we can connect to database.
#Cannot connect.
root@host1:~# mysql -u root -h 10.0.0.55
ERROR 1045 (28000): Access denied for user 'root'@'host1' (using password: NO)
#It is possible only from 10.0.0.25 using password.
root@host1:~# mysql -u root -h 10.0.0.55 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
root@host2:~# mysql -h 10.0.0.55 -u root -p
Enter password:
ERROR 1130 (HY000): Host 'host2' is not allowed to connect to this MySQL server
#Just in case, let's allow one more ip that can connect to database.
mysql> insert into user(host,user,password)values('10.0.0.1','root',password('ddpass'));
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#Well, it is all done.
#Thanks for reading. This simple? article might help . ^^;
No comments:
Post a Comment