MySQLのマスタースレーブレプリケーションと読み取り書き込み分離を理解するための記事

導入

企業の業務には通常、大量のデータがあり、単一のデータベースでは、データ ストレージ、セキュリティ、および高い同時実行性に関して実際のニーズを満たすことができません。そのため、複数のマスター スレーブ データ サーバーを構成して、マスター スレーブ レプリケーションを実現し、データの信頼性を高め、読み取りと書き込みを分離し、ストレージ エンジンによって発生するデータベースの負荷とテーブルおよび行のロックの問題を軽減する必要があります。

1. MySQL マスタースレーブレプリケーション

実際の運用環境では、データベースの読み取りと書き込みはすべて同じデータベース サーバーで行われるため、実際のニーズを満たすことができません。セキュリティ、高可用性、高同時実行性のいずれの点でも、実際のニーズに完全に応えることはできません。したがって、マスタースレーブレプリケーションを通じてデータが同期され、読み取りと書き込みの分離を通じてデータベースの同時負荷容量が向上します。これは rsync と多少似ていますが、違いは、rsync はディスク ファイルをバックアップするのに対し、MySQL マスター スレーブ レプリケーションはデータベース内のデータとステートメントをバックアップすることです。

1. MySQLレプリケーションタイプ

（１）ステートメントベースのレプリケーション（ステートメント）

サーバー上で SQL ステートメントを実行し、スレーブ サーバー上で同じステートメントを実行します。MySQL はデフォルトでステートメント ベースのレプリケーションを使用するため、実行効率が高くなります。

（２）行ベースのレプリケーション（ROW）

スレーブ サーバーでコマンドを再度実行するのではなく、変更をコピーします。

（３）混合型援助（MIXED）

デフォルトでは、ステートメント ベースのレプリケーションが使用されます。ステートメント ベースのレプリケーションを正確にレプリケートできないことが判明すると、行ベースのレプリケーションが使用されます。

2. MySQLマスタースレーブレプリケーションの原理

（１）マスターノードはデータの変更をバイナリログに記録します。マスターノード上のデータが変更されると、その変更がバイナリログに書き込まれます。

（２）スレーブノードは、一定時間間隔でマスターのバイナリログが変更されたかどうかを検出し、変更があった場合はI/Oスレッドを開始してマスターのバイナリイベントを要求します。

（3）同時に、マスターノードは各I/Oスレッドのダンプスレッドを開始し、バイナリイベントを送信してスレーブノードのローカルリレーログに保存します。スレーブノードはSQLスレッドを開始し、リレーログからバイナリログを読み取り、ローカルで再生します。つまり、SQL文に解析し、マスターノードのデータと一致するように1つずつ実行します。最後に、I/OスレッドとSQLスレッドはスリープ状態に入り、次回起動されるまで待機します。

注: リレー ログは通常、OS キャッシュに配置されるため、リレー ログのオーバーヘッドは非常に小さくなります。レプリケーション プロセスには非常に重要な制限があります。つまり、レプリケーションはスレーブ上でシリアル化されるため、マスター上の並列更新操作をスレーブ上で並列に実行することはできません。

3. MySQL マスタースレーブレプリケーションの遅延

①マスターサーバーの同時実行性が高く、大量のトランザクションが発生する

②ネットワーク遅延

③マスタースレーブハードウェアデバイスは

CPUメイン周波数、メモリIO、ハードディスクIO

④同期レプリケーションではなく、非同期レプリケーションです

データベースからMysqlパラメータを最適化します。たとえば、innodb_buffer_pool_size を増やすと、MySQL メモリ内で完了できる操作が増え、ディスク操作が減ります。

スレーブ ライブラリには高性能ホストを使用します。強力なCPUと大容量メモリを搭載。仮想クラウド ホストの使用を避け、物理ホストを使用すると、I/O パフォーマンスが向上します。

ライブラリのSSDディスクを使用する

コンピュータルーム間の同期を回避するためのネットワーク最適化

2. MySQLの読み書き分離

