mysql drbd pacemaker_Corosync+Pacemaker+DRBD+MySQL 实现高可用(HA)的MySQL集群

六、MySQL 安装与配置

1.安装Mysql

node1:

[root@node1 ~]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/

[root@node1 ~]# cd /usr/local/

[root@node1 local]# ln -sv mysql-5.5.33-linux2.6-x86_64 mysql

"mysql" -> "mysql-5.5.33-linux2.6-x86_64"

[root@node1 ~]# chown -R root:mysql /usr/local/mysql/*

[root@node1 ~]# scp mysql-5.5.33-linux2.6-x86_64.tar.gz node2:/root/

node2:

[root@node2 ~]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/

[root@node2 ~]# cd /usr/local/

[root@node2 local]# ln -sv mysql-5.5.33-linux2.6-x86_64 mysql

"mysql" -> "mysql-5.5.33-linux2.6-x86_64"

[root@node2 ~]# chown -R root:mysql /usr/local/mysql/*

2.创建Mysql用户与组

node1:

(1).创建mysql组

1 [root@node1 ~]# groupadd -g 3306 mysql

(2).创建mysql用户

[root@node1 ~]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql

(3).查看

[root@node1 ~]# id mysql

uid=3306(mysql) gid=3306(mysql) groups=3306(mysql)

node2:

(1).创建mysql组

[root@node2 ~]# groupadd -g 3306 mysql

(2).创建mysql用户

[root@node2 ~]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql

(3).查看

[root@node2 ~]# id mysql

uid=3306(mysql) gid=3306(mysql) groups=3306(mysql)

3.将node1的DRBD设置为主节点并挂载

[root@node1 ~]# drbd-overview

0:web/0  Connected Primary/Secondary UpToDate/UpToDate C r-----

[root@node1 ~]# mkdir /mydata

[root@node1 ~]# mount /dev/drbd0 /mydata/

[root@node1 ~]# cd /mydata/

[root@node1 mydata]# mkdir data

[root@node1 mydata]# chown -R  mysql.mysql /mydata/data/

[root@node1 mydata]# ll

总用量 20

drwxr-xr-x 2 mysql mysql  4096 8月  17 18:37 data

drwx------ 2 root  root  16384 8月  17 13:49 lost+found

4.提供配置文件

[root@node1 ~]# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf

[root@node1 ~]# vim /etc/my.cnf

#增加一行

datadir = /mydata/data

5.初始化Mysql

[root@node1 data]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql

[root@node1 data]# ll

总用量 8

drwx------ 2 mysql root 4096 8月  17 18:40 mysql

drwx------ 2 mysql root 4096 8月  17 18:40 test

6.提供启动脚本

[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@node1 ~]# chmod +x /etc/init.d/mysqld

7.启动并测试Mysql

[root@node1 data]# service mysqld start

Starting MySQL..... SUCCESS!

[root@node1 data]# /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test              |

+--------------------+

4 rows in set (0.08 sec)

mysql>

8.将node1上mysql配置文件与启动脚本复制到node2上

[root@node1 ~]# scp /etc/my.cnf node2:/etc/

my.cnf                                                                                100% 4687    4.6KB/s  00:00

[root@node1 ~]# scp /etc/init.d/mysqld node2:/etc/init.d/

mysqld                                                                                100%  11KB  10.6KB/s  00:00

9.关闭mysql并设置开机不启动

[root@node1 ~]# service mysqld stop

Shutting down MySQL. SUCCESS!

[root@node1 data]# chkconfig mysqld off

[root@node1 data]# chkconfig mysqld --list

mysqld            0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

10.将node2节点上的DRBD设置为主节点并挂载

[root@node1 ~]# umount /mydata/

[root@node1 ~]# drbdadm secondary web

[root@node1 ~]# drbd-overview

0:web/0  Connected Secondary/Secondary UpToDate/UpToDate C r-----

[root@node2 ~]# drbdadm primary web

[root@node2 ~]# drbd-overview

0:web/0  Connected Primary/Secondary UpToDate/UpToDate C r-----

