The difference between mysql cluster and master-slave

The difference between the cluster and the master-slave in mysql: the master-slave ensures data consistency through mysql replication; it is asynchronous compared to the data synchronization method of the mysql cluster. Because it is asynchronous, there may be a slight delay in copying data between the master and the slave, and inconsistencies may occur.

Recommended course: MySQL Tutorial.

The master and slave use mysql replication to ensure data consistency. Compared with the data synchronization method of mysql cluster, it is asynchronous.

The cluster uses PXC (Percona XtraDB Cluster), multi-node data is synchronized in real time (read and write)

About master-slave

Master-slave can ensure the separation of reading and writing, that is, the writing operation is on the master and the reading operation is on the slave. There are also multiple master-slave modes. Here we only talk about one master and multiple slaves.

For example, there are two business modules. One module continuously writes order records, etc., and the other module generates reports. If read and write separation is not adopted at this time, the read and write operations will collide, which may cause conflicts and affect Performance, if reading and writing are separated, there is no need to consider reading and writing the same table to affect performance, and multiple slaves can well share the pressure on the server and reduce the pressure on a single machine.

The master-slave uses replication of mysql to ensure data consistency. Compared with the data synchronization method of the cluster, it is asynchronous. Because of asynchronous, there may be a slight delay in copying data between the master and the slave. Delay, and inconsistencies will occur.

Replication: The master node needs to enable binlog, set a unique server-id (unique within the local area network), set the server-id from the slave node, and the binlog records all the messages on the master. The operation will be copied to the relaylog of the slave node and played back on the slave node.

But the master-slave also has shortcomings. One is that it does not meet high availability. The master is down and needs to be switched manually. The business will be interrupted and is not allowed.

The other is that the data is inconsistent and inconsistent. There are many possible reasons. The following are some common ones.

• The master library or slave library unexpectedly crashes. The downtime may cause damage to the binlog or relaylog files, resulting in master-slave inconsistency. (There is an article in this blog, which was fixed after the downtime)

• If set sql_log_bin=0 is executed before the main library performs changes, the main library will not record the binlog, and the slave library cannot change this. Part of the data

• The binlog format of the main library is Statement. After synchronization to the slave library, it may cause inconsistency between the master and the slave.

• The slave node is not set to read-only , write data by mistake

• The versions of the master and slave instances are inconsistent, especially when the higher version is the master and the lower version is the slave, the functions supported by the master database may not be supported by the slave database. This function is not supported

• MySql BUG

Then you need to pay attention to the following matters when using it

• The main library binlog adopts ROW format

• The master and slave instance database versions are consistent

• The main library controls the account permissions. You can execute set sql_log_bin=0

• The slave library turns on read-only and does not allow manual writing

• Perform master-slave consistency check on a regular basis

About the cluster

The biggest advantage of the cluster is real-time data synchronization, high availability, and the data of each node is synchronized Consistent, unlike master-slave, where data inconsistency sometimes occurs, and high availability, any node downtime will not affect the business.

But the disadvantage is performance, write performance. Each write operation will be synchronized between all nodes. There are gains and losses, and a little performance is lost to ensure high availability and data consistency.

In the cluster, there is one management node and multiple SQL nodes and data nodes. Synchronous replication is used between data nodes to ensure data consistency between nodes. Generally, it is submitted through two stages. The general working process is as follows

• When the Master executes the commit statement, the transaction is sent to the slave, and the slave begins to prepare for the submission of the transaction

• Each slave must prepare the transaction and then send an OK (or ABORT) message to the master, indicating that the transaction is ready (or the transaction cannot be prepared)

• Master waits for all slaves to send OK or ABORT message

If the Master receives OK messages from all Slave, it will send a commit message to all Slave, telling the Slave to submit the transaction;

If the Master receives ABORT message from any Slave, it sends ABORT message to all Slave, telling the Slave to abort the transaction.

• Each Slave waits for an OK or ABORT message from the Master

If the Slave receives a commit request, they will commit the transaction and send it to Master sends a confirmation that the transaction has been committed;

If the Slave receives a cancellation request, they will undo all changes and release the resources they occupy, thereby aborting the transaction, and then send a confirmation that the transaction has been aborted to Masterv.

• When the Master receives confirmation from all Slaves, it will report that the transaction has been committed (or aborted), and then continue with the next transaction

Since synchronous replication requires a total of 4 message transfers, the data update speed of mysql cluster is slower than that of stand-alone mysql. Therefore, mysql cluster is required to run in a LAN of Gigabit or above. The nodes can use dual network cards, and the node groups are directly connected to ensure the data update speed.

The above is the detailed content of The difference between mysql cluster and master-slave. For more information, please follow other related articles on the PHP Chinese website!