Dockerで学ぶMySQLのレプリケーション(master/slave構成)

本記事は[東京学芸大学 櫨山研究室 Advent Calendar 2020](https://qiita.com/advent-calendar/2020/hazelab)の21日目の記事になります．


# はじめに

本記事ではMySQLのレプリケーションの概要からDockerを使って実際に2台のMySQLサーバを使ったレプリケーションを組むという内容を扱います．

なお本記事は2台のサーバでのレプリケーションについて扱います．

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

レプリケーションはMySQLが提供する標準機能の一つです．

レプリケーションでは同じデータを持つデータベースを複数用意し，それぞれに同じ操作を行います．
MySQLはデータの複製を他のサーバに持たせる機能を提供しています．

それぞれに命名と役割は以下の通りです．

1. master（マスター）  
クライアントがデータを変更するサーバ  
データが変更されたら変更内容をスレーブに転送する  
2. slave（スレーブ）  
マスターの変更内容を受け取り，データベースに反映する

※なおmaster/slaveという名称は差別的という意見もあり今後はPrimary/Secondaryという表現に変化していく可能性があります．

レプリケーションの動作の流れを図に示すと以下のようになります．

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/225901/6cc714b9-bf0d-6df2-1fb5-060401a45d86.png)

## マスターサーバ

マスターとなるMySQLサーバは変更を記録するためにバイナリログを作成します．
**バイナリログはMySQLサーバ上で発生した全ての変更を記録する仕組みです．**

マスターはスレーブにデータを転送するためにはマスタースレッドを提供します．

## スレーブサーバ

スレーブとなるMySQLサーバはマスターへ接続し，更新を連続して受け取ります．
受け取った更新データはリレーログに記録します．リレーログはマスターのバイナリログと同じ形式です．

更新を受け取りリレーログに保存するのはI/Oスレッドの役割になります．

スレーブSQLスレッドではリレーログから更新の差分を読み取り，その内容をスレーブ上で再生します．

# Dockerでmaster/slave構成を組む

今回はDockerで起動した2台にMySQLサーバを使ってmaster/slaveサーバを使ってやってみたいと思います．
用意するファイルは以下のような形です．

```
.
├── Dockerfile
├── docker-compose.yml
├── master
│   └── conf
│       └── my.cnf
├── setup.sh
└── slave
    └── conf
        └── my.cnf
```


はじめに`Dockerfile`と`docker-compose.yml`を用意します．

```docker:Dockerfile
FROM mysql:5.7

RUN apt-get update \
    && apt-get install -y locales \
    && locale-gen ja_JP.UTF-8
ENV LANG ja_JP.UTF-8
ENV LANGUAGE ja_JP:ja
ENV LC_ALL=ja_JP.UTF-8
RUN localedef -f UTF-8 -i ja_JP ja_JP.utf8
```

```yml
version: '3.7'

services:

  master:
    container_name: mysql_master
    build:
      context: .
      dockerfile: ./Dockerfile
    volumes:
      - ./master/conf/:/conf
      - ./setup.sh:/usr/local/bin/setup.sh
      - master_log/:/var/log/mysql/
      - master_db:/var/lib/mysql
    tty: true
    environment:
      TZ: 'Asia/Tokyo'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'master'
    command: >
      bash -c "
      cp /conf/my.cnf /etc/mysql/conf.d/ &&
      chmod 644 /etc/mysql/conf.d/my.cnf &&
      /entrypoint.sh mysqld"
    networks:
      - demo-network

  slave:
    container_name: mysql_slave
    build:
      context: .
      dockerfile: ./Dockerfile
    volumes:
      - ./slave/conf/:/conf
      - ./setup.sh:/usr/local/bin/setup.sh
      - slave_log/:/var/log/mysql/
      - slave_db:/var/lib/mysql
    tty: true
    environment:
      TZ: 'Asia/Tokyo'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'slave'
    command: >
      bash -c "
      cp /conf/my.cnf /etc/mysql/conf.d/ &&
      chmod 644 /etc/mysql/conf.d/my.cnf &&
      /entrypoint.sh mysqld"
    networks:
      - demo-network

volumes:
  master_db: {}
  master_log: {}
  slave_db: {}
  slave_log: {}

networks:
  demo-network:
    driver: bridge
```

