mysql pxc mysql5.7_mysql之PXC5.7.18集群系列——1. Percona XtraDB Cluster 搭建

1. 相關准備

2. 集群節點

Nodes

Hostname

IP

內存

HAProxy

haproxy

172.16.3.30

4G

Node1

mysql01

172.16.3.31

4G

Node2

mysql02

172.16.3.32

4G

Node3

mysql03

172.16.3.33

4G

3. 先決條件

(1) 開放端口

# iptables -I INPUT -p tcp -m multiport –dport 3306,4444,4567,4568 -j ACCEPT

# service iptables save

# service iptables restart備注：1.service iptables status 查看防火牆狀態

(2) SELinux

# vim /etc/selinux/config

SELINUX = disabled

# setenforce 0備注：1.SELINUX = permissive 或者 setenforce 0 都是設置SELINUX狀態為permissive，只不過前者需要重啟服務器，后者立即生效

備注：2.getenforce 查看SELINUX狀態

4. 安裝文件

文件

描述

Percona-XtraDB-Cluster

Percona-XtraDB-Cluster-5.7.18-29.20-r346-el6-x86_64-bundle.tar

socat

yum -y install socat

libev

yum -y install libev

perl-DBD-MySQL

yum -y install perl-DBD-MySQL

xtrabackup

percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm

cluster

Percona-XtraDB-Cluster-57-5.7.18-29.20.1.el6.x86_64.rpm

client

Percona-XtraDB-Cluster-client-57-5.7.18-29.20.1.el6.x86_64.rpm

server

Percona-XtraDB-Cluster-server-57-5.7.18-29.20.1.el6.x86_64.rpm

shared

Percona-XtraDB-Cluster-shared-57-5.7.18-29.20.1.el6.x86_64.rpm

5. 創建目錄

# mkdir -p /home/mysql/{data,log,run,backup}備注：1.data，mysql數據目錄

備注：2.log，mysql日志目錄

備注：3.run，mysql運行目錄

備注：4.backup，mysql備份目錄，推薦使用xtrabackup

6. my.cnf 配置

(1) Node1

[mysql]

port = 3306

socket = /home/mysql/run/mysql.soc

[mysqld]

### Percona XtraDB Cluster 官方配置 begin

wsrep_provider = /usr/lib64/galera3/libgalera_smm.so

wsrep_cluster_name = pxc-cluster

wsrep_cluster_address = gcomm://172.16.3.31,172.16.3.32,172.16.3.33

wsrep_node_name = pxc1

wsrep_node_address = 172.16.3.31

wsrep_sst_method = xtrabackup-v2

wsrep_sst_auth = sstuser:sstpassword

wsrep_provider_options=”gcache.size=8G;” #個人(非官方)強烈推薦

pxc_strict_mode = enforcing

binlog_format = ROW

default_storage_engine = InnoDB

innodb_autoinc_lock_mode = 2

### Percona XtraDB Cluster 官方配置 end

#### 自定義配置 begin

user = mysql

port = 3306

datadir = /home/mysql/data

log-bin = /home/mysql/log/mysql-bin

expire_logs_days = 14

log-error = /home/mysql/log/mysql.err

pid-file = /home/mysql/run/mysql.pid

socket = /home/mysql/run/mysql.soc

character_set_server = utf8

collation_server = utf8_general_ci

### 自定義配置 end

### 性能優化 begin

innodb_buffer_pool_size = 3G #80%

innodb_log_file_size = 256M #5%

innodb_flush_method = O_DIRECT #避免雙緩沖技術

max_allowed_packet = 16777216 #最大允許的數據包大小，16M，默認1024*1024*4

sync_binlog = 0 #事務已提交，而無需同步到磁盤

innodb_flush_log_at_trx_commit = 0 #0意味着刷新到磁盤，但不同步(在提交時不執行實際IO)

### 性能優化 end備注：wsrep_provider_options="gcache.size=8G"，個人強烈推薦，原因：

利用xtrabackup+IST方式而不是SST向已有集群中加入新Node，詳見接下來的系列教程。

(2) Node2

wsrep_node_name = pxc2

wsrep_node_address = 172.16.3.32

(3) Node3

wsrep_node_name = pxc3

wsrep_node_address = 172.16.3.33

7. 文件權限

# chowm -R mysql.mysql /home/mysql

8. 集群啟動

(1) Node1

# service mysql bootstrap-pxc

(2) Node2

# service mysql start

(3) Node3

# service mysql start

9. 創建賬號

sstuser，用於xtrabackup在集群之間同步數據

mysql> create user ‘sstuser’@’localhost’ identified by ‘sstpassword’;

mysql> grant process, reload, lock tables, replication client on *.* to ‘sstuser’@’localhost’;

10. 查看集群狀態

mysql> show status like ‘wsrep%’;

11. HAProxy

haproxy的搭建請留意下一篇博客。