Summary of building high-performance MySQL cluster system

MySQL high-availability Cluster Scenario summary

One, a common high-availability MySQL solution

High-availability solutions:

1, data sharing or data synchronization, data sharing can be achieved through the San (Storage area Network), data synchronization through rsync software or DRDB technology to achieve

2, processing failover, downtime migration to the backup server, does not affect the business operation

Master-Slave Replication solution:

MySQL's own high-availability solution, data synchronization method using the MySQL replication technology, MySQL replication technology is the process of log replication, a master more from, Pull the binary log file of the primary server from the server, and then parse the log file into the corresponding SQL to execute from the server to achieve the consistency of the data.

Note: The slave server here can only provide read operations, and when the primary server fails, you must manually failover, a slave server into the primary server, achieve 90% SLA (Service level agreement), generally in the application environment, MYSQL Replication technology with highly available cluster software for automated failover, up to 95% SLA

MMM high-availability solutions:

MMM (master-master Replication Manager for MySQL) MySQL master-slave Replication Manager, provides a set of scalable scripting suites for monitoring, failover, and management of MySQL primary master replication configuration. In the MMM high-availability solution, the typical application is a dual-master multi-Slave architecture, through the MySQL replication technology to realize that the two servers are mainly from each other, and at any time only one node can write, avoid multi-point write data conflicts. At the same time, when the writable master node fails, the MMM suite can be immediately monitored, then automatically switch the service to another master node and continue to provide services to enable MySQL to be highly available. This solution is a more mature MySQL high-availability solution that can achieve a 99% SLA.

Heartbeat/san High-availability solutions:

This solution is implemented with third-party hardware and software that handles failover in a highly available cluster software heartbeat that monitors and manages the network of connections between nodes, and monitors cluster services, when a node fails or the service is unavailable, Automatically start the Cluster service on other nodes.

In the area of data sharing, through SAN storage in shared data, in normal state, the cluster master node will mount the storage to read and write, when the cluster fails, heartbeat first through a quorum device to mount the primary node storage device, and then mount the storage on the standby node, start the service. This approach is characterized by high costs and the possibility of a brain fissure that can achieve a 99.99% SLA

Heartbeat/drdb High-availability solutions:

This is also the use of third-party software to achieve, processing failover still use heartbeat, the difference is that data sharing is based on block-level data synchronization software DRDB (distributed Replicated block Device (DRBD) is a software-implemented, non-shared storage replication Solution for mirrored block device content between servers . Unlike Sans, sans are shared, and Drdb are replicated through the network between servers. Features: complex, with a brain fissure problem that can achieve 99.9 SLA

MYSQL Cluster High-availability solution:

MySQL cluster consists of a set of service nodes, each service node is running a variety of processes, including MYSQL server, NDB Cluster data node, Management Server, as well as dedicated data visit procedures, features, powerful, cumbersome configuration, Up to 99.999% SLA

This article from "High Good bright" blog, declined reprint!

Summary of building high-performance MySQL cluster system