本記事は東京学芸大学櫨山研究室 Advent Calendar 2020の21日目の記事になります．

はじめに

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

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

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

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

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

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

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

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

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

マスターサーバ

マスターとなる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を用意します．

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

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をホストマシン上からコンテナに送ります．

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] > '

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オプションで指定することで有効にできます．

my.cnf

# binary-log
log_bin = /var/log/mysql/mysql-bin

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

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

今回はこの処理を行うシェルスクリプトsetup.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つのコンテナを立ち上げなおします．

# シェルスクリプトに実行権限をつけておく
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アドレスを知っておく必要があるので先にチェックしておきます．

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

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

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

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

mysql_client(master)

CREATE USER 'repl'@'192.168.%.%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%';

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

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側を操作し追従するマスターサーバの設定をします．

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

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

mysql_client(slave)

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

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

mysql_client(slave)

START SLAVE USER = 'repl' PASSWORD = 'repl';

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

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というデータベースを作成してみます．

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命令を実行してみます．

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 止まらぬ進化に乗り遅れないためのテクニカルガイド”
山田明憲，”Docker/Kubernetes 実践コンテナ開発入門”

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

はじめに

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

マスターサーバ

スレーブサーバ

Dockerでmaster/slave構成を組む

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

master側の設定

slave側の設定

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

おわりに

参考