👧 🤸🏽 👨‍👨‍👦 MySQLレプリケーションの基本 🏇🏼 🐎 📒

私は比較的最近MySQLサーバーの複製に精通し、チューニングでさまざまな実験を行っていたので、自分がやったことを書き留めました。多くの資料が集められたとき、この記事を書くというアイデアが浮上しました。私が遭遇した最も基本的な問題のいくつかに関するアドバイスと解決策を集めようとしました。その過程で、ドキュメントやその他のソースへのリンクを提供します。完全な説明を主張することはできませんが、この記事が役立つことを願っています。

小さな紹介

レプリケーション（lat。Replico-repeatから）は、1つ以上の依存サーバー上のメインデータベースサーバーからのデータ変更のレプリケーションです。メインサーバーをマスターと呼び、依存サーバーをレプリカと呼びます。

ウィザードで発生したデータ変更はレプリカで繰り返されます（逆の場合も同様です）。したがって、データ変更要求（INSERT、UPDATE、DELETEなど）はウィザードでのみ実行され、データ読み取り要求（つまり、SELECT）はレプリカとウィザードの両方で実行できます。レプリカの1つでのレプリケーションプロセスは、他のレプリカの作業には影響せず、ウィザードの作業には実質的に影響しません。

レプリケーションは、マスターが保持するバイナリログを使用して実行されます。これらは、データベースへの変更を導く（または潜在的に導く）すべてのクエリを保存します（クエリは明示的に保存されないため、表示したい場合はmysqlbinlogユーティリティを使用する必要があります）。 Binlogはレプリカに送信され（ウィザードからダウンロードされたBinlogは「リレーbinlog 」と呼ばれます）、保存されたリクエストは特定の位置から実行されます。レプリケーション中は、変更されたデータ自体ではなく、変更を引き起こすリクエストのみが送信されることを理解することが重要です。

複製中、データベースの内容は複数のサーバーに複製されます。なぜ複製に頼る必要があるのですか？いくつかの理由があります。

パフォーマンスとスケーラビリティ 。 1つのサーバーが、データベースの同時読み取りおよび書き込み操作によって引き起こされる負荷に対処できない場合があります。レプリカを作成する利点は、システムの書き込み操作ごとの読み取り操作が多いほど大きくなります。
耐障害性 。レプリカに障害が発生した場合、すべての読み取り要求をウィザードに安全に転送できます。マスターに障害が発生した場合、書き込み要求をレプリカに転送できます（マスターが復元された後、レプリカの役割を引き受けることができます）。
データバックアップ 。レプリカはしばらくの間「スローダウン」してmysqldumpを実行できますが、マスターはできません。
遅延計算 。システム全体の通常の動作を妨げることを恐れずに、重い低速のSQLクエリを別のレプリカで実行できます。

さらに、他にも興味深い機能がいくつかあります。データ自体はレプリカに転送されず、それらを変更するクエリが転送されるため、マスターとレプリカで異なるテーブル構造を使用できます。特に、テーブル（エンジン）またはインデックスのセットのタイプは異なる場合があります。たとえば、フルテキスト検索を実行するには、ウィザードがInnoDBを使用するという事実にもかかわらず、レプリカでMyISAMテーブルタイプを使用できます。

レプリケーションのセットアップ

すでにデータで満たされ、作業に含まれている作業用MySQLデータベースがあるとします。また、上記の理由の1つにより、サーバーのレプリケーションを有効にします。生データ：

ウィザードのIPアドレスは192.168.1.101、レプリカは192.168.1.102です。
MySQLがインストールおよび構成されている
testdbデータベースの複製が必要
しばらくウィザードを一時停止できます
もちろん、両方のマシンにルートがあります

ウィザード設定

[mysqld]セクションで、一意のサーバーID、バイナリログのパス、およびレプリケーション用のデータベースの名前を必ず指定してください。

  server-id = 1 
      

        
        
        
      

     log-bin = /var/lib/mysql/mysql-bin 
      

        
        
        
      

     replicate-do-db = testdb

バイナリログ用の十分なディスク領域があることを確認してください。

複製が実行される権限を持つ複製ユーザーを追加します。「レプリケーションスレーブ」権限で十分です。

mysql@master> GRANT replication slave ON "testdb".* TO "replication"@"192.168.1.102" IDENTIFIED BY "password";

設定の変更を有効にするためにMySQLを再起動します。

root@master# service mysqld restart

すべてがうまくいった場合、 show master statusコマンドは次のように表示されます。

mysql@master> SHOW MASTER STATUS\G 
      

        
        
        
      

     File: mysql-bin.000003 
      

        
        
        
      

     Position: 98 
      

        
        
        
      

     Binlog_Do_DB: 
      

        
        
        
      

     Binlog_Ignore_DB:

