?

?Percona XtraDB Cluster安装部署手冊

?

引言

编写目的

编写此文档，供PerconaXtraDB Cluster部署时使用。

预期读者

系统维护人员及实施人员。

编制根据及參考资料

目标

通过阅读该手冊，让读者明白PerconaXtraDB Cluster的安装、配置和维护情况，为兴许数据库运维工作提供指导。

应用部署方案

环境准备

服务器列表

序号	IP	用途	HOSTNAME	操作系统
1	192.168.0.7	Percona XtraDB Cluster	?	RedHat 6.3
2	192.168.0.9	Percona XtraDB Cluster	?	RedHat 6.3
3	192.168.0.15	LVS+Keepalived	?	RedHat 5.6
4	192.168.0.17	LVS+Keepalived	?	RedHat 5.6

IP列表

序号	名称	IP
1	LVS-DR-VIP	192.168.0.110
2	LVS-DR-Master	192.168.0.15
3	LVS-DR-BACKUP	192.168.0.17
4	MySQL1-Realserver	192.168.0.7
5	MySQL2-Realserver	192.168.0.9
6	GateWay	192.168.0.1

配置网易的YUM源

1. 导入证书（证书的位置http://mirrors.163.com/centos/）

rpm -importhttp://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-5

2. 使用网易的源

cd /etc/yum.repos.d/

wgethttp://mirrors.163.com/.help/CentOS5-Base-163.repo

3. 改动文件（把全部$releasever替换成5，保存）

vim CentOS5-Base-163.repo

4. 更新yum

yum -y update

LVS安装

1.??? 安装

yum install ipvsadm –y

Installing : ipvsadm-1.25-9.el6.i686 1/1

Installed:

ipvsadm.i686 0:1.25-9.el6

Complete!

2.?? Ipvsadm命令參数

-C 清除表中全部的记录

ipvsadm -A -t 192.168.3.187:80 -s rr

-A?--add-service在服务器列表中新加入一条新的虚拟服务器记录

-t 表示为tcp服务

-u 表示为udp服务

-s --scheduler 使用的调度算法， rr | wrr | lc | wlc | lblb |lblcr | dh | sh | sed | nq 默认调度算法是 wlc

ipvsadm -a -t 192.168.3.187:80 -r 192.168.200.10:80 -m-w 1

-a --add-server 在服务器表中加入一条新的真实主机记录

-t --tcp-service?说明虚拟服务器提供tcp服务

-u --udp-service 说明虚拟服务器提供udp服务

-r --real-server?真实服务器地址

-m --masquerading 指定LVS工作模式为NAT模式

-w --weight 真实服务器的权值

-g --gatewaying 指定LVS工作模式为直接路由器模式（也是LVS默认的模式）

-i --ipip 指定LVS的工作模式为隧道模式

-p 会话保持时间，定义流量呗转到同一个realserver的会话存留时间

调度算法

rr 轮询 round robin, wrr 加强轮询 weighted round robin。

新的请求被轮流分配到RealServer上，它如果服务器处理性能都同样。无论服务器当前的连接数和响应速度，不适合服务器性能不同的集群。这会导致服务器间的负载不平衡

dh 目的地址散列调度destination hashing

?

Keepalived安装

安装

1.??? gcc

???? 安装命令：yum install gcc gcc-c++

2.??? openssl-devel ,openssl

???? 安装命令：yum install openssl-developenssl

3.??? 源代码内核的

???? 安装命令：yum install kernel-devel kernel

???? 建立软连接：ln -s /usr/src/kernels/内核名称/?? /usr/src/linux

4.??? popt-devel,libnl-devel

???? 安装命令：yum install popt-devellibnl-devel

5.??? 安装keepalived

#wgethttp://www.keepalived.org/software/keepalived-1.2.12.tar.gz

#tar xvzf keepalived-1.2.12.tar.gz

#cd keepalived-1.2.12

#uname –r

# ln -sv /usr/src/kernels/`uname-r`-`uname -m`/ /usr/src/linux # 必须做不然，编译会出错

#./configure --sysconf=/usr/local/keepalived--prefix=/usr/local/keepalived

