MySQL High-availability scenario

A. Normal master-slave replication ―――― Client reads/writes to the database through master, and slave as a standby machine, which can be used for some queries, backups and other operations.

Advantages: Simple deployment, easy expansion, and the ability to provide certain data protection.

Disadvantage: If the master host hardware fails and cannot be recovered, it may result in some data loss that is not transferred to the slave end, and if some maintenance operations are required on the master side, after the slave is temporarily served as master, the master-slave environment needs to be re-built. will have a certain performance impact on the master.

B. Dual master replication ―――― two MySQL server each other as their own master, as the other side of the slave to replicate, one end of the provision of write services, the other end of the service or just as a standby without providing any services, It is also capable of providing read services specifically to one or more slave.

Pros: The biggest benefit is that the primary master's write operation is not affected by the replication of the slave cluster, while the primary master does not have to be re-replication when it needs to switch.

Disadvantage: This architecture also has a disadvantage, that is, the backup master may become a bottleneck, because if the subsequent slave cluster is larger, the standby master may become a bottleneck because of excessive SLAVEIO thread requests.

C. master-slave replication extension: read-write separation ―――― from a master copy to one or more slave schema mode, the client through master to write to the database, through the slave to read operations, and can be backed up, master problems, You can switch the app to the slave end by moving it back. This scheme is mainly used in the application system with large reading pressure ratio.

Advantages: The structure is flexible, the database side is cheap to expand, can solve many small and medium-sized website database pressure bottleneck problem.

Disadvantage: Requires a program to achieve read and write separation, increase the complexity of the program, if the server architecture tuning or have a host failure, you also need to adjust the program.

d.hearbeat+ Dual master replication The core of the ――――hearbeat consists of two parts: Heartbeat monitoring and resource takeover. Use Hearbeat+mysql Master synchronization to achieve high availability of MySQL database, when Master's host or hearbeat downtime will automatically switch to the standby, when the recovery can automatically switch back, Master continues to provide services.

Advantages: Simple configuration, to a certain extent, to avoid a single point of failure.

Cons: If MySQL on Master is hung, it cannot be detected and switched, and some scripts are required to assist with monitoring. By default, after switching to the standby machine, MySQL does not start automatically, requires manual or scripted startup, and is inconvenient to expand, which may cause problems with brain fissures.

E.hearbeat+drbd+mysql―――― This program uses hearbeat dual-machine hot standby software to ensure the high stability and continuity of the database, the data consistency has the DRBD tool to ensure. By default, only one MySQL is working, when the main MySQL server problems, the system will automatically switch to the standby to continue to provide services, when the primary database has been repaired, and the service will be cut back to continue to be serviced by the main MySQL.

Applicable scenario: For the database access is not too large, short-term traffic growth will not be too fast, the database availability is very high requirements of the scene.

Advantages: High security, high stability, high availability, automatic switching of failures.

Disadvantage: Only one server provides service, the cost is relatively high, inconvenient to expand, may appear the database brain fissure.

The f.lvs+keepalive+ dual master replication ――――lvs provides load balancing, Keepalive as a failover. Server single-point write, read implementation load is very horizontal and failover. When the network, MySQL service, server, KeepAlive service failure, the server can automatically jump to the standby machine, when the primary server service starts up automatically switch back.

Application scenario: For scenarios where database availability requirements are high, the read-pressure ratio is large (the backend can be followed by one or more slave servers, allowing LVS to achieve read load balancing). This scheme can also be easily managed and maintained on a single database, as well as switching tasks, such as table structure changes for large tables, database upgrades, etc.

Advantages: High availability efficiency, can be based on the service and system availability of various aspects of switching. You can set both the write VIP and the read VIP separately to prepare for the read-write separation. Extension is convenient. You can add multiple slave servers at a later time and do read load balancing.

Disadvantage: If you want to implement the specified condition substitution or not replace after starting or recovering, you need to implement it in other ways, such as temporarily changing the port of MySQL. The installation configuration is slightly more complex than a single write and requires another VIP. Management is more complex than single writes. After the main switch, manually switch from the need. Primary and standby switching takes about 1s of time.

g.mmm+mysqlproxy+ Dual master replication ――――mmm (MySQL Master replication Manager) is a flexible set of scripts for monitoring and failover of MySQL replication, and the ability to manage the configuration of MySQL master-master replication. The accompanying toolkit enables read load balancing for multiple slaves. Two MySQL server servers are mainly from, MMM provides the function of floating IP, if the current primary server hangs, the client's read and write requests will be automatically transferred to another server through the virtual IP drift, Thus, the failover of the server is realized automatically, and the Mysqlproxy realizes the read and write separation.

Application scenario: This scheme is a more mature solution, which is suitable for the scenario of large database access and fast business growth. Suitable for web2.0 applications where read/write is high.

Advantages: Security, high stability, good scalability, high availability, when the primary server hangs, another master immediately take over, the other from the server can automatically switch, without manual intervention.

Disadvantage: At least three nodes, the number of host requirements, for a high-real-time situation may need to do some processing.

Mysqlprox and MySQL mmm integration of the necessity: to achieve the MySQL database layer load Balancing, database node implementation ha dynamic switch, read and write separation, reduce the primary database load

MySQL High-availability scenario