ウィザードでデータベースに変更が加えられると、位置の値が増加するはずです。

レプリカ設定

構成の[mysqld]セクションでサーバーID、レプリケーション用のデータベース名、およびリレーbinlogへのパスを指定して、MySQLを再起動します。

  server-id = 2 
      

        
        
        
      

     relay-log = /var/lib/mysql/mysql-relay-bin 
      

        
        
        
      

     relay-log-index = /var/lib/mysql/mysql-relay-bin.index 
      

        
        
        
      

     replicate-do-db = testdb 
      

        
        
        
      

     
      

        
        
        
      

     root@replica# service mysqld restart

データを転送する

ここでは、記録のためにデータベースをロックする必要があります。これを行うには、アプリケーションを停止するか、ウィザードでread_onlyフラグを使用します（注：このフラグは、SUPER特権を持つユーザーに対しては機能しません）。 MyISAMテーブルがある場合、「フラッシュテーブル」も作成します。

mysql@master> FLUSH TABLES WITH READ LOCK; 
      

        
        
        
      

     mysql@master> SET GLOBAL read_only = ON;

「show master status」コマンドを使用してウィザードの状態を確認し、FileとPositionの値を覚えてみましょう（ウィザードが正常にロックされた後、これらは変更されません）。

File: mysql-bin.000003 
      

        
        
        
      

     Position: 98 
      

        
        
        
      

     
      

        
        
        
      

       ,       :

mysql@master> SET GLOBAL read_only = OFF; 
      

        
        
        
      

     
      

        
        
        
      

             .

最後に、「 change master to 」および「 start slave 」コマンドでレプリケーションを開始し、すべてがうまくいったかどうかを確認します。

mysql@replica> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000003 ", MASTER_LOG_POS = 98; 
      

        
        
        
      

     mysql@replica> start slave;

ウィザードから取得する値MASTER_LOG_FILEおよびMASTER_LOG_POS。

「 show slave status 」コマンドでレプリケーションがどのように行われるかを見てみましょう。

mysql@replica> SHOW SLAVE STATUS\G 
      

        
        
        
      

     Slave_IO_State: Waiting for master to send event 
      

        
        
        
      

     Master_Host: 192.168.1.101 
      

        
        
        
      

     Master_User: replication 
      

        
        
        
      

     Master_Port: 3306 
      

        
        
        
      

     Connect_Retry: 60 
      

        
        
        
      

     Master_Log_File: mysql-bin.000003 
      

        
        
        
      

     Read_Master_Log_Pos: 98 
      

        
        
        
      

     Relay_Log_File: mysql-relay-bin.001152 
      

        
        
        
      

     Relay_Log_Pos: 235 
      

        
        
        
      

     Relay_Master_Log_File: mysql-bin.000003 
      

        
        
        
      

     Slave_IO_Running: Yes 
      

        
        
        
      

     Slave_SQL_Running: Yes 
      

        
        
        
      

     Replicate_Do_DB: testdb,testdb 
      

        
        
        
      

     Replicate_Ignore_DB: 
      

        
        
        
      

     Replicate_Do_Table: 
      

        
        
        
      

     Replicate_Ignore_Table: 
      

        
        
        
      

     Replicate_Wild_Do_Table: 
      

        
        
        
      

     Replicate_Wild_Ignore_Table: 
      

        
        
        
      

     Last_Errno: 0 
      

        
        
        
      

     Last_Error: 
      

        
        
        
      

     Skip_Counter: 0 
      

        
        
        
      

     Exec_Master_Log_Pos: 98 
      

        
        
        
      

     Relay_Log_Space: 235 
      

        
        
        
      

     Until_Condition: None 
      

        
        
        
      

     Until_Log_File: 
      

        
        
        
      

     Until_Log_Pos: 0 
      

        
        
        
      

     Master_SSL_Allowed: No 
      

        
        
        
      

     Master_SSL_CA_File: 
      

        
        
        
      

     Master_SSL_CA_Path: 
      

        
        
        
      

     Master_SSL_Cert: 
      

        
        
        
      

     Master_SSL_Cipher: 
      

        
        
        
      

     Master_SSL_Key: 
      

        
        
        
      

     Seconds_Behind_Master: 5

今私が強調した最も興味深い値。レプリケーションが正常に開始されると、それらの値はリストとほぼ同じになります（ドキュメントの「 show slave status 」コマンドの説明を参照）。 Seconds_Behind_Masterの値は任意の整数です。

レプリケーションが正常に進行している場合、レプリカはマスターに従います（Master_Log_Fileのログ番号とExec_Master_Log_Posの位置が増加します）。マスター（Seconds_Behind_Master）からのレプリカの遅延時間は、理想的にはゼロである必要があります。縮小または拡大しない場合、レプリカの負荷が高すぎる可能性があります。ウィザードで発生する変更を繰り返す時間がありません。

