MySQL〜レプリケーション〜

今回はMySQLの機能の１つであるレプリケーションについて、紹介します。
レプリケーションは、データベースの内容を複数のサーバーに複製する機能です。

MySQLの概要については、以下の記事で紹介していますので、ご参考ください。

MySQLを理解する〜概要〜
MySQLは、世界で最も広く使用されているオープンソースのリレーショナルデータベース管理システム(RDBMS)の1つです。この記事では、MySQLの概要について調べた内容を共有します。特徴オープンソース: GNU General Publi...
infnotes.com
2024.09.15

レプリケーションの概要

レプリケーションは、データベースの内容を複数のサーバーに複製する機能です。
主な特徴は以下の通りです。

  • 1台のソース(マスター)サーバと1台以上のレプリカ(スレーブ)サーバで構成される
  • ソースサーバでデータ更新が行われ、その内容がレプリカサーバに複製される
  • 標準機能のため、追加のミドルウェアなしで利用可能

レプリケーションの目的

MySQLのレプリケーションの主な目的は以下の通りです。

高可用性の実現

  • ソース(マスター)サーバに障害が発生した場合、レプリカ(スレーブ)サーバに切り替えることでサービスを継続できます。
  • 障害時の冗長化により、システムの信頼性が向上します。

負荷分散

  • 読み取りクエリをレプリカサーバに振り分けることで、ソースサーバの負荷を軽減し、全体的なパフォーマンスを向上させます。
  • 特に読み取りアクセスが多いデータ集計などの処理にレプリカを利用することで効果的です。

バックアップの取得

  • レプリカサーバーでバックアップを取得することで、ソースサーバの負荷を増やすことなくバックアップを行えます。

データ分析環境の構築

  • 運用環境とは別にレプリカサーバを用意し、そこでデータ分析を行うことができます。
  • 重いレポート生成処理などをレプリカで実行することで、本番環境への影響を最小限に抑えられます。

地理的な冗長性

  • 物理的に離れた場所にレプリカサーバを設置することで、災害対策としても機能します。

スケールアウト

  • 読み取り処理を複数のレプリカサーバに分散させることで、システム全体のスケーラビリティを向上させます。

サーバーの役割分担

  • 更新用と参照用でサーバを分けることで、それぞれの用途に最適化した設定や運用が可能になります。