???? # make && make install

#cp/usr/local/keepalived/sbin/keepalived /usr/sbin/

#cp/usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

#cp /usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/init.d/

#mkdir /etc/keepalived

#vi/etc/keepalived/keepalived.conf

#chkconfig --add keepalived

#chkconfig keepalived on #做成系统服务

#service keepalivedstart|stop|status

配置

1.??? 主服务器配置keepalived.conf

global_defs{

?? notification_email {

???? duhj@c-sunrise.com

?? }

?? notification_email_from duhj@c-sunrise.com

?? smtp_server 127.0.0.1

?? smtp_connect_timeout 30

?? router_id mysql-ha

}

?

vrrp_instanceVI_1 {

??? state MASTER

??? interface eth0

??? virtual_router_id 51

??? priority 100

??? advert_int 1

??? nopreempt??#不抢占,仅仅在priority高的节点上设置

??? authentication {

??????? auth_type PASS

??????? auth_pass 1111

??? }

??? virtual_ipaddress {

??????? 192.168.0.110

??? }

}

?

virtual_server192.168.0.110 3306 {

??? delay_loop 2

??? lb_algo wrr

??? lb_kind DR

??? persistence_timeout 600?#超时设置，单位秒

protocol TCP

real_server 192.168.0.7 3306

??????? weight 3

??????? # notify_down /root/sh/mysql.sh

??????????? TCP_CHECK {

??????????? connect_timeout 300?#超时设置，单位秒

??????????? nb_get_retry 3

??????????? delay_before_retry 3

??????????? connect_port 3306

??????? }

??? }

?

??? real_server 192.168.0.9 3306

??????? weight 3

??????? # notify_down /root/sh/mysql.sh

??????????? TCP_CHECK {

??????????? connect_timeout 300?#超时设置，单位秒

??????????? nb_get_retry 3

??????????? delay_before_retry 3

??????????? connect_port 3306

??????? }

??? }

}

2.??? 从服务器配置/etc/keepalived/keepalived.conf

?global_defs {

?? notification_email {

???? duhj@c-sunrise.com

?? }

?? notification_email_from duhj@c-sunrise.com

?? smtp_server 127.0.0.1

?? smtp_connect_timeout 30

?? router_id mysql-ha

}

?

vrrp_instanceVI_1 {

??? state BACKUP

??? interface eth0

??? virtual_router_id 51

??? priority 88

??? advert_int 1

??? nopreempt??#不抢占,仅仅在priority高的节点上设置

??? authentication {

??????? auth_type PASS

??????? auth_pass 1111

??? }

??? virtual_ipaddress {

??????? 192.168.0.110

??? }

}

?

virtual_server192.168.0.110 3306 {

??? delay_loop 2

??? lb_algo wrr

??? lb_kind DR

??? persistence_timeout 600?#超时设置，单位秒

protocolTCP

real_server192.168.0.7 3306

??????? weight 3

??????? #notify_down /root/sh/mysql.sh

??????????? TCP_CHECK {

??????????? connect_timeout 300?#超时设置。单位秒

??????????? nb_get_retry 3

??????????? delay_before_retry 3

??????????? connect_port 3306

??????? }

??? }

?

??? real_server 192.168.0.9 3306

??????? weight 3

??????? # notify_down /root/sh/mysql.sh

??????????? TCP_CHECK {

??????????? connect_timeout 300?#超时设置，单位秒

??????????? nb_get_retry 3

??????????? delay_before_retry 3

??????????? connect_port 3306

??????? }

??? }

}s

Percona XtraDB Cluster安装

安装说明

安装准备

??服务器版本号查看

# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.4 (Santiago)

??新建文件夹：

#mkdir -p /softwares

??下载RPM包

l?下载XTRADB Cluster包

wget http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-56-debuginfo-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel6/x86_64/Percona-XtraDB-Cluster-client-56-5.6.15-25.5.759.rhel6.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-devel-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-full-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wgethttp://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-galera-3-3.4-1.213.rhel5.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-galera-3-debuginfo-3.4-1.213.rhel5.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-server-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-shared-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/Percona-XtraDB-Cluster-56/LATEST/RPM/rhel5/x86_64/Percona-XtraDB-Cluster-test-56-5.6.15-25.5.759.rhel5.x86_64.rpm

