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:
Synchronization can be achieved at all times, no delay phenomenon occurs
Fully compatible with MySQL
For new nodes in the cluster, maintenance is simple.
Strong consistency of data
Summary of deficiencies:
Supports only INNODB storage engines
There is a multi-node update update problem, that is, write amplification problem
Online DDL statements, locking table issues
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