如何搭建Percona XtraDB Cluster集群
一、环境准备
主机IP 主机名 操作系统版本 PXC
192.168.244.146 node1 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30
192.168.244.147 node2 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30
192.168.244.148 node3 CentOS7.1 Percona-XtraDB-Cluster-56-5.6.30
关闭防火墙或者允许3306, 4444, 4567和4568四个端口的连接
关闭SElinux
二、下载PXC
安装PXC yum源
# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
这样会在/etc/yum.repos.d下生成percona-release.repo文件
安装PXC
# yum install Percona-XtraDB-Cluster-56
最终下载下来的版本是Percona-XtraDB-Cluster-56-5.6.30
注意：三个节点上均要安装。
三、配置节点
配置节点一
修改node1的/etc/my.cnf
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so # Cluster connection URL contains the IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #1 address wsrep_node_address=192.168.244.146 # SST method wsrep_sst_method=xtrabackup-v2 # Cluster name wsrep_cluster_name=my_centos_cluster # Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
启动node1
# systemctl start mysql@bootstrap.service
注意：这个是CentOS 7下的启动方式，如果是CentOS 6，则启动方式为 # /etc/init.d/mysql bootstrap-pxc
之所以采用bootstrap启动，其实是告诉数据库，这是第一个节点，不用进行数据的同步。
利用这种方式启动，相当于wsrep_cluster_address方式设置为gcomm://。
此时，可登录客户端查看数据库的状态
mysql> show status like 'wsrep%';
主要关注以下参数的状态
+------------------------------+--------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c | ... | wsrep_local_state | 4 | wsrep_local_state_comment | Synced | ... | wsrep_cluster_size | 1 ... | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON |
在上面的配置文件中，有个wsrep_sst_auth参数。该参数是用于其它节点加入到该集群中，利用XtraBackup执行State Snapshot Transfer（类似于全量同步）的。
所以，接下来是授权
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret'; mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql> FLUSH PRIVILEGES;
配置节点二
修改node2的/etc/my.cnf
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so # Cluster connection URL contains the IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #2 address wsrep_node_address=192.168.244.147 # SST method wsrep_sst_method=xtrabackup-v2 # Cluster name wsrep_cluster_name=my_centos_cluster # Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
启动node2
# systemctl start mysql
如果是CentOS 6，则启动方式为 # /etc/init.d/mysql start
如果在启动的过程中出现问题，可查看mysql的错误日志，如果是RPM安装，默认是/var/lib/mysql/主机名.err
启动完毕后，也可通过mysql> show status like 'wsrep%';命令查看集群的信息。
配置节点三
修改node3的/etc/my.cnf
[mysqld] datadir=/var/lib/mysql user=mysql # Path to Galera library wsrep_provider=/usr/lib64/galera3/libgalera_smm.so # Cluster connection URL contains the IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148 # In order for Galera to work correctly binlog format should be ROW binlog_format=ROW # MyISAM storage engine has only experimental support default_storage_engine=InnoDB # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2 # Node #3 address wsrep_node_address=192.168.244.148 # SST method wsrep_sst_method=xtrabackup-v2 # Cluster name wsrep_cluster_name=my_centos_cluster # Authentication for SST method wsrep_sst_auth="sstuser:s3cret"
启动node3
# systemctl start mysql
登录数据库，查看集群的状态
+------------------------------+--------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c | ... | wsrep_local_state | 4 | wsrep_local_state_comment | Synced | ... | wsrep_cluster_size | 3 ... | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON
通过wsrep_cluster_size可以看出集群有3个节点。
四、 测试
下面来测试一把，在node3中创建一张表，并插入记录，看node1和node2中能否查询得到。
node3中创建测试表并插入记录
root@node3 > create table test.test(id int,description varchar(10)); Query OK, 0 rows affected (0.18 sec) root@node3 > insert into test.test values(1,'hello,pxc'); Query OK, 1 row affected (0.01 sec)
node1和node2中查询
root@node1 > select * from test.test; +------+-------------+ | id | description | +------+-------------+ | 1 | hello,pxc | +------+-------------+ 1 row in set (0.00 sec)
root@node2 > select * from test.test; +------+-------------+ | id | description | +------+-------------+ | 1 | hello,pxc | +------+-------------+ 1 row in set (0.05 sec)
至此，Percona XtraDB Cluster搭建完毕~
总结：
1. 刚开始启动node2的时候，启动失败，错误日志中报如下信息：
2016-06-15 20:06:09 4937 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) at gcomm/src/pc.cpp:connect():162 2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out) 2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1387: Failed to open channel 'my_centos_cluster' at 'gcomm://192.168.244.146,192.168.244.147,192.168.244.148': -110 (Connection timed out) 2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs connect failed: Connection timed out 2016-06-15 20:06:09 4937 [ERROR] WSREP: wsrep::connect(gcomm://192.168.244.146,192.168.244.147,192.168.244.148) failed: 7 2016-06-15 20:06:09 4937 [ERROR] Aborting
2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.244.147' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5870' '' Read: '(null)' 2016-06-15 20:27:03 5870 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.244.147' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5870' '' : 2 (No such file or directory) 2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable. 2016-06-15 20:27:03 5870 [ERROR] Aborting
特别是下面的报错信息，根据https://mariadb.com/kb/en/mariadb/problem-with-the-galera-wsrep_sst_method-xtrabackup-v2/的解决思路，还以为是socat的版本太低。
后来才发现，是SElinux没有关闭。。。
另外，在节点加入集群的过程中，如果报有关xtrabackup-v2的错误，不妨先将wsrep_sst_method的方式设置为rsync或者mysqldump，看能否成功。
2. 以systemctl start mysql@bootstrap.service启动的节点，必须以systemctl stop mysql@bootstrap.service关闭，如果以systemctl stop mysql关闭，则没效果。
参考文档：
1. http://www.cnblogs.com/zejin2008/p/5475285.html
2. PXC官方文档
3. http://galeracluster.com/documentation-webpages/
