Shulou(Shulou.com)06/01 Report--

In the general production environment, the master-slave replication of MySQL is generally used to separate reading and writing, so as to reduce the pressure on the master server and improve the efficiency of data reading and writing. In general, master-slave replication can basically do real-time synchronization. Due to the actual running process of the server, the read and write data of the client can not be uniform, a large number of concurrent connections occur at a certain point in time, and the master server constantly writes update operations, but when a statement is executed on the slave server for a long time, or when a statement has to lock the table, it will lead to a large backlog of SQL statements on the master server, which will not be synchronized to the slave server. This will lead to inconsistency between master and slave data at some point. There is also master-slave replication, which transmits data through the network. the jitter of the network and the network interruption between the master and slave servers will certainly affect the data transmission and also cause data inconsistency. This is the master-slave delay, although with the passage of time, or the master server is no longer a large number of update operations, the master-slave server will gradually be consistent (except for network interruptions). For some enterprises that do not write data synchronously, data delay is not a problem, but in some transactional enterprises (or requiring real-time consistency), data delay is not acceptable.

Solution:

1. (the scheme given by netizens) the simplest way to reduce slave synchronization delay is to optimize the architecture so that the DDL of the main library can be executed as quickly as possible. In addition, the main library is write, which is highly secure for data, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such high data security. You can set sync_binlog to 0 or close binlog,innodb_flushlog to 0 to improve the efficiency of sql execution. The other thing is to use a better hardware device than the main library as the slave.

2. Improve the hardware performance of master-slave servers.

3. Use the later version of MySQL5.6.3, because mysql-5.6.3 already supports master-slave replication with multi-thread.

Although these schemes can solve the data delay to some extent, but limited by the principle of MySQL master-slave replication, there is still the possibility of data delay. I think the more feasible solution is to use MySQL Galera Cluster clusters.

Mysql Galera Cluster is a set of multi-master MySQL cluster solution based on synchronous replication, which is easy to use, no single point of failure, high availability, and can ensure the security of our data and expand at any time when the business is growing.

Advantages:

1. Synchronous replication, real row-level concurrent replication, no delay between nodes and node downtime will not lead to data loss.

2. The topology of multi-master servers, which can be read and written on any node.

3. New nodes are automatically added to automatic membership control, and faulty nodes are automatically removed from the cluster, providing 99.999% availability.

4. Client connection is the same as the experience of operating a single MySQL database.

Disadvantages:

1. Currently, only InnoDB storage engine is supported. Any tables written to other engines, including mysql.* tables, will not be replicated, but DDL statements will be replicated, so the creating user will be replicated, but insert into mysql.user … It will not be copied.

2. DELETE operation does not support tables without primary keys. Tables without primary keys will have different order in different nodes. If SELECT is performed, the order of tables will be different. LIMIT... Different result sets will appear.

3. LOCK/UNLOCK TABLES is not supported in multi-host environment, and lock functions GET_LOCK (), RELEASE_LOCK () …

4. The query log cannot be saved in the table. If you open the query log, it can only be saved to a file.

5. The maximum transaction size allowed is defined by wsrep_max_ws_rows and wsrep_max_ws_size. Any large operation will be rejected. Such as large LOAD DATA operations.

6. Because the cluster is optimistic about concurrency control, the transaction commit may be aborted at this stage. If two transactions are written and committed to the same line to different nodes in the cluster, the failed node will abort. For cluster-level aborts, the cluster returns a deadlock error code (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

7. XA transactions are not supported because they may be rolled back on the commit.

8. The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the whole cluster will be slow. In order to achieve stable and high performance requirements, all nodes should use unified hardware.

9. At least 3 cluster nodes are recommended. The data of all servers are identical, and the data is updated synchronously, which takes up hardware resources. The more servers, the more hardware resources will be sacrificed.

10. If there is a problem with the DDL statement, the cluster will be destroyed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.