如何搭建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- ...

一、環境準備

主機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 [email protected]

註意：這個是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