[root@node2 ~]# mkdir /mydata

[root@node2 ~]# mount /dev/drbd0 /mydata/

[root@node2 ~]# cd /mydata/

[root@node2 mydata]# ll

总用量 20

drwxr-xr-x 5 mysql mysql  4096 8月  17 19:41 data

drwx------ 2 root  root  16384 8月  17 13:49 lost+found

11.启动并测试node2上的mysql

[root@node2 data]# service mysqld start

Starting MySQL... SUCCESS!

[root@node2 data]# /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>

12.关闭node2上mysql并设置开机不启动

[root@node2 ~]# service mysqld stop

Shutting down MySQL. SUCCESS!

[root@node2 ~]# chkconfig mysqld off

[root@node2 ~]# chkconfig mysqld --list

mysqld            0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

好了，到这里mysql配置全部完成，有一点得说明一下。启动mysql时，可能会出错查看一下错误日志会发现缺一下库文件 libaio，这里我们用yum install -y libaio安装一下，便能顺利启动。

七、crmsh 资源管理

1.关闭drbd并设置开机不启动

node1:

[root@node1 ~]# service drbd stop

Stopping all DRBD resources: .

[root@node1 ~]# chkconfig drbd off

[root@node1 ~]# chkconfig drbd --list

drbd              0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

node2:

12345 [root@node2 ~]# service drbd stop

Stopping all DRBD resources:

[root@node2 ~]# chkconfig drbd off

[root@node2 ~]# chkconfig drbd --list

drbd              0:关闭    1:关闭    2:关闭    3:关闭    4:关闭    5:关闭    6:关闭

2.增加drbd资源

[root@node1 ~]# crm

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

crm(live)# configure

crm(live)configure# property stonith-enabled=false

crm(live)configure# property no-quorum-policy=ignore

crm(live)configure# verify

crm(live)configure# commit

crm(live)configure# primitive mysqldrbd ocf:heartbeat:drbd params drbd_resource=web op start timeout=240 op stop timeout=100 op monitor role=Master interval=20 timeout=30 op monitor role=Slave interval=30 timeout=30

crm(live)configure# ms ms_mysqldrbd mysqldrbd meta master-max=1 master-node-max=1 clone-max=2 clone-node-max=1 notify=true

crm(live)configure# show

node node1.test.com

node node2.test.com

primitive mysqldrbd ocf:heartbeat:drbd \

params drbd_resource="web" \

op start timeout="240" interval="0" \

op stop timeout="100" interval="0" \

op monitor role="Master" interval="20" timeout="30" \

op monitor role="Slave" interval="30" timeout="30"

ms ms_mysqldrbd mysqldrbd \

meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

property $id="cib-bootstrap-options" \

dc-version="1.1.8-7.el6-394e906" \

cluster-infrastructure="classic openais (with plugin)" \

expected-quorum-votes="2" \

stonith-enabled="false" \

no-quorum-policy="ignore"

[root@node1 ~]# crm status

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

Last updated: Sat Aug 17 20:14:16 2013

Last change: Sat Aug 17 20:12:55 2013 via cibadmin on node1.test.com

Stack: classic openais (with plugin)

Current DC: node1.test.com - partition with quorum

Version: 1.1.8-7.el6-394e906

2 Nodes configured, 2 expected votes

2 Resources configured.

Online: [ node1.test.com node2.test.com ]

Master/Slave Set: ms_mysqldrbd [mysqldrbd]

Masters: [ node2.test.com ]

Slaves: [ node1.test.com ]

3.增加文件系统资源

crm(live)configure# primitive mystore ocf:heartbeat:Filesystem params device=/dev/drbd0 directory=/mydata fstype=ext3 op start timeout=60 op stop timeout=60

crm(live)configure# verify

crm(live)configure# colocation mystore_with_ms_mysqldrbd inf: ms_mysqldrbd:Master

ERROR: syntax in colocation: colocation mystore_with_ms_mysqldrbd inf: ms_mysqldrbd:Master