?

l?下载XTRABACKUP包

wgethttp://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-2.2.3-4982.el5.x86_64.rpm

?

wget

http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-debuginfo-2.2.3-4982.el5.x86_64.rpm

?

wget

ttp://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/5/x86_64/percona-xtrabackup-test-2.2.3-4982.el5.x86_64.rpm

?

??安装依赖包

l?安装包准备

安装光盘package文件夹下：perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

?

wgethttp://repo.webtatic.com/yum/el6/x86_64/libmysqlclient16-5.1.69-1.w6.x86_64.rpm

?

???? wget?????http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/socat-1.7.2.4-1.el6.rf.x86_64.rpm

????

wget

http://mirror.centos.org/centos/6/os/x86_64/Packages/nc-1.84-22.el6.x86_64.rpm

l?安装

rpm -ivhlibmysqlclient16-5.1.69-1.w6.x86_64.rpm

?

rpm -ivhperl-DBD-MySQL-4.013-3.el6.x86_64.rpm socat-1.7.2.4-1.el6.rf.x86_64.rpmnc-1.84-22.el6.x86_64.rpm

?

??删除原有软件包

? rpm -qa | grep mysql? | xargs sudo rpm -e –nodeps

?

安装Percona XtraDB Cluster

??安装percona-xtrabackup

??? rpm -ivhpercona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm

??安装Percona-XtraDB-Cluster-galera

??? rpm -ivhPercona-XtraDB-Cluster-galera-3-3.5-1.216.rhel6.x86_64.rpmPercona-XtraDB-Cluster-galera-3-debuginfo-3.5-1.216.rhel6.x86_64.rpm

??安装Percona-XtraDB-Cluster-client

rpm -ivhPercona-XtraDB-Cluster-client-56-5.6.15-25.5.759.rhel6.x86_64.rpm

??安装libmysqlclient16-5.1.69-1.w6.x86_64

?? rpm -ivhlibmysqlclient16-5.1.69-1.w6.x86_64.rpm

??安装Percona-XtraDB-Cluster-server

??? rpm -ivhPercona-XtraDB-Cluster-server-56-5.6.15-25.5.759.rhel6.x86_64.rpm

配置第一个节点

1、编辑配置文件

find / -name ‘my-default.cnf‘

cp my-default.cnf /etc/my.cnf

vi /etc/my.cnf

# REPLICATION #

relay-log????????????????????? =/data/munion_db/relay-bin

slave-net-timeout????????????? = 60

?

# CACHES AND LIMITS #

tmp-table-size???????????????? = 250M

max-heap-table-size??????????? = 250M

query-cache-type?????????????? = 0

query-cache-size?????????????? = 0

max-connections??????????????? = 2000

thread-cache-size?????????? ???= 100

open-files-limit?????????????? = 65535

table-definition-cache???????? = 4096

table-open-cache?????????????? = 6000

?

# INNODB #

innodb-flush-method??????????? = O_DIRECT

innodb-log-files-in-group????? = 4

innodb-log-file-size?????????? = 1g

innodb_log_buffer_size???????? = 30M

innodb-flush-log-at-trx-commit = 2

innodb-file-per-table????????? = 1

innodb-buffer-pool-size??????? = 25G

innodb_buffer_pool_instances?? = 24

innodb_thread_sleep_delay????? = 40

innodb_change_buffer_max_size? = 50

innodb_adaptive_hash_index???? = 1

?

?

init_connect=‘SET autocommit=0‘

wait_timeout=100

#interactive_timeout=100

?

# LOGGING #

log-error????????????????????? =/data/munion_db/mysql-error.log

log-queries-not-using-indexes? = 1

slow-query-log???????????????? = 1

slow-query-log-file??????????? = /data/munion_db/mysql-slow.log

?

?

#config for wsrep

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_cluster_address=gcomm://10.25.141.55,10.25.141.56

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_node_address=10.25.141.55

