六、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>
大家可以看到依然可以访问，好了今天的所有的演示到这里就全部结束了，我们下次再见。^_^……