共计 19624 个字符，预计需要花费 50 分钟才能阅读完成。
PXC 简介
Percona XtraDB Cluster（简称 PXC 集群）提供了 MySQL 高可用的一种实现方法。
1. 集群是有节点组成的，推荐配置至少 3 个节点，但是也可以运行在 2 个节点上。
2. 每个节点都是普通的 mysql/percona 服务器，可以将现有的数据库服务器组成集群，反之，也可以将集群拆分成单独的服务器。
3. 每个节点都包含完整的数据副本。
PXC 集群主要由两部分组成：Percona Server with XtraDB 和 Write Set Replication patches（使用了 Galera library，一个通用的用于事务型应用的同步、多主复制插件）。
PXC 特性：
1，同步复制，事务要么在所有节点提交或不提交。
2，多主复制，可以在任意节点进行写操作。
3，在从服务器上并行应用事件，真正意义上的并行复制。
4，节点自动配置，数据一致性，不再是异步复制。
PXC 劣势：
1、当前版本（5.6.20）的复制只支持 InnoDB 引擎，其他存储引擎的更改不复制。然而，DDL（Data Definition Language）语句在 statement 级别被复制，并且，对 mysql.* 表的更改会基于此被复制。例如 CREATE USER… 语句会被复制，但是 INSERT INTO mysql.user… 语句则不会。（也可以通过 wsrep_replicate_myisam 参数开启 myisam 引擎的复制，但这是一个实验性的参数）。
2、PXC 集群一致性控制机制，事有可能被终止，原因如下：集群允许在两个节点上同时执行操作同一行的两个事务，但是只有一个能执行成功，另一个会被终止，集群会给被终止的客户端返回死锁错误(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).
3、写入效率取决于节点中最弱的一台，因为 PXC 集群采用的是强一致性原则，一个更改操作在所有节点都成功才算执行成功。
环境介绍：
操作系统版本:CentOS-6.5-X64
数据库版本:Percona-Server-5.6.15-rel63.0.tar
192.168.3.81 node1
192.168.3.82 node2
192.168.3.83 node3
1. 安装 Percona mysql(所有节点安装)
安装源
rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
安装 Precona XtraDB Cluster 所需要的扩展包
yum -y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bisonlibtool ncurses5-devel boost
安装 perl 组件（xtrabackup 需要的组件）
yum -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes
安装 socat (Socat 是一个在两个独立数据的双向传输之间起到中继作用的软件）
配置好 epel 源之后，可直接执行 yum -y install socat
若无法 yum 安装 socat，则按照以下步骤编译安装
wget http://www.dest-unreach.org/socat/download/socat-1.7.2.4.tar.gz
tar zxvf socat-1.7.2.4.tar.gz
./configure
make && make install
安装 Percona-XtraDB-Cluster 及其相关组件
wget http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
rpm -ivh percona-release-0.0-1.x86_64.rpm
yum -y install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56
二．初始化 Percona-XtraDB-Cluster 集群
Percona mysql 配置文件修改
配置 /etc/my.cnf，(所有节点安装)：
# vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size = 1024M //innodb_buffer_pool_size 内存的 80%
datadir = /data/mysql
port = 3306
server_id = 81 //server_id 必须要不一样
socket = /tmp/mysql.sock
log-bin=mysql-bin
expire_logs_days = 10
sort_buffer_size = 1M
join_buffer_size = 1M
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 246M
max_heap_table_size = 246M
key_buffer_size = 300M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
slow_query_log = 1
log-error = /data/mysql/log/error.log
long_query_time = 2
log-queries-not-using-indexes = ON
slow_query_log_file = /data/mysql/log/slowquery.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
general_log = 1 // 数据库执行日志开关, 一般不打开
general_log_file = /data/mysql/log/mysql-general.log // 数据库执行日志文件, 一般不打开
创建所需目录，(所有节点安装)：
mkdir -p /data/mysql/log
chown mysql.mysql /data/mysql/log
初始化数据库，(所有节点安装)：
mysql_install_db –user=mysql –datadir=/data/mysql
/etc/init.d/mysql start
mysqladmin -u root password ‘123456’ -S /tmp/mysql.sock // 首次修改 mysql 密码
删除原有软件包，(所有节点安装)：
因为 XtraDB Cluster 的软件包与原有软件包冲突：
/etc/init.d/mysql stop
rpm -qa | grep Percona-Server | grep -v compat |xargs rpm -e –nodeps
安装 Percona XtraDB Cluster 5.6 (所有节点安装)
yum -y install Percona-XtraDB-Cluster-server-56 Percona-XtraDB-Cluster-client-56 Percona-XtraDB-Cluster-galera-3
添加 XtraDB Cluster 的支持，(所有节点安装)
vim /etc/my.cnf
在 [mysqld] 模块下面增加下面内容:
# xtradb cluster settings
binlog_format = ROW
wsrep_cluster_name = PXC
wsrep_cluster_address = gcomm://192.168.3.81,192.168.3.82,192.168.3.83
wsrep_node_address = 192.168.3.81 // 注意，每台服务器需要将 wsrep_node_address 设置为本机的 IP 地址或主机名。
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = rsync
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
default_storage_engine=InnoDB
初始化第一个 Node:
/etc/init.d/mysql bootstrap-pxc // 第一个节点才使用
mysql> show global status like ‘wsrep%’;
+——————————+————————————–+
| Variable_name | Value |
+——————————+————————————–+
| wsrep_local_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 2 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 141 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.3.81:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 597ae492-dcc6-11e4-ba30-e6dfd92be1c6 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.9(r93aca2d) |
| wsrep_ready | ON |
+——————————+————————————–+
参数 “wsrep_cluster_size” 为 1，因为目前 Cluster 中只有一个 Node。
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| log |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)
mysql> create database liu;
Query OK, 1 row affected (0.00 sec)
mysql> use liu
Database changed
mysql> create table liu01 (id int,name char(4));
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+—————+
| Tables_in_liu |
+—————+
| liu01 |
+—————+
1 row in set (0.01 sec)
添加新的 Node 到 Cluster 中：
service mysql start
[root@M1905 mysql]# mysql -u root -p123456 -S /tmp/mysql.sock
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| liu |
| log |
| mysql |
| performance_schema |
| test |
+——————–+
6 rows in set (0.00 sec)
mysql> use liu
Database changed
mysql> show tables;
+—————+
| Tables_in_liu |
+—————+
| liu01 |
+—————+
1 row in set (0.00 sec)
查看 Cluster 的状态：
mysql> show global status like ‘wsrep%’;
+——————————+————————————–+
| Variable_name | Value |
+——————————+————————————–+
| wsrep_local_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 7 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 4 |
| wsrep_received_bytes | 510 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 7 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 3 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.3.82:3306,192.168.3.81:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 16e598fb-dccb-11e4-9ca9-cb37e03eb618 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.9(r93aca2d) |
| wsrep_ready | ON |
+——————————+————————————–+
参数 “wsrep_cluster_size” 变为了 2。因为目前 Cluster 中已经有 2 个 Node 了。
下面就可按照同样步骤，添加更多节点到 Cluster 中了，在这个测试环境中，我们一共有 3 个 Node。
添加第三个节点到集群中
service mysql start
[root@M1905 mysql]# mysql -u root -p123456 -S /tmp/mysql.sock
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| liu |
| log |
| mysql |
| performance_schema |
| test |
+——————–+
6 rows in set (0.00 sec)
mysql> use liu
Database changed
mysql> show tables;
+—————+
| Tables_in_liu |
+—————+
| liu01 |
+—————+
1 row in set (0.00 sec)
测试 Cluster 功能
7.1 模拟 192.168.3.81 节点 1 宕机
[root@M1905 ~]# service mysql stop
Shutting down MySQL (Percona XtraDB Cluster)…. SUCCESS!
[root@M1905 ~]# netstat -tunlp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 809/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 898/master
// 在任意其它节的查看 cluster 状态
mysql> show global status like ‘wsrep%’; // 在任意其它节的查看 cluster 状态
+——————————+—————————————————+
| Variable_name | Value |
+——————————+—————————————————+
| wsrep_local_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 7 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 4 |
| wsrep_received_bytes | 489 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.3.82:3306,192.168.3.83:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0.000483336/0.000670287/0.000857238/0.000186951/2 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 6c70c34d-dccd-11e4-a9fd-a30f91414348 |
| wsrep_cluster_conf_id | 4 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.9(r93aca2d) |
| wsrep_ready | ON |
+——————————+—————————————————+
# 发现 wsrep_incoming_addresses 的 192.168.3.81:3306 不存在，并且 wsrep_cluster_size 可用节点为 2.
7.2 在 192.168.3.83 节点 3 上面插入新数据
mysql> select * from liu01;
+——+——+
| id | name |
+——+——+
| 1 | aa |
+——+——+
1 row in set (0.00 sec)
mysql> insert into liu01 values(2,’bb’);
mysql> select * from liu01;
+——+——+
| id | name |
+——+——+
| 1 | aa |
| 2 | bb |
+——+——+
2 rows in set (0.00 sec
7.3 恢复 192.168.3.81 节点 1mysql 服务，及查询节点 1 数据是否正常。
[root@M1905 ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster)…. SUCCESS!
[root@M1905 ~]# mysql -u root -p123456 -S /tmp/mysql.sock
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| liu |
| log |
| mysql |
| performance_schema |
| test |
+——————–+
6 rows in set (0.00 sec)
mysql> use liu
Database changed
mysql> select * from liu01;
+——+——+
| id | name |
+——+——+
| 1 | aa |
| 2 | bb |
+——+——+
2 rows in set (0.00 sec)
mysql> show global status like ‘wsrep%’;
+——————————+——————————————————-+
| Variable_name | Value |
+——————————+——————————————————-+
| wsrep_local_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 8 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 287 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.3.82:3306,192.168.3.81:3306,192.168.3.83:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 4be90b42-dccf-11e4-83f4-5238aff4e818 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | fdfec3ab-d9e8-11e4-94aa-ae45256a81fb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.9(r93aca2d) |
| wsrep_ready | ON |
+——————————+——————————————————-+
发现 wsrep_incoming_addresses 的 192.168.3.81:3306 已经存在，并且 wsrep_cluster_size 可用节点为 3.
8. 感觉 ”Percona XtraDB cluster“的高可用增加删除节点很方便，自动切换故障，如果能配合 atlas 做读写分离就完美了，但是经过测试,cluster 写性能真的不咋的，如果对写要求不高，还是不错的选择。
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-05/143497.htm
