SUSE 11 SP3下搭建PXC(Percona XtraDB Cluster)

1.  下载源码

Percona-XtraDB-Cluster-5.7.19-29.22.tar.gz  - https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/

openssl-1.2.j.tar.gz

2. 编译openssl

#CFLAGS=-fPIC ./config --prefix=/opt/openssl-1.0.2j/

#make && make install

3. 编译PXC

#mkdir output

#./build-ps/build-binary.sh output --with-ssl /opt/openssl-1.0.2j/

一切顺利的话，在output中可以找到最终编译结果的压缩包

4. 安装

#将编译出来的压缩包，解压到/opt/mysql-pxc

#cp support-files/mysql.server /etc/init.d/mysql-pxc

#useradd mysql ; groupadd mysql; usermod mysql -g mysql

#mkdir -p /data/mysql-pxc/{tmp,data,logs} ; chown -R mysql:mysql /data/mysql-pxc

#修改/etc/init.d/mysql-pxc文件

# 1) 注释掉. /etc/rc.d/init.d/functions

# 2) 修改basedir指向/opt/mysql-pxc

# 3) 修改datadir指向/data/mysql-pxc/data/

#修改/opt/mysql-pxc/bin/mysqld_safe中所有的硬编码路径，使指向/opt/mysql-pxc

5. 配置：3台服务器场景

服务器地址：157.0.0.51 , 157.0.0.52, 157.0.0.61

5.1 配置/etc/my.cnf

[mysqld]

server_id = 51# 必须保证每台服务器的编号不同

basedir = /opt/mysql-pxc

port = 3306

datadir = /data/mysql-pxc/data/

tmpdir = /data/mysql-pxc/tmp

socket = /data/mysql-pxc/tmp/mysql.sock

user = mysql

pid-file = /data/mysql-pxc/data/mysqld.pid

log-error = /data/mysql-pxc/logs/mysql.err

general-log-file = /data/mysql-pxc/logs/mysql.log

slow-query-log-file = /data/mysql-pxc/logs/mysql.log

default_storage_engine = InnoDB

innodb_locks_unsafe_for_binlog = 1

innodb_autoinc_lock_mode = 2

wsrep_cluster_name = "mycluster"

wsrep_cluster_address = gcomm://157.0.0.51,157.0.0.52,157.0.0.61

wsrep_node_address = 157.0.0.51 #  本机地址

wsrep_provider = /opt/mysql-pxc/lib/libgalera_smm.so

wsrep_sst_auth = "mytest:mytest"

wsrep_node_name = pxc51 # 本机名称

5.2 初始化 & 启动

1)初始化每台数据库

#/opt/mysql-pxc/bin/mysqld --initialize --basedir=/opt/mysql-pxc

2) 启动第一台

#/etc/init.d/mysql-pxc bootstrap-pxc --wsrep-cluster-address="gcomm://"

3)启动其它机器

#/etc/init.d/mysql-pxc start

4)重启第一台机器

#/etc/init.d/mysql-pxc restart

5)验证

#修改密码

#在mysql日志中查找初始密码，登录mysql: /opt/mysql-pxc/bin/mysql -uroot -p -h127.0.0.1

mysql>set password=password('root');

mysql>flush privileges;

# 查看集群节点个数

mysql> show global status like 'wsrep_cluster_size';

# 查看集群各节点地址

mysql> show global status like 'wsrep_incoming_addresses';

6. 补充

6.1 支持整个集群的binlog日志远程复制

选定一台PXC节点作为master，假设IP为157.0.0.51，假设远程slave机器地址为157.0.0.99，需要进行如下配置

1) 在每个PXC节点的my.cnf 配置文件[mysqld]节中增加如下几行配置

server_id = xx #每个节点不能相同

log-bin = mysql-bin

binlog-do-db = n1 # 需要复制的数据库名

replicate-do-db = n1 # 需要复制的数据库名

log_slave_updates 

在PXC节点上增加一个用户例如：

mysql>grant replication slave on *.* to 'rep'@'157.0.0.99' identified by 'pass';

mysql>flush privileges; 

2) 在远程的slave节点上配置

mysql>change master to master_host='157.0.0.51', master_user='rep', master_password='pass', master_log_file='mysql-bin.000001',master_log_pos=1;

mysql>start slave;

mysql>show slave status\G;

6.2 如何自举

重启mysql方式：

/etc/init.d/mysql-pxc bootstrap-pxc --wsrep-cluster-address="gcomm://"

不重启mysql方式：

mysql>SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';

6.3 如何忽略脑裂

mysql>SET GLOBAL wsrep_provider_options="pc.ignore_sb=true";

6.4 设置节点权重，用于控制投票，可以解决脑裂问题，但同时会导致可用性降低

#默认都为1

mysql>SET GLOBAL wsrep_provider_options="pc.weight=3";

6.5 两节点避免脑裂的正确方法是使用PXC自带的仲裁者garbd