COROSYNC+PACEMAKER+MYSQL+DRBD enables high availability of MySQL

# Link up: http://xiong51.blog.51cto.com/5239058/1929054

########### node1 MySQL installation starts ##################

[Email protected] ~]# pcs cluster stop--all

[[email protected] ~]# systemctl start DRBD

[[email protected] ~]# systemctl start DRBD

# set node 1 as the primary node mount disk configuration mariadb

[Email protected] ~]# Drbdadm primary Mystore

[Email protected] ~]# Drbd-overview

0:mystore/0 Connected primary/secondary uptodate/uptodate

[[email protected] ~]# groupadd-r-G MySQL

[[email protected] ~]# useradd-r-g 3000-u 3001 MySQL

# mount the DRBD disk and create the directory

[Email protected] ~]# mount/dev/drbd0/mydata/

[Email protected] ~]# Mkdir/mydata/mysql

# set directory permissions for MySQL group, MySQL user

[Email protected] ~]# Chown mysql.mysql/mydata/mysql/-R

# Unzip to/usr/local and soft-link to MySQL directory

[Email protected] ~]# tar XF mariadb-5.5.54-linux-x86_64.tar.gz-c/usr/local/

[Email protected] ~]# CD!$

[Email protected] local]# chown mysql.mysql mariadb-5.5.54-linux-x86_64/-R

[Email protected] local]# LN-SV mariadb-5.5.54-linux-x86_64/mysql

[Email protected] local]# CD mysql/

[email protected] mysql]# CP support-files/my-large.cnf/etc/my.cnf

# Add file attributes, do not check names, add the following lines to the directory

[Email protected] mysql]# VIM/ETC/MY.CNF

innodb_file_per_table = 1

Skip_name_resolve = 1

DataDir =/mydata/mysql

Basedir =/usr/local/mysql

# Initialize Database

[Email protected] mysql]#/scripts/mysql_install_db--user=mysql--group=mysql--basedir=/usr/local/mysql/-- datadir=/mydata/mysql/

# Copy Startup script

[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld

[Email protected] mysql]# chmod +x/etc/init.d/mysqld

# installation Complete directly

[[Email protected] mysql]# service mysqld start

Starting mysql.170524 17:12:04 mysqld_safe Logging to '/mydata/mysql/node1.err '.

170524 17:12:04 Mysqld_safe starting mysqld daemon with databases From/mydata/mysql

.. success!

# Set Environment variables

[Email protected] bin]# vim/etc/profile.d/mysql.sh

Export Path=/usr/local/mysql/bin: $PATH

[Email protected] bin]# source!$

# can be directly logged in to indicate installation success

[[email protected] bin]# MySQL

Welcome to the MariaDB Monitor. Commands End With; or \g.

Your MariaDB Connection ID is 2

Server VERSION:5.5.54-MARIADB MariaDB Server

[[Email protected] bin]# service mysqld stop

Shutting down MySQL ... success!

# unmount disk Note the above step to stop MySQL must do

[Email protected] mysql]# umount/mydata/

# set Node1 to slave node

[Email protected] bin]# Drbdadm secondary mystore

[Email protected] bin]# Drbd-overview

0:mystore/0 Connected secondary/secondary uptodate/uptodate

# Copy Configuration to another host

[Email protected] mysql]# scp/etc/my.cnf node2:/etc/

################## node1 MySQL Installation complete ###################

################## node2 MySQL installation starts ###################

# Create a new MySQL user for node 2 here

[[email protected] ~]# groupadd-r-G MySQL

[[email protected] ~]# useradd-r-g 3000-u 3001 MySQL

[Email protected] ~]# Drbdadm primary Mystore

[Email protected] ~]# Drbd-overview

0:mystore/0 Connected primary/secondary uptodate/uptodate

# mount the DRBD disk and create the directory

[Email protected] ~]# mount/dev/drbd0/mydata/

# Unzip to/usr/local and soft-link to MySQL directory

[Email protected] ~]# tar XF mariadb-5.5.54-linux-x86_64.tar.gz-c/usr/local/

[Email protected] ~]# CD!$

[Email protected] local]# chown mysql.mysql mariadb-5.5.54-linux-x86_64/-R

[Email protected] local]# LN-SV mariadb-5.5.54-linux-x86_64/mysql

[Email protected] local]# CD mysql/

#无需初始化, configure the startup file

[email protected] mysql]# CP support-files/mysql.server/etc/init.d/mysqld

[Email protected] mysql]# chmod +x/etc/init.d/mysqld

# installation Complete directly

[[Email protected] mysql]# service mysqld start

Starting mysql.170524 17:12:04 mysqld_safe Logging to '/mydata/mysql/node2.err '.

170524 17:12:04 Mysqld_safe starting mysqld daemon with databases From/mydata/mysql

.. success!

# Set Environment variables

[Email protected] bin]# vim/etc/profile.d/mysql.sh

Export Path=/usr/local/mysql/bin: $PATH

[Email protected] bin]# source!$

# can be directly logged in to indicate installation success

[[Email protected] bin]# service mysqld stop

Shutting down MySQL ... success!

# unmount disk Note the above step to stop MySQL must do

[Email protected] mysql]# umount/mydata/

# set Node1 to slave node

[Email protected] bin]# Drbdadm secondary mystore

[Email protected] bin]# Drbd-overview

0:mystore/0 Connected secondary/secondary uptodate/uptodate

#################### node2 MySQL Installation complete #####################

# Configure high availability must stop DRBD

[[email protected] ~]# Systemctl stop DRBD

[[email protected] ~]# Systemctl stop DRBD

[Email protected] ~]# pcs cluster start--all

