MySQL configures PXC high availability cluster

To configure PXC High availability cluster

Percona XtraDB Cluster( abbreviation PXC) - Is based on Galera Of MySQL High availability cluster solution -Galera Cluster yes Codership A set of free and open source high availability solutions developed by the company

PXC Cluster features
Strong data consistency 、 No synchronization delay - There is no master-slave switching operation , No need to use virtual IP
Support InnoDB Storage engine - Multithreaded replication
Deployment is easy to use
Support nodes to join automatically , No need to copy data manually

 Service port description 
3306		 Database service port 
4444		SST port    Non real time open port 
4568		IST port 		 Non real time open port non real time open port 
4567 Cluster communication port 
SST 	State Snapshot Transfer Full amount of synchronization 
IST		Incremental State Transfer  The incremental synchronization 

1、 Software package – Install in order

percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm			# Online hot standby program 
qpress-1.1-14.11.×86_64.rpm					# Daily compression program 
Percona-XtraDB-Cluster-server-57-5.7.25-31.35.1.el7.x86_64.rpm# Cluster service program 

 Install the software  -3 Servers are installed in turn  
~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm			 Installation dependency 
~]# yum -y install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm# Installation dependency  
~]# rpm -ivh qpress-1.1-14.11.x86_64.rpm
~]# tar -xvf Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
~]# yum -y install Percona-XtraDB-Cluster-*.rpm
~]#ls /etc/percona-xtradb-cluster.conf.d/
mysqld.cnf  		# Database service profile , modify server_id
mysqld_safe.cnf  		# Database service process mysqld Configuration parameters for , There is no need to modify 
wsrep.cnf   			# Cluster profile 

2、 The configuration file mysqld.cnf file

[mysqld] 
server-id=1				//server-id No repetition  
datadir=/var/lib/mysql				/ Database directory  
socket=/var/lib/mysql/mysql.sock 		//socket file 
log-error=/var/log/mysqld.log			/ Log files  
pid-file=/var/run/mysqld/mysqld.pid		 //pid file  
log-bin				// Enable binlog journal  
log_slave_updates			/ Enable chained replication  
expire_logs_days=7		// Log file retention days 

2） To configure id colony IP sstuser

db1 ~]# vim  /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
server-id=11			##db2  db3  Modify in turn 

db1~ ]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf		
8  wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13
25 wsrep_node_address=192.168.1.11				# This machine ip
27 wsrep_cluster_name=pxc-cluster						# identical 
30 wsrep_node_name=pxc-cluster-node-11				# Different 
39 wsrep_sst_auth="sstuser:123qqq...A"					# The password is the same \

db2 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf		
8 wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13
25 wsrep_node_address=192.168.1.12				
27 wsrep_cluster_name=pxc-cluster						
30 wsrep_node_name=pxc-cluster-node-12				
39 wsrep_sst_auth="sstuser:123qqq...A"		

73 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf		
8 wsrep_cluster_address=gcomm://192.168.1.11,192.168.1.12,192.168.1.13
25 wsrep_node_address=192.168.1.13				
27 wsrep_cluster_name=pxc-cluster						
30 wsrep_node_name=pxc-cluster-node-13				
39 wsrep_sst_auth="sstuser:123qqq...A"	

3） Cluster initialization [email protected] to grant authorization sstuser

db1 ~]# systemctl start [email protected]			# Wait patiently , Slightly longer 
db1 ~]# systemctl status [email protected]
db1 ~]# ss -ntulp | grep 3306
db1 ~]# ss -ntulp | grep 4567
db1 ~]# grep -i password /var/log/mysqld.log
mysql> alter user root@localhost identified by "123456";
mysql> exit

db1 ~]# mysql -uroot -p123456
mysql> grant  all  on  *.*  to  sstuser@localhost identified  by "123qqq...A"; 
##sstuser The user has written... In the configuration 
mysql> garnt reload, lock tables,replication client,process on *.*  to
 sstuser@"localhost” identified by  “123qqq…A”; # Main required permissions 
mysql>create database db;			# Build a database to store data , Auto sync later 
mysql> create table db.a(id int primary key auto_increment,name char(10));
mysql> insert into db.a(name) values("A");    
#pxc When the cluster stores data , Table must have a primary key , Self increasing step size random , Less than or equal to the number of servers , Not more than 4.
mysql> select  * from db.a;

4） start-up 12,13 The database service of the host , Complete synchronization

~]# ls /var/lib/mysql
~]# systemctl start mysql     # Do full synchronization , Services in the cluster do not need to bring d
~]# ls /var/lib/mysql     # Can see sock And others 
~]# ss -ntulp | grep 3306
~]# ss -ntulp | grep 4567
~]# mysql -uroot -p123456
> insert into db.a(name) values("B");
> insert into db.a(name) values("c");
 start-up 33 The database service of the host , The check operation is the same as 72

db1 initialization failed ,  Follow the steps below to solve the problem 
1  Check  mysqld.cnf  and   wsrep.cnf Modification of 
2 rm -rf /var/lib/mysql/*
3  Check  firewalld   selinux  Whether to shut down 
4  Perform initialization again 
db1 Successful initialization ,  but db2,3 Failed to start service , Follow the steps below to solve the problem 
1  Check  firewalld   selinux  Whether to shut down 
2  Check db2,3mysqld.cnf  and   wsrep.cnf Modification of 
3  , respectively, db2,3 perform rm -rf /var/lib/mysql/*
4  , respectively, db2,3 Execute startup service  

5） test PXC colony （ At least make sure you do 1 The desk is working )

db2
mysql> show status like "%wsre%";     # Main information 
wsrep_incoming_addresses    |192.168.1.13:3306,192.168.1.11:3306,192.168.1.12:3306 |
wsrep_cluster_size       3 					// Number of cluster servers 
wsrep_cluster_status   Primary     // The host state                            
wsrep_connected        ON            // Connection status 
wsrep_ready             ON           // Service status 

db1 ~]# systemctl stop [email protected]      Start and stop for the first time 

db2
mysql> show status like "%wsre%";
|wsrep_incoming_addresses    |192.168.1.13:3306,192.168.1.12:3306 |

db1 ~]# systemctl start mysql
db1 ~]# mysql -uroot -p123456 -e  'select  * from db.a'    # Auto join auto sync 
db1 ~]# mysql -uroot -p123456 -e 'grant all on *.*  to testt@"%" identified by "123456"'

db3 ~]# mysql -uroot -p123456 -e 'select user from mysql.user'  

client ~]# mysql -h192.168.1.11 -utest-p123456   
mysql> insert into db.a(name)values("AAA");
mysql> exit
client ~]# mysql -h192.168.1.12 -utest-p123456 -e 'select * from db.a'