【MySQL】レプリケーションの設定をしろって言われたんです。

# レプリケーション設定の備忘録

## 目次
1. [レプリケーションとは](#レプリケーションとは)
1. [レプリケーションのメリット](#レプリケーションのメリット)
1. [レプリケーションの設定を入れてみる](#レプリケーションの設定を入れてみる)

---

後輩ちゃん「上司からMySQLのレプリケーション設定を組み込んでくれって言われたんですけど、レプリケーションってなんですか？」

すずこ「レプリカ（複製）をつくることだよ」
すずこ「簡単に言うと、**一つのMySQLデータベース（マスター）の変更が、
他のMySQLデータベース（スレーブ）に自動的に反映される**仕組みのことをレプリケーションっていうんだよ～」

:::note info
多様性の観点からマスター（master）/スレーブ（slave）から、メイン（main）/レプリカ（replica）と表現されるケースもある
:::

## レプリケーションとは
レプリケーションは、データベースの情報を複製して複数の場所で同じデータを利用できるようにする仕組み。
データベースの可用性を向上させ、冗長性を確保することができる。

<br>

後輩ちゃん「... 複製するメリットがいまいち分からないのですが、なんのためにレプリケーション設定を入れるのですか？」

すずこ「例えば、データベースサーバーが1台の場合、サーバーに障害が発生したらどうなる？」

後輩ちゃん「アプリケーションの利用ができなくなる...？」

すずこ「その通り！レプリケーションを導入することで、1台のサーバーに頼らない冗長性を確保できるんだよ。」

後輩ちゃん「冗長性って、具体的にどういうことですか？」

すずこ「冗長性とは、冗長な構成を持つことでシステムやサービスの可用性や信頼性を向上させること。」
すずこ「具体的には、マスターサーバーと1つ以上のスレーブサーバーを持つことによって、マスターが故障した場合でもスレーブが引き続きサービスを提供できるようにするんだ。」

後輩ちゃん「なるほど、冗長な構成を持つことで障害が発生してもサービスを止めずに続けられるってことですね。」
すずこ「そうゆうこと！他にもメリットあるよ！」

## レプリケーションのメリット
* **冗長性**
    * **障害対策**：マスターサーバーに障害が発生した場合、スレーブが引き続きサービスを提供することができる
    * **フェイルオーバー機能**：マスターサーバーの障害時には、自動的に別のスレーブが昇格して新たなマスターとなり、シームレスにサービスを継続できる
* **スケーラビリティ**
    * **読み取り専用レプリケーション**：スレーブを読み取り専用として活用することで、マスターサーバーが書き込みトランザクションに専念し、同時に多くの読み取り操作に対応できる
* **バックアップ**
    * **リアルタイム同期**：バックアップとしてのスレーブは、マスターサーバーのデータとリアルタイムで同期しているため、マスターサーバーのデータが破損した場合、スレーブのデータを使用してほぼ即座に復旧できる
    * **ポイントインタイムリカバリ(PITR)**：スレーブはバイナリログを使用してマスターサーバーと同期しているため、バイナリログの変更履歴を利用することで、特定の時点までデータを戻すことができる


## レプリケーションの設定を入れてみる
### ローカル環境を作る
後輩ちゃん「レプリケーションを実現するためには、少なくとも2つのデータベースサーバーが必要になりますね」
後輩ちゃん「設定手順を作成するにあたり、Dockerで2台のMySQLコンテナを立てて試してみます」

:::note warn
マスター、スレーブのMySQLのバージョンは同一であることが前提
:::

<details><summary>docker-compose.yml (参考というほどのものでもないけど)</summary><div>

``` yaml:docker-compose.yml
version: "2"
services:
  master-db:
    container_name: master
    image: mysql:5.7.37
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: slj
      MYSQL_USER: slj
      MYSQL_PASSWORD: sljsljslj
    ports:
      - "3306:3306"

  slave-db: 
    container_name: slave
    image: mysql:5.7.37
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: slj
      MYSQL_USER: slj
      MYSQL_PASSWORD: sljsljslj
    ports:
      - "3307:3306"
```

</div></details>

※dockerコマンド関連（コンテナの起動の仕方など）は本記事には記載しません、悪しからず。

### レプリケーション設定の作業サマリ
1. マスターデータベースの書き込みを一時停止
1. マスターデータベースのデータをエクスポート
1. マスターデータベースにレプリケーション用ユーザーの作成
1. マスターデータベースにバイナリログの有効化設定
1. マスターデータベース再起動
1. バイナリログの状態確認
1. スレーブデータベースにデータをインポート
1. スレーブデータベースにレプリケーション用ユーザーの作成
1. スレーブデータベースにバイナリログの有効化設定
1. スレーブデータベースの再起動
1. スレーブデータベースにマスターデータベースの登録をする
1. スレーブデータベースでレプリケーションの開始
1. スレーブデータベースのレプリケーション設定の確認
1. マスターデータベースの書き込みを再開

### レプリケーション設定の作業詳細
##### 1. マスターデータベースの書き込みを一時停止
レプリケーション設定をする前に、マスターデータベース上の書き込みを一時停止することで安全に設定を入れる。
``` shell-session:データベース
> FLUSH TABLES WITH READ LOCK;
```

##### 2. マスターデータベースのデータをエクスポート
マスターデータベースの現在の状態をエクスポートし、そのデータを元にレプリケーションを開始する。
``` shell-session:ターミナル
$ mysqldump -u ユーザー名 -p パスワード --host=ホスト名 --port=ポート番号 --databases データベース名 > ダンプファイル.sql
```

##### 3. マスターデータベースにレプリケーション用ユーザーの作成
レプリケーション用のユーザーを作成する。
``` shell-session:データベース
# ユーザーの作成
> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
# レプリケーション権限の設定
> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 作成したユーザーの確認
> SELECT User, Host, Plugin FROM mysql.user;
```

##### 4. マスターデータベースにバイナリログの有効化設定
バイナリログを有効にして、マスターデータベースで発生した変更をバイナリログに記録する。
これにより、スレーブデータベースが変更を追跡することができる。

``` my.cnf:my.cnf
[mysqld]
# Binary log settings
server-id=1001 #ユニークなIDを設定
log-bin=mysql-bin #バイナリログの設定
expire_logs_days=7 #バイナリログの保持期間
```

##### 5. マスターデータベースの再起動
my.cnfの変更が反映されるように、マスターデータベースを再起動する。

##### 6. バイナリログの状態確認
バイナリログの有効化が正常に設定されているか確認する。

``` shell-session:データベース
> SHOW MASTER STATUS;
※出力結果（File, Position）をメモしておくこと

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

```

##### 7. スレーブデータベースにデータをインポートする
マスターデータベースからエクスポートしたデータをスレーブデータベースにインポートする。

``` shell-session:ターミナル
mysql -u ユーザー名 -p データベース名 < ダンプファイル.sql
```

##### 8. スレーブデータベースにレプリケーション用ユーザーの作成
レプリケーション用のユーザーを作成する。
``` shell-session:データベース
# ユーザーの作成
> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
# レプリケーション権限の設定
> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 作成したユーザーの確認
> SELECT User, Host, Plugin FROM mysql.user;
```

##### 9. スレーブデータベースにバイナリログの有効化設定
バイナリログを有効にし、マスターデータベースの変更を追跡できるようする。
``` my.cnf:my.cnf
[mysqld]
# Binary log settings
server-id=1002 #ユニークなIDを設定（マスターデータベースと異なるID）
log-bin=mysql-bin #バイナリログの設定
expire_logs_days=7 #バイナリログの保持期間
read only # 読み取り専用レプリケーションの場合
```

##### 10. スレーブデータベースの再起動
my.cnfの変更が反映されるように、スレーブデータベースを再起動する。

##### 11. スレーブデータベースにマスターデータベースの登録をする
スレーブデータベースに、マスターデータベースの情報を登録する。

``` shell-session:データベース
※下記マスターデータベースの情報を設定する
※バイナリログ = 6. バイナリログの状態確認で確認したバイナリログの情報

> CHANGE MASTER TO
    MASTER_HOST = '[IPアドレス/ホスト名]',
    MASTER_USER = '[レプリケーションユーザー]',
    MASTER_PASSWORD = '[レプリケーションユーザーのパスワード]',
    MASTER_PORT = [ポート番号],
    MASTER_LOG_FILE = '[バイナリログのファイル名]',
    MASTER_LOG_POS = [バイナリログのポジション]
    ;
```

##### 12. スレーブデータベースででレプリケーションの開始
レプリケーションプロセスを開始。
``` shell-session:データベース
> START SLAVE;
```

##### 13. スレーブデータベースのレプリケーション設定の確認
レプリケーションの設定が正しく行われているかを確認。
``` shell-session:データベース
> SHOW SLAVE STATUS\G

※この辺りを確認しておけばOK
...
Slave_IO_State: Waiting for master to send event
Master_Host: master-db
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 899
Relay_Log_File: 9bc602b450ce-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes 
Slave_SQL_Running: No
...
```
**Slave_IO_Running**：マスターのバイナリログを読み取るためのI/Oスレッドが実行しているかどうか
**Slave_SQL_Running**：SQLスレッドが起動しているかどうか

##### 14. マスターデータベースの書き込みを再開
マスターデータベースの書き込みを再開する。
``` shell-session:データベース
> UNLOCK TABLES;
```

---
後輩ちゃん「マスターデータベースにデータの挿入したら、スレーブデータベースにも反映されていました！」
すずこ「今回はシンプルなレプリケーションの設定をしたんだけど、他にも双方向レプリケーションや、非同期レプリケーションもあるんだよ～！」
後輩ちゃん「そうなんですね！異なるレプリケーション手法も理解して、適切なデータベース設計ができるようになりたいです！」


うむ。