MySQL レプリケーションの原理と実際のアプリケーションの詳細な説明

この記事では、例を使用して、MySQL レプリケーションの原理と実際のアプリケーションについて説明します。ご参考までに、詳細は以下の通りです。

レプリケーション機能は、MySQL データベース上のデータを 1 つ以上の MySQL スレーブ データベースにコピーすることです。

レプリケーションの仕組み: マスター サーバーで実行されたすべての DDL および DML ステートメントはバイナリ ログに記録され、マスター サーバーに接続されたスレーブ サーバーによって取得され、スレーブにコピーされ、リレー ログとして保存されます。

このプロセスは、IO スレッドと呼ばれるスレッドによって処理され、SQL スレッドと呼ばれる別のスレッドがリレー ログ内のステートメントを順番に実行します。

レプリケーション トポロジにはいくつかの種類があります。

1. 従来のレプリケーション、1 つのマスターと複数のスレーブ、1 つのマスター サーバーと複数のスレーブ サーバー。

2. チェーン レプリケーション: 1 つのサーバーがマスター データベースからコピーし、別のサーバーがこのサーバーからコピーします。中間サーバーはリレー マスター データベースとも呼ばれます。

3. マスター間レプリケーションでは、2 つのマスター ライブラリが相互に書き込みとレプリケーションを受け入れます。

4. マルチソースレプリケーション: 1 つのスレーブ データベースが複数のマスター データベースから複製されます。

1. コピー方法

1. マスターでバイナリ ログを有効にします。

2. マスター データベースにレプリケーション ユーザーを作成します。

3. スレーブに一意の server_id を設定します。

4. メインデータベースからデータをバックアップします。

5. マスター データベースにバックアップされたデータをスレーブ データベースに復元します。

6. CHANGE MASTER TO コマンドを実行します。

7. コピーを開始します。

2. 具体的な手順は以下のとおりです。

1. マスター データベースでバイナリ ログを有効にし、server_id を設定します。

#server_id を設定する
サーバーID = 1
#binlogを開く log-bin = mysql-bin

2. マスターデータベースにレプリケーションユーザーを作成する

'password' で識別されるユーザー 'username'@'%' を作成します。
*.* のレプリケーションスレーブを 'username'@'%' に付与します。

3.スレーブにserver_idを設定する

#server_id を設定する
サーバーID = 10

4. メインデータベースのデータをバックアップする

mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > エクスポートパス

5. マスターデータベースからエクスポートしたデータをスレーブデータベースに復元する

mysql -u root -p -f <メインデータベースのバックアップファイル.sql

6.スレーブでCHANGE MASTER TOコマンドを実行する

マスターを変更 
MASTER_HOST='マスターライブラリIP',
MASTER_USER = 'マスターデータベースレプリケーションユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE='バイナリログ名',
MASTER_LOG_POS=バイナリログの位置;

バイナリ ログ名とバイナリ ログの位置は、次のように、バックアップ マスター ライブラリ ファイルに既に含まれています。

MASTER を MASTER_LOG_FILE='mysql-bin.000016'、MASTER_LOG_POS=47845 に変更します。

7. スレーブ ライブラリで start slave を実行し、show slave status\G; を実行してレプリケーション ステータスを表示します。

3. マスター-マスターレプリケーションの設定

マスター ライブラリが master1 と master2 であると仮定します。

1. マスター2を読み取り専用に設定する

@@global.read_only をオンに設定します。

2. master2 にレプリケーション ユーザーを作成します。存在する場合は、作成する必要はありません。

'password' で識別されるユーザー 'username'@'%' を作成します。
*.* のレプリケーションスレーブを 'username'@'%' に付与します。

3. マスター2でバイナリログがオンになっていることを確認し、マスター2のバイナリログの座標を確認します。

マスターステータスを表示します。

4. 手順2の情報に従って、master1でCHANGE MASTER TOコマンドを実行します。

