一、環境准備
主機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/