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

# はじめに

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

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

https://qiita.com/boro1234/items/797b97fc06e7c688b7b2

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

というわけで手順の差異に随分惑わされた挙句、結局、業後に本屋に行って[MySQL 徹底入門第４版](https://www.shoeisha.co.jp/book/detail/9784798161488)を買ってそれにならって設定しました。

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

## 今回の前提とか条件
- マスター、スレーブともにデータがない状態であること
- MySQL8.0.33を使用
- 検証環境はDockerを使用


# レプリケーション設定
以下の流れで進めます。

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

## 1. docker-composeでマスター、スレーブコンテナを用意する
実際のサーバーに近い想定でIPも設定してみました。

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




## 3. docker-composeを立ち上げる
docker composeを立ち上げます。
```terminal
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)にしてみました。

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

https://dev.mysql.com/doc/refman/8.0/ja/privileges-provided.html


```sql:
-- 適当な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
```

スレーブ側ではバイナリログを取得するマスターの情報を登録します。
そしてレプリケーション開始！
```sql:
-- マスターのIPとレプリケーション用ユーザーの情報を登録
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.10.11',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'repl',
  SOURCE_AUTO_POSITION = 1;

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

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

```sql:
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
`と<br>`Executed_Gtid_Set: 46412030-5ad5-11ee-8e5e-0242c0a80a0b:1-9`のトランザクション範囲(:以降の数字の範囲)が一致していること

マスターで作成したDBやユーザーがスレーブに複製されているか確認します。
```sql
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_test`DBや`repl`,`test`ユーザーが存在していればOKです。

### ちなみに
`Executed_Gtid_Set`が2つありますが、1つはマスターのUUIDでもう1つはスレーブ自身のUUIDです。
```sql
                  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;`を実行することで確認できます。
```sql
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`というツールセットを使用してレプリケーション構築を行なっているので、ツールがよしなに行なってくれている設定や内容は読み解いていく必要があるという。。。

https://www.shoeisha.co.jp/book/detail/9784798161488

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

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