MySQL の単純なマスター/スレーブ方式が問題を明らかにする

1. 概要

この記事から、mysql のさまざまなサービス クラスターを構築する方法を読者に紹介します。一般的なディスカッションのアイデアは、最も単純な MySQL マスター/スレーブ ソリューションから始めて、このソリューションの欠点をより複雑なクラスター ソリューションに拡張し、後者がこれらの欠点をどのように改善するかを紹介することです。

2. MySQL の最も単純なマスター/スレーブ スキームと動作原理

ここで説明するバージョンは、現在運用環境で最もよく使用されているバージョン 5.6 に基づいています。その機能の一部は、バージョン 5.7 で改善されています。および最新のバージョン 8.0 が含まれていますが、この記事を通じて読者が MySQL クラスターを構築するための技術的アイデアを理解することに影響するものではなく、このメカニズムは MariaDB に拡張することもできます。たとえば、MySQL に付属するログ レプリケーション メカニズム (Replicaion メカニズム) については、すぐに説明します。

MySQL 独自のログ レプリケーション メカニズムは、MySQL-Replicaion と呼ばれます。レプリケーション テクノロジは MySQL の非常に初期のバージョン 5.1 から利用可能であり、現在のバージョンではこのテクノロジは非常に成熟しており、そのサポート技術者はさまざまな MySQL クラスタ構造を作成できます。もちろん、サードパーティのソフトウェア/コンポーネントでサポートされているいくつかの MySQL クラスター ソリューションも後で紹介します。

2-1. MySQL-Replicaion の基本的な動作原理

レプリケーションのメカニズム 技術的な観点から見ると、マスターとスレーブの 2 つの基本的な役割があります。マスター ノードはレプリカ メカニズムの 1 つ以上のターゲットにデータを出力する役割を果たし、一方、スレーブ ノードはレプリカ メカニズムのマスター ノードからデータを受け入れる役割を担います。実際のビジネス環境では、マスター ノードとスレーブ ノードにはそれぞれ、書き込みノードと読み取りノードという別の名前が付いています。はい、レプリケーション メカニズムを使用して、読み取りと書き込みの分離をターゲットとした MySQL クラスター サービスを構築できます。ただし、記事の内容を読むときに読者が曖昧にならないように、この記事 (および後続の記事) ではマスター ノードとスレーブ ノードという用語を使用します。レプリケーション メカニズムは、MySQL サービスのバイナリ ログに依存してデータを同期します。

上の図に示すように、マスター ノードのバイナリ ログが変更されると、Salve はマスター ノードとのネットワーク接続を確立します。 、1 つ以上の MySQL Salve サービス ノードは、ネットワークを通じてこれらの変更ログを監視します。次に、Salve ノードは、まずこれらの変更をローカルのリレー ログ ファイル (リレー ログ) に書き込みます。これは、例外が発生したときに MySQL サービスがデータを同期できないことを回避するために行われます。以前紹介した InnoDB ログ。リレー ログ ファイルが記録されると、MySQL Salve サービスはこれらの変更を対応するデータ テーブルに反映して、データ同期プロセスを完了します。最後に、Salve は REDO ログ ファイル内の更新ポイント (位置) を更新し、次のレプリケーション操作の準備をします。

このプロセスでは複数の要素を構成できます。たとえば、マスターノードでのデータ操作の数とログの書き込み数の比率を、sync_binlog パラメーターを通じて構成でき、ログデータの情報構造を構成できます。 binlog_format パラメータを使用して、sync_relay_log パラメータを設定できます。Salve ノードでログ データを受信するシステムと、それがリレー ログ ファイルに書き込まれる回数との比率を設定します。これらのパラメーターと例で使用されるその他のパラメーターについては、この記事の後続のセクションで説明します。

2-2. MySQL 1 つのマスターと複数のスレーブの構築方法

MySQL レプリケーション メカニズムの基本的な動作方法を紹介した後、マスター ノードとスレーブ ノードで構成される MySQL クラスターを簡単に構築します。読者は、この 1 マスター 1 スレーブの MySQL クラスター ソリューションを、任意の 1 マスター、複数スレーブのクラスター ソリューションに拡張できます。

この例では、セットアップにバージョン 5.6 を使用します。もちろん、バージョン 5.7 のインストールです。似ています。また、MySQL サービスをインストールし、Linux オペレーティング システム (Centos 5.6/5.7/6.X) で基本設定を実行するプロセスについては、長さと記事の位置づけの理由から、ここでは説明しません。次の IP を使用して、クラスターのマスター ノードとサルブ ノードをそれぞれ Linux にインストールします:

MySQL マスター サービス: 192.168.61.140

MySQL Salve サービス: 192.168.61.141

2-2-1、マスター サーバーをセットアップします。

まず、MySQL マスター サービスの my.cnf メイン構成ファイル内の情報を変更する必要があります。主な目的は、マスター ノードでバイナリ ログ機能を有効にすることです (ここで言及されているログは InnoDB ではないことに注意してください)。エンジンログ）。

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

# 以下のパラメータについては後で説明します

sync_binlog=1

binlog_format=mixed

binlog-do-db=qiang

binlog_checksum=CRC32

binlog_cache_size=2M

max_binlog_c G

max_binlog_size = 100M

# この MySQL サービス ノードにはクラスター内で一意のサーバー ID 情報を設定する必要があります

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. ソリューションによって明らかになった問題

ただし、この MySQL クラスター ソリューションには、さらなる改善が必要な多くの問題もあります。今後の記事では、MySQL クラスターに依然として存在する以下の問題について説明します:

上位層システムが直面する問題: MySQL ワンマスター マルチスレーブ クラスターには、複数のサービス ノードがあります。では、上位層のビジネス システムがデータベース操作 (書き込み操作または読み取り操作) を実行するとき、これらの特定のサービス ノードを明確に認識してそれらを接続する必要があるのでしょうか。ご存知のように、上位レベルのビジネス システムが制御する必要がある要素が増えると、ビジネス システム開発者が必要とするメンテナンスの労力は飛躍的に増加します。

高可用性レベルの問題: MySQL 1 マスター、複数スレーブ クラスターでは、複数のサルブ ノード (ビジネス プロパティの読み取りノード) がありますが、通常、マスター ノード (ビジネス プロパティの書き込みノード) は 1 つだけです。 1 つ (または複数) の Salve ノードがクラッシュしても、クラスター全体には大きな影響はありません (ただし、上位レベルのビジネス システムの特定のサブシステムには影響する可能性があります)。 MySQL クラスターの欠点は、マスター ノードが 1 つしかないことです。マスター ノードがクラッシュすると、基本的にクラスター全体が正常に動作できなくなります。したがって、この潜在的なリスクを変える方法をいくつか考えなければなりません。