# define a virtual IP address

CRM (Live) configure# primitive mysqlip OCF:HEARTBEAT:IPADDR2 params ip=200

# define MySQL Startup service

CRM (Live) configure# primitive MySQLServer lsb:mysqld op monitor timeout=30s interval=20s

# Mandatory: can also be defined as INF

# definition Mysqlip must be with MySQLServer.

CRM (Live) configure# colocation mysqlip_with_mysqlserver inf:mysqlip MySQLServer

# define order Constraints Master-slave startup followed by Mount, (there is one in the sort constraint, which defines that mount must be with the master of DRBD)

CRM (Live) configure# order Mount_after_ms_mydatas_master Mandatory:ms_mydatas:promote Mount

# The order constraint is probably the following: DRBD (mounts directly when the primary node is started)--mysqlip--and MySQLServer

CRM (Live) configure# order Mysqlip_after_mount Mandatory:mysqlip Mount

CRM (Live) configure# order Mysqlserver_after_mysqlip Mandatory:mysqlip MySQLServer

################## #查看所有定义资源的信息 ####################

CRM (live) # Configure

CRM (live) configure# Show

Node 1:node1

Node 2:node2

Primitive Mount Filesystem \

params device= "/dev/drbd0" directory= "/mydata" FSTYPE=EXT4 \

Op start timeout=60s interval=0 \

Op Stop timeout=60s interval=0

Primitive Mydatas OCF:LINBIT:DRBD \

params drbd_resource=mystore \

OP monitor role=master interval=10s timeout=20s \

OP monitor role=slave interval=20s timeout=20s \

Op start timeout=240s interval=0 \

Op Stop timeout=100s interval=0

Primitive Mysqlip ipaddr \

params ip=192.168.8.200 broadcast=192.168.8.255

Primitive MySQLServer lsb:mysqld \

OP Monitor timeout=30s interval=20s

Ms Ms_mydatas Mydatas \

Meta clone-max=2 clone-node-max=1 master-max=1 master-node-max=1 notify=true

Order Mount_after_ms_mydatas_master Inf:ms_mydatas:promote Mount

colocation mount_with_ms_mydatas_master Inf:mount Ms_mydatas:master

Order Mysqlip_after_mount Mandatory:mysqlip Mount

colocation mysqlip_with_mysqlserver Inf:mysqlip MySQLServer

Order Mysqlserver_after_mysqlip Mandatory:mysqlip MySQLServer

##################################################################

############################# #状态查看 ##############################

CRM (live) # Show

ERROR:show:No such command

CRM (live) # Status

Last Updated:fri 26 11:51:42 2017

Last Change:fri-11:43:38 by the root via cibadmin on Node1

Stack:corosync

Current Dc:node1 (version 1.1.13-10.EL7-44EB2DD)-Partition with quorum

2 nodes and 5 resources configured

Online: [Node1 Node2]

Full list of resources:

Master/slave Set:ms_mydatas [Mydatas]

Masters: [Node1]

Slaves: [Node2]

Mount(ocf::heartbeat:filesystem):Started Node1

Mysqlip(ocf::heartbeat:ipaddr):Started Node1

MySQLServer(lsb:mysqld):Started Node1

####################################################################

# Toggle the node to see if the status can be transferred

CRM (live) # node

CRM (Live) node# standby Node1

# Error Hints

Master/slave Set:ms_mydatas [Mydatas]

Masters: [Node2]

Stopped: [Node1]

Mount(ocf::heartbeat:filesystem):Stopped

Mysqlip(ocf::heartbeat:ipaddr):Stopped

MySQLServer(lsb:mysqld):Stopped

Failed Actions:

* Mysqlserver_start_0 on Node2 ' Unknown error ' (1): call=216, Status=complete, exitreason= ' None ',

Last-rc-change= ' Fri may 11:52:53 ', queued=0ms, exec=1060ms

* Mount_start_0 on Node2 ' Unknown error ' (1): call=215, Status=complete, exitreason= ' couldn ' t mount Filesystem/dev/drbd0 On/mydata ',

Last-rc-change= ' Fri may 11:52:53 ', queued=0ms, exec=349ms

# Solution: When there is a situation that cannot be mounted, first clear the state, it may be that the previous node information has not been passed to another node

CRM (Live) node# clearstate Node2

Do you really want to drop State for node node2 (y/n)? Y

Waiting for 1 replies from the CRMD OK

########### #清空之后再查看 #############

CRM (live) # Status

Last Updated:fri 26 11:54:28 2017

Last Change:fri-11:52:47 by the root via Crm_attribute on Node1

Stack:corosync

Current Dc:node1 (version 1.1.13-10.EL7-44EB2DD)-Partition with quorum

2 nodes and 5 resources configured

Node Node1:standby

Online: [Node2]

Full list of resources:

Master/slave Set:ms_mydatas [Mydatas]

Masters: [Node2]

Stopped: [Node1]

Mount(ocf::heartbeat:filesystem):Started Node2

Mysqlip(ocf::heartbeat:ipaddr):Started Node2

MySQLServer(lsb:mysqld):Started Node2

########### #成功切换至另一节点

# Avoid Node1 recovery grab back, grab back may also lead to service not working properly, set stickiness

CRM (Live) configure# property default-resource-stickiness=100

# then set the Node1 to the online status

CRM (live) node# online

# Check the service again, Node2 still working

Resolution links for some common errors: http://blog.csdn.net/t1anyuan/article/details/52143789

http://litaotao.blog.51cto.com/6224470/1303307

COROSYNC+PACEMAKER+MYSQL+DRBD enables high availability of MySQL