crm(live)configure# colocation mystore_with_ms_mysqldrbd inf: mystore ms_mysqldrbd:Master

crm(live)configure# order mystore_after_ms_mysqldrbd mandatory: ms_mysqldrbd:promote mystore:start

crm(live)configure# verify

crm(live)configure# commit

crm(live)configure# show

node node1.test.com

node node2.test.com

primitive mysqldrbd ocf:heartbeat:drbd \

params drbd_resource="web" \

op start timeout="240" interval="0" \

op stop timeout="100" interval="0" \

op monitor role="Master" interval="20" timeout="30" \

op monitor role="Slave" interval="30" timeout="30"

primitive mystore ocf:heartbeat:Filesystem \

params device="/dev/drbd0" directory="/mydata" fstype="ext3" \

op start timeout="60" interval="0" \

op stop timeout="60" interval="0"

ms ms_mysqldrbd mysqldrbd \

meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

colocation mystore_with_ms_mysqldrbd inf: mystore ms_mysqldrbd:Master

order mystore_after_ms_mysqldrbd inf: ms_mysqldrbd:promote mystore:start

property $id="cib-bootstrap-options" \

dc-version="1.1.8-7.el6-394e906" \

cluster-infrastructure="classic openais (with plugin)" \

expected-quorum-votes="2" \

stonith-enabled="false" \

no-quorum-policy="ignore"

[root@node1 ~]# crm status

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

Last updated: Sat Aug 17 20:37:26 2013

Last change: Sat Aug 17 20:19:51 2013 via cibadmin on node1.test.com

Stack: classic openais (with plugin)

Current DC: node1.test.com - partition with quorum

Version: 1.1.8-7.el6-394e906

2 Nodes configured, 2 expected votes

3 Resources configured.

Online: [ node1.test.com node2.test.com ]

Master/Slave Set: ms_mysqldrbd [mysqldrbd]

Masters: [ node2.test.com ]

Slaves: [ node1.test.com ]

mystore    (ocf::heartbeat:Filesystem):    Started node2.test.com

[root@node2 ~]# mount

/dev/sda2 on / type ext4 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

tmpfs on /dev/shm type tmpfs (rw)

/dev/sda1 on /boot type ext4 (rw)

/dev/sda3 on /data type ext4 (rw)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

/dev/drbd0 on /mydata type ext3 (rw)

[root@node2 ~]# cd /mydata/data/

[root@node2 data]# ll

总用量 29824

-rw-rw---- 1 mysql mysql 18874368 8月  17 19:48 ibdata1

-rw-rw---- 1 mysql mysql  5242880 8月  17 19:48 ib_logfile0

-rw-rw---- 1 mysql mysql  5242880 8月  17 18:57 ib_logfile1

drwx------ 2 mysql root      4096 8月  17 18:57 mysql

-rw-rw---- 1 mysql mysql    27698 8月  17 18:57 mysql-bin.000001

-rw-rw---- 1 mysql mysql  1061358 8月  17 18:57 mysql-bin.000002

-rw-rw---- 1 mysql mysql      126 8月  17 19:41 mysql-bin.000003

-rw-rw---- 1 mysql mysql      126 8月  17 19:48 mysql-bin.000004

-rw-rw---- 1 mysql mysql      76 8月  17 19:47 mysql-bin.index

-rw-r----- 1 mysql root      2302 8月  17 19:41 node1.test.com.err

-rw-r----- 1 mysql root      1849 8月  17 19:48 node2.test.com.err

drwx------ 2 mysql mysql    4096 8月  17 18:57 performance_schema

drwx------ 2 mysql root      4096 8月  17 18:57 test

4.增加mysql资源

crm(live)# configure

crm(live)configure# primitive mysqld lsb:mysqld

crm(live)configure# colocation mysqld_with_mystore inf: mysqld mystore

crm(live)configure# verify

crm(live)configure# show

node node1.test.com

node node2.test.com

primitive mysqld lsb:mysqld

primitive mysqldrbd ocf:heartbeat:drbd \

params drbd_resource="web" \