読み取りと書き込みの分離の基本原則は、マスター データベースでトランザクションの追加、変更、削除操作 (INSERT、UPDATE、DELETE) を処理し、スレーブ データベースで SELECT クエリ操作を処理することです。データベース レプリケーションは、トランザクション操作によって発生した変更をクラスター内のスレーブ データベースに同期するために使用されます。

データベースは、必ずしも読み取りと書き込みに分ける必要はありません。プログラムがデータベースをより頻繁に使用するが、更新が少なく、クエリが多い場合は、使用が検討されます。データベースのマスター/スレーブ同期と読み取り/書き込み分離を使用することで、データベースの負荷を分散し、パフォーマンスを向上させることができます。

1. MySQLの一般的な読み取りと書き込みの分離

（１）プログラムコードの内部実装に基づく

コードでは、ルートは選択と挿入によって分類されます。この方法は、実稼働環境でも最も広く使用されています。メリットは、プログラムコードに実装され、ハードウェア費用などの追加設備を必要としないため、パフォーマンスが向上することです。デメリットは、開発者が実装する必要があり、運用・保守担当者がどこから始めればよいかわからないことです。ただし、すべてのアプリケーションがプログラム コードで読み取りと書き込みの分離を実装するのに適しているわけではありません。たとえば、一部の大規模で複雑な Java アプリケーションでは、プログラム コードで読み取りと書き込みの分離を実装するには、大幅なコード変更が必要になります。

（２）中間プロキシ層に基づく実装

プロキシは通常、クライアントとサーバーの間に配置されます。プロキシサーバーはクライアントのリクエストを受信した後、判断してバックエンドデータベースに転送します。代表的なプログラムは次のとおりです。

①MySQLプロキシ。 MySQL-Proxy は、独自の Lua スクリプトを使用して SQL 判定を実行する MySQL オープンソース プロジェクトです。

②アトラス。これは、Qihoo 360 の Web プラットフォーム部門のインフラストラクチャ チームによって開発および保守されている、MySQL プロトコルに基づくデータ中間層プロジェクトです。これは、mysql-proxy バージョン 0.8.2 に基づいており、最適化され、いくつかの新機能が追加されています。 360 は社内で MySQL ビジネスを実行するために Atlas を使用しており、毎日実行される読み取りおよび書き込み要求の数は数十億に達します。トランザクションとストアド プロシージャをサポートします。

③アメーバ。開発者はChen Siru氏で、作者はかつてAlibabaで働いていました。このプログラムは Java 言語で開発されており、Alibaba はそれを本番環境で使用します。ただし、トランザクションとストアド プロシージャはサポートされていません。

MySQL Proxy を使用するには、多数の Lua スクリプトを作成する必要があるため、これらの Lua スクリプトは既成ではなく、自分で作成する必要があります。これは、MySQL Proxy の組み込み変数と MySQL プロトコルに精通していない人にとっては非常に困難です。 Amoeba は非常に使いやすく、移植性も高いソフトウェアです。そのため、実稼働環境のデータベース プロキシ層で広く使用されています。

2. MySQLの読み書き分離原則

読み取りと書き込みの分離とは、マスター サーバーでは書き込みのみ、スレーブ サーバーでは読み取りのみを行うことを意味します。基本的な原則は、マスター データベースでトランザクション操作を処理し、スレーブ データベースで選択クエリを処理することです。データベース レプリケーションは、マスター データベースでのトランザクション操作の結果として生じた変更をクラスター内のスレーブ データベースに同期するために使用されます。

3. MySQL マスタースレーブレプリケーションと読み取り/書き込み分離の設定手順

1. 環境を構築する

2. ニーズを解決する

クライアントがプロキシ サーバーにアクセスします。プロキシ サーバーはマスター サーバーに書き込みます。マスター サーバーは追加、削除、変更を自身のバイナリ ログに書き込みます。スレーブ サーバーはマスター サーバーのバイナリ ログを自身のリレー ログに同期します。スレーブ サーバーはリレー ログをデータベースに再生します。クライアントが読み取る場合、プロキシ サーバーはスレーブ サーバーに直接アクセスします。これにより負荷が軽減され、負荷分散の役割を果たします。

