Original: Http://imysql.com/2015/09/14/solutions-of-mysql-ha.shtml?hmsr=toutiao.io&utm_medium=toutiao.io&utm_ Source=toutiao.io
This topic is MySQL高可用方案选型 , this topic presumably has many classmates interest.
High availability and corresponding downtime for a variety of high availability levels I don't have to say more, go straight to the subject.
Optional MySQL high-availability scenario
The various high-availability scenarios for MySQL are mostly deployed on the basis of the following:
- Based on master-slave replication;
- Based on the Galera protocol;
- Based on the NDB engine;
- Based on middleware/proxy;
- Based on shared storage;
- Host-based high availability;
Of these options, the most common is based on the master-slave replication scheme, followed by the Galera-based approach, and we will focus on these two scenarios. The other options are not much used in production, we simply say.
High-availability scheme based on master-slave replication Dual-node Master + keepalived/heartbeat
Generally speaking, this architecture is the easiest to use when it comes to small and medium size.
Two nodes can be in a simple one -to-one mode, or dual-master mode, and placed in the same VLAN , after the master node fails, the use of keepalived/ Heartbeat's high-availability mechanism enables fast switching to the slave node.
In this scenario, there are a few points to note:
When
- uses keepalived as a high-availability scheme, it is best to set the two nodes to backup mode to avoid conflicts that can occur when the same data is written to two nodes because of an unexpected situation (such as a brain fissure ).
The
- sets the auto_increment_increment (self-increment start value) and auto_increment_offset (increment step) of two nodes to different values. The purpose is to avoid accidental downtime of the master node, some binlog may not be copied to the slave on time to be applied, resulting in slave the new write data of the self-increment and the original master conflict, so it was staggered from the beginning; of course, If there is a suitable fault-tolerant mechanism to resolve the master-slave ID conflict, you can also not do this;
- slave node server configuration is not too bad, or it is more likely to cause replication delays. As a slave server for a hot standby node, the hardware configuration cannot be lower than the master node;
- If you are sensitive to latency issues, consider using the MARIADB branch version, or go directly to the latest MySQL 5.7 version. The use of multi-threaded replication can greatly reduce replication latency, and another alternative for which
- is particularly sensitive to replication latency is the use of semi sync replication (which is called semi-synchronous replication) or the PXC scheme that is referred to later, with virtually no delay. However, the transaction concurrency performance will have a small degree of loss, need a comprehensive assessment to decide again; the detection mechanism of
- keepalived needs to be properly perfected, not just to check if the mysqld process is alive, or if the MySQL service port is available, should also do further data writing or operation detection, to determine the response time, if the threshold is exceeded, you can start the switching mechanism; the
- keepalived determines the latency of the slave when it is finalized to switch. The rules need to be set in advance in order to decide which strategies to switch or wait for in the event of a delay. Direct switchover may occur because replication delays some data cannot be queried and repeated writes,
- keepalived or heartbeat itself cannot solve the problem of brain fissure , so in the case of service anomaly judgment, you can adjust the judgment script, The risk of a brain fissure problem can be reduced by supplemental detection of third-party nodes to determine whether or not to switch.
Two-node master-slave +keepalived/heartbeat scheme architecture see below:
Illustration: MySQL Dual-node (one-way/bidirectional master-slave replication) with keepalived for high-availability architectures.
Multi-node master-slave +mha/mmm
Multi-node master-Slave, can adopt a master Multi -Slave, or dual-master multi-slave mode.
This mode, you can use MHA or MMM to manage the entire cluster, the current MHA application of the most, priority recommended MHA, the latest MHA has also supported the MySQL 5.6 gtid mode, is a good news.
The advantages of MHA are obvious:
- Open source, with Perl development, code structure clear, two times development easy;
- When the scheme is mature and failover, MHA will make more stringent judgments, minimize data loss and ensure data consistency.
- Provide a general framework to customize the development according to your own situation, especially the judgment and switch operation steps;
- Support for Binlog server can increase binlog transfer efficiency and further reduce the risk of data loss.
But MHA also has some limitations :
- The need to get SSH trust across nodes is a challenge for some corporate security systems, because if a node is compromised by hackers, other nodes will suffer as well;
- The script that comes with it also needs to be supplemented, of course, the general use is sufficient.
Multi-node master-slave +etcd/zookeeper
In a large-scale node environment, using keepalived or MHA as MySQL's high-availability management is still somewhat complex or cumbersome.
First of all, so many nodes without the configuration services to manage, it is necessary to be disorganized, online switching is easy to operate by mistake.
In larger-scale environments, etcd/zookeeper management clusters are recommended for fast detection switching and easy node management.
Highly available scenarios based on the Galera protocol
Galera is a multi-master data synchronous replication mechanism provided by codership, which can realize data synchronous replication and read and write between multiple nodes, and can guarantee the high availability and data consistency of database service.
Galera-based high-availability schemes mainly include mariadb Galera Cluster and Percona XtraDB Cluster (abbreviated as PXC), which are now used more pxc.
The architecture of the PXC is shown below:
(image from the network), diagram: In the bottom of the use of WSREP interface to achieve data synchronization between multiple nodes.
(image from the web), diagram: In PXC, a data write is written to the authentication/rollback process between the various nodes.
Advantages of PXC
- High availability of services;
- Data synchronous replication (concurrent replication) with virtually no latency;
- More than one can read and write nodes, can achieve write extension, but preferably in advance to the sub-database table, let each node write different tables or libraries, to avoid galera to resolve data conflicts;
- The new node can be deployed automatically, the deployment operation is simple;
- Strict data consistency, especially suitable for e-commerce applications;
- fully compatible with MySQL;
While there are so many benefits, there are some limitations:
- only support InnoDB engine;
- All tables must have a primary key;
- Explicit lock operations such as lock table are not supported;
- Lock conflict, deadlock problem is more;
- Xa not supported;
- Cluster throughput/performance depends on the short board;
- The new Join node adopts SST time Price high;
- There is write enlargement problem;
- If the concurrent transaction volume is large, it is recommended to use InfiniBand network to reduce network latency;
In fact, the main purpose of the adoption of PXC is to solve the data consistency problem, high availability is incidentally implemented. Because PXC has write enlargement and short board effect, the concurrency efficiency will have a large loss, similar to the semi sync replication mechanism.
Other high-availability scenarios
- Based on NDB Cluster, there are still many defects and limitations in NDB, and it is not recommended to use in production environment.
- Based on shared storage, on the one hand, the need for a less poor storage device, the other shared storage can also become a new single point, unless the use of high-speed network-based distributed storage, RDS-like scenarios, the architecture is more complex, the cost may be higher;
- Based on the middleware (proxy), now reliable proxy selection is not many, and there is no generic proxy, there are some targeted, such as some focus on the separation of reading and writing, and some focus on the sub-database table, and so on, the real good proxy generally to self-development;
- Host-based high availability refers to the scenario of deploying a MySQL application after building a highly available cluster with similar RHCS. Frankly speaking, I have not actually used, but from the side to understand that the production of such a program is not much, there may be some limitations caused it;
With the wisdom of DBAs, there are certainly other options that I don't know about, and I also welcome the many exchanges between our peers.
Reproduced MySQL High-availability Solution selection Reference
