一、部署
Percona xtradb cluster通过galera实现多节点同时提供读写服务，高可用，负载均衡。
1. 准备
Ø 3台服务器
| 主机名 | IP地址 | 操作系统 |
| Host1 | 192.168.2.128 | CentOS6.5 |
| Host2 | 192.168.2.129 | CentOS6.5 |
| Host3 | 192.168.2.130 | CentOS6.5 |
Ø 防火墙关闭或打开3310,4444, 4567 ,4568端口
Ø 关闭SELinux
*注最好3个节点(或以上奇数)，偶数节点容易出现脑裂问题。
2. 配置Percona源
| vi /etc/yum.repos.d/Percona.repo [percona] name = CentOS $releasever - Percona baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/ enabled = 1 gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona gpgcheck = 1 |
| vi /etc/pki/rpm-gpg/RPM-GPG-KEY-percona -----BEGIN PGP PUBLIC KEY BLOCK----- Version: GnuPG v1.4.9 (GNU/Linux)
mQGiBEsm3aERBACyB1E9ixebIMRGtmD45c6c/wi2IVIa6O3G1f6cyHH4ump6ejOi AX63hhEs4MUCGO7KnON1hpjuNN7MQZtGTJC0iX97X2Mk+IwB1KmBYN9sS/OqhA5C itj2RAkug4PFHR9dy21v0flj66KjBS3GpuOadpcrZ/k0g7Zi6t7kDWV0hwCgxCa2 f/ESC2MN3q3j9hfMTBhhDCsD/3+iOxtDAUlPMIH50MdK5yqagdj8V/sxaHJ5u/zw YQunRlhB9f9QUFfhfnjRn8wjeYasMARDctCde5nbx3Pc+nRIXoB4D1Z1ZxRzR/lb 7S4i8KRr9xhommFnDv/egkx+7X1aFp1f2wN2DQ4ecGF4EAAVHwFz8H4eQgsbLsa6 7DV3BACj1cBwCf8tckWsvFtQfCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79 Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5 Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm lHkwGqEqfbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72 O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFqFTqA0UW2gWQ cKFN4Q6NPV6IW0rV61ONLUc0VFXvYDtwsRbUmUYkB/L/R9fHj4lRUDbGEQrLCoE+ /HyYvr2rxP94PT6Bkjk/aiCCPAKZRj5CFUKRpShfDIiow9qxtqv7yVd514Qqmjb4 eEihtcjltGAoS54+6C3lbjrHUQhLwPGqlAh8uZKzfSZq0C06kTxiEqsG6VDDYWy6 L7qaMwOqWdQtdekKiCk8w/FoovsMYED2qlWEt0i52G+0CjoRFx2zNsN3v4dWiIhk ZSL00Mx+g3NA7pQ1Yo5Vhok034mP8L2fBLhhWaK3LG63jYvd0HLkUFhNG+xjkpeI SQQYEQIACQUCSybdoQIbDAAKCRAcTL3NzS79KlacAJ0aAkBQapIaHNvmAhtVjLPN wke4ZgCePe3sPPF49lBal7QaYPdjqapa1SQ= =qcCk -----END PGP PUBLIC KEY BLOCK----- |
3. 安装配置集群
Ø 安装软件包
| Yum install socat Percona-XtraDB-Cluster-server |
Ø 配置文件my.cnf
|
[mysqld] port = 3310 socket = /tmp/mysql.sock datadir = /data/bestbuy back-log = 500 query_cache_size= 0 log-error=/data/log/bestbuy.err binlog_format=row innodb_log_group_home_dir=/data/bestbuy innodb_data_home_dir=/data/bestbuy innodb_file_per_table=1 innodb_log_files_in_group=4 innodb_log_file_size=50M # xtradb cluster settings wsrep_cluster_name = mycluster wsrep_cluster_address = gcomm:// wsrep_node_address = 192.168.2.128 wsrep_provider = /usr/lib/libgalera_smm.so wsrep_sst_method = xtrabackup wsrep_sst_auth = "sstuser:sst123" innodb_locks_unsafe_for_binlog = 1 innodb_autoinc_lock_mode = 2 default_storage_engine=InnoDB
|
Ø 集群初始化
| Service mysql bootstrap-pxc |
Ø 修改配置文件my.cnf中参数
| wsrep_cluster_address = gcomm://192.168.2.128,192.168.2.129,192.168.2.130 |
Ø 创建验证用户
| GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' identified by 'sst123'; flush privileges; |
Ø 在其余2节点修改my.cnf参数
| wsrep_node_address = <本机IP地址> |
Ø 启动节点
| Service mysql start |
Ø 验证
| Show status like ‘wsrep%’; wsrep_local_index的值不同 |
Ø Keepalived+lvs负载均衡
| ! Configuration File for keepalived
global_defs { router_id LVS_MASTER }
vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 99 advert_int 1 nopreempt 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.2.132 } }
virtual_server 192.168.2.132 3310 { delay_loop 10 lb_algo rr lb_kind DR protocol TCP real_server 192.168.2.128 3310 { weight 5 inhibit_on_failure TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3310 } }
real_server 192.168.2.129 3310 { weight 5 inhibit_on_failure TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3310 } }
real_server 192.168.2.130 3310 { weight 5 inhibit_on_failure TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3310 } }
} |
Ø 各数据库节点执行lvs.sh
|
#!/bin/bash
vip=192.168.2.132
/sbin/ifconfig lo:0 $vip broadcast $vip netmask 255.255.255.255 up
/sbin/route add -host $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 |
二、监控
1.手动监控
参数说明：
Cluster包含time, name, P, cnf
Name :集群名字
P :状态为Primary或者Non-primary，Non-primary不允许进行数据库操作
Cnf:是状态参数 wserep_cluster_conf_id的值，节点离开及重新加入的次数
#：集群里节点的数量
Node包含name, cmt, sta
Name:节点的主机名
Sta: wsrep_ready和wsrep_connected的值，T是true的缩写，F是false的缩写
Cmt: 状态参数wsrep_local_state_comment的值，当前节点的状态包含[同步\加入]等
Queue复制队列数
Up:出去的
Dn:进来的
Ops复制的事务数
Up:出去的
Dn:进来的
Bytes类似于Ops
Flow包含pau, snt, dst
Pau:是wsrep_flow_control_paused的值，可以反映流控制的快慢
Snt: wsrep_flow_control_sent 的值，从本节点发出的流控制数量
Dst: wsrep_cert_deps_distancede的值，表示复制进程的并发量
Conflct复制争用
Lcf:验证失败次数
Bfa：被强制中断次数
2.自动监控
a.安装监控模版
yum install percona-zabbix-templates php-mysql
b.创建目录并拷贝用户参数文件
mkdir -p/etc/zabbix_agentd.conf.d/
cp/var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf/etc/zabbix_agentd.conf.d/userparameter_percona_mysql.conf
c.确保zabbix配置文件中包含Include=/etc/zabbix_agentd.conf.d/
d.配置用户信息/var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
<?php
$mysql_user = 'root';
$mysql_pass = '123456';
e.配置数据库客户端/etc/my.cnf
user = root
password = 123456
f.导入模版zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.3.xml
h. 链接mysql.sock
ln -s /tmp/mysql.sock/var/lib/mysql/mysql.sock
注：如果mysql端口不是3306会触发程序Bug,将ss_get_mysql_stats.php中后面部分删除
$cache_file= "$cache_dir/$sanitized_host-mysql_cacti_stats.txt" . ($port !=3306 ? ":$port" : '');
三、备份与恢复
备份策略
| 周日 | 周一 | 周二 | 周三 | 周四 | 周五 | 周六 |
| 全备 | 增量 | 增量 | 增量 | 增量 | 增量 | 增量 |
全量备份
innobackupex --user=root --password=123456--defaults-file=/etc/my.cnf --port=3310 /backup
全量恢复
innobackupex --defaults-file=/etc/my.cnf--user=root --password=123456 --apply-log /backup/2014-05-15_17-54-13/
innobackupex --defaults-file=/etc/my.cnf--user=root --password=123456 --copy-back /backup/2014-05-15_17-54-13/
chown –R mysql.mysql /data/
增量备份
innobackupex --defaults-file=/etc/my.cnf--user=root --password=123456 --incremental-basedir=上一次的备份目录--incremental /backup
增量恢复
innobackupex --apply-log --redo-only 全备的目录
innobackupex --apply-log --redo-only 全备的目录--incremental-dir=第一个增量的目录
innobackupex --apply-log 全备的目录--incremental-dir=第二个增量备份的目录
innobackupex --apply-log 全备的目录
innobackupex --defaults-file=/etc/my.cnf--user=root --password=123456 --copy-back 全备的目录
chown –R mysql.mysql /data/
定时备份脚本
#!/usr/bin/env perl
use warningsFATAL => 'all';
use strict;
use FileHandle;
my $backupdir= '/backup';
my $incdir= '';
my $user= 'root';
my $passwd= '123456';
my $port= '3310';
my $cntfile= '/etc/my.cnf';
my $fullcmd;
my $inccmd;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday)=localtime(time);
$fullcmd = 'innobackupex --user='
.$user . ' --password=' . $passwd
.' --defaults-file=' .$cntfile
.' --port=' . $port . ' '
.$backupdir;
if ($wday != 7) {
$incdir=findLastDir( $backupdir);
$inccmd='innobackupex--user='
.$user . '--password=' . $passwd
.'--defaults-file=' .$cntfile
.'--port=' . $port
.' --incremental-basedir=' .$incdir
.' --incremental ' .$backupdir;
system($inccmd);
}
else {
system($fullcmd);
}
sub findLastDir{
my$dir = shift;
my$actDir = 0;
my%dir;
opendir(DIR,"$dir") || die $!;
my@dirs = readdir(DIR);
closeDIR;
foreach my$tmp (@dirs) {
chomp$tmp;
if($tmpne "\."&& $tmp ne "\.\." &&-d $tmp){
$actDir=$tmp;
$tmp=~s/-//g;
$tmp=~ s/_//g;
$dir{$tmp} =$actDir;
}
}
foreach my$key ( sort { $b<=> $a } keys %dir) {
return$dir{$key};
last;
}
}
7779
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