3. 準備段階（ファイアウォールを閉じ、アクセスメカニズムを制御する）

[root@localhost ~]# systemctl stop ファイアウォールd
[root@localhost ~]# systemctl ファイアウォールを無効にする
[root@localhost ~]# setenforce 0

4. MySQLマスタースレーブレプリケーションを構築する

（1）MySQLマスタースレーブサーバの時刻同期設定手順

①マスターサーバー（192.168.32.128）の設定

[root@localhost ~]# yum -y install ntp #ntpをインストール
[root@localhost ~]# vim /etc/ntp.conf #ntpの設定
# 25 行目に次の内容を追加します server 127.127.32.0 # ローカルをクロック ソースとして設定し、ネットワーク セグメントの変更に注意してください fudge 127.127.32.0 stratum 8 # 時間レベルを 8 に設定します (15 に制限)
[root@localhost ~]# systemctl restart ntpd #ntp サービスを再起動する②Slave1 サーバー (192.168.32.133) と Slave2 サーバー (192.168.32.134) の設定[root@localhost ~]# yum -y install ntp ntpdate #サービスをインストールします。ntpdate は時刻を同期するために使用されます[root@localhost ~]# systemctl start ntpd #サービスを開始します[root@localhost ~]# /usr/sbin/ntpdate 192.168.32.128 #時刻の同期を実行し、マスター サーバーの IP を指定します
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.32.128 #30分ごとに時刻を同期するスケジュールタスクを作成します

（２）マスターサーバー（192.168.32.128）を構成する

[root@localhost ~]# vim /etc/my.cnf
server-id = 1 # 同じ ID にすることはできないので注意 log-bin=master-bin # 追加、マスター サーバーがバイナリ ログを開きます log-slave-updates=true # 追加、スレーブ サーバーがバイナリ ログを更新できるようにします [root@localhost ~]# systemctl restart mysqld # 構成を有効にするためにサービスを再起動します [root@localhost ~]# mysql -uroot -p123456 # データベース プログラムにログインします mysql> grant replication slave on *.* to 'myslave'@'192.168.32.%' identified by '123456'; # スレーブ サーバーを承認します mysql> flush privileges;
mysql> show master status; #File列にはログ名が表示され、Fosition列にはオフセットが表示されます

（3）スレーブ1サーバ（192.168.32.133）とスレーブ2サーバ（192.168.32.134）の構成

①スレーブ1サーバ（192.168.32.133）の設定

[root@localhost ~]# vim /etc/my.cnf
server-id = 2 # 変更、ID はマスターと異なり、2 つのスレーブの ID も異なる必要があることに注意してください relay-log=relay-log-bin # 追加、リレー ログをオンにして、ログ ファイル レコードをマスター サーバーからローカルに同期します relay-log-index=slave-relay-bin.index # 追加、リレー ログ ファイルの場所と名前を定義しますmysql> change master to master_host='192.168.32.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; # 同期を構成します。master_log_file と master_log_pos の値は、マスターの値と一致している必要があることに注意してくださいmysql> start slave; # 同期を開始します。エラーがある場合は、reset slave; を実行します
mysql> show slave status\G #スレーブのステータスを確認します（IO スレッドと SQL スレッドの両方が Yes で、正常な同期を示していることを確認します）
Slave_IO_Running: Yes #ホストとのIO通信を担当します Slave_SQL_Running: Yes #独自のスレーブmysqlプロセスを担当します

②スレーブ2サーバ（192.168.32.134）の設定

[root@localhost ~]# vim /etc/my.cnf
server-id = 3 # 変更、ID はマスターと異なり、2 つのスレーブの ID も異なる必要があることに注意してください relay-log=relay-log-bin # 追加、リレー ログをオンにして、ログ ファイル レコードをマスター サーバーからローカルに同期します relay-log-index=slave-relay-bin.index # 追加、リレー ログ ファイルの場所と名前を定義しますmysql> change master to master_host='192.168.32.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; # 同期を構成します。master_log_file と master_log_pos の値は、マスターの値と一致している必要があることに注意してくださいmysql> start slave; # 同期を開始します。エラーがある場合は、reset slave; を実行します
mysql> show slave status\G #スレーブのステータスを確認します（IO スレッドと SQL スレッドの両方が Yes で、正常な同期を示していることを確認します）
Slave_IO_Running: Yes #ホストとのIO通信を担当します Slave_SQL_Running: Yes #独自のスレーブmysqlプロセスを担当します

