Xtradb+Haproxy高可用数据库集群（一）xtradb部署篇
部署xtradb cluster时，建议使用3台及以上服务器。原因有二：1、默认情况下，如果一个同伴死去或者两个节点之间的通信不稳定，两个节点都将不接受查询。当然这个可以通过添加忽略仲裁来解决：
set globalwsrep_provider_options=”pc.ignore_quorum=true”;
2、当宕掉的那台启动时，会进行同步，负责提供数据的节点角色变为Donor， Donor会有一定的时间无法写入。断开情况如下：
Mysqldump 适合小库
Rsync 复制时间内断开，速度快
XtraBackup 短时间内断开，速度略慢
节点信息node #1hostname:percona1IP:192.168.70.71
node #2hostname:percona2IP:192.168.70.72
node #3hostname:percona3IP:192.168.70.73
前提条件：三个节点都安装了centos系统 防火墙放通了端口：3306,4444,4567和4568
关闭selinux
安装percona xtradb集群先安装percona官方源
yum installhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm安装epel源，以便安装依赖包socat
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm安装xtradb
yum installPercona-XtraDB-Cluster-56
官网安装参考地址：
https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html
配置节点官网参考地址：
https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html
配置第一个节点第一个节点的/etc/my.cnf配置内容如下：
datadir=/var/lib/mysqluser=mysql
# Path to Galeralibrarywsrep_provider=/usr/lib64/libgalera_smm.so
# Clusterconnection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order forGalera to work correctly binlog format should be ROWbinlog_format=ROW
# MyISAM storageengine has only experimental supportdefault_storage_engine=InnoDB
# This changeshow InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2
# Node #1addresswsrep_node_address=192.168.70.71
# SST methodwsrep_sst_method=xtrabackup-v2
# Cluster namewsrep_cluster_name=my_centos_cluster
# Authenticationfor SST methodwsrep_sst_auth="sstuser:s3cret"
在此之后，第一个节点可以启动以下命令：
#/etc/init.d/mysql bootstrap-pxc如果在centos7上运行本教程，系统服务如下：
#systemctl start mysql@bootstrap.service
此命令将启动集群初步wsrep_cluster_address设置为gcomm://。这样的集群将自举以防后面的节点或MySQL重新开始，这不需要改变该配置文件。
第一个节点启动后，集群状态检测：
mysql> show status like 'wsrep%';+----------------------------+--------------------------------------+| Variable_name |Value |+----------------------------+--------------------------------------+| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |...| wsrep_local_state | 4 || wsrep_local_state_comment| Synced |...| wsrep_cluster_size | 1 || wsrep_cluster_status | Primary || wsrep_connected |ON |...| wsrep_ready |ON |+----------------------------+--------------------------------------+40 rows in set (0.01 sec)该输出显示集群已成功自举
修改root密码，以及删除空用户
UPDATE mysql.user SET password=PASSWORD("Passw0rd")where user='root';delete from mysql.user whereuser='';FLUSH PRIVILEGES;
为了使xtrabackup成功建立快照，需要给新用户建立适当的权限。
mysql@percona1> CREATE USER'sstuser'@'localhost' IDENTIFIED BY 's3cret';mysql@percona1> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';mysql@percona1> FLUSH PRIVILEGES;注：MySQL的root账户也可用于建立PerconaXtrabackup的SST，但建议使用不同用户做这一点。
配置第二个节点第二个节点的/etc/my.cnf配置内容如下：
datadir=/var/lib/mysqluser=mysql
# Path to Galera librarywsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URLcontains IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order for Galera to workcorrectly binlog format should be ROWbinlog_format=ROW
# MyISAM storage engine hasonly experimental supportdefault_storage_engine=InnoDB
# This changes how InnoDBautoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2
# Node #2 addresswsrep_node_address=192.168.70.72
# Cluster namewsrep_cluster_name=my_centos_cluster
# SST methodwsrep_sst_method=xtrabackup-v2
#Authentication for SSTmethodwsrep_sst_auth="sstuser:s3cret"
第二个节点的启动命令
/etc/init.d/mysql start
服务器已经启动后，它会自动接收状态快照传送。因此，第二个节点不会有空root密码了。
这是第二个节点的状态
mysql> show status like 'wsrep%';+----------------------------+--------------------------------------+| Variable_name | Value |+----------------------------+--------------------------------------+| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |...| wsrep_local_state | 4 || wsrep_local_state_comment | Synced |...| wsrep_cluster_size | 2 || wsrep_cluster_status | Primary || wsrep_connected | ON |...| wsrep_ready | ON |+----------------------------+--------------------------------------+40 rows in set (0.01 sec)
配置第三个节点第三个节点的/etc/my.cnf配置文件
datadir=/var/lib/mysqluser=mysql
# Path to Galera librarywsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URLcontains IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order for Galera to workcorrectly binlog format should be ROWbinlog_format=ROW
# MyISAM storage engine hasonly experimental supportdefault_storage_engine=InnoDB
# This changes how InnoDBautoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2
# Node #3 addresswsrep_node_address=192.168.70.73
# Cluster namewsrep_cluster_name=my_centos_cluster
# SST methodwsrep_sst_method=xtrabackup-v2
#Authentication for SSTmethodwsrep_sst_auth="sstuser:s3cret"
然后启动节点
/etc/init.d/mysql start
服务器已经启动后，它会自动接收状态快照传送。
集群状态查看：
mysql> show status like 'wsrep%';+----------------------------+--------------------------------------+| Variable_name | Value |+----------------------------+--------------------------------------+| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |...| wsrep_local_state | 4 || wsrep_local_state_comment | Synced |...| wsrep_cluster_size | 3 || wsrep_cluster_status | Primary || wsrep_connected | ON |...| wsrep_ready | ON |+----------------------------+--------------------------------------+40 rows in set (0.01 sec)该输出证实了第三个节点加入集群。
测试复制
在节点2上创建数据库
mysql@percona2> CREATE DATABASE percona;Query OK, 1row affected(0.01 sec)
在节点3上创建表
mysql@percona3> USE percona;Database changed
mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));Query OK, 0rows affected(0.05 sec)
在节点1上插入数据
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');QueryOK, 1 rowaffected (0.02 sec)
在第二个节点上查看数据
mysql@percona2> SELECT * FROM percona.example;+---------+-----------+| node_id | node_name |+---------+-----------+| 1 | percona1|+---------+-----------+1 row in set (0.00 sec)
集群搭建完成
页:
[1]