Shulou(Shulou.com)06/01 Report--

This article briefly describes the PPT of a high availability scheme of MySQL that I sorted out when I was a beginner of MySQL. Although it is not very comprehensive and not necessarily accurate, I still want to share it with you, discuss it together, and make progress together.

In addition, this partial article made into PPT, hoodwinking leaders or superiors is very easy to use.

Welcome to reprint, please indicate the author and source.

Author: Zhang Zheng blog: http://space.itpub.net/26355921 QQ:176036317 if you have any questions, please feel free to contact us.

MySQL-Transefer (hereinafter referred to as Transfer) is a master-slave synchronization tool based on MySQL+patch.

Its main purpose is to solve the delay caused by the binlog that the slave library is the master library of single-thread apply in the master-slave synchronization of the original version.

In future versions of MySQL5.6, you can add the binlog of the current apply master library from the library.

For applications where the real-time requirement of data is not particularly strict, we only need to use cheap pc server to expand the number of Slave and distribute the read pressure to multiple Slave machines, that is, we can solve the read performance bottleneck of the database by dispersing the read pressure of a single database server. After all, in most database application systems, the read pressure is much greater than the write pressure. To a large extent, this has solved the database pressure bottleneck of many small and medium-sized websites, and even some large websites are using similar solutions to solve database bottlenecks.

A Dual Master environment is not built to provide writing services on both sides. Under normal circumstances, we will only turn on the write service on one end, and only provide read service on the other end, or provide no service at all, just as a backup machine. The main purpose is to avoid data conflicts and prevent data inconsistencies. Because even if the changes are performed sequentially on both sides, because Replication is an asynchronous implementation mechanism, even late changes may be overwritten by early changes.

Through the Dual Master replication architecture, we can not only avoid rebuilding the Replication environment caused by downtime required by normal regular maintenance operations, because we both record where we are currently replicating to the other, and when the system is up, we will automatically start replication from the previous location without any human intervention, resulting in significant savings in maintenance costs.

In the MySQL Cluster main configuration file (above the management node, usually config.ini), there is a very important parameter called NoOfReplicas, which is

Specifies the number of copies of each piece of data that are redundant stored on different nodes, and this parameter should generally be set to at least 2

Mysql cluster is cheaper, oracle rac is more mature, and there are many application cases.

Although mysql cluster is well designed, its functional implementation is not perfect. At present, there are not many application cases, and more cases are mysql replication.

Because MySQL Cluster distributes data to several NDB nodes, if the tables that need to be joined are located on different NDB nodes, you need to take the data from different nodes locally and then query them, which consumes a lot of resources. (however, in the MySQL Cluster7.3 version, adaptive join queries are added to reduce the resource consumption of previous join queries.)

Heartbeat is a component of the Linux-HA project. The core of heartbeat consists of two parts: heartbeat monitoring and resource takeover. If the message sent by the other party is not received within the specified time, then the other party is considered invalid, and it is necessary to start the resource takeover module to take over the resources or services of the operation bank on the other side's host.

For example, if you use the shell script to detect that the mysql of the master is not available, stop the heartbeat on the host, and then switch to the backup.

DRBD is an open source Cluster software that synchronizes data mirrors of block devices through the network.

Lvs is a virtual server cluster system, which can realize simple load balancing under LINUX platform.

Keepalived is a software similar to layer3, 4-5 switching mechanism, which is mainly used for failover between host and standby.

Highly available web schema: LVS+keepalived+nginx+apache+php+eaccelerator, also often used with MySQL databases

]

Is MMM, or Master-Master Replication Manager for MySQL (mysql Master replication Manager), a flexible scripting program? Used for monitoring and failover of mysql replication? And can manage the configuration of mysql Master-Master replication. The included tool suite can achieve read load balancing for multiple slaves

HAProxy provides high availability and load balancing, so it is more suitable for web sites with heavy load.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.