マスターを変更 
MASTER_HOST='MASTER2 ホスト IP',
MASTER_USER = 'MASTER2 レプリケーション ユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE='バイナリログ名',
MASTER_LOG_POS=バイナリログの位置;

5. マスター1でスレーブモードを有効にする

スレーブを起動します。

6. マスター2を読み取りと書き込みに設定する

@@global.read_only をオフに設定します。

4. マルチソースレプリケーションの設定

server3 を server1 および server2 のスレーブ ライブラリとして設定します。

1. server1 と server2 のバイナリログと server_id を設定します。具体的な操作については上記を参照してください。

2. server1 と server2 にレプリケーション ユーザーを作成します。具体的な操作については、上記を参照してください。

3. server3 に server_id を設定します。

4. server1 と server2 のデータをバックアップします。

5. server1 と server2 にバックアップされたデータを server3 に復元します。

6. server3 で、レプリケーション リポジトリを FILE から TABLE に変更します。

奴隷を停止します。
グローバルmaster_info_repositoryを'TABLE'に設定します。
グローバルrelay_log_info_repositoryを'TABLE'に設定します。

設定ファイルも変更する必要があります。

[mysqld]
マスター情報リポジトリ = テーブル
リレーログ情報リポジトリ = テーブル

7. server3でCHANGE MASTER TOコマンドを実行し、チャネルに名前を付けます。

マスターを変更 
MASTER_HOST='server1 ホスト IP',
MASTER_USER = 'server1 レプリ​​ケーション ユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE='server1 バイナリログ名',
MASTER_LOG_POS=チャネル 'server1' の server1 バイナリ ログ位置;

マスターを変更 
MASTER_HOST='server2 ホスト IP',
MASTER_USER = 'server2 レプリケーション ユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE='server2 バイナリログ名',
MASTER_LOG_POS=チャネル 'server2' の server2 バイナリ ログ位置;

8. server3 で、各チャネルに対して START SLAVE FOR CHANNEL ステートメントを実行します。

チャネル 'server1' のスレーブを起動します。
チャネル 'server2' のスレーブを起動します。

9. 同期ステータスを確認し、スレーブステータスを表示します\G;

指定されたチャネルのスレーブ ステータスを取得するには、チャネル 'チャネル名'\G のスレーブ ステータスを表示します。

5. レプリケーションフィルターを設定する

複製するテーブルまたはデータベースを選択できます。マスターでは、バイナリ ログを制御するために、--binlog-do-db および --binlog-ignore-db オプションを使用して、変更を記録するデータベースを選択できます。より良いアプローチは、スレーブ ライブラリを制御することです。

1. 指定されたデータベースをコピーする

レプリケーション フィルターの変更 REPLICATE_DO_DB = (db1, db2);

2. 指定されたテーブルをコピーする

レプリケーション フィルターの変更 REPLICATE_DO_TABLE = ('db1.table1');

3. ワイルドカードを使用してテーブルを選択する場合

レプリケーション フィルターの変更 REPLICATE_WILD_DO_TABLE = ('db1.tb_%');

4. データベースを無視する

レプリケーション フィルターの変更 REPLICATE_IGNORE_DB = (db1, db2);

5. 指定されたテーブルを無視する

レプリケーション フィルターの変更 REPLICATE_IGNORE_TABLE = ('db1.table1');

6. スレーブライブラリをマスタースレーブレプリケーションからチェーンレプリケーションに切り替える

たとえば、現在、サーバー A はマスター データベース、サーバー B とサーバー C はスレーブ データベースであり、これらはサーバー A に複製されています。ここで、サーバー C をサーバー B のスレーブ ライブラリとして使用します。

1. サーバーCのスレーブライブラリを停止する

奴隷を停止します。
スレーブステータスを表示\G;

Relay_Master_Log_FileとExec_Master_Log_Posの値を記録する

2. サーバーBのスレーブデータベースを停止する

奴隷を停止します。
スレーブステータスを表示\G;

Relay_Master_Log_FileとExec_Master_Log_Posの値を記録する

