Common MySQL high-availability solutions

MySQL is a relational database management system that stores data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility. MySQL software uses a dual licensing policy (this term "authorization policy"), it is divided into community and commercial version, because of its small size, speed, low total cost of ownership, especially the open source of this feature, the general small and medium-sized web site development has chosen MySQL as the site database. Thanks to the performance of its community edition, PHP and Apache make a good development environment.

Tools/Materials

• Computer operating system
• Mysql

Method/Step

2. 1

   Master-Slave Replication solution: This is a highly available solution provided by MySQL itself, and the data synchronization method uses MySQL replication technology. MySQL replication is to pull the binary log file from the server to the master server, and then parse the log file into the corresponding SQL to re-perform the operation of the master server from the server, in this way to ensure the consistency of the data. In order to achieve higher availability, in the actual application environment, the use of MySQL replication technology with high-availability cluster software keepalived to achieve automatic failover, this way can achieve 95% SLA.

4. 2

   MMM/MHA High-availability solution: MMM provides a scalable suite of scripts for monitoring, failover, and management of MySQL master replication configurations. In the MMM high-availability scheme, the typical application is the dual master multi-slave architecture, through the MySQL replication technology can realize two servers mutual primary from, and at any time only one node can be written, avoids the multi-point writes the data conflict. At the same time, when a writable master node fails, the MMM suite can be immediately monitored, then automatically switch the service to the other master node, continue to provide services, to achieve high availability of MySQL.

6. 3

   Heartbeat/san High-availability solution: In this scenario, the failover is handled in a highly available cluster software Heartbeat, which monitors and manages the networks connected between the nodes and monitors the Cluster service, when the node fails or the service is unavailable, Automatically start the Cluster service on other nodes. In terms of data sharing, data is shared through San (Storage area Network) storage, which enables a 99.99% SLA.

8. 4

   HEARTBEAT/DRBD High-availability solution: This scheme still uses Heartbeat in the way of processing failover, but it is implemented by using the block-level data synchronization software DRBD in data sharing. DRBD is a software-implemented, non-shared storage replication solution for mirrored block device content between servers. Unlike San Networks, it does not share storage, but rather replicates data through the network between servers.

10. 5

    NDB CLUSTER: Domestic use of NDB clusters of companies very few, it seems that some banks useful. NDB clusters do not need to rely on third-party components, all use the official components, to ensure consistency of data, a data node hangs, other data nodes can still provide services, management nodes need to do redundancy to prevent hanging off. The downside is that both management and configuration are complex, and some SQL statements such as join statements need to be avoided.

12. 6

    Use of third-party tungsten software: Written in Java, not MySQL built-in, but also MySQL database replication, but he did not use the built-in MySQL components, not only support MySQL database replication also support heterogeneous database replication, and support for heterogeneous database replication is good, such as MySQL replication to Oracle.

    END

Precautions

• NDB is a storage engine that is typically used in MySQL cluster.
• LVS is a shorthand for Linux virtual server, that is, a virtualized server cluster system.
• Keepalived is primarily used here as a realserver health check and failover between the LoadBalance host and the backup host

Common MySQL high-availability solutions