SUSE SP3 to build pxc (Percona xtradb Cluster)

1. Download source

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. Compile OpenSSL

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

#make && make Install

3. Compile PXC

#mkdir output

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

If everything goes well, you can find the compression package for the final compilation result in output

4. Installation

#将编译出来的压缩包, extract to/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 File

# 1) Comment out. /etc/rc.d/init.d/functions

# 2) Modify Basedir point/OPT/MYSQL-PXC

# 3) Modify DataDir point/data/mysql-pxc/data/

#修改 all hard-coded paths in the/opt/mysql-pxc/bin/mysqld_safe, pointing to the/OPT/MYSQL-PXC

5. Configuration: 3 Server Scenarios

Server address: 157.0.0.51, 157.0.0.52, 157.0.0.61

5.1 Configuration/etc/my.cnf

[Mysqld]

SERVER_ID = 51# must ensure that each server has a different number

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 # native Address

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

Wsrep_sst_auth = "Mytest:mytest"

Wsrep_node_name = pxc51 # native Name

5.2 Initialization & Startup

1 initialization of each database

#/opt/mysql-pxc/bin/mysqld--initialize--BASEDIR=/OPT/MYSQL-PXC

2) Start the first station

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

3) Start Other machines

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

4 Reboot the first machine

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

5) Verify

#修改密码

#在mysql日志中查找初始密码, login MySQL:/opt/mysql-pxc/bin/mysql-uroot-p-h127.0.0.1

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

Mysql>flush privileges;

# View the number of cluster nodes

Mysql> show global status like ' Wsrep_cluster_size ';

# View the node addresses of the cluster

Mysql> show global status like ' wsrep_incoming_addresses ';

6. Supplementing

6.1 Support for Binlog log remote replication across the cluster

Select a PXC node as master, assuming that IP is 157.0.0.51, assuming that the remote slave machine address is 157.0.0.99, you need to configure the following

1 Add the following lines in the MY.CNF configuration file [mysqld] section of each PXC node

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

Log-bin = Mysql-bin

binlog-do-db = N1 # Name of the database to be replicated

replicate-do-db = N1 # Name of the database to be replicated

Log_slave_updates

Add a user to the PXC node for example:

Mysql>grant replication Slave on *.* to ' rep ' @ ' 157.0.0.99 ' identified by ' pass ';

Mysql>flush privileges;

2) configured on remote slave node

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 How to lift yourself

Restart MySQL mode:

/ETC/INIT.D/MYSQL-PXC bootstrap-pxc--wsrep-cluster-address= "gcomm://"

Do not restart MySQL mode:

Mysql>set GLOBAL wsrep_provider_options= ' Pc.bootstrap=yes ';

6.3 How to ignore the brain crack

Mysql>set GLOBAL wsrep_provider_options= "Pc.ignore_sb=true";

6.4 Set the node weight to control the vote, can solve the problem of brain crack, but also lead to reduced availability

#默认都为1

Mysql>set GLOBAL wsrep_provider_options= "pc.weight=3";

The correct way to avoid brain crack in 6.5-node is to use PXC's own arbitrator GARBD