程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 如何搭建Percona XtraDB Cluster集群,perconaxtradb

如何搭建Percona XtraDB Cluster集群,perconaxtradb

編輯:MySQL綜合教程

如何搭建Percona XtraDB Cluster集群,perconaxtradb


一、環境准備

     主機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

特別是下面的報錯信息,根據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 [email protected]啟動的節點,必須以systemctl stop [email protected]關閉,如果以systemctl stop mysql關閉,則沒效果。

 

參考文檔:

1. http://www.cnblogs.com/zejin2008/p/5475285.html

2. PXC官方文檔

3. http://galeracluster.com/documentation-webpages/

    

 

    

 

     

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved