MySQL simple master-slave scheme exposes problems

1. Overview

Starting from this article, we will introduce to readers how to build various service clusters of mysql. The general discussion idea is to start with the simplest MySQL master-slave solution, extend the shortcomings of this solution to a more complex cluster solution, and introduce how the latter improves these shortcomings.

2. The simplest master-slave scheme and working principle of MySQL

The version we explain is still based on version 5.6, which is currently used most in production environments. Some of its features have been improved in Version 5.7 and the latest Version 8.0 , but this does not affect readers' understanding of the technical ideas for building a MySQL cluster through the article, and this mechanism can even be extended to MariaDB. For example, the log replication mechanism (Replicaion mechanism) that comes with MySQL will be mentioned soon.

MySQL’s own log replication mechanism is called MySQL-Replicaion. Replicaion technology has been available since MySQL's very early Version 5.1, and to the current version, this technology has become very mature, and its support technicians can create a variety of MySQL cluster structures. Of course, we will also introduce some MySQL cluster solutions supported by third-party software/components later.

2-1. Basic working principle of MySQL-Replicaion

Replicaion mechanism From a technical perspective, there are two basic roles: Master and Salve. The Master node is responsible for outputting data to one or more targets in the Replicaion mechanism, while the Salve node is responsible for accepting data from the Master node in the Replicaion mechanism. In the actual business environment, the Master node and the Salve node have another name respectively: Write node and Read node - yes, using the Replicaion mechanism we can build a MySQL cluster service targeting read and write separation. However, in order to ensure that readers do not have ambiguity when reading the content of the article, we will use the terms Master node and Salve node in this article (and subsequent articles). The Replicaion mechanism relies on the binary log of the MySQL service to synchronize data:

As shown in the figure above, Salve will establish a network connection with the Master node after startup. When the binary log of the Master node changes, one or more MySQL The Salve service node will monitor these change logs through the network. Then the Salve node will first write these changes locally to the relay log file (Relay Log). This is done to try to avoid the failure of the MySQL service to synchronize data when an exception occurs. The principle is similar to the working principle of the InnoDB Log introduced before. . When the relay log file is recorded, the MySQL Salve service will reflect these changes to the corresponding data table to complete a data synchronization process. Finally, Salve will update the update point (Position) in the redo log file and prepare for the next Replicaion operation.

Multiple elements can be configured in this process. For example, the ratio between the number of data operations and the number of log writes on the Master node can be configured through the sync_binlog parameter, the information structure of the log data can be configured through the binlog_format parameter, and the sync_relay_log parameter can be configured Configure the ratio between the system receiving log data on the Salve node and the number of times it is written to the relay log file. These parameters and others used in the examples are described in subsequent sections of this article.

2-2. MySQL one master and multiple slaves construction method

After introducing the basic working method of the MySQL Replicaion mechanism, we will quickly build a MySQL cluster consisting of a Master node and a Salve node. Readers can extend this one-master-one-slave MySQL cluster solution to any one-master, multiple-slave cluster solution:

In this example, we use Version 5.6 for setup. Of course, the installation of version 5.7 is similar. In addition, the process of installing the MySQL service and making basic settings on the Linux operating system (Centos 5.6/5.7/6.X) will not be described here due to length and article positioning reasons. We will install the cluster's Master node and Salve node respectively in the Linux operation of the following IP:

MySQL Master service: 192.168.61.140

MySQL Salve service: 192.168.61.141

2-2-1, set up the Master server

First, you need to change the information in the my.cnf main configuration file of the MySQL Master service. The main purpose is to enable the binary log function on the Master node (note that the log mentioned here is not the InnoDB engine log).

# my.cnf文件中没有涉及Replicaion机制的配置信息，
就不在这里列出了
......
# 开启日志
log_bin

# The following parameters will be explained later

sync_binlog=1

binlog_format=mixed

binlog-do-db=qiang

binlog_checksum=CRC32

binlog_cache_size=2M

max_binlog_c ache_size=1G

max_binlog_size =100M

# A unique server id information in the cluster must be set for this MySQL service node

server_id=140

......

在Master节点的设置中，有很多参数可以对日志的生成、存储、传输过程进行控制。具体可以参见MySQL官网中的介绍：http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html。这里我们主要对以上配置示例中出现的参数进行概要介绍：

sync_binlog：该参数可以设置为1到N的任何值。该参数表示MySQL服务在成功完成多少次不可分割的数据操作后（例如InnoDB引擎下的事务操作），才进行一次二进制日志文件的写入操作。设置成1时，写入日志文件的次数是最频繁的，也会造成一定的I/O性能消耗，但同时这样的设置值也是最安全的。

binlog_format：该参数可以有三种设置值：row、statement和mixed。row代表二进制日志中记录数据表每一行经过写操作后被修改的最终值。各个参与同步的Salve节点，也会参照这个最终值，将自己数据表上的数据进行修改；statement形式是在日志中记录数据操作过程，而非最终的执行结果。各个参与同步的Salve节点会解析这个过程，并形成最终记录；mixed设置值，是以上两种记录方式的混合体，MySQL服务会自动选择当前运行状态下最适合的日志记录方式。

binlog-do-db：该参数用于设置MySQL Master节点上需要进行Replicaion操作的数据库名称。

binlog_checksum：该参数用于设置Master节点和Salve节点在进行日志文件数据同步时，所使用的日志数据校验方式。这个参数是在version 5.6版本开始才支持的新配置功能，默认值就是CRC32。如果MySQL集群中有MySQL 节点使用的是version 5.5或更早的版本，请设置该参数的值为none。

binlog_cache_size：该参数设置Master节点上为每个客户端连接会话（session）所使用的，在事务过程中临时存储日志数据的缓存大小。如果没有使用支持事务的引擎，可以忽略这个值的设置。但是一般来说我们都会使用InnoDB引擎，所以该值最好设置成1M——2M，如果经常会执行较复杂的事务，则可以适当加大为3M——4M。

max_binlog_cache_size：该值表示整个MySQL服务中，能够使用的binlog_cache区域的最大值。该值不以session为单位，而是对全局进行设置。

max_binlog_size ： 该参数设置单个binlog文件的最大大小。MySQL服务为了避免binlog日志出错或者Salve同步失败，会在两种情况下创建新的binlog文件：一种情况是MySQL服务重启后，另一种情况是binlog文件的大小达到一个设定的阀值（默认为1GB）。max_binlog_size参数就是设置这个阀值的。

完成my.cnf文件的更改后，重启Linux MySql服务新的配置就生效了。接下来需要在Master节点中设置可供连接的Salve节点信息，包括进行Replicaion同步的用户和密码信息：

# 只用MySQL客户端，都可以进行设置：
# 这里我们直接使用root账号进行同步，
但是生产环境下不建议这样使用
> grant replication slave on *.* to root@192.168.61.141 identified by '123456'

# 通过以下命令，可以查看设置完成后的Master节点工作状态

> show master status;

+----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------+----------+--------------+------------------+-------------------+

| kp2-bin.000002 | 404 | qiang | | |

+----------------+----------+--------------+------------------+-------------------+

以上master节点状态的描述中，File属性说明了当前二进制日志文件的名称，它的默认位置在Linux操作系统下的var/lib/mysql目录下。Position属性说明了当前已完成日志同步的数据点在日志文件中的位置。Binlog_Do_DB属性是我们之前设置的，需要进行Replicaion操作的数据库名称，Binlog_Ignore_DB属性就是明确忽略的，不需要进行Replicaion操作的数据库名称。

2-2-2、设置Salve服务器

完成MySQL Master服务的配置后，我们来看看Salve节点该如何进行设置。这里我们只演示一个Salve节点的设置，如果您还要在集群中增加新的Salve节点，那么配置过程都是类似的。无非是要注意在Master节点上增加新的Salve节点描述信息。

首先我们还是需要设置Salve节点的my.cnf文件：

# my.cnf文件中没有涉及Replicaion机制的配置信息，就不在这里列出了
......
# 开启日志
log-bin

sync_relay_log=1

# 必须为这个MySQL服务节点设置一个集群中唯一的server id信息

server_id=140

......

在MySQL官方文档中也详细描述了中继日志的各种控制参数，这里我们只使用了sync_relay_log参数。这个参数说明了Salve节点在成功接受到多少次Master同步日志信息后，才刷入中继日志文件。这个参数可以设置为1到N的任意一个值，当然设置为1的情况下虽然会消耗一些性能，但对于日志数据来说却是最安全的。

Salve的设置相对简单，接下来我们需要在Salve端开启相应的同步功能。主要是指定用于同步的Master服务地址、用户和密码信息：

# 请注意这里设置的用户名和密码信息要和Master上的设置一致
# 另外master log file所指定的文件
名也必须和Master上使用的日志文件名一致
> change master to master_host='192.168.61.140',
master_user='root',master_password='123456',
 master_log_file='kp2-bin.000002',master_log_pos=120;

