共计 7376 个字符，预计需要花费 19 分钟才能阅读完成。
PXC(Percona XtraDB Cluster) 原理：
节点接收 SQL 请求后，在 commit 之前，由 wsrepAPI 调用 galera 库进行集群内广播，所有其他节点验证成功后事务在所有节点进行提交，反之 rollback。pxc 保证整个集群所有数据的强一致性，满足 CAP 理论中：consistency 和 availability
在一个事务提交的过程，Node1 提交一个事物，必须要所有的节点通过了这个事务的请求，并且返回成功（ok）或者失败（conflict）信号之后，才真正的 commit 之后返回结果给用户，但是这里注意，最后在其它节点 apply 过程，并不会影响到给用户返回结果的过程。
大多数 PXC 集群都是单机单实例的，但是也有例外，为了充分利用机器性能，并且需要 PXC 集群架构，于是，多实例与多集群的部署需求就来了，以下便是安装步骤：
1． 环境描述：
CentOS7.2 PXC5.7.14 | cluster1 | cluster2 | cluster3 |
node1,192.168.252.227 | 3307,4567 | 3308,5567 | 3309,6567 |
node2,192.168.252.228 | 3307,4567 | 3308,5567 | 3309,6567 |
node3,192.168.252.229 | 3307,4567 | 3308,5567 | 3309,6567 |
2． 安装配置 cluster1
yum install socat nc -y
tar zxf Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101.tar.gz -C/usr/local/
ln -s /usr/local/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl101/usr/local/MySQL
mkdir /export/mysql/mysql3307/{binlog,data,innodb,logs,tmp} -p
vim /export/mysql/mysql3307/my3307.cnf
[client]
port = 3307
socket = /export/mysql/mysql3307/tmp/mysql3307.sock
[mysql]
prompt = ‘<\U:\p [\d]>’
default_character_set = utf8
no_auto_rehash
[mysqld]
###GENERAL ###
user = mysql
port = 3307
socket = /export/mysql/mysql3307/tmp/mysql3307.sock
pid_file = /export/mysql/mysql3307/tmp/3307mysql.pid
datadir = /export/mysql/mysql3307/data
tmpdir = /export/mysql/mysql3307/tmp
character_set_server = utf8
lower_case_table_names = 1
default_storage_engine = InnoDB
#innodb = FORCE
###NETWORK AND LIMITS ###
back_log = 100
max_allowed_packet = 16M
max_connect_errors = 10000
max_connections = 1024
max_user_connections = 1000
interactive_timeout = 7200
wait_timeout = 7200
connect_timeout = 30
skip_external_locking
skip_name_resolve
###CACHES ###
thread_cache_size = 64
table_open_cache = 4096
table_definition_cache = 2048
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 512M
max_heap_table_size = 512M
###PER-THREAD BUFFERS ###
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
bulk_insert_buffer_size = 64M
###PERCONA SERVER ###
#extra_port = 13306
#gtid_mode = on
#enforce_gtid_consistency = 1
thread_handling = pool-of-threads
thread_pool_oversubscribe = 8
explicit_defaults_for_timestamp
###MyISAM ###
key_buffer_size = 128M
myisam_sort_buffer_size = 32M
###INNODB ###
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 32G
#innodb_buffer_pool_instances = 8
innodb_data_home_dir = /export/mysql/mysql3307/innodb
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_group_home_dir = /export/mysql/mysql3307/innodb
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 10
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_open_files = 4096
#log_bin_trust_function_creators= 1
#innodb_flush_neighbors = 0
#innodb_io_capacity = 2000
#innodb_page_size = 8K
###REPLICATION ###
server_id = 2273307
sync_binlog = 0
log_bin = /export/mysql/mysql3307/binlog/mysql-bin
binlog_format = ROW
max_binlog_size = 256M
expire_logs_days = 10
binlog_cache_size = 1M
master_info_file = /export/mysql/mysql3307/binlog/master.info
relay_log = /export/mysql/mysql3307/binlog/relay-log
relay_log_info_file = /export/mysql/mysql3307/binlog/relay-log.info
max-relay-log-size = 256M
#relay_log_purge = 0
#read_only = 1
log_slave_updates = 1
slave_net_timeout = 60
skip_slave_start = 1
slave_parallel_workers = 16
#replicate-wild-do-table = mysql.%
#replicate-wild-ignore-table = test.%
#auto-increment-offset = 1
#auto-increment-increment = 2
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
###LOG ###
slow_query_log = 1
slow_query_log_file = /export/mysql/mysql3307/logs/slow.log
log_queries_not_using_indexes = 0
long_query_time = 1
log_error = /export/mysql/mysql3307/logs/error.log
#general_log = 0
general_log_file = /export/mysql/mysql3307/logs/general.log
###PXC ###
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = cluster1
wsrep_cluster_address = gcomm://192.168.252.227,192.168.252.228,192.168.252.229
wsrep_node_address = 192.168.252.227
wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = thunder:thunder
[mysqld_safe]
open-files-limit = 65535
#malloc-lib = /usr/local/mysql/lib/mysql/libjemalloc.so
[mysqldump]
quick
max_allowed_packet = 128M
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive_timeout
# 将配置文件传到其它服务器上，并修改里面对应的 wsrep_node_address
scp -r /export/mysql/mysql3307 192.168.252.228:/export/mysql
scp -r /export/mysql/mysql3307 192.168.252.229:/export/mysql
# 在 node1 上面初始化并启动 pxc 的第一个节点，并对配置文件中的用户授权
cd /usr/local/mysql
./bin/mysqld–defaults-file=/export/mysql/mysql3307/my3307.cnf –initialize-insecure
./bin/mysqld–defaults-file=/export/mysql/mysql3307/my3307.cnf –wsrep-new-cluster &
mysql>GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘thunder’@’localhost’IDENTIFIED BY ‘thunder’;FLUSH PRIVILEGES;
# 在 node2,node3 上面启动 pxc 的其它节点，观察是否启动成功，并在 node1 上面进行写入，观察 node2,node3 上面是否有进行同步
./bin/mysqld–defaults-file=/export/mysql/mysql3307/my3307.cnf &
到此，cluster1 就安装完毕了
3． 安装 cluster2
除配置文件外，其它与 cluster1 安装一样，cluster 的端口为 5567
###PXC ###
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = cluster2
wsrep_cluster_address =gcomm://192.168.252.227:5567,192.168.252.228:5567,192.168.252.229:5567wsrep_node_address = 192.168.252.228:5567
wsrep_provider_options = “base_port=5567”
wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = thunder:thunder
4． 安装 cluster3
除配置文件外，其它与 cluster1 安装一样，
###PXC ###
default_storage_engine=Innodb
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = cluster3
wsrep_cluster_address = gcomm://192.168.252.227:6567,192.168.252.228:6567,192.168.252.229:6567
wsrep_node_address = 192.168.252.228:6567
wsrep_provider_options = “base_port=6567”
wsrep_provider =/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = thunder:thunder
5． 说明：
1．PXC 不支持 myisam 引擎，在 node1 上创建 myisam 表后，其它节点不会进行复制。
2．建议的最小集群节点数为 3，虽然两个节点也能运行，但是无法保证数据的稳定，当两节点时，任何一个节点出故障，将导致集群无法访问。最大值 <=8。
3．3 台机器损失一台机器性能。
4．关于 wsrep_sst_method，有 3 种可选:mysqldump,rsync,xtrabackup
mysqldump 最慢
rsync 最快，但是在同步期间会锁表
xtrabackup 可以在线传输，只有在 copy 表结构文件时会锁表，和其备份原理一样
5．启动顺序，如果为单机单实例，则
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
第一个实例启动命令为：
/etc/init.d/mysql bootstarp-pxc
如果为多实例，则通过 mysqld_safe 的方式来启动：
./bin/mysqld –defaults-file=/export/mysql/mysql3307/my3307.cnf –wsrep-new-cluster &
6. 可在启动时指定从哪台机器进行同步
./bin/mysqld –defaults-file=/export/mysql/mysql3307/my3307.cnf –wsrep-sst-donor=’192.168.252.228’&
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-05/143931.htm