3. サーバー B のログの位置とサーバー C のログの位置を比較して、どちらがサーバー A の最新の同期であるかを確認します。通常、サーバー C は最初にライブラリからの実行を停止し、サーバー B のログの方が早くなります。

4. サーバー C で、START SLAVE UNTIL ステートメントを使用して、サーバー B のログの位置に同期します。

START SLAVE UNTIL MASTER_LOG_FILE='前のステップのサーバー B ログの名前'、MASTER_LOG_POS=前のステップのサーバー B ログの位置。

5. サーバー C で、show slave status を確認します。Exec_Master_Log_Pos と Until_Log_Pos は同じである必要があります。

6. サーバー B で、マスター データベースの状態を確認し、スレーブ データベースを起動します。

マスターステータスを表示します。
スレーブを起動します。
スレーブステータスを表示\G;

7. サーバー C でスレーブ データベースを停止し、CHANGE MASTER TO コマンドを実行します。

奴隷を停止します。
マスターを変更
MASTER_HOST='サーバーBのIP'、
MASTER_USER = 'サーバーBのレプリケーションユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE='前の手順で show master status からログ名を取得します',
MASTER_LOG_POS=ログの位置は、前の手順で show master status によって取得されました。

8. サーバーCでレプリケーションを開始し、ステータスを確認します。

スレーブを起動します。
スレーブステータスを表示\G;

7. チェーンレプリケーションをマスタースレーブレプリケーションに切り替える

サーバー A -> サーバー B -> サーバー C。サーバー C をサーバー A のスレーブとして直接使用したい場合は、どうすればよいでしょうか。

1. サーバー B でスレーブ データベースを停止し、マスター データベースのステータスを記録します。

奴隷を停止します。
マスターステータスを表示\G;

2. サーバー C で、スレーブの遅延が追いついていることを確認します。Relay_Master_Log_File と Exec_Master_Log_Pos は、サーバー B のマスター ステータスと同じである必要があります。

遅延が追いつくと、スレーブ データベースは停止されます。

奴隷を停止します。

3. サーバーBで、show slave statusからサーバーAのログ座標値（Relay_Master_Log_FileとExec_Master_Log_Pos）を取得し、スレーブを起動します。

スレーブステータスを表示\G;
スレーブを起動します。

4. サーバー C でスレーブ データベースを停止し、CHANGE MASTER TO コマンドを実行してサーバー A を指定します。

奴隷を停止します。
マスターを変更 
MASTER_HOST='サーバーAのIP'、
MASTER_USER = 'サーバーAのレプリケーションユーザー'、
MASTER_PASSWORD='パスワード',
MASTER_LOG_FILE = '前のステップで取得したログ'、
MASTER_LOG_POS=前の手順で取得したログ位置。

5. サーバー C でスレーブ データベースを起動し、そのステータスを確認します。

スレーブを起動します。
スレーブステータスを表示\G;

8. 遅延レプリケーションを設定する

なぜ遅延レプリケーションが必要なのでしょうか? プライマリ データベースで壊滅的なステートメントが実行され、バックアップの時点から回復する必要がある場合があります。データベースのサイズが大きすぎると、ダウンタイムが長くなります。

この状況を回避するには、遅延スレーブを使用します。災害が発生し、遅延スレーブがまだ壊滅的なステートメントを実行していない場合は、最初にレプリケーションを停止し、スレーブが災害ステートメントをスキップできるようにして、最後にスレーブをマスターに昇格させることができます。

1. 図書館から逃げるのはやめましょう

奴隷を停止します。

2. 遅延時間を秒単位で設定します

MASTERをMASTER_DELAY = 3600に変更します。
スレーブを起動します。

3.スレーブステータスを確認する

スレーブステータスを表示\G;

SQL_Delay: スレーブ データベースがマスター データベースから遅延される秒数。

SQL_Remaining_Delay: 遅延の残り秒数。遅延が維持される場合、この値は NULL になります。

Slave_SQL_Running_State: SQLスレッドの状態

