PXC introduction

Percona XtraDB Cluster (PXC for short) Cluster is a universal multi-master synchronous replication plug-in based on Galera 2.x Library for transactional applications. It is mainly used to solve the problem of strong consistency, keep real-time data synchronization between nodes and achieve simultaneous read and write of multiple nodes. Improves the reliability of the database, but also can achieve read and write separation, is the MySQL relational database is recognized as one of the preferred cluster solutions.

PXC is a set of MySQL high availability cluster solution. Compared with the traditional cluster architecture based on master-slave replication mode, the most prominent feature of PXC is to solve the long-standing problem of data replication delay and basically achieve real-time synchronization. And the relationship between nodes is equal. PXC focuses on the consistency of data. When dealing with the behavior of things, it either executes on all nodes or does not execute at all. Its implementation mechanism determines that it is very strict about consistency, which can perfectly ensure the consistency of data in MySQL cluster.

What is Galera Cluster? Galera Cluster is a new, non-shared, highly redundant high availability solution. At present, there are two versions of Galera Cluster, namely Percona Xtradb Cluster and MariaDB Cluster. All of them are based on Galera, so they are collectively called Galera Cluster. Because Galera itself has multi-master features, Galera Cluster is also the multi-master Cluster architecture, as shown in the figure

Figure in the three instances, formed a cluster, and the three nodes, unlike ordinary master-slave architecture, they can be as the main nodes, three nodes are equivalent, the general called multi – master architecture, when have the client to write or read data, literally the connection which instance is the same, read data is the same, After writing to a node, the cluster itself synchronizes the new data to other nodes. This architecture does not share any data and is a highly redundant architecture.

The general method of use is in the cluster, then build a middle layer, the middle layer features include establishing a connection, connection pool management, responsible for the balance of three instances of basic load, is responsible for the client and instance disconnected after reconnection, can also be responsible for reading and writing separation (in the case of different machine performance can do such optimization), etc. After using the middle tier, due to the three examples of architecture is transparent in the client, the client only need to specify the cluster data source address, and connected to the middle layer, middle layer is responsible for the client to connect to the server instance of transfer work, as a result of this architecture to support more write, so often completely avoids the master-slave replication data inconsistency problem, In this way, the master/slave read/write switch can be highly elegant, without affecting users, offline maintenance and other work, MySQL high availability, from now on, very perfect.

Advantages and disadvantages of PXC

advantages

• High service availability

• Synchronous data replication (concurrent replication) with almost no latency

• Multiple nodes that can read and write at the same time can realize write expansion. However, it is better to separate libraries and tables in advance and let each node write different tables or libraries separately to avoid data conflicts resolved by Galera

• New nodes can be automatically deployed, which is easy to deploy

• Strict consistency of data, especially suitable for e-commerce applications

• Fully compatible with MySQL

disadvantages

• Replication is only supported by the InnoDB engine. Changes from other storage engines are not replicated

• Write efficiency depends on the weakest of the nodes, and because PXC clusters operate on the strong consistency principle, a change operation is successful only if it succeeds on all nodes

• All tables must have primary keys

• Explicit LOCK operations such as LOCK TABLE are not supported

• There are more lock conflicts and deadlocks

• The more nodes in a PXC cluster, the slower data synchronization is

Applicable scenario

• Strong data consistency

  Because Galera Cluster can ensure strong data consistency, it is more suitable for scenarios that require high data consistency and integrity, such as transactions

• Write more

  Emphasize more write here, I don’t want to support to provide services in the form of written more, and more importantly, because of the more writing, will only be made when the DBA normal maintenance database cluster, will not affect the business, really no sense, because as long as it is master-slave replication, cannot appear more write, when leading to the switch, It is inevitable to break the connection of the old node, and then cut to the new node, which is unavoidable. Multi-point write is supported, which allows a short multi-point write at the switching time, so as not to affect the old connection, only need to route the new connection to the new node. This feature is also desirable for transactional businesses

• performance

  Galera Cluster can support strong consistency, no doubt, at the expense of performance, strive for data consistency, but ask: “performance sacrifice, will not result in poor performance, such an architecture can not meet the needs?” Suffice it to say that this is a tradeoff process, how many businesses are QPS too big for Galera Cluster to meet? I think it is not much (of course there are some, you can do some tests), in pursuit of very high extreme performance, maybe a single Galera Cluster Cluster is not enough, but after all there are few, so it is good enough, Galera Cluster must be the leader of MySQL solution

Difference between PXC and Replication

The principle of PXC

• Fully compatible with MySQL

• Synchronous replication, where transactions are either committed or not committed on all nodes

• Multi-master replication, which can write on any node

• Apply events in parallel on the slave server, true parallel replication

• Automatic node configuration, data consistency, no longer asynchronous replication

• Failover: Because multipoint write is supported, it is easy to failover in the event of a database failure

• Automatic node cloning: When new nodes are added or maintenance is down, incremental data or basic data does not need to be manually backed up. Galera Cluster automatically pulls online node data and the cluster becomes consistent