master側とslave側の設定ファイル`my.cnf`をホストマシン上からコンテナに送ります．

```conf:msater/conf/my.cnf
[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb-use-native-aio = 0

# START
# binary-log
# log_bin = /var/log/mysql/mysql-bin
# server-id
# server_id = SERVER_ID
# END

[mysql]
prompt = '(master) [\\d] > '
```

```conf:slave/conf/my.cnf
[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb-use-native-aio = 0

# START
# binary-log
# log_bin = /var/log/mysql/mysql-bin
# server-id
# server_id = SERVER_ID
# END

[mysql]
prompt = '(slave) [\\d] > '
```

これで`docker-compose up`コマンドで2台のMySQLサーバがコンテナとして起動します．

```
docker-compose up -d --remove-orphans
```

## バイナリログの有効化とサーバIDの付与

はじめにバイナリログを有効化する必要があります．
これは設定ファイルの`log_bin`オプションで指定することで有効にできます．

```conf:my.cnf
# binary-log
log_bin = /var/log/mysql/mysql-bin
```

なおバイナリログのファイル名は`log_bin`オプションで指定した値がプレフィックスになり6桁の連番がついた名前になります．
例））mysql-bin.000001

またレプリケーションを使う上でMySQLはサーバを一意に特定するためにサーバIDを振る必要があります．
このサーバIDは慣習的に各サーバの持つIPv4アドレスにもとづていて振ります．

今回はこの処理を行うシェルスクリプト`setup.sh`を用意します．

```sh:setup.sh
#!/bin/sh

OCTETS=`hostname -i | tr -s '.' ' '`

i=3
set $OCTETS
if [ $i -ge 10 ]
then
 i=`expr $i - 9`
 shift 9
fi
v1="v1=\$$i"
eval $v1

i=4
if [ $i -ge 10 ]
then
 i=`expr $i - 9`
 shift 9
fi
v2="v2=\$$i"
eval $v2

MYSQL_SERVER_ID=`expr $v1 \* 256 + $v2`

sed -i -e "/# START/,/# END/ s/SERVER_ID/$MYSQL_SERVER_ID/g" /conf/my.cnf
sed -i -e '/# START/,/# END/ s/# server_id/server_id/g' /conf/my.cnf
sed -i -e '/# START/,/# END/ s/# log_bin/log_bin/g' /conf/my.cnf
```

masterとslaveのそれぞれでスクリプトを実行して2つのコンテナを立ち上げなおします．

```sh
# シェルスクリプトに実行権限をつけておく
chmod +x ./setup.sh

# バイナリログの有効化とサーバIDの付与
docker container exec -it mysql_master /usr/local/bin/setup.sh
docker container exec -it mysql_slave /usr/local/bin/setup.sh

# 再起動
docker-compose down
docker-compose up -d --remove-orphans
```

## master側の設定

masterサーバにスレーブ側から接続するユーザーを作成します．
ユーザー作成時にIPアドレスを知っておく必要があるので先にチェックしておきます．

```sh
# コンテナのIPアドレスの確認
docker container exec -it mysql_master hostname -i
192.168.32.3
```

まず`docker exec`コマンドでmaster側を操作します．

```sh
docker container exec -it mysql_master mysql -u root -p
```

そしてレプリケーション用のユーザーを作成します．
この時先ほどのIPアドレスの情報を元にアクセス制御をします．

```sql:mysql_client(master)
CREATE USER 'repl'@'192.168.%.%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%';
```

master側のバイナリログの情報を確認しておきます．

