MySQLデータベースのマスタースレーブレプリケーションと読み取り書き込み分離に関する詳細なチュートリアル

序文

実際の運用環境では、MySQL データベースへの読み取りと書き込みの両方を 1 つのデータベース サービスで実行すると、セキュリティ、高可用性、高同時実行性などの実際のニーズを満たすことができません。一般的には、マスター スレーブ レプリケーションを通じてデータを同期し、読み取りと書き込みの分離を使用してデータベースの同時負荷容量を向上させます。

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

1. サポートされているレプリケーションの種類

ステートメントベースのレプリケーション (ステートメント): サーバー上で SQL ステートメントを実行し、スレーブ サーバー上で同じステートメントを実行します。MySQL は、デフォルトで実行効率の高いステートメントベースのレプリケーションを使用します。行ベースのレプリケーション (行): スレーブ サーバー上でコマンドを実行する代わりに、変更されたコンテンツをコピーします。混合レプリケーション (混合): サーバー上で SQL ステートメントを実行し、スレーブ サーバー上で同じステートメントを実行します。MySQL は、デフォルトで実行効率の高いステートメントベースのレプリケーションを使用します。

2. マスタースレーブレプリケーションの動作プロセスはログに基づいています

マスターバイナリログ

スレーブリレーログ

3. リクエスト方法

I/O スレッド

ダンプスレッド

SQL スレッド

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

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

②スレーブノードは、一定の時間間隔でマスターのバイナリログが変更されたかどうかを検出します。

変更が発生すると、マスターのバイナリイベントを要求するI/Oスレッドが開始されます。

③同時に、マスターノードは各I/Oスレッドに対してダンプスレッドを開始し、バイナリイベントを送信してスレーブノードのローカルリレーログに保存します。

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

レプリケーション プロセスには重要な制限があります。つまり、レプリケーションはスレーブ上でシリアル化されるため、マスター上の並列更新操作をスレーブ上で並列に実行することはできません。

リレー ログは通常、OS キャッシュに配置されるため、リレー ログのオーバーヘッドは非常に小さくなります。

5. MySQL クラスタとマスター/スレーブ レプリケーションはどのようなシナリオに適していますか?

クラスタリングとマスタースレーブレプリケーションは、高い同時実行性と大量のトラフィックに対応するように設計されています。Web サイトのトラフィックと同時実行性が大きすぎる場合、少数のデータベースサーバーでは処理できず、Web サイトへのアクセスが遅くなります。データの書き込みにより、データテーブルまたはレコードがロックされます。ロックとは、他のアクセススレッドが一時的に読み取りまたは書き込みできず、書き込みが完了するまで待機してから続行する必要があることを意味し、他のユーザーの読み取り速度に影響します。マスタースレーブレプリケーションを使用すると、一部のサーバーを読み取りに特化し、一部のサーバーを書き込みに特化することで、この問題を解決できます。

6. マスター/スレーブ レプリケーションと読み取り/書き込み分離を使用する理由は何ですか?

マスタースレーブレプリケーションと読み取り/書き込み分離は、通常、一緒に使用されます。その目的は非常に単純で、データベースの同時パフォーマンスを向上させることです。単一のマシンで、読み取りと書き込みの両方を 1 つの MySQL で実行する場合、パフォーマンスは確実に高くならないことが想像できます。 MySQL サーバーが 3 台あり、1 つのマスターが書き込み操作のみを担当し、2 つのスレーブが読み取り操作のみを担当する場合、パフォーマンスは大幅に向上するのではないでしょうか。

したがって、マスター/スレーブ レプリケーションと読み取り/書き込みの分離により、データベースはより高い同時実行性をサポートできるようになります。

業務量が拡大するにつれて、MySQL を単一のマシンに導入すると、I/O 頻度が高くなりすぎます。マスタースレーブレプリケーションと読み取り書き込み分離を使用すると、データベースの可用性が向上します。

7. 目的と条件

MySQL マスタースレーブレプリケーションの目的:

フェイルオーバーのためのリアルタイム災害復旧

読み取りと書き込みの分離、クエリサービスの提供

サービスへの影響を避けるためのバックアップ

必要条件:

メインデータベースはbinlogログを開きます（log-binパラメータを設定します）

マスターとスレーブのサーバーIDが異なります

スレーブサーバーはマスターに接続できる

8. MySQL マスタースレーブレプリケーションの問題

メインデータベースがダウンすると、データが失われる可能性があります

スレーブデータベースにはSQLスレッドが1つしかなく、マスターデータベースには書き込み負荷がかかり、レプリケーションが遅れる可能性があります。

解決

半同期レプリケーション - データ損失の問題を解決

