LoginSignup
2
1
@igossou(異骨相)in株式会社マイスター・ギルド

MySQLでGTIDを使ったレプリケーションをしてみた

Last updated at Posted at 2023-09-29

はじめに

経緯

レプリケーションにまつわる用語や仕組みについては既に多くの記事で説明されているため、この記事ではそれらについての解説はせず、GTIDを用いたレプリケーションの設定方法について紹介します。

GTIDについては以下の記事をどうぞ。

過日、初めてレプリケーションの設定をすることになって、MySQLのレプリケーションの方法について調べたんですがサイトとか記事によって、
やれSHOW MASTER STATUSでバイナリログとポジションを確認しろだのGTIDを使えだのFLUSH TABLES　~~でロックをやれだのmysqldumpでバックアップを取れだの、自分が初心者すぎて情報の取捨選択ができない！(知識不足を棚に上げたただの愚痴)

というわけで手順の差異に随分惑わされた挙句、結局、業後に本屋に行ってMySQL 徹底入門第４版を買ってそれにならって設定しました。

QiitaにもGTIDを使ったレプリケーション手順についての記事は少ない気がしたので備忘録も兼ねて書くに至った次第です。

今回の前提とか条件

  • マスター、スレーブともにデータがない状態であること
  • MySQL8.0.33を使用
  • 検証環境はDockerを使用

レプリケーション設定

以下の流れで進めます。

  1. docker-composeでマスター、スレーブコンテナを用意する
  2. マスター、スレーブのmy.cnfを設定する
  3. docker-composeを立ち上げる
  4. マスターでDBとユーザーを作る
  5. スレーブでレプリケーションを開始する

1. docker-composeでマスター、スレーブコンテナを用意する

実際のサーバーに近い想定でIPも設定してみました。

docker-compose.yml
version: "3.9"
services:
  db01:
    image: mysql:8.0.33
    ports:
      - 3307:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Tokyo/Japan
    volumes:
      - ./db01/my.cnf:/etc/my.cnf
    networks:
      db_net:
        ipv4_address: 192.168.10.11
  db02:
    image: mysql:8.0.33
    ports:
      - 3308:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Tokyo/Japan
    volumes:
      - ./db02/my.cnf:/etc/my.cnf
    networks:
      db_net:
        ipv4_address: 192.168.10.12

networks:
  db_net:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 192.168.10.0/24

2. マスター、スレーブのmy.cnfを設定する

マスター用のmy.cnfです。

/db01/my.cnf
[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld]
default_authentication_plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# master用設定
server_id=1001
port=3306

# バイナリログ設定
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_rows_query_log_events=ON
relay_log_recovery=ON

スレーブ用のmy.cnfです。

db02/my.cnf
[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld]
default_authentication_plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# slave用設定
server_id=1002
port=3306
read_only=ON
report_host=db02

# バイナリログ設定
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_rows_query_log_events=ON
relay_log_recovery=ON

my.cnfの内容少し補足

設定しているオプションの意味を備忘録も含めて補足。

設定 役割・意味
server_id デフォルト値1
マスターとスレーブのmysqldを一意に識別するため
read_only 読み取り専用
スレーブがSUPER権限のないユーザーからの更新DMLを受け付けないようにするため
report_host マスターでSHOW REPLICAS;を実行した時に表示されるスレーブのホスト名
gtid_mode GTIDを使用するかどうか
enforce_gtid_consistency GTIDがサポートしていない更新SQLは拒否する
binlog_rows_query_log_events 行ベースのバイナリログに元の更新SQLそのものを記録する
デバッグや追跡が楽になる
relay_log_recovery スレーブがクラッシュした後で再起動したときに、クラッシュ前に持っていた実行予定の更新SQLを破棄し、マスターから再受信を行う
破棄することで更新SQLの二重受信を防ぐ

3. docker-composeを立ち上げる

docker composeを立ち上げます。

docker compose up -d

4. マスターでDBとユーザーを作る

まずはマスターのコンテナに入ってMySQLにログインします。

docker compose exec db01 bash
mysql -uroot -proot

※Docker環境なのでパスワード丸出しでログインしてますが実際のサーバー上ではやめましょう

DBを構築する以上、それを利用するアプリケーションがあるはずなので、そのアプリケーションがログインするためのユーザーと、必要になるであろう権限(参照・追加・更新・削除とか)を付与します。
接続できる範囲は同じサブネット内(192.168.10.0/24)にしてみました。

またスレーブがレプリケーションをするためのユーザーと権限も専用に作成します。
接続できる範囲はレプリケーションコンテナのIPのみ(192.168.10.12)にしてみました。

権限一覧は公式ドキュメントをどうぞ、色々あります。

-- 適当なDB作成
CREATE DATABASE repl_test;