Slave_IO_Running: No の考えられる理由は、ネットワークが接続されていない、my.cnf 構成に問題がある、パスワード、ファイル名、および pos オフセットが正しくない、ファイアウォールがオフになっていない、などです。

（４）マスタースレーブ複製効果の検証

①プライマリサーバーで実行を入力し、「mysql01」というデータベースを作成します

②2つのサーバーから検証

5. MySQLの読み書き分離を構築する手順

このソフトウェアは、MySQL の分散データベース フロントエンド プロキシ レイヤー専用です。主にアプリケーション レイヤーが MySQL にアクセスする際の SQL ルーティングとして機能し、負荷分散、高可用性、SQL フィルタリング、読み取り/書き込み分離、ターゲット データベースへのルーティング、複数のデータベースへの同時要求などの機能を備えています。Amoeba を通じて、複数のデータ ソースの高可用性、負荷分散、データ スライシングの機能を実現できます。

（1）Amoebaサーバーを設定し、Java環境をインストールします（Amoebaはjdk1.5をベースに開発されているため、公式の推奨はjdk1.5または1.6であり、それ以上のバージョンは推奨されません）。

[root@localhost ~]# cd /opt/
[root@localhost opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin
[root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@localhost opt]# cd /usr/local/
[root@localhost local]# chmod +x jdk-6u14-linux-x64.bin
: : : : : : : : : : : : : : : 

（2）Amoebaのインストールと設定

[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba # amoeba start|stop が表示されればインストールは成功です

（３）Amowbaサーバーの読み取りと書き込みを分離し、2つのスレーブを設定して読み取りと書き込みの負荷を分散させる

①まず、Amoebaがアクセスできるように、Master、Slave1、Slave2のmysqlの権限を開きます。

マスター サーバー (192.168.32.128)、スレーブ 1 サーバー (192.168.32.133)、スレーブ 2 サーバー (192.168.32.134) はすべて次のように構成されています。

mysql> '123.com' によって識別される te​​st@'192.168.32.%' に *.* のすべての権限を許可します。

②Amoebaサーバ内のamoeba.xml設定ファイルを編集する

[root@localhost ~]# cd /usr/local/amoeba/conf/
[root@localhost conf]# cp amoeba.xml amoeba.xml.bak #バックアップ [root@localhost conf]# vim amoeba.xml #amoeba 設定ファイルの修正 #修正する 30 行 <property name="user">amoeba</property>
#32行目を修正 <property name="password">123123</property>
#115 行の変更 <property name="defaultPool">master</property>
#117 コメント <property name="writePool">master</property> を削除します
<property name="readPool">スレーブ</property>

③dbServers.xml設定ファイルを編集する

[root@localhost conf]# cp dbServers.xml dbServers.xml.bak #バックアップ [root@localhost conf]# vim dbServers.xml #データベース構成ファイルの変更 #23 行のコメントアウト 効果: デフォルトでテスト ライブラリを入力します。mysql にテスト ライブラリがない場合、エラーが報告されます <!-- <property name="schema">test</property> -->
#26 行の変更 <property name="user">test</property>
# 28-30 行目のコメントを解除します<!-- mysql パスワード -->
<property name="password">123.com</property>
#45行目を変更し、マスターサーバーの名前をMasterに設定します
<dbServer 名="マスター" 親="抽象サーバー">
# 48行目を変更し、プライマリサーバーのアドレスを設定します <property name="ipAddress">192.168.32.128</property>
#52行目を変更し、スレーブサーバー名をslave1に設定します
<dbServer 名="slave1" 親="abstractServer">
# 55行目を変更し、スレーブサーバー1のアドレスを設定します <property name="ipAddress">192.168.132.133</property>
#58 上記の 6 行をコピーして貼り付け、スレーブ サーバー 2 の名前とアドレスを設定します <dbServer name="slave2" parent="abstractServer">
<プロパティ名="ipアドレス">192.168.32.134</プロパティ>
#65 行の変更 <dbServer name="slaves" virtual="true">
#71 <property name="poolNames">slave1,slave2</property> を変更する

④設定が正しいことを確認したら、Amoebaソフトウェアを起動します。デフォルトのポートはtcp 8066です。

[root@localhost conf]# /usr/local/amoeba/bin/amoeba スタート &

[root@localhost conf]# netstat -antulp | grep 8066

6. 読み取りと書き込みの分離をテストする

（１）クライアント構成

[root@localhost ~]# yum install -y mariadb-server mariadb #YUM を使用して MySQL 仮想クライアントをすばやくインストールします [root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.32.135 -P8066 #amoeba サーバー プロキシ経由で mysql にアクセスします。クライアント経由で mysql に接続した後、書き込まれたデータはマスター サービスによってのみ記録され、その後スレーブ サーバーに同期されます。

（２）マスターサーバーにテーブルを作成し、それを２つのスレーブサーバーに同期する

mysql> xm を使用します。
mysql> テーブル test (id int(10),name varchar(10),address varchar(20)) を作成します。

（３）次にスレーブサーバのスレーブ機能をオフにし、マスターサーバからテーブルを同期し、データ内容を手動で挿入する。

mysql> stop slave; #同期を終了しますmysql> use xm;

①スレーブ1で設定

mysql> テストに値を挿入します('1','zhangsan','this_is_slave1');

②スレーブ2で設定

mysql> テストに値を挿入します('2','lisi','this_is_slave2');

（4）メインサーバーに戻り、他のコンテンツを挿入する

mysql> テストに値を挿入します('3','wangwu','this_is_master');

（５）読み取り操作をテストし、クライアントホストにアクセスして結果を照会する

mysql> xm を使用します。
mysql> select * from test; #クライアントはそれぞれスレーブ1とスレーブ2からデータを読み取り、マスターサーバーに追加されたデータではなく、2つのスレーブサーバーに追加されたデータのみが表示されます。

（6）クライアントにステートメントが挿入されますが、クライアントでクエリすることはできません。最後に、このステートメントの内容はマスターでのみ表示でき、書き込み操作がマスターサーバー上で行われていることを示しています。

mysql>insert into test values('4','kanglong','this_is_client'); #このデータはプライマリサーバーでのみ利用可能です

（7）両方のスレーブサーバーでstart slaveを実行し、マスターサーバーに追加されたデータを同期します。

mysql> スレーブを起動します。

要約する

1. マスタースレーブレプリケーションの原則: マスターサーバーはデータを追加、削除、変更、更新し、トランザクションがコミットされた後、バイナリファイルに書き込まれます。スレーブ サーバーの I/O スレッドは、マスター サーバーのバイナリ ファイルを検出するように要求します。新しいデータがある場合、マスター サーバーはダンプ スレッドを使用してバイナリ ファイルをスレーブ サービス I/O スレッドのメモリに送信し、リレー ログに書き込みます。SQL スレッドはリレー ログ ファイルをリッスンし、新しいデータがある場合にリレー ログ ファイルを読み取り、データをスレーブ サーバーのデータベースに再生します。

2. マスタースレーブレプリケーションの動作プロセスは、ログ（マスターバイナリログとスレーブリレーログ）、要求方法（I/Oスレッド、ダンプスレッド、SQLスレッド）に基づいています。

3. 読み取りと書き込みの分離とは、マスター サーバーでは書き込みのみ、スレーブ サーバーでは読み取りのみを行うことを意味します。

これで、MySQL マスター スレーブ レプリケーションと読み取り/書き込み分離に関するこの記事は終了です。MySQL マスター スレーブ レプリケーションと読み取り/書き込み分離に関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。