Enterprise mainstream MySQL high-availability Cluster architecture trilogy of PXC

Some time ago, Lao Zhang introduced the mainstream MySQL high-availability Cluster Architecture trilogy in the first two parts, there are students who do not know to visit my previous blog content.

First through train >> MySQL mainstream high-availability architecture in the enterprise the MHA of the actual battle trilogy

Second section through train >> MySQL high-availability cluster architecture in the Enterprise mm+keepalived of the trilogy

Exclusive new courses on-line >>mysql architecture in-depth analysis and actual practice DBA Video Course

Today , we introduce the last song, is Percona Company's Percona xtraDB cluster. Short PXC. It is a highly available cluster scheme based on the Galera protocol . It can realize synchronous replication of data and read and write between multiple nodes, and can guarantee the high availability of the database and strong data consistency .

PXC Frame Composition:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9E/5E/wKiom1mPuZ-AcvEJAAB5ptTZsXk738.jpg "title=" Wkiom1l3dyrgmaz4aab5pfygsa4564.jpg "alt=" Wkiom1mpuz-acvejaab5pttzsxk738.jpg "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/9E/4D/wKioL1mPue6S13UXAAC4JD27z34209.png-wh_500x0-wm_ 3-wmp_4-s_2681636939.png "title=" 1.png "alt=" Wkiol1mpue6s13uxaac4jd27z34209.png-wh_50 "/>

PXC belongs to a nearly perfect MySQL high-availability cluster architecture scheme;

Summary of Advantages:

1. Synchronization can be achieved at all times, no delay phenomenon occurs

2. Fully compatible with MySQL

3. For new nodes in the cluster, maintenance is simple.

4. Strong consistency of data

Summary of deficiencies:

1. Supports only INNODB storage engines

2. There is a multi-node update update problem, that is, write amplification problem

3. Online DDL statements, locking table issues

4. The problem of high transport cost for new node added by SST

Actual combat process:

Environment Introduction:

192.168.56.100 node1192.168.56.101 node2192.168.56.102 Node3

Pre-Installation considerations:

Three of the machine's firewall iptables to shut down, three machines server-id can not be the same.

PXC software package Download:
https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/LATEST/

Here I downloaded: percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64.tar.gz

The next three machines will need to be installed on the base package, using Yum to install and resolve dependencies.

Perl-io-socket-ssl.noarch perl-dbd-mysql.x86_64 perl-time-hires OpenSSL openssl-devel socat

Perform the operation on node one (192.168.56.100):

Unpack the package and give it permission first

CD/USR/LOCAL/TAR-ZXVF percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64.tar.gzln-s percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64 Mysqlchown mysql:mysql-r mysqlmkdir-p/data/mysqlchown Mysql:mysql-r/data/mysql

Configure the PXC parameter file, here is more than the normal MySQL parameter file, the following points:

Mention a little bit of caution: Here the Binlog format must be row

#pxcdefault_storage_engine =innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs---------------The name of the cluster wsrep_ cluster_address=gcomm://192.168.56.100,192.168.56.101,192.168.56.102 (IP of the node in the cluster) wsrep_node_address= 192.168.56.100------The IP address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method= XTRABACKUP-V2 (SST transfer method) Wsrep_sst_auth=sst:zs (account privileges)

Initialize data:

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql/--defaults-file=/etc/ MY.CNF--user=mysql

Start the service for the first node:

CD/USR/LOCAL/MYSQL/SUPPORT-FILESCP Mysql.server/etc/init.d/mysql

[Email protected] support-files]#/etc/init.d/mysql bootstrap-pxcbootstrapping PXC (Percona XtraDB Cluster) MySQL ( Percona xt[OK]ster) running (4740)

After successful startup, maintain the database environment and create the user

Mysql> Delete from mysql.user where user!= ' root ' or host!= ' localhost ';mysql> grant all privileges on * * to ' zs ' @ '% ' Identified by ' ZS ';mysql> grant all privileges on * * to ' sst ' @ ' localhost ' identified by ' ZS ';mysql> flush Privileg Es

Perform the operation on node two (192.168.56.101):

CD/USR/LOCAL/TAR-ZXVF percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64.tar.gzln-s percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64 Mysqlchown mysql:mysql-r mysqlmkdir-p/data/mysqlchown Mysql:mysql-r/data/mysql

To edit a PXC configuration file:

#pxcdefault_storage_engine =innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs---------------The name of the cluster wsrep_ cluster_address=gcomm://192.168.56.100,192.168.56.101,192.168.56.102 (IP of the node in the cluster) wsrep_node_address= 192.168.56.101------The IP address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method= XTRABACKUP-V2 (SST transfer method) Wsrep_sst_auth=sst:zs (account privileges)

Initialize data:

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql/--defaults-file=/etc/ MY.CNF--user=mysql

Start a service for the second node:

CD/USR/LOCAL/MYSQL/SUPPORT-FILESCP Mysql.server/etc/init.d/mysql

[Email protected] mysql]#/etc/init.d/mysql startstarting MySQL (Percona XtraDB Cluster). [OK]

Execute on the third node (192.168.56.102):

CD/USR/LOCAL/TAR-ZXVF percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64.tar.gzln-s percona-xtradb-cluster-5.6.21-rel70.1-25.8.938.linux.x86_64 Mysqlchown mysql:mysql-r mysqlmkdir-p/data/mysqlchown Mysql:mysql-r/data/mysql

To edit a PXC configuration file:

#pxcdefault_storage_engine =innodbinnodb_autoinc_lock_mode=2wsrep_cluster_name=pxc_zs---------------The name of the cluster wsrep_ cluster_address=gcomm://192.168.56.100,192.168.56.101,192.168.56.102 (IP of the node in the cluster) wsrep_node_address= 192.168.56.102------The IP address of the current machine wsrep_provider=/usr/local/mysql/lib/libgalera_smm.sowsrep_sst_method= XTRABACKUP-V2 (SST transfer method) Wsrep_sst_auth=sst:zs (account privileges)

Initialize data:

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql/--defaults-file=/etc/ MY.CNF--user=mysql

Start a service for the third node:

CD/USR/LOCAL/MYSQL/SUPPORT-FILESCP Mysql.server/etc/init.d/mysql

[Email protected] mysql]#/etc/init.d/mysql startstarting MySQL (Percona XtraDB Cluster). [OK]

Note: The other two nodes are also installed and initialized operation;

But the starting method is not to/etc/init.d/mysql BOOTSTRAP-PXC,/etc/init.d/mysql start this way

Three nodes have been successfully started, so that the operation of one DML statement on any one node will be synchronized to another two nodes.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9E/66/wKiom1mQQ7Tx2e4MAAAsJ2ViqQ8543.png "title=" 2.png "alt=" Wkiom1mqq7tx2e4maaasj2viqq8543.png "/>

This is the end of teacher Zhang's enterprise MySQL mainstream high-availability Cluster Architecture trilogy! There is a defect in the place, please give us a lot of advice.

In the future will be more good articles dedicated to everyone, technology only in the unlimited sharing, to get further sublimation!

Let's refuel together!

This article from "Zhang Mengsu Blog" blog, reproduced please contact the author!

Enterprise mainstream MySQL high-availability Cluster architecture trilogy of PXC