並列レプリケーション - スレーブレプリケーション遅延の問題を解決する

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

①マスターサーバーの同時実行性が高く、トランザクション数が多い ②ネットワーク遅延 ③マスタースレーブハードウェアデバイス - CPUメイン周波数、メモリIO、ハードディスクIO
④同期レプリケーションではなく、非同期レプリケーションです

• データベースからMysqlパラメータを最適化します。たとえば、innodb_buffer_pool_size を増やすと、MySQL メモリ内で完了できる操作が増え、ディスク操作が減ります。
• スレーブ ライブラリは、強力な CPU と大容量メモリを含む高性能ホストを使用します。仮想クラウドホストの使用を避け、物理ホストを使用するとI/Oパフォーマンスが向上します。
• ライブラリのSSDディスクを使用する
• コンピュータルーム間の同期を回避するためのネットワーク最適化

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

3. 読み取りと書き込みの分離

1. 原則

①マスターサーバーでは書き込みのみ、スレーブサーバーでは読み取りのみ

②マスターデータベースはトランザクションクエリを処理し、スレーブデータベースは選択クエリを処理します

③データベースレプリケーションは、トランザクションクエリによって生じた変更をクラスタ内のスレーブデータベースに同期するために使用されます。

2. 読むことと書くことを分ける必要があるのはなぜですか?

データベースの「書き込み」操作 (10,000 データの書き込みには 3 分かかる場合があります) は比較的時間がかかりますが、データベースの「読み取り」操作 (10,000 データの読み取りには 5 秒しかかからない場合があります) は、読み取りと書き込みを分離することで、データベースの書き込みがクエリの効率に影響を与えるという問題を解決します。

3. 読み取りと書き込みはいつ分離する必要がありますか?

データベースは、必ずしも読み取りと書き込みを分離する必要はありません。プログラムがデータベースをより頻繁に使用するが、更新が少なく、クエリが頻繁に行われる場合は、データベースのマスタースレーブ同期の使用を検討します。その後、読み取りと書き込みの分離により、データベースの負荷を共有し、パフォーマンスを向上させることができます。4. マスタースレーブレプリケーションと読み取りと書き込みの分離実際の運用環境では、データベースの読み取りと書き込みはすべて同じデータベースサーバーで行われるため、実際のニーズを満たすことができません。

5. 現在ではより一般的なMySQLの読み取りと書き込みの分離

以下の2つのタイプに分かれます

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

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

②中間プロキシ層に基づく実装

プロキシは通常、クライアントとサーバーの間に配置されます。プロキシサーバーはクライアントのリクエストを受信した後、判断した後、バックエンドデータベースに転送します。代表的なプログラムは次のとおりです。(1) MySQL-Proxy。MySQL-Proxyは、独自のLuaスクリプトを通じてSQL判断を行うMySQLオープンソースプロジェクトです。
（2）Atlasは、Qihoo 360のWebプラットフォーム部門のインフラストラクチャチームによって開発および保守されている、MySQLプロトコルに基づくデータ中間層プロジェクトです。mysql-proxy 0.8.2バージョンに基づいており、いくつかの新機能が追加されるように最適化されています。 360 は社内で MySQL ビジネスを実行するために Atlas を使用しており、毎日実行される読み取りおよび書き込み要求の数は数十億に達します。トランザクションとストアドプロシージャをサポート (3) かつてアリババで働いていた陳思如氏が開発したAmoeba。このプログラムは Java 言語で開発されており、Alibaba はそれを本番環境で使用します。ただし、トランザクションとストアド プロシージャはサポートされていません。

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

IV. ケースの実装

1. ケース環境

このケース環境はステージサーバーを使用して構築されています。トポロジー図は次のとおりです。

2. 実験的なアイデア（ニーズの解決）

クライアント アクセス プロキシ サーバー

プロキシサーバーがマスターサーバーに書き込む

マスターサーバーは追加、削除、変更を自身のバイナリログに書き込みます。

スレーブサーバーはマスターサーバーのバイナリログを自身のリレーログに同期します。

サーバーからデータベースにリレーログを再生する

クライアントが読み取り、プロキシサーバーがスレーブサーバーに直接アクセスします

負荷を軽減し、負荷分散の役割を果たす

3. 準備

• クライアントに加えて、まずソースコードからMySQLをコンパイルしてインストールする必要があります。
• すべてのファイアウォールとアクセス制御メカニズムをオフにする必要があります

systemctl 停止 ファイアウォール
systemctl ファイアウォールを無効にする
#ファイアウォールをオフにする（起動時に無効にする）
強制0を設定する
#セキュリティアクセス制御メカニズムを無効にする

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