メンテナンス時の可用性確保

  • ソースサーバのメンテナンス時にレプリカに切り替えることで、ダウンタイムを最小限に抑えられます。

    レプリケーションの種類

    種類説明
    非同期レプリケーションデフォルトの方式
    ソース(マスター)サーバはレプリカ(スレーブ)への反映を待たずにクライアントに応答
    最も一般的で設定が簡単だが、データ損失のリスクがある
    準同期レプリケーション少なくとも1台のレプリカへの反映を確認してからクライアントに応答
    データ損失のリスクを軽減できるが、レイテンシが増加する
    プラグインのインストールが必要
    同期レプリケーション全てのレプリカへの反映を確認してからクライアントに応答
    データの一貫性は高いが、パフォーマンスへの影響が大きい
    MySQLの標準機能ではサポートされていない
    マルチソースレプリケーション1台のレプリカが複数のソースからデータを受け取る
    複雑な構成が可能だが、設定と管理が難しい
    カスケードレプリケーションレプリカがさらに別のレプリカのソースとなる
    階層構造を作れるが、遅延が蓄積する可能性がある
    双方向レプリケーション2台のサーバーが互いにソースとレプリカになる
    書き込みの分散が可能だが、データの競合に注意が必要
    GTID (Global Transaction Identifier)
    ベースのレプリケーション    		MySQL 5.6以降で利用可能
    トランザクションに一意のIDを付与し、レプリケーションの管理を容易にする

    レプリケーションの仕組み

    レプリケーションの流れ

    レプリケーションの流れは以下の通りです。

    1. アプリケーションがソースサーバにデータ更新をリクエストする。
    2. ソースサーバがデータを更新し、その内容をバイナリログに記録する。
    3. レプリカサーバのI/Oスレッドがソースのバイナリログを監視し、新しい更新があればリレーログにコピーする。
    4. レプリカサーバのSQLスレッドがリレーログの内容を読み取り、自身のデータに反映する。

    レプリケーションの主な構成要素

    レプリケーションの主な構成要素は以下の通りです。

    構成要素役割
    バイナリログソースサーバの更新内容を記録するログファイル
    リレーログレプリカサーバがソースから取得したバイナリログの内容を一時的に保存するログファイル
    I/Oスレッドレプリカサーバ上でバイナリログの取得を担当するスレッド
    SQLスレッドレプリカサーバ上でリレーログの内容をデータに反映するスレッド

    レプリケーションの設定方法

    レプリケーション設定手順

    MySQLのレプリケーションを設定する基本的な手順を紹介します。

    実行環境は以下の通りです。
    レプリカサーバのデータベースはソースサーバからデータインポートして作成します。

    OSCentOS Stream release 9
    MySQL8.4.2 MySQL Community Server
    データベース名my_database

    サーバの初期設定

    最初に、ソース（マスター）サーバ、レプリカ(スレーブ)サーバ側でレプリケーションのための設定を実施します。

    ソース（マスター）サーバ側の設定

    ソース（マスター）サーバ側で実施する設定は以下の３つです。

    • サーバーIDの設定
    • バイナリログの有効化
    • レプリケーション用ユーザの作成
    サーバーIDの設定

    my.cnfファイルでserver-idを設定します。

    mysqldの配下に属性server-idをします。
    値は1を設定します。

    [root@master ~]# vi /etc/my.cnf
・・・
[mysqld]
・・・
server-id=1
[root@master ~]#
    バイナリログの有効化

    my.cnfファイルでlog-binを設定します。

    mysqldの配下に属性log-binをします。
    特に名前に命名規則はありませんが、わかりやすい名前を設定します。

    [root@master ~]# vi /etc/my.cnf
・・・
[mysqld]
・・・
server-id=1
log-bin=mysql-bin
[root@master ~]#

    設定反映のためサービスを再起動します。

    [root@master ~]# systemctl restart mysqld
[root@master ~]#
    レプリケーション用のユーザ作成

    レプリケーションで利用するユーザを作成します。
    レプリケーション用の専用ユーザーを作成することで、セキュリティを向上させ、レプリケーション関連の操作を他の一般的なデータベース操作から分離することができます。

    今回は「repl」という名前で作成します。

    mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'pAssw0rd!';
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | repl             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql>

    作成したレプリケーション用のユーザに対して、権限を付与します。

    この権限により、任意のホストからマスターサーバーへ「repl」というユーザで接続してバイナリログを読み取り、レプリケーションを実行できるようになります。

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> show grants for 'repl'@'%';
+----------------------------------------------+
| Grants for repl@%                            |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql>
    レプリカ(スレーブ)サーバ側の設定

    レプリカ(スレーブ)サーバ側で実施する設定は以下です。

    • サーバーIDの設定
    サーバーIDの設定

    my.cnfファイルでserver-idを設定します。
    手順はソース（マスター）サーバと同じです。

    mysqldの配下に属性server-idをします。
    値は2を設定します。（ソースサーバと重複しない値を設定する必要があります。）

    [root@slave ~]# vi /etc/my.cnf
・・・
[mysqld]
・・・
server-id=2
[root@slave ~]#

    設定反映のためサービスを再起動します。

    [root@slave ~]# systemctl restart mysqld
[root@slave ~]#

    ソースからレプリカへデータコピー

    次に、ソース（マスター）サーバからレプリカ(スレーブ)サーバへDBデータをコピーします。
    ソース（マスター）サーバでデータをエクスポートしたのち、レプリカ(スレーブ)サーバへエクスポートしたデータをインポートします。

    ソース（マスター）サーバでDBデータのエクスポート

    最初にソース（マスター）サーバでDBデータをエクスポートします。
    コマンドはmysqldumpを利用します。

    実行結果から、正常にDBデータエクスポートが実行されました。

    [root@master ~]# mysqldump -u root -p --all-databases --source-data > /tmp/dump.sql
Enter password:
[root@master ~]#
[root@master ~]# ls -l /tmp/dump.sql
-rw-r--r-- 1 root root 1210778  9月 16 15:36 /tmp/dump.sql
[root@master ~]#
    レプリカ(スレーブ)サーバへDBデータをインポート

    次にエクスポートしたデータをレプリカ(スレーブ)サーバへインポートします。
    コマンドはmysqlを利用します。

    実行結果から、ソース（マスター）サーバのDBデータをインポートできました。

    [root@slave ~]# mysql -u root -p < /tmp/dump.sql
Enter password:
[root@slave ~]#
[root@slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.2 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql>

    レプリカ(スレーブ)サーバでマスター情報の設定

    レプリカ(スレーブ)サーバでマスタの設定をソース（マスター）サーバに変更します。
    CHANGE REPLICATION SOURCE TOコマンドを実行します。

    mysql> CHANGE REPLICATION SOURCE TO
    ->   SOURCE_HOST='ソース（マスター）サーバのIPアドレス',
    ->   SOURCE_USER='repl',
    ->   SOURCE_PASSWORD='pAssw0rd!',
    ->   SOURCE_LOG_FILE='mysql-bin.000001',
    ->   SOURCE_LOG_POS=1115;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>

    SOURCE_LOG_FILEとSOURCE_LOG_POSはソース（マスター）サーバ側でSHOW BINARY LOG STATUSコマンドを実施した結果の属性Positionを指定します。（SHOW BINARY LOGSコマンドでも確認可能）

    mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      1115 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

    レプリカ(スレーブ)サーバでレプリケーションを開始

    レプリカ(スレーブ)サーバでレプリケーションを開始します。
    コマンドはSTART REPLICAを実行します。

    mysql> START REPLICA;
Query OK, 0 rows affected (0.11 sec)

mysql>

    レプリカ(スレーブ)サーバのレプリケーションの状態を確認します。
    コマンドはSHOW REPLICA STATUSを実行します。

    実行結果から、ソース（マスター）サーバをマスタとする設定が完了し、接続状態も問題ない（属性Replica_IO_Running、Replica_SQL_RunningがYesとなっている）ことが確認できました。

    mysql> SHOW REPLICA STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Replica_IO_State                 | Source_Host   | Source_User | Source_Port | Connect_Retry | Source_Log_File  | Read_Source_Log_Pos | Relay_Log_File             | Relay_Log_Pos | Relay_Source_Log_File | Replica_IO_Running | Replica_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Source_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Source_SSL_Allowed | Source_SSL_CA_File | Source_SSL_CA_Path | Source_SSL_Cert | Source_SSL_Cipher | Source_SSL_Key | Seconds_Behind_Source | Source_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Source_Server_Id | Source_UUID                          | Source_Info_File        | SQL_Delay | SQL_Remaining_Delay | Replica_SQL_Running_State                                | Source_Retry_Count | Source_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Source_SSL_Crl | Source_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Source_TLS_Version | Source_public_key_path | Get_Source_public_key | Network_Namespace |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Waiting for source to send event | ソース（マスター）サーバのIPアドレス | repl        |        3306 |            60 | mysql-bin.000001 |                1115 | localhost-relay-bin.000002 |           647 | mysql-bin.000001      | Yes                | Yes                 |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                1115 |             862 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | 96cf0bc4-7367-11ef-b7c7-2a14b1574830 | mysql.slave_master_info |         0 |                NULL | Replica has read all relay log; waiting for more updates |                 10 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set (0.01 sec)

mysql>

    Replica_IO_RunningがNoとなっている場合は、レプリケーションI/Oスレッドが実行されておらず、正常にレプリケーションできない状態です。

    原因として、以下の項目を確認してみてください。

    1. ネットワーク接続の問題
      • ソースサーバーへのネットワーク接続が不安定であるか、ファイアウォールなどでブロックされている可能性があります。
    2. ソースサーバーの設定
      • ソースサーバーが正しく起動していないか、バイナリログが有効になっていない場合、接続できない可能性があります。
    3. 認証情報の誤り
      • 使用しているユーザー名やパスワードが間違っている場合、接続が拒否される場合があります。
    4. ホスト名やポートの誤設定
      • ソースサーバーのホスト名やポート番号が正しく設定されていない場合、接続できない場合があります。
    5. SSL/TLS設定の問題
      • SSL/TLSを使用している場合、証明書や鍵の設定に問題があると接続できない場合があります。

    以上で、レプリケーション設定が完了しました。

    ソース（マスター）サーバからレプリカ(スレーブ)サーバへレプリケーションができていることを確認します。

    レプリケーション動作確認

    レプリケーションの動作確認を実施します。

    ソース（マスター）サーバで特定のテーブルusersへデータを追加し、レプリカ(スレーブ)サーバへ同期されることを確認します。

    事前情報として、ソース（マスター）サーバ、レプリカ(スレーブ)サーバどちらもテーブルusersにはデータが登録されていない状態です。

    ソース（マスター）サーバ

    mysql> select * from users;
Empty set (0.00 sec)

mysql>

    レプリカ(スレーブ)サーバ

    mysql> select * from users;
Empty set (0.00 sec)

mysql>

    ソース（マスター）サーバでデータ追加

    最初に、ソース（マスター）サーバでテーブルusersのデータを追加します。

    mysql> INSERT INTO users (user_id, name, age, birthplace) VALUES (1, 'user01', 25, 'tokyo');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select * from users;
+---------+--------+------+------------+
| user_id | name   | age  | birthplace |
+---------+--------+------+------------+
|       1 | user01 |   25 | tokyo      |
+---------+--------+------+------------+
1 row in set (0.01 sec)

mysql>

    レプリカ(スレーブ)サーバの同期確認

    レプリカ(スレーブ)サーバのテーブルusersのデータを確認します。

    実行結果かから、ソース（マスター）サーバで追加したデータがレプリカ(スレーブ)サーバへ同期されたことが確認できました。

    mysql> select * from users;
+---------+--------+------+------------+
| user_id | name   | age  | birthplace |
+---------+--------+------+------------+
|       1 | user01 |   25 | tokyo      |
+---------+--------+------+------------+
1 row in set (0.01 sec)

mysql>

    まとめ

    今回はMySQLの機能の１つであるレプリケーションについて、紹介しました。

    MySQLのレプリケーションは柔軟性が高く、多くの用途で活用できる重要な機能です。

    適切に設計・運用することで、可用性とスケーラビリティの向上に大きく貢献します。

    参考になれば、幸いです。

    以上になります。ありがとうございました。

    参考

    https://www.system-exe.co.jp/dbexpert34
    https://style.potepan.com/articles/19397.html
    https://qiita.com/suzuko24/items/ace0a08f2b2aabd95045
    https://www.yuus-program.com/2023/05/29/db-2/
    https://qiita.com/Tocyuki/items/c224cef57493f536a941
    https://www.reddit.com/r/mysql/comments/1d6ysx0/show_master_status_not_working/?rdt=48240
    https://dev.mysql.com/doc/refman/8.0/en/start-slave.html
    https://dev.mysql.com/doc/refman/8.0/ja/replication-administration-status.html

    コメント

    タイトルとURLをコピーしました