Slave_IO_Stateが空でSeconds_Behind_MasterがNULLの場合、レプリケーションは開始されていません。 MySQLログで理由を確認し、修正して、レプリケーションを再開します。

mysql @ replica> start slave;

これらの簡単なアクションにより、マスター上のデータと同一のデータを持つレプリカを取得します。

ところで、ウィザードのロック時間は、ダンプを作成する時間です。許容できないほど長く作成された場合、これを行うことができます：

read_onlyフラグを使用してマスターへの書き込みをブロックし、位置を記憶してMySQLを停止します。
その後、データベースファイルをレプリカにコピーし、ウィザードを有効にします。
通常の方法でレプリケーションを開始します。

ウィザードをまったく停止せずにレプリカを作成する方法はいくつかありますが、常に機能するとは限りません。

レプリカを追加する

既に作業中のマスターとレプリカがあり、それらに別のマスターを追加する必要があるとします。これは、最初のレプリカをウィザードに追加するよりも簡単です。そして、もっと良いのは、このためにマスターを停止する必要がないことです。

最初に、2番目のレプリカでMySQLを構成し、構成で必要なパラメーターを作成したことを確認します。

server-id = 3 
      

        
        
        
      

     replicate-do-db = testdb

次に、最初のレプリカでレプリケーションを停止します。

mysql@replica-1> stop slave;

レプリカは引き続き正常に機能しますが、レプリカ上のデータは関係なくなります。ステータスを見て、複製を停止する前にレプリカが到達したウィザードの位置を覚えてみましょう。

mysql@replica-1> SHOW SLAVE STATUS\G

Master_Log_FileとExec_Master_Log_Posの値が必要です。

Master_Log_File: mysql-bin.000004 
      

        
        
        
      

     Exec_Master_Log_Pos: 155

データベースダンプを作成し、最初のレプリカでレプリケーションを続行します。

mysql@replica-1> START SLAVE;

2番目のレプリカのダンプからデータを回復します。次に、レプリケーションを有効にします。

mysql@replica-2> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000004 ", MASTER_LOG_POS = 155; 
      

        
        
        
      

     mysql@replica-2> START SLAVE;

値MASTER_LOG_FILEおよびMASTER_LOG_POSは、最初のレプリカでのshow slave statusコマンドの結果からのMaster_Log_FileおよびExec_Master_Log_Posの値です。

複製は、最初のレプリカが停止された位置から開始する必要があります（したがって、ダンプが作成されました）。したがって、同じデータを持つ2つのレプリカがあります。

レプリカのマージ

この状況が発生する場合があります。マスターには2つのデータベースがあり、一方は一方のレプリカに複製され、もう一方はもう一方のレプリカに複製されます。マスターでダンプを作成せず、作業からシャットダウンせずに、両方のレプリカで2つのデータベースのレプリケーションを構成する方法「 start slave until 」コマンドを使用して十分に簡単です。

したがって、masterにはtestdb1およびtestdb2データベースがあり、これらはそれぞれreplica-1および replica-2レプリカに複製されます。ウィザードを停止せずに、両方のデータベースのレプリカ1へのレプリケーションを構成します。

コマンドを使用してレプリカ2のレプリケーションを停止し、ウィザードの位置を記憶します。

mysql@replica-2> STOP SLAVE; 
      

        
        
        
      

     mysql@replica-2> SHOW SLAVE STATUS\G 
      

        
        
        
      

     Master_Log_File: mysql-bin.000015 
      

        
        
        
      

     Exec_Master_Log_Pos: 231

testdb2データベースのダンプを作成して、レプリケーションを再開しましょう（これでレプリカ2の操作は終了しました）。ダンプをreplica-1に復元します。

replica-1の状況は次のとおりです。testdb1データベースはマスターの1つの位置にあり、複製を続行し、testdb2データベースは別の位置のダンプから復元されます。それらを同期します。

複製を停止し、ウィザードの位置を覚えておいてください。

mysql@replica-1> STOP SLAVE; 
      

        
        
        
      

     mysql@replica-1> SHOW SLAVE STATUS\G 
      

        
        
        
      

     Master_Log_File: mysql-bin.000016

Exec_Master_Log_Pos：501

[mysqld]セクションのreplica-1構成に2番目のデータベースの名前が含まれていることを確認します。

replicate-do-db = testdb2

設定の変更を有効にするためにMySQLを再起動します。ちなみに、レプリケーションを停止せずにMySQLを再起動することもできます。ログから、ウィザードのレプリケーションのどの位置で停止したかがわかります。