wsrep_sst_method=xtrabackup-v2

wsrep_cluster_name=my_centos_cluster

wsrep_sst_auth="sstuser:s3cret"

wsrep_slave_threads=8

wsrep_provider_options=‘gcs.max_packet_size=10485760;gcache.size=2147483648;replicator.commit_order=3‘

wsrep_max_ws_rows=10485760

wsrep_max_ws_size=2147483648

#config for others

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

配置第二个节点

?

sync-binlog??????????????????? = 1

?

# REPLICATION #

relay-log????????????????????? =/data/munion_db/relay-bin

slave-net-timeout????????????? = 60

?

# CACHES AND LIMITS #

tmp-table-size?? ??????????????= 32M

max-heap-table-size??????????? = 32M

query-cache-type?????????????? = 0

query-cache-size?????????????? = 0

max-connections??????????????? = 3000

thread-cache-size????????????? = 100

open-files-limit?????????????? = 65535

table-definition-cache???????? = 4096

table-open-cache?????????????? = 3000

?

# INNODB #

innodb-flush-method??????????? = O_DIRECT

innodb-log-files-in-group????? = 4

innodb-log-file-size?????????? = 1g

innodb_log_buffer_size???????? = 30M

innodb-flush-log-at-trx-commit = 2

innodb-file-per-table????????? = 1

innodb-buffer-pool-size??????? = 25G

innodb_buffer_pool_instances?? = 12

innodb_thread_sleep_delay????? = 40

init_connect=‘SET autocommit=0‘

wait_timeout=100

interactive_timeout=100

?

# LOGGING #

log-error????????????????????? =/data/munion_db/mysql-error.log

log-queries-not-using-indexes? = 1

slow-query-log???????????????? = 1

slow-query-log-file??????????? = /data/munion_db/mysql-slow.log

?

#config for wsrep

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_cluster_address=gcomm://10.25.141.55,10.25.141.56

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

wsrep_node_address=10.25.141.56

wsrep_cluster_name=my_centos_cluster

#wsrep_sst_method=xtrabackup-v2

wsrep_sst_method=rsync

wsrep_sst_auth="sstuser:s3cret"

wsrep_slave_threads=8

wsrep_provider_options=‘gcs.max_packet_size=10485760;gcache.size=2147483648;replicator.commit_order=3‘

wsrep_max_ws_rows=10485760

wsrep_max_ws_size=2147483648

?

#config for others

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

LVS+Keepalived配合脚本

#vi /etc/rc.d/init.d/realserver.sh

#!/bin/bash?

# description: Config realserver lo andapply noarp?

SNS_VIP=192.168.0.110?

# /etc/rc.d/init.d/functions?

case "$1" in?

start)?

??????ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP?

??????/sbin/route add -host $SNS_VIP dev lo:0?

??????echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore?

??????echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce?

??????echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore?

??????echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce?

??????sysctl -p >/dev/null 2>&1?

??????echo "RealServer Start OK"?

??????;;?

stop)?

??????ifconfig lo:0 down?

??????route del $SNS_VIP >/dev/null 2>&1?

??????echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore?

??????echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce?

??????echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore?

??????echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce?

??????echo "RealServer Stoped"?

??????;;?

*)?

??????echo "Usage: $0 {start|stop}"?

??????exit 1?

esac?

exit 0?

加入开机自启动：

vim/etc/rc.local

#!/bin/sh

#

# This script will be executed *after* allthe other init scripts.

# You can put your own initialization stuffin here if you don‘t

# want to do the full Sys V style initstuff.

?

touch /var/lock/subsys/local

./etc/rc.d/init.d/realserver.shstart

启动相关服务

??主备LVS

#servicekeepalived start #启动keepalived服务

??MySQL Cluster中的2台SQL节点

?#cd /etc/rc.d/init.d/

?#chmod 755 realserver.sh

???? #./realserver.sh

測试

方法一：

#telnet192.168.0.110 3306

方法二：

?使用mysql的各种客户端工具连接mysql。