• Local implementation

  This is the initial stage of transaction execution, so to speak, and the execution process of this stage is no different from the single point of MySQL execution, and the concurrency control is of course the database concurrency control, not the Galera Cluster concurrency control, right

• Write a set to send

  After the execution, it comes to the submission stage. Before the submission, the generated write set will be broadcast out first. In order to ensure the consistency of global data, when the write set is sent, serial is required, which is part of the concurrency control of Galera Cluster

• Writing was verified

  This stage is the validation of Galera Cluster. The validation is to verify the current transaction with the local write set to verify the cache set. By comparing the database KEYS affected by the write set to find if there is any similarity, the verification can be passed

• Write a set to submit

  Repl.com mit_ORDER (default: 3) indicates that the commit is serial. The default configuration is recommended because the binlogs generated by different nodes in the cluster are the same, which is beneficial and convenient for O&M

• Write set the APPLY

The slave node consists of only two phases, namely write set validation and write set APPLY. The concurrency control of write set APPLY is related to wsREP_SLAVe_threads. Wsrep_slave_threads can be set with reference to wsREP_cert_DEPs_distance

PXC common port

• 3306: indicates the port number for external database services.

• 4444: Port requesting SST. Full mirror image transfer port, full mirror can use xtrabackup, rsync,mysqldump and other tools, can use wsREP_sST_method variable configuration. It only works when a new node is added

• 4567: A port number for communication between group members. Used when nodes communicate with each other when synchronizing data.

• 4568: Used to transfer THE IST. An increment relative to SST. 4568 port IST is useful only when the node is offline and restarting to join that time

State Snapshot Transfer (SST): full transmission

Incremental State Transfer (IST): Incremental Transfer

MySQL derivative selection

The PXC cluster is constructed

PXC cluster It is recommended to use three nodes to build a cluster

Install the PXC node online

Percona XtraDB Cluster 8.0 on CentOS 7

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

sudo percona-release enable-only pxc-80 release

sudo percona-release enable tools release

sudo yum install percona-xtradb-cluster
Copy the code

www.percona.com/doc/percona…

Manually install the PXC node

• Open firewall ports, the four ports used by the PXC cluster

Firewall-cmd --zone=public ȁƕadd-port=3306/ TCP ȁƕpermanent firewall-cmd --zone=public ȁƕadd-port=4444/ TCP ȁƕpermanent Firewall-cmd --zone=public ȁƕadd-port=4567/ TCP ȁƕpermanent firewall-cmd --zone=public ȁƕadd-port=4568/ TCP ȁƕpermanent firewall-cmd --reloadCopy the code

Alternatively, you can disable the firewall

Systemctl stop firewalld. Service systemctl disable firewalld. ServiceCopy the code

• Close the SELINUX

vim /etc/selinux/config

Set the SELINUX property value to Disabled and save

• restart

reboot

• Download the PXC installation package

The PXC is integrated with the Percona Server database, so there is no need to install the Percona Server database

Before installing the software package, you also need to download the qpress-11-1.el7.x86_64. RPM package

Run the following command to install it

yum localinstall *.rpm

Configure the PXC node

Modify the configuration

vim /etc/my.cnf

# all IP wsrep_cluster_address = gcomm: PXC cluster ȁ i192.168.68.140, 192.168.68.141, 192.168.68.142 # node IP address Wsrep_cluster_name = pxC-cluster # Node name, Node names vary wsREP_node_name = pxC-cluster-node-1Copy the code

Copying a Certificate File

In Percona XtraDB Cluster 8.0, encrypted data replication is enabled by default (controlled by the pxC-encrypt-cluster-traffic variable). If you start the node without doing anything, you cannot start it successfully. There are two possible solutions

Disabling Encrypted Replication

Add the following configuration to the my.cnf file

Pxc-encrypt-cluster-traffic =OFFCopy the code

Encrypted data replication can also be enabled, and if you disable it before starting the cluster, you must stop the cluster. Then set up encryption and start again. Example Set data encryption and replication. Each node in the cluster must use the same SSL certificate

Synchronous certificate

The certificates apply to the active node. Copy the certificates, that is, all. Pem files, on the active node to the non-active node

Start node

The primary node is started

Select any node as the active node and run the following command to start the active node

systemctl start [email protected]

Management commands for the master node (the first PXC node to start)

systemctl start [email protected]

systemctl stop [email protected]

systemctl restart [email protected]
Copy the code

Start a non-primary node

The non-primary node starts normally, using the following command to manage

systemctl start mysql.service

systemctl stop mysql.service

systemctl status mysql.service
Copy the code

Disable the automatic startup of mysql

The PXC cluster is started in sequence and the amount of data to be synchronized is not too large. Therefore, you need to manually shut down and start the mysql node. The following command can disable automatic startup of the service

chkconfig mysql off

Cluster testing

Changing the Root Password

Find the default password generated by mysql from the log file and log in to mysql

cat /var/log/mysqld.log |grep password

Change the password of mysql@localhost

alter user 'root'@'localhost' identified by 'root';

Since the cluster has been started successfully, the content of all cluster nodes is highly consistent. If the password is updated on one node, the other nodes will be synchronized