```sql:mysql_client(master)
SHOW MASTER STATUS;
```

```:出力
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      611 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```

## slave側の設定

`docker exec`コマンドでslave側を操作し追従するマスターサーバの設定をします．

```sh
docker container exec -it mysql_slave mysql -u root -p
```

先ほどmaster側で取得したバイナリログの情報を元に設定をします．

```sql:mysql_client(slave)
CHANGE MASTER TO
  MASTER_HOST='mysql_master',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='{masterのbinlogのファイル名}',
  MASTER_LOG_POS={binlogのポジション};
```

そしてslaveとしての動作を開始します．

```sql:mysql_client(slave)
START SLAVE USER = 'repl' PASSWORD = 'repl';
```

正常にmasterと接続できているか確認してみましょう．

```sql:mysql_client(client)
SHOW SLAVE STATUS\G;
```

```:出力
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql_master
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1213
               Relay_Log_File: 25e09487b653-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 1213
              Relay_Log_Space: 534
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
                  Master_UUID: 48aa7470-4371-11eb-8a57-0242c0a80003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
```

`Slave_IO_Running`と`Slave_SQL_Running`が`Yes`になっていれば問題なさそうです🙆‍♂️

## masterでの操作がslaveで反映されているかの検証

masterでの操作がslaveで反映されているかを確認します．
初期状態ではどちらも以下のようなデータベースがあります．

```:出力
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
```

master側で新しく`sample`というデータベースを作成してみます．

```sql:mysql_client(master)
CREATE DATABASE sample;

SHOW DATABASES;
```
```:出力(master)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
```

次にslave側で`SHOW DATABASES`命令を実行してみます．

```sql:mysql_client(slave)
SHOW DATABASES;
```
```:出力(slave)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
```

きちんとmasterの操作が反映されてslave側にも`sample`データベースが作成されていることがわかります，

# おわりに

本記事ではMySQLのレプリケーション(master/slave構成)について扱いました．
何かの役に立てば幸いです🙏

# 参考

- 奥野幹也，”[詳解 MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド](https://www.amazon.co.jp/%E8%A9%B3%E8%A7%A3MySQL-5-7-%E6%AD%A2%E3%81%BE%E3%82%89%E3%81%AC%E9%80%B2%E5%8C%96%E3%81%AB%E4%B9%97%E3%82%8A%E9%81%85%E3%82%8C%E3%81%AA%E3%81%84%E3%81%9F%E3%82%81%E3%81%AE%E3%83%86%E3%82%AF%E3%83%8B%E3%82%AB%E3%83%AB%E3%82%AC%E3%82%A4%E3%83%89-NEXT-ONE/dp/4798147400/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&dchild=1&keywords=%E8%A9%B3%E8%A7%A3+MySQL+5.7+%E6%AD%A2%E3%81%BE%E3%82%89%E3%81%AC%E9%80%B2%E5%8C%96%E3%81%AB%E4%B9%97%E3%82%8A%E9%81%85%E3%82%8C%E3%81%AA%E3%81%84%E3%81%9F%E3%82%81%E3%81%AE%E3%83%86%E3%82%AF%E3%83%8B%E3%82%AB%E3%83%AB%E3%82%AC%E3%82%A4%E3%83%89&qid=1608547977&sr=8-1)”
- 山田明憲，”[Docker/Kubernetes 実践コンテナ開発入門](https://www.amazon.co.jp/Docker-Kubernetes-%E5%AE%9F%E8%B7%B5%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A%E9%96%8B%E7%99%BA%E5%85%A5%E9%96%80-%E5%B1%B1%E7%94%B0-%E6%98%8E%E6%86%B2-ebook/dp/B07GP1Q3VT/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&dchild=1&keywords=docker%2Fkubernetes+%E5%AE%9F%E8%B7%B5%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A+%E9%96%8B%E7%99%BA+%E5%85%A5%E9%96%80&qid=1608547991&sr=8-1)”