op start timeout="240" interval="0" \

op stop timeout="100" interval="0" \

op monitor role="Master" interval="20" timeout="30" \

op monitor role="Slave" interval="30" timeout="30"

primitive mystore ocf:heartbeat:Filesystem \

params device="/dev/drbd0" directory="/mydata" fstype="ext3" \

op start timeout="60" interval="0" \

op stop timeout="60" interval="0"

ms ms_mysqldrbd mysqldrbd \

meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

colocation mysqld_with_mystore inf: mysqld mystore

colocation mystore_with_ms_mysqldrbd inf: mystore ms_mysqldrbd:Master

order mystore_after_ms_mysqldrbd inf: ms_mysqldrbd:promote mystore:start

property $id="cib-bootstrap-options" \

dc-version="1.1.8-7.el6-394e906" \

cluster-infrastructure="classic openais (with plugin)" \

expected-quorum-votes="2" \

stonith-enabled="false" \

no-quorum-policy="ignore"

crm(live)configure# order mysqld_after_mystore mandatory: mystore mysqld

crm(live)configure# verify

crm(live)configure# show

node node1.test.com

node node2.test.com

primitive mysqld lsb:mysqld

primitive mysqldrbd ocf:heartbeat:drbd \

params drbd_resource="web" \

op start timeout="240" interval="0" \

op stop timeout="100" interval="0" \

op monitor role="Master" interval="20" timeout="30" \

op monitor role="Slave" interval="30" timeout="30"

primitive mystore ocf:heartbeat:Filesystem \

params device="/dev/drbd0" directory="/mydata" fstype="ext3" \

op start timeout="60" interval="0" \

op stop timeout="60" interval="0"

ms ms_mysqldrbd mysqldrbd \

meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

colocation mysqld_with_mystore inf: mysqld mystore

colocation mystore_with_ms_mysqldrbd inf: mystore ms_mysqldrbd:Master

order mysqld_after_mystore inf: mystore mysqld

order mystore_after_ms_mysqldrbd inf: ms_mysqldrbd:promote mystore:start

property $id="cib-bootstrap-options" \

dc-version="1.1.8-7.el6-394e906" \

cluster-infrastructure="classic openais (with plugin)" \

expected-quorum-votes="2" \

stonith-enabled="false" \

no-quorum-policy="ignore"

crm(live)configure# commit

[root@node1 ~]# crm status

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

Last updated: Sat Aug 17 20:46:35 2013

Last change: Sat Aug 17 20:46:07 2013 via cibadmin on node1.test.com

Stack: classic openais (with plugin)

Current DC: node1.test.com - partition with quorum

Version: 1.1.8-7.el6-394e906

2 Nodes configured, 2 expected votes

4 Resources configured.

Online: [ node1.test.com node2.test.com ]

Master/Slave Set: ms_mysqldrbd [mysqldrbd]

Masters: [ node2.test.com ]

Slaves: [ node1.test.com ]

mystore    (ocf::heartbeat:Filesystem):    Started node2.test.com

mysqld    (lsb:mysqld):    Started node2.test.com

[root@node2 ~]# netstat -ntulp | grep :3306

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                  LISTEN      26907/mysqld

[root@node2 ~]# /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>

5.增加vip资源

crm(live)# configure

crm(live)configure# primitive vip ocf:heartbeat:IPaddr params ip=192.168.1.200 nic=eth0 cidr_netmask=255.255.255.0

crm(live)configure# colocation vip_with_ms_mysqldrbd inf: ms_mysqldrbd:Master vip

crm(live)configure# verify

crm(live)configure# show

node node1.test.com

node node2.test.com

primitive mysqld lsb:mysqld

primitive mysqldrbd ocf:heartbeat:drbd \

params drbd_resource="web" \

op start timeout="240" interval="0" \

op stop timeout="100" interval="0" \

op monitor role="Master" interval="20" timeout="30" \

op monitor role="Slave" interval="30" timeout="30"

primitive mystore ocf:heartbeat:Filesystem \

params device="/dev/drbd0" directory="/mydata" fstype="ext3" \