次に、レプリカ2が中断された位置からレプリケーションを停止した位置にレプリケートします。

mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000015 ", MASTER_LOG_POS = 231; 
      

        
        
        
      

     mysql@replica-1> start slave until MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;

複製は、レプリカがuntilセクションの指定された位置に達するとすぐに終了します。その後、両方のデータベースがウィザードの同じ位置（レプリカ1で複製を停止した場所）に対応します。これを確認してください：

mysql@replica-1> SHOW SLAVE STATUS\G 
      

        
        
        
      

     mysql@replica-1> START SLAVE; 
      

        
        
        
      

     Master_Log_File: mysql-bin.000016 
      

        
        
        
      

     Exec_Master_Log_Pos: 501 
      

        
        
        
      

     
      

        
        
        
      

         replica-1   [mysqld]   :

replicate-do-db = testdb1 
      

        
        
        
      

     replicate-do-db = testdb2 
      

        
        
        
      

     
      

        
        
        
      

     :        .

MySQLを再起動してレプリケーションを続行します。

mysql@replica-1> CHANGE MASTER TO MASTER_HOST = "192.168.1.101 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000016 ", MASTER_LOG_POS = 501;

マスターreplica-1が追いついた後、データベースの内容は同じになります。同様の方法で、またはreplica-1の完全なダンプを作成して、replica-2上のデータベースをマージできます。

キャスリングマスターとレプリカ

たとえば、ウィザードが失敗した場合や技術的な作業中に、レプリカをウィザードモードに切り替える必要がある場合があります。このようなスイッチを有効にするには、レプリカをマスターのように構成するか、それをパッシブマスターにする必要があります。

[mysqld]セクションの設定で（リレーbinlogに加えて）バイナリログを有効にします。

log-bin = /var/lib/mysql/mysql-bin

そして、レプリケーション用のユーザーを追加します。

mysql@master> GRANT replication slave ON 'testdb'.* TO 'replication'@'192.168.1.101′ IDENTIFIED BY "password ";

パッシブウィザードは通常のレプリカのように複製しますが、さらにバイナリロジックを作成します。つまり、そこから複製を開始できます。これをコマンド「 show master status 」で確認します。

mysql@replica> SHOW MASTER STATUS\G 
      

        
        
        
      

     File: mysql-bin.000001 
      

        
        
        
      

     Position: 61 
      

        
        
        
      

     Binlog_Do_DB: 
      

        
        
        
      

     Binlog_Ignore_DB:

ここで、パッシブマスターをアクティブモードにするには、そのマスターでレプリケーションを停止し、以前のアクティブマスターでレプリケーションを有効にする必要があります。切り替え時にデータが失われないようにするには、 アクティブマスターを書き込み用にロックする必要があります。

mysql@master> FLUSH TABLES WITH READ LOCK 
      

        
        
        
      

     mysql@master> SET GLOBAL read_only = ON; 
      

        
        
        
      

     mysql@replica> STOP SLAVE; 
      

        
        
        
      

     mysql@replica> SHOW MASTER STATUS; 
      

        
        
        
      

     File: mysql-bin.000001 
      

        
        
        
      

     Position: 61 
      

        
        
        
      

     mysql@master> CHANGE MASTER TO MASTER_HOST = "192.168.1.102 ", MASTER_USER = "replication ", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "mysql-bin.000001 ", MASTER_LOG_POS = 61; 
      

        
        
        
      

     mysql@master> start slave;

以上で、アクティブマスターを変更しました。元のマスターからロックを削除できます。

おわりに

MySQLでレプリケーションを構成し、いくつかの基本的な操作を実行する方法を少し理解しました。残念ながら、次の重要な質問は記事の範囲外でした。

単一障害点（SPF、単一障害点）の除去。単一のMySQLサーバーを使用する場合、その障害はシステム全体の障害につながりました。複数のサーバーを使用している場合、それらのいずれかに障害が発生すると、特に対処しない限り、システム障害が発生します。マスターとレプリカに障害が発生した状況を処理する必要があります。ただし、既存のツールの1つであるMMMは、ファイルでファイナライズする必要があります。
負荷分散。複数のレプリカを使用する場合、特にレプリカのパフォーマンスが同じではない場合、透過的なバランス調整メカニズムを使用すると便利です。 Linuxでは、標準ソリューションであるLVSを使用できます。
アプリケーションのロジックを変更します。理想的な状況では、データの読み取り要求はレプリカに送信され、変更要求はマスターに送信されます。ただし、レプリカの遅延の可能性があるため、このようなスキームは多くの場合機能しないため、ウィザードで実行する必要がある読み取り要求を識別する必要があります。

今後の記事でこれらの問題を取り上げたいと思います。

ご清聴ありがとうございました！

MySQLレプリケーションの基本