9. GTIDレプリケーションの設定

グローバル トランザクション識別子 GTID は、プログラム内で作成され、マスターで送信される各トランザクションに関連付けられる一意の識別子です。この識別子は、マスター データベースだけでなく、他のスレーブ データベースでも一意です。

上で説明したレプリケーションはすべて、バイナリ ファイルの場所とレプリケーションの開始点を指定する必要があります。スレーブ ライブラリのマスターを別のスレーブ ライブラリに切り替えると、バイナリ ファイルの場所を再度取得する必要があり、面倒になります。

これを回避するには、GTID ベースのレプリケーションを使用できます。MySQL は GTID を使用してバイナリ ログの位置を自動的に検出します。

1. すべてのデータベースのmy.cnfでGTIDを有効にする

[mysqld]
gtid_mode = オン
強制GTID一貫性 = 1
スレーブ開始をスキップ

2. マスター データベースを読み取り専用に設定して、マスター データベースのデータとスレーブ データベースのデータが一致するようにします。

@@global.read_only をオンに設定します。

3. GTID を有効にするには、すべてのスレーブ ライブラリを再起動します。

4. メインライブラリを再起動します。

5.スレーブでCHANGE MASTER TOコマンドを実行してGTIDレプリケーションを設定する

マスターを変更
MASTER_HOST='マスターライブラリIP',
マスターポート=3306、
MASTER_USER = 'ユーザーをコピー'、
MASTER_PASSWORD='パスワード',
マスター自動位置=1;

6. すべてのスレーブ ライブラリで start slave を実行し、ステータスを確認します。

10. 準同期レプリケーションの設定

デフォルトでは、レプリケーションは非同期であり、マスターは書き込み操作がスレーブに到達したかどうかを認識しません。マスターとスレーブの間に遅延が発生すると、マスターがクラッシュし、スレーブにまだ到達していないデータが失われます。

この問題を解決するために、半同期レプリケーションでは、マスター ライブラリは少なくとも 1 つのスレーブ ライブラリが書き込まれたデータを受信するまで待機します。

1. マスターデータベースにrpl_semi_sync_masterプラグインをインストールする

プラグイン rpl_semi_sync_master SONAME 'semisync_master.so' をインストールします。

Windowsの場合は以下を使用してください。

プラグイン rpl_semi_sync_master SONAME 'semisync_master.dll' をインストールします。

2. プラグインが有効化されていることを確認する

information_schema.plugins から plugin_name、plugin_status を選択します (plugin_name が '%semi%' のような場合)。

3. 半同期レプリケーションを有効にし、タイムアウトを調整する

@@global.rpl_semi_sync_master_enabled=1 を設定します。
@@global.rpl_semi_sync_master_timeout=100 を設定します。

4. スレーブライブラリにrpl_semi_sync_slaveプラグインをインストールする

プラグイン rpl_semi_sync_slave SONAME 'semisync_slave.so' をインストールします。

Windowsの場合は以下を使用してください。

プラグイン rpl_semi_sync_slave SONAME 'semisync_slave.dll' をインストールします。

5. プラグインが有効化されていることを確認する

information_schema.plugins から plugin_name、plugin_status を選択します (plugin_name が '%semi%' のような場合)。

6. スレーブライブラリで、準同期レプリケーションを有効にし、スレーブライブラリのIOスレッドを再起動します。

グローバルrpl_semi_sync_slave_enabld = 1を設定します。
スレーブIO_THREADを停止します。
スレーブ IO_THREAD を開始します。

7. 以下の方法で半同期ステータスを確認します。

'rpl_semi_sync_master_clients' のようなステータスを表示します。

半同期モードでマスターデータベースに接続されているクライアントの数を表示します。

'rpl_semi_sync_master_status' のようなステータスを表示します。

マスター データベースは、非同期レプリケーションと半同期レプリケーションを切り替えます。オンは半同期レプリケーションを意味し、オフは非同期レプリケーションを意味します。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキル」、および「MySQL データベース ロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。