op start timeout="60" interval="0" \

op stop timeout="60" interval="0"

primitive vip ocf:heartbeat:IPaddr \

params ip="192.168.1.200" nic="eth0" cidr_netmask="255.255.255.0"

ms ms_mysqldrbd mysqldrbd \

meta master-max="1" master-node-max="1" clone-max="2" clone-node-max="1" notify="true"

colocation mysqld_with_mystore inf: mysqld mystore

colocation mystore_with_ms_mysqldrbd inf: mystore ms_mysqldrbd:Master

colocation vip_with_ms_mysqldrbd inf: ms_mysqldrbd:Master vip

order mysqld_after_mystore inf: mystore mysqld

order mystore_after_ms_mysqldrbd inf: ms_mysqldrbd:promote mystore:start

property $id="cib-bootstrap-options" \

dc-version="1.1.8-7.el6-394e906" \

cluster-infrastructure="classic openais (with plugin)" \

expected-quorum-votes="2" \

stonith-enabled="false" \

crm(live)configure# commit

[root@node1 ~]# crm status

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

Last updated: Sat Aug 17 20:53:15 2013

Last change: Sat Aug 17 20:52:11 2013 via cibadmin on node1.test.com

Stack: classic openais (with plugin)

Current DC: node1.test.com - partition with quorum

Version: 1.1.8-7.el6-394e906

2 Nodes configured, 2 expected votes

5 Resources configured.

Online: [ node1.test.com node2.test.com ]

Master/Slave Set: ms_mysqldrbd [mysqldrbd]

Masters: [ node1.test.com ]

Slaves: [ node2.test.com ]

mystore    (ocf::heartbeat:Filesystem):    Started node1.test.com

mysqld    (lsb:mysqld):    Started node1.test.com

vip    (ocf::heartbeat:IPaddr):    Started node1.test.com

好了，到这里所有的资源配置全部完成，下面我们进行测试一下。

6.测试mysql高可用集群

(1).新增授权

[root@node1 ~]# /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> grant all on *.* to root@"192.168.1.%" identified by "123456";

Query OK, 0 rows affected (0.10 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

(2).远程测试一下

[root@nfs ~]# yum install -y mysql

[root@nfs ~]# mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[root@nfs ~]# mysql -uroot -p123456 -h 192.168.1.200

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.5.33-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test              |

+--------------------+

4 rows in set (0.06 sec)

mysql> create database mydb;

Query OK, 1 row affected (0.00 sec)

mysql>

(3).模拟一下故障

[root@node1 ~]# crm

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

crm(live)# node

crm(live)node# standby

crm(live)node# show

node1.test.com: normal

standby: on

node2.test.com: normal

[root@node2 ~]# crm status

Cannot change active directory to /var/lib/pacemaker/cores/root: No such file or directory (2)

Last updated: Sat Aug 17 21:02:13 2013

Last change: Sat Aug 17 21:02:00 2013 via crm_attribute on node1.test.com

Stack: classic openais (with plugin)

Current DC: node1.test.com - partition with quorum

Version: 1.1.8-7.el6-394e906

2 Nodes configured, 2 expected votes

5 Resources configured.

Node node1.test.com: standby

Online: [ node2.test.com ]

Master/Slave Set: ms_mysqldrbd [mysqldrbd]

Masters: [ node2.test.com ]

Stopped: [ mysqldrbd:1 ]

mystore    (ocf::heartbeat:Filesystem):    Started node2.test.com

mysqld    (lsb:mysqld):    Started node2.test.com

vip    (ocf::heartbeat:IPaddr):    Started node2.test.com

(4).再进行远程测试一下

mysql> create database testdb;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    1

Current database: *** NONE ***

Query OK, 1 row affected (0.42 sec)

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

| mydb              |

| mysql              |

| performance_schema |

| test              |

| testdb            |

+--------------------+

6 rows in set (0.12 sec)

mysql>

大家可以看到依然可以访问，好了今天的所有的演示到这里就全部结束了，我们下次再见。^_^……