Analysis and Comparison of five MySQL database reliability Solutions

Source: Internet
Author: User
Tags node server

This article mainly analyzes and compares five MySQL reliability solutions in terms of basic conditions, costs, advantages and disadvantages, and application scenarios. In addition, this article provides some reference for MySQL database development and management. For details, refer to the following:

  1. MySQL Clustering (ndb-cluster stogare)

  Introduction:

The high-reliability solution provided by MySQL in the form of storage engines is transaction-safe, real-time data replication, and can be used in scenarios requiring high reliability and load balancing. This solution requires at least three node servers to achieve better results.

  Cost:

The node server has a large demand for RAM and is linearly proportional to the database size;

It is best to use a Gigabit Ethernet network;

You also need to use the expensive SCI cards provided by doldolphin.

  Advantages:

It can be used in Server Load balancer scenarios;

It can be used in scenarios with high reliability;

High scalability;

Real database Redundancy;

Easy to maintain.

  Disadvantages:

As the database grows, the demand for RAM becomes greater, so the cost is high;

  Speed:

It is almost 10 times slower than a typical single server (no Gigabit Ethernet, no SCI card, and storage engine restrictions.

  Application scenarios:

Redundancy, high reliability, and load balancing

  MySQL/GFS-GNBD/HA (Active/Passive)

  Introduction:

If multiple MySQL servers use a shared hard disk as data storage, what is the solution?

GFS/GNBD provides the required shared hard disk.

GFS is a transaction-safe file system. You can share data with a MySQL instance at the same time.

  Cost:

The cost of up to n High-Performance servers, one of which is activated and others are used as backup servers.

  Advantages:

High reliability

Some degree of Redundancy

Scaling Based on High Reliability

  Disadvantages:

No Server Load balancer

Unguaranteed Redundancy

Unable to scale write operations

  Speed

2 times the size of a single server. The read operation is well supported.

  Application scenarios

Read-intensive applications that require high reliability

  3. MySQL/DRBD/HA (Active/Passive)

Introduction:

If multiple MySQL servers use a shared hard disk as data storage, what is the solution?

DRBD can provide shared hard disks. DRBD can be set to transaction security.

You can share data with a MySQL instance at the same time.

  Cost:

The cost of up to n High-Performance servers, one of which is activated, and the other is used as backup servers.

  Advantages:

High reliability;

A certain degree of redundancy;

In the name of high reliability, it is scalable.

  Disadvantages:

No Server Load balancer

Unguaranteed Redundancy

No scalability in writing Load

  Speed:

Reading and writing is equivalent to a separate server.

  Application scenarios

Highly Reliable and read-intensive applications

  4. MySQL Write Master/Multiple MySQL Read Slaves (Active/Active)

  Introduction:

Consider different read and write database connections. You can use one master server for write operations, and n slave servers for read operations.

  Cost:

The cost of up to one high-performance write server and n read servers

  Advantages:

High reliability of read operations;

Load Balancing of read operations;

Load Balancing for read operations is scalable.

  Disadvantages:

High reliability without write operations;

Load Balancing without write operations;

No scalability in write operations;

  Speed:

Same as a single server; better support for reading operations

  Application scenarios

Read-intensive applications that require high reliability and load balancing.

  5. Standalone MySQL Servers (Functionally separated) (Active)

Multiple independent servers with separated functions do not have high reliability and load balancing capabilities. They have many obvious disadvantages and are not considered.

  Summary:

The HA solution recommended on the official MySQL website is combined with DRBD (solution 3 in this article) and Replication (solution 4 in this article ). In addition to Linux Heartbeat, we can also implement the Auto-failover function. In this case, we will find that the down time will be greatly reduced.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.