①Mysqlマスタースレーブサーバの時刻同期

マスターサーバーの設定

#NTPをインストールする
yum -y ntpをインストール
#NTPの設定
vim /etc/ntp.conf
#最後の行に次の内容を追加します server 127.127.126.0
ファッジ 127.127.126.0 ストラタム8
#ローカルクロックソースを設定し、ネットワークセグメントの変更に注意してください #時間レベルを8に設定します（15に制限されます）
#サービスを再起動します service ntpd restart

サーバー設定から

yum -y インストール ntp ntpdate
#サービスをインストールします。ntpdate は時刻を同期するために使用されます。サービス ntpd の開始
#サービス /usr/sbin/ntpdate 192.168.126.11 を開始します
#時刻同期を実行し、マスターサーバーのIPを指定します
crontab -e
#30分ごとに時刻を同期するスケジュールタスクを記述します*/30 * * * * /usr/sbin/ntpdate 192.168.126.11

②MySQLマスターサーバーの設定

vim /etc/my.cnf
#次のコンテンツを設定します server-id = 1
ログビン=マスタービン
#追加、マスターサーバーがバイナリログを開く log-slave-updates=true
#追加、サーバーからのバイナリログの更新を許可する systemctl restart mysqld
#設定を有効にするにはサービスを再起動します。mysql -uroot -p123123
# データベース プログラムにログインし、スレーブ サーバーに権限を付与します。GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.126.%' IDENTIFIED BY '123123';
権限のフラッシュ;
マスターステータスを表示します。
やめる
#File列にはログ名が表示され、Fosition列にはオフセットが表示されます

③スレーブサーバーの設定

vim /etc/my.cnf
サーバーID = 2
#変更、IDはマスターと異なり、2つのスレーブのIDも異なる必要があることに注意してください。relay-log=relay-log-bin
#追加、リレーログを開き、マスターサーバーからローカルのrelay-log-index=slave-relay-bin.indexにログファイルレコードを同期します
#リレーログファイルの場所と名前を追加定義します。systemctl restart mysqld
mysql -uroot -p123123
マスターをmaster_host='192.168.126.11'、master_user='myslave'、master_password='123123'、master_log_file='master-bin.000001'、master_log_pos=604に変更します。
#同期を設定します。master_log_file と master_log_pos の値はマスター開始スレーブの値と一致している必要があることに注意してください。
#同期を開始し、エラーがある場合はスレーブのリセットを実行します。
スレーブステータスを表示\G
#スレーブのステータスを確認します // IO スレッドと SQL スレッドの両方が Yes であることを確認します。これは、同期が正常であることを示します。Slave_IO_Running: Yes
#ホストとのIO通信を担当Slave_SQL_Running: Yes
#自分のスレーブmysqlプロセスに責任を持つ

スレーブ1:

スレーブ2: スレーブ1と同じ構成ですが、IDが異なります

一般的に、Slave_IO_Running にはいくつかの可能性があります: いいえ:

• ネットワークが機能していません
• my.cnf 設定に問題があります
• パスワード、ファイル名、位置オフセットが正しくありません
• ファイアウォールがオフになっていません

④マスタースレーブレプリケーションの効果を検証する

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

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

① ホスト Amoeba に Java 環境をインストールします。Amoeba は jdk1.5 をベースに開発されているため、公式の推奨は jdk1.5 または 1.6 を使用することです。それ以上のバージョンは推奨されません。

cd /opt/
#FinalShellで、ソフトウェアパッケージamoeba-mysql-binary-2.2.0.tar.gzをドラッグします。
jdk-6u14-linux-x64.bin
cp jdk-6u14-linux-x64.bin /usr/local/
/usr/local/ をコピーします
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
# 一番下まで Enter キーを押し続けます。プロンプトが表示されたら、YES と入力して Enter キーを押します。mv jdk1.6.0_14/ /usr/local/jdk1.6
#vim /etc/profile の名前を変更する
#グローバル設定ファイルを編集し、最後の行に次の設定を追加します。export JAVA_HOME=/usr/local/jdk1.6
エクスポート CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
エクスポート PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
AMOEBA_HOME=/usr/local/amoeba をエクスポートします。
PATH=$PATH:$AMOEBA_HOME/bin をエクスポートします

#出力はJava作業ディレクトリを定義します #指定されたJavaタイプを出力します #パス環境変数にjavaを追加します #出力はアメーバ作業ディレクトリを定義します #パス環境変数を追加します source /etc/profile
#変更したグローバル設定ファイルを実行する java -version
#Javaバージョン情報をチェックしてインストールが成功したかどうかを確認します

②Amoebaをインストールして設定する

/usr/local/amoeba ディレクトリに移動します。
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
アメーバ
#amoeba start|stopが表示されたらインストールは成功です

③Amowbaの読み書き分離と、2つのスレーブの読み書き負荷分散を設定します。マスター、スレーブ1、スレーブ2のmysqlでAmoebaがアクセスできるように権限を開きます。

④ホストアメーバ内のamoeba.xml設定ファイルを編集する

/usr/local/amoeba/conf/ をコピーします。
cp amoeba.xml amoeba.xml.bak
vim アメーバ.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設定ファイルを編集する

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

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

/usr/local/amoeba/bin/amoeba スタート&
#Amoeba ソフトウェアを起動し、Ctrl + C を押して netstat -anpt | grep java に戻ります。
#ポート8066が開いているかどうかを確認します。デフォルトのポートはTCP 8066です

⑦テスト

1. クライアントにアクセスし、MySQL仮想クライアントをすばやくインストールし、プロキシ経由でMySQLにアクセスします。

yum -y mysqlをインストールします
#YUM を使用して MySQL 仮想クライアントをすばやくインストールします。mysql -u amoeba -p123123 -h 192.168.126.12 -P8066
#プロキシ経由でMySQLにアクセスします。IPアドレスはamobaを指します
#クライアント経由でMySQLに接続した後、書き込まれたデータはマスターサービスによってのみ記録され、その後スレーブサーバーに同期されます。

2. マスターにテーブルを作成し、2つのスレーブサーバーに同期します。

クラブを使用する。
テーブル puxin (id int(10),name varchar(10),address varchar(20)) を作成します。

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

奴隷を停止します。
#同期の使用をオフにするクラブ;
puxin に値 ('1'、'wangyi'、'this_is_slave1') を挿入します。
#奴隷2
puxin に値 ('2'、'wanger'、'this_is_slave2') を挿入します。

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

pucin に値 ('3'、'wangwu'、'this_is_master') を挿入します。

5. 読み取り操作をテストし、クライアントホストに移動して結果を照会します。

クラブを使用する。
puxin から * を選択します。

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

puxin に値 ('4'、'liuliu'、'this_is_client') を挿入します。

7. 次に、2つのスレーブサーバーでstart slaveを実行します。マスターサーバーに追加されたデータは同期されます。

要約する

これにより、MySQL の読み取りと書き込みの分離が達成されたことが確認されます。現在、データの非同期を回避するために、すべての書き込み操作はマスター サーバー上で実行されます。

すべての読み取り操作はスレーブ サーバーに分散され、データベースの負荷が分散されます。

1. マスタースレーブ同期ステータスが成功かどうかを確認する方法

スレーブ サーバーでコマンド show slave status\G を入力して、マスター スレーブ情報を表示します。この情報には、IO スレッドのステータス情報、マスター サーバーの IP アドレス、ポート、トランザクション開始番号が含まれます。

slave_io_running と slave_sql_running の両方が yes と表示されている場合、マスターとスレーブの同期ステータスが成功していることを意味します。

2. I/O と SQL が「はい」でない場合は、どのようにトラブルシューティングしますか?

• まず、ネットワークの問題を排除し、ping コマンドを使用してスレーブ サービスがマスター サーバーと通信できるかどうかを確認します。
• また、ファイアウォールとコア保護がオフになっているかどうかも確認してください。
• 次にスレーブサーバーのスレーブがオンになっているかどうかを確認します
• 2 つのスレーブ サーバーのサーバー ID は同じですか? これが、接続できるのが 1 つだけである理由です。
• master_log_file と master_log_pos の値は、マスタークエリの値と一致している必要があります。

3. show slave status で確認できる情報（より重要）

• IOスレッドステータス情報
• マスターサーバーの IP アドレス、ポート、トランザクション開始場所
• 最新のエラーメッセージやエラー箇所など

4. マスター スレーブ レプリケーションが遅い (遅延) 理由として考えられるものは何ですか?

• メイン サーバーが過負荷になり、複数のスリープ スレッドまたはゾンビ スレッドによって占有され、システムが過負荷になります。
• スレーブデータベースのハードウェアがマスターデータベースよりも劣っており、レプリケーションの遅延が発生する
• マスタースレーブレプリケーションはシングルスレッドです。マスターデータベースが同時に書き込みすぎると、スレーブデータベースに時間内に転送できず、遅延が発生します。
• 遅いSQL文が多すぎる
• ネットワーク遅延

上記は、MySQL データベースのマスタースレーブレプリケーションと読み取り/書き込み分離に関する集中チュートリアルの詳細な内容です。MySQL データベースのマスタースレーブレプリケーションと読み取り/書き込み分離の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。