Make writing a habit together! This is the 15th day of my participation in the “Gold Digging Day New Plan · April More text Challenge”. Click here for more details.

This section describes one of the Mysql cluster solutions, percona XTRADb Cluster (PXC for short) implemented by percona, a well-known Mysql service and tool provider

PXC introduction

PXC architecture requires at least three MySQL instances to form a cluster. The three instances are not in master/slave mode, but in their own master/slave mode. Therefore, the relationship between the three instances is equal, regardless of master/slave mode. After writing to any instance, the cluster synchronizes its newly written data to other instances. This architecture does not share any data and is a highly redundant MySQL cluster architecture.

Any updated transactions need to be globally validated before they can be executed on other nodes, and cluster performance is limited to the worst nodes, known as the short board effect (the Barrel Law).

To ensure data consistency, PXC adopts the storage engine layer to realize synchronous replication in real time. Therefore, the lock conflict problem is serious when multiple nodes write concurrently.

Write expansion occurs. Write operations occur on all nodes. PXC is not recommended when the write load is heavy.

Restart mysql services in a high availability environment in strict sequence; otherwise, the mysql cluster will fail to restart and break down

1. On the active node, systemctl restart mysql@bootstrap

2. On the node, systemctl restart mysql

Master node judgment

  • The primary node with the largest safe_TO_bootstrap value of all nodes;
  • The safe_to_bootstrap value of all nodes is 0. Check the SEqNO value and manually change the safe_to_bootstrap value of the node with the largest seqNO value to 1. Then start the primary node.
  • The safe_TO_bootstrap value of all nodes is 0 and the seQNO value is the same. You can manually change the safe_to_bootstrap value of any node to 1 and then start the primary node.
  • The safe_TO_bootstrap value of all nodes is 0, and the maximum value of seqNO of two nodes is equal, which can be used to compare database data size. Generally, the largest node is the primary node

Matters needing attention

  • After the cluster is shut down and restarted, run systemctl start mysql@bootstrap if the first node is used.
  • If the last PXC node to be shut down exits safely, the next startup should start this node first and as the primary node.
  • Dat file, set safe_to_bootstrap value to 1, and then install the primary node to start. If the PXC node is not shut down safely, change the safe_to_bootstrap value to 1.
  • All nodes except master need to start mysql, that is, systemctl start mysql

Finally, the database is temporarily connected to the northbound IP and external port, although this can temporarily solve the problem, but PXC itself should be the purpose of high availability and load, after this change, lost the purpose of load, every time write to the pod on the master Node