你的位置:首页 > 数据库

[数据库]如何搭建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/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_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 ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #1 addresswsrep_node_address=192.168.244.146# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_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/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_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 ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #2 addresswsrep_node_address=192.168.244.147# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_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/mysqluser=mysql# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_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 ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node #3 addresswsrep_node_address=192.168.244.148# SST methodwsrep_sst_method=xtrabackup-v2# Cluster namewsrep_cluster_name=my_centos_cluster# Authentication for SST methodwsrep_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():1622016-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 out2016-06-15 20:06:09 4937 [ERROR] WSREP: wsrep::connect(gcomm://192.168.244.146,192.168.244.147,192.168.244.148) failed: 72016-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/