-- 一般的な操作ができるユーザー作成と権限付与
CREATE USER 'test'@'192.168.10.%' IDENTIFIED BY 'test';
GRANT
  SELECT,
  INSERT,
  UPDATE,
  DELETE,
  CREATE,
  ALTER,
  DROP
ON *.* TO 'test'@'192.168.10.%';

-- DB02からのみアクセスできるレプリケーション用ユーザー作成と権限付与
CREATE USER 'repl'@'192.168.10.12' IDENTIFIED BY 'repl';
GRANT
  REPLICATION SLAVE
ON *.* TO 'repl'@'192.168.10.12';

5. スレーブでレプリケーションを開始する

別のターミナルを開いてスレーブコンテナに入ってMySQLにログインします。

docker compose exec db02 bash
mysql -uroot -proot

スレーブ側ではバイナリログを取得するマスターの情報を登録します。
そしてレプリケーション開始！

-- マスターのIPとレプリケーション用ユーザーの情報を登録
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.10.11',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'repl',
  SOURCE_AUTO_POSITION = 1;

-- レプリケーション開始
START REPLICA;

あっけないですね。これで設定終わりです。
レプリケーションステータスを確認します。

SHOW REPLICA STATUS\G;

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.10.11
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 1285
               Relay_Log_File: 88e090745665-relay-bin.000003
                Relay_Log_Pos: 1495
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...
                   Last_Error:
...
               Last_SQL_Error:
...
             Source_Server_Id: 1001
                  Source_UUID: 46412030-5ad5-11ee-8e5e-0242c0a80a0b
...
           Retrieved_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9
            Executed_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9,
464a0535-5ad5-11ee-9025-0242c0a80a0c:1-5
                Auto_Position: 1
...

個人的に確認しているポイントをあげてみます。

  • Replica_IO_State: Waiting for source to send eventになっていること
  • Last_Error,Last_SQL_Errorがないこと
  • Retrieved_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9 と
    Executed_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9のトランザクション範囲(:以降の数字の範囲)が一致していること

マスターで作成したDBやユーザーがスレーブに複製されているか確認します。

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+

SELECT user, host FROM mysql.user;
+------------------+---------------+
| user             | host          |
+------------------+---------------+
| root             | %             |
| test             | 192.168.10.%  |
| repl             | 192.168.10.12 |
| mysql.infoschema | localhost     |
| mysql.session    | localhost     |
| mysql.sys        | localhost     |
| root             | localhost     |
+------------------+---------------+

マスターで作成したrepl_testDBやrepl,testユーザーが存在していればOKです。

ちなみに

Executed_Gtid_Setが2つありますが、1つはマスターのUUIDでもう1つはスレーブ自身のUUIDです。

                  Source_UUID: 46412030-5ad5-11ee-8e5e-0242c0a80a0b
...
           Retrieved_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9
            Executed_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9,
464a0535-5ad5-11ee-9025-0242c0a80a0c:1-5

スレーブのUUIDはマスターでSHOW REPLICAS;を実行することで確認できます。

SHOW REPLICAS;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID                         |
+-----------+------+------+-----------+--------------------------------------+
|      1002 | db02 | 3306 |      1001 | 464a0535-5ad5-11ee-9025-0242c0a80a0c |
+-----------+------+------+-----------+--------------------------------------+

結論

書籍を読んだ後になってわかりましたが、既に運用しているDBがあってそれをレプリケーション構成にするのであればmysqldumpなんかでフルバックアップを取ってスレーブにリストアしてからGTID設定とかしないといけないようです。
今回のようにデータが何もないような構築初期段階ではレプリケーションの設定は非常にシンプルにできてしまいます。

実際にLinuxでDBサーバーを立ててMySQLのミドルウェアをインストールした場合、パスワードは今回のようにreplのような脆弱な設定はできなかったり、my.cnfのsocketのパスがDockerと違ってたり若干の違いはありますが設定手順に関してはこのままいけるはずです。

MySQLの公式ドキュメントはボリューム多くてあちこち飛んで読まないといけないし、結局どれを使えばいいのか判断難しいし、やっぱり有識者が情報を整理してまとめてくれてる書籍がわかりやすいですね。
とはいえ今回参考にした書籍のレプリケーションの章はInnoDB ReplicaSetというツールセットを使用してレプリケーション構築を行なっているので、ツールがよしなに行なってくれている設定や内容は読み解いていく必要があるという。。。

何はともあれ重要な設定箇所はmy.cnfです。
オプションもたくさんあるのでここの調査と設定が大変になると思います。
今回は最低限必要そうなものを設定しました。
参考になれば幸いです。

余談

SHOW SLAVE ~~;
START SLAVE;

などSLAVEという単語を使ったコマンドが紹介されていることが多いですが、非推奨になっていて、代わりにREPLICAを使いなさいと警告が出るようになっています。
初めて見た時SLAVEってすごい単語使ってんなと思いましたが、まぁそうなりますよね。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1