# 启动Savle同步

> start slave;

# 然后我们就可以使用以下命令查看salve节点的同步状态

> show slave status;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.61.140

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: kp2-bin.000002

Read_Master_Log_Pos: 404

Relay_Log_File: vm2-relay-bin.000002

Relay_Log_Pos: 565

Relay_Master_Log_File: kp2-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

......

Master_Server_Id: 140

Master_UUID: 19632f72-9a90-11e6-82bd-000c290973df

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log;

waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

......

Auto_Position: 0

完成以上过程，一主一从的MySQL集群就配置完成了。

2-3、一主多从方案的使用建议

一主多从的MySQL集群方案，已经可以解决大部分系统结构化数据存储的性能要求。特别是那种数据查询频率/次数远远大于数据写入频率/次数的业务场景，例如电商系统的商品模块、物流系统的车辆/司机信息模块、电信CRM系统的客户信息模块、监控系统中保存的基本日志数据。但是这种架构方案并不能解决所有的问题，而且方案本身有一些明显的问题（后文详细讨论），所以在这里本文需要为各位将要使用类似MySQL集群方案的读者提供一些使用建议。

Master单节点性能应该足够强大，且只负责数据写入操作：一主多从的MySQL集群方式主要针对读密集型业务系统，其主要目标是将MySQL服务的读写压力进行分离。所以Master节点需要集中精力处理业务的写操作请求，这也就意味着业务系统所有的写操作压力都集中到了这一个节点上（write业务操作）。我们暂且不去分析这个现象可能导致的问题（后续内容会提到这种做法的问题），但这至少要求Master节点的性能足够强大。这里的性能不单单指通过MySQL InnoDB引擎提供的各种配置（一般我们使用InnoDB引擎），并结合业务特点所尽可能榨取的性能，最根本的还需要提升Master节点的硬件性能。

使用固态硬盘作为MySQL服务的块存储基础，并使用RAID 10磁盘阵列作为硬件层构建方案——这是生产环境下单个MySQL服务节点的基本组成逻辑，当然读者可以视自己生产环境下的的实际容量和性能要求进行必要的调整：

应使用一个独立的Salve节点作为备用的Master节点，虽然这种方式不可作为异地多活方案的基础但可作为本地高可用方案的实现基础。当然，为了防止由于日志错误导致的备份失败，这个备份的Salve节点也可以采用MySQL Replicaion机制以外的第三方同步机制，例如：Rsync、DRBD。Rsync是笔者在工作实践中经常使用的，进行MySQL数据增量同步的方式，而DRBD的差异块同步方式是互联网上能够找到最多资料的方式：

在后续的文章中，我们还会专门讨论针对Master节点的集群调整方案，并且建议读者如何使用适合系统自身业务的高可用方案。例如使用Keepalived / Heartbeat进行主备Master节点的切换：

复杂的统计查询需要专门的Salve节点进行支持。参与生产环境实时业务处理的任何MySQL服务节点，在这些服务节点上所运行的SQL查询应该尽可能简单，并且需要使用索引对检索进行支持。特别是数据量非常大的数据表，必须保证所有的检索操作都有索引提供支持，否则Table Full Scan的检索过滤方式不但会拖慢检索操作本身，还可能会明显拖慢其它的事务操作。通过MySQL提供的执行计划功能，技术人员能够很方便实现以上的要求。如果您的业务系统存在复杂的业务查询要求，例如周期性的财务流水报表，周期性的业务分组统计报表等，那么您最好专门准备一个（或多个）脱离实时业务的Salve节点，完成这个工作。

3. Problems exposed by the solution

However, this MySQL cluster solution also has many problems that need further improvement. In subsequent articles, we will discuss the following problems that still exist in the MySQL cluster:

Problems facing the upper-layer system: In the MySQL one-master multi-slave cluster, there are multiple service nodes. So when the upper-layer business system performs database operations (whether it is a write operation or a read operation), does it need to clearly know these specific service nodes and connect them? You know, when the upper-level business system needs to control more and more elements, the maintenance effort required by business system developers will increase exponentially.

Problems at the high-availability level: In a MySQL one-master, multiple-slave cluster, although there are multiple Salve nodes (nodes with read business properties), there is generally only one Master node (node ​​with write business properties). If one (or more) Salve nodes crash, it will not have much impact on the entire cluster (but it may affect a certain subsystem of the upper-level business system). The shortcoming of MySQL cluster is that there is only one Master node - once it crashes, the entire cluster will basically not be able to work normally. So we must think of some ways to change this potential risk.