MySQL マスタースレーブレプリケーションと読み取り書き込み分離の詳細な説明

序文

実際のエンタープライズ アプリケーションでは、成熟したビジネスでは通常、大量のデータが存在し、単一の MySQL サーバーでは、セキュリティ、高可用性、高同時実行性などの実際のニーズを満たすことができません。複数の MySQL サーバー (マスター スレーブ) にマスター スレーブ レプリケーションを展開してデータを同期し、読み取りと書き込みの分離によってデータベースの同時負荷容量を向上させることができます。これは rsync と多少似ていますが、違いは、rsync はディスク ファイルをバックアップするのに対し、MySQL マスター スレーブ レプリケーションはデータベース内のデータとステートメントをバックアップすることです。

1. 概要

マスタースレーブレプリケーション: マスターデータベース (マスター) は更新イベントをスレーブデータベース (スレーブ) に送信します。スレーブデータベースは更新レコードを読み取って実行し、スレーブデータベースの内容とマスターデータベースの一貫性を保ちます。

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

• ステートメントベースのレプリケーション (STATEMENT)。マスター データベースで実行される SQL ステートメントは、スレーブ データベースで同じステートメントを実行します。 MySQL はデフォルトでステートメントベースのレプリケーションを使用するため、より効率的です。
• 行ベースのレプリケーション (ROW)。スレーブ ライブラリでコマンドを実行する代わりに、変更されたコンテンツをコピーします。
• 混合型レプリケーション (MIXED)。デフォルトでは、ステートメント ベースのレプリケーションが使用されます。ステートメント ベースのレプリケーションを正確にレプリケートできないことが判明すると、行ベースのレプリケーションが使用されます。

(II) MySQLマスタースレーブレプリケーションの動作プロセス

1.マスターサーバーはレコードをバイナリログに保存します

• MySQLマスターデータベースに追加、削除、変更されたデータの更新は、独自のバイナリログ

2. スレーブサーバーはマスターサーバーのログをコピーします

• その後MySQLはI/Oスレッドはメインデータベースに接続し、メインデータベースを読み取ります。バイナリ ログはスレーブ サーバーのリレー ログにバックアップされます。マスターに追いついた場合は、スリープ状態になり、マスターが新しいイベントを生成するのを待機し、I/O スレッドがこれらのイベントをリレー ログに書き込みます。

3. スレーブサーバーはコピーされたログを再生する

• ライブラリから開くSQLスレッド、SQLスレッドはI/Oスレッドによって書き込まれたデータを読み取りますリレーログを実行し、その内容に従ってスレーブデータベースのデータを更新し、マスターデータベースのデータと整合をとる

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

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

（１）読み書き分離の概念

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

（２）読み書き分離の役割

• データベースの「書き込み」操作 (10,000 データの書き込みには 3 分かかる場合があります) には時間がかかるためです。しかし、データベースの「読み取り」（10,000 件のレコードの読み取りには 5 秒しかかからない場合があります）
• したがって、読み取りと書き込みの分離により、データベースへの書き込みがクエリの効率に影響を与えるという問題が解決されます。注: データベースは、必ずしも読み取りと書き込みで分離する必要はありません。プログラムがデータベースをより頻繁に使用しても、更新が少なく、クエリが多い場合は、使用が検討されます。データベースのマスター/スレーブ同期と読み取り/書き込み分離を使用することで、データベースの負荷を分散し、パフォーマンスを向上させることができます。

（３）MySQLの読み書き分離原則

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

(4) MySQLの一般的な読み書き分離

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

1. コード内の選択と挿入に応じてルートを分類します。この方法は、実稼働環境でも最も広く使用されています。

2. 利点と欠点:

• 利点は、プログラム コードに実装されており、ハードウェア費用のための追加機器が不要なため、パフォーマンスが向上することです。
• 欠点は、開発者が実装する必要があり、運用および保守担当者がどこから始めればよいかわからないことです。

3. すべてのアプリケーションが、プログラム コードで読み取りと書き込みの分離を実装するのに適しているわけではありません。たとえば、大規模で複雑な Java アプリケーションの場合、プログラム コードで読み取りと書き込みの分離を実装するには、大幅なコード変更が必要になります。

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

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

• MySQL-Proxy: MySQL-Proxy は、独自の Lua スクリプトを使用して SQL 判定を実行する MySQL オープンソース プロジェクトです。
• Atlas: Qihoo 360 の Web プラットフォーム部門のインフラストラクチャ チームによって開発および保守されている、MySQL プロトコルに基づくデータ中間層プロジェクトです。これは、mysql-proxy バージョン 0.8.2 に基づいており、最適化され、いくつかの新機能が追加されています。 360 は社内で MySQL ビジネスを実行するために Atlas を使用しており、毎日実行される読み取りおよび書き込み要求の数は数十億に達します。トランザクションとストアド プロシージャをサポートします。
• Amoeba: アリババで働いていたChen Siru氏によって開発されました。このプログラムは Java 言語で開発されており、Alibaba はそれを本番環境で使用します。ただし、トランザクションとストアド プロシージャはサポートされていません。
  

2. MySQL Proxy を使用するには、大量の Lua スクリプトを作成する必要があります。これらの Lua スクリプトは既成ではなく、自分で作成する必要があります。これは、MySQL Proxy の組み込み変数と MySQL プロトコルに精通していない人にとっては非常に困難です。

3.Amoeba は非常に使いやすく、移植性も高いソフトウェアであるため、実稼働環境ではデータベースのプロキシ層として広く使用されています。

3. MySQL マスタースレーブレプリケーションの実験的展開

必要な関連ソフトウェアパッケージ
amoeba-mysql-バイナリ-2.2.0.tar.gz
jdk-6u14-linux-x64.bin
mysql 圧縮パッケージ

（１）マスタースレーブ複製実験の手順と準備

実験手順 ステップ1: クライアントがプロキシサーバーアメーバにアクセスする
ステップ 2: プロキシ サーバーが読み取りと書き込みの判断を行います。書き込み操作: マスター サーバーに書き込みます。ステップ 3: マスター サーバーは、追加、削除、変更を独自のバイナリ ログに書き込みます。ステップ 4: スレーブ サーバーは、マスター サーバーのバイナリ ログを独自のリレー ログに同期します。ステップ 5: スレーブ サーバーは、リレー ログをデータベースに再生します。読み取り操作: スレーブ サーバーに直接アクセスします。最終結果: 負荷を軽減し、負荷分散の役割を果たします。ホスト オペレーティング システムの IP アドレス必要なツール/ソフトウェア/インストール パッケージ

1. ファイアウォールとセキュリティメカニズムをオフにする

4台のサーバーをすべてシャットダウンする必要があります

systemctl 停止 ファイアウォール
systemctl ファイアウォールを無効にする
強制0を設定する

2. 時刻同期サービスntpをインストールする

（１）プライマリサーバ

yum -y ntpをインストール

vim /etc/ntp.conf
##---------最後に追加---------
server 127.127.71.0 #ローカルクロックソースを設定し、ネットワークセグメントの変更に注意します fudge 127.127.71.0 stratum 8 #時間レベルを8に設定します（15に制限されます）

サービスntpd開始

（２）スレーブサーバー２台

yum -y インストール ntp ntpdate

サービスntpd開始

/usr/sbin/ntpdate 192.168.71.12 #マスターサーバーのIPを指す時間同期

crontab -e # 30 分ごとに時刻を同期するスケジュールタスクを設定します */30 * * * * /usr/sbin/ntpdate 192.168.71.12

3. メインサーバーのMySQL設定

vim /etc/my.cnf
サーバーID = 1
log-bin=master-bin #追加、マスターサーバーがバイナリログを開きます log-slave-updates=true #追加、スレーブサーバーがバイナリログを更新できるようにします systemctl restart mysqld

mysql -u ルート -p
grant replication slave on *.* to 'myslave'@'192.168.71.%' identified by '123'; #スレーブ サーバーに権限を昇格 #権限付与 #レプリケーション replication #ネットワーク セグメント 192.168.71 がパスワード 123 を使用してすべてのテーブルとライブラリをレプリケートできるようにします flush 権限;

マスターステータスを表示します。
#File列にはログ名が表示され、Fosition列にはオフセットが表示されます

4. サーバーからのMySQL設定

スレーブ1サーバー: 192.168.163.13
スレーブ2サーバー: 192.168.163.14

1) 設定ファイルを変更する

vim /etc/my.cnf
#変更、IDはマスターと異なり、2つのスレーブのIDも異なる必要があることに注意してください。server-id = 2

#追加、リレーログを開き、マスターサーバーからローカルのrelay-log=relay-log-binにログファイルを同期します

#リレーログファイルの場所と名前を追加定義します。relay-log-index=slave-relay-bin.index

systemctl で mysqld を再起動します。

奴隷1

奴隷2

2) サーバーからデータベースを操作する

mysql -u ルート -p123
マスターをmaster_host='192.168.71.12'、master_user='myslave'、master_password='123'、master_log_file='master-bin.000002'、master_log_pos=306に変更します。#マスターステータスを表示します。出力バイナリファイルは位置ポイントと同じです。#同期を構成します。master_log_fileとmaster_log_posの値はマスタークエリと一致している必要があることに注意してください。	 
start slave; #同期を開始し、エラーがある場合はreset slave;を実行します
show slave status\G; #スレーブ ステータスを確認します // IO スレッドと SQL スレッドの両方が Yes であり、同期が正常であることを示します。
Slave_IO_Running:Yes #ホストとのIO通信を担当します Slave_SQL_Running:Yes #独自のスレーブmysqlプロセスを担当します

5. 結果を確認する

マスターサーバーにライブラリを作成し、スレーブサーバーで表示する

4. MySQLの読み書き分離実験

アメーバサーバー (192.168.153.30)
Java 環境をインストールします。Amoeba は jdk1.5 に基づいて開発されているため、公式には jdk1.5 または 1.6 の使用が推奨されています。これより高いバージョンは推奨されません。

1) JDK Java環境を作成する

cd /opt/
#jdk-6u14-linux-x64.bin と amoeba-mysql-binary-2.2.0.tar.gz を /opt ディレクトリにアップロードします cp jdk-6u14-linux-x64.bin /usr/local/
/usr/local/ をコピーします
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
//はいを押してEnterを押します

mv jdk1.6.0_14/ /usr/1ocal/jdk1.6

vim /etc/プロファイル
JAVA_HOME=/usr/local/jdk1.6 をエクスポートします。
エクスポート CLASSPATH=SCLASSPATH:$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 をエクスポートします

ソース /etc/profile
java バージョン

2) 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 が表示されたらインストールは成功です

3) マスターサーバーとスレーブサーバーのmysqlで認証する

マスターサーバー: 192.168.71.12
スレーブ1サーバー: 192.168.71.13
スレーブ2サーバー: 192.168.71.14

まず、Amoebaがアクセスできるように、マスター、スレーブ1、スレーブ2のmysqlの権限を開きます。

'abc123' で識別される 'test'@'192.168.163.%' に *.* 上のすべての権限を許可します。 

4) AmoebaでAmoebaサービスを設定する

/usr/local/amoeba/conf/ をコピーします。

cp amoeba.xml amoeba.xml.bak # 設定ファイルをバックアップ vim amoeba.xml # amoeba 設定ファイルを変更する <property name="user">amoeba</property> # 行 30 を変更する <property name="password">abc123</property> # 行 32 で、amoeba サーバーに接続するためのクライアントのパスワードを変更する <property.name="defaultPool">master</property> # 行 115 で、デフォルト プールを変更する <property name="writePool">master</property> # 行 117 のコメントを削除し、master を書き込みプールとして設定する <property name="readPool">slaves</property> #slaves を読み取りプールとして設定する

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml # データベース構成ファイルを変更します<!-- <property name="schema"> test</property> --> # 行 23、目的をコメント アウトします。mysql にテスト ライブラリがない場合、エラーが報告されるため、デフォルトでテスト ライブラリを入力します<property name="user">test</property> # 行 26、マスター サーバーとスレーブ サーバーの権限昇格用のユーザーとパスワードを変更します<property.name-"password">123</property> # 行 28 ～ 30、コメントを削除します<dbServer name= "master" parent="abstractServer"> # 行 45、変更して、マスター サーバーの名前を Master に設定します

<property name= "ipAddress">192.168.71.12</property> #48 行目、変更、マスター サーバーのアドレスを設定します <dbServer name="slave1" parent="abstractServer"> #52 行目、変更、スレーブ サーバーの名前を設定します slave1

<property.name="ipAddress">192.168.71.13</property> #55 行目、変更、スレーブ サーバー 1 のアドレスを設定します <dbServer name="slave2 " parent="abstractserver"> #59 行目、上記の 6 行をコピーして貼り付け、スレーブ サーバー 2 の名前とアドレスを設定します slave2 <property, name="ipAddress">192.168.71.14</property>

<dbServer name="slaves" virtual="true"> #65 行目、<property name="poolNames">slave1,slave2</property> を変更します。 #71 行目、/usr/local/amoeba/bin/amoeba start& を変更します。 #Amoeba ソフトウェアを起動し、Ctrl + C を押して戻ります。 netstat -anpt | grep java #ポート 8066 が開いているかどうかを確認します。デフォルトのポートは TCP 8066 です。

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

クライアントサーバーでのテスト

yum install -y mysql mysql-server #mysql を素早くインストールするか、mysql をコンパイルしてインストールすることもできます -u amoeba -pabc123 -h 192.168.71.20 -P8066 

このテーブルはマスター サーバーとスレーブ サーバーの両方で確認できます。

Amoeba サーバー プロキシを介して MySQL にアクセスします。クライアントを介して MySQL に接続すると、書き込まれたデータはマスター サービスによってのみ記録され、その後スレーブ サーバーに同期されます。

stop slave; #データ使用schoolの同期書き込みを終了します。
//スレーブ1の場合:
test1 に値 ('1'、'slave1') を挿入します。

//スレーブ2の場合:
test1 に値 ('2'、'slave2') を挿入します。

プライマリサーバーにデータを挿入する

test1 に値 ('3'、'master') を挿入します。

クライアントのテーブルにデータを挿入する

//クライアントサーバー側:
学校を利用する;
select * from test1; //クライアントはそれぞれslave1とs1ave2からデータを読み取り、2つのスレーブサーバーに追加されたデータのみが表示され、マスターサーバーに追加されたデータは表示されません。 insert into test1 values('4','climet'); //このデータはマスターサーバーでのみ利用可能です。 

プライマリサーバー上のデータを表示する

2つのスレーブサーバーを同時に起動し、テーブルレコードを表示する

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