はじめに

Dockerを使って、MySQLのレプリケーション環境を構築してみました。
レプリケーションに関しては、公式ドキュメントや、こちらの記事が大変参考になりました。

当記事では、バイナリログファイルの位置ベースでレプリケーションを実現し、
2つのMySQLサーバーを使ってソース/レプリカ構成を新規構築しています。

稼働中のMySQLサーバーにレプリケーションを構築する場合、ソースとレプリカの状態を一致させる必要があります。
そのため、ソース側のバックアップが別途必要になります。

環境

OS：MacOS Ventura 13.1
Dockerイメージ：mysql:8.0.32

1.全体像

.
├── conf
│   ├── replica
│   │   └── my.cnf
│   └── source
│       └── my.cnf
├── volumes
│   ├── replica
│   │   ├── db
│   │   └── log
│   └── source
│       ├── db
│       └── log
├── docker-compose.yml
├── Dockerfile

2.MySQLサーバの構築

Dockerfile

まず、Dockerfileを作成します。
レプリカ側の設定でソースを指定する必要があるため、IP確認用にhostnameコマンドをインストールしておきます。

あとは、文字コードをja_JP.UTF-8に指定してます。

FROM mysql:8.0.32

RUN microdnf update \
    && microdnf install -y hostname \
    && microdnf install -y glibc-locale-source \
    && microdnf install -y glibc-langpack-ja
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

Docker Compose

次にDockerfileを使ってコンテナを立ち上げます。
Docker Composeは下記の通りになります。

ソース、レプリカ用にMySQLサーバをそれぞれ立ち上げ、
設定ファイルであるmy.cnfを「/etc/mysql/conf.d/」に配置します。

あとは、データディレクトリとログをvolumesで外出ししています。

docker-compose.yml

version: "3"

networks:
  mysql-network:
    driver: bridge

services:

  source:
    container_name: mysql_source
    build:
      context: .
      dockerfile: Dockerfile
    volumes:
      - ./conf/source/:/conf
      - ./volumes/source/log/:/var/log/mysql/
      - ./volumes/source/db:/var/lib/mysql
    tty: true
    environment:
      TZ: 'Asia/Tokyo'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'source'
    command: >
      bash -c "
      cp /conf/my.cnf /etc/mysql/conf.d/ &&
      chmod 644 /etc/mysql/conf.d/my.cnf &&
      /entrypoint.sh mysqld"
    networks:
      - mysql-network

  replica:
    container_name: mysql_replica
    build:
      context: .
      dockerfile: Dockerfile
    volumes:
      - ./conf/replica/:/conf
      - ./volumes/replica/log/:/var/log/mysql/
      - ./volumes/replica/db:/var/lib/mysql
    tty: true
    environment:
      TZ: 'Asia/Tokyo'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'replica'
    command: >
      bash -c "
      cp /conf/my.cnf /etc/mysql/conf.d/ &&
      chmod 644 /etc/mysql/conf.d/my.cnf &&
      /entrypoint.sh mysqld"
    networks:
      - mysql-network

設定ファイル(my.cnf)

ソースとレプリカにそれぞれ設定ファイルを配置します。
設定内容のポイントは２つ。

サーバーID(server-id)
レプリケーション内の全MySQLサーバーのサーバーIDはユニークである必要があります。
そのため、それぞれにサーバーIDを割り振ります。

バイナリログ出力(log_bin)
バイナリログによるレプリケーションを構築するので、log_binにログファイルのシーケンスベース名を指定します。

設定ファイルの内容は以下のようになります。

my.cnf(source)

[mysqld]
# ・・・

# binary-log
log_bin = /var/log/mysql/mysql-bin
# server-id
server_id = 1

my.cnf(replica)

[mysqld]
# ・・・

# binary-log
log_bin = /var/log/mysql/mysql-bin
# server-id
server_id = 2

コンテナ起動

Docker Composeでコンテナを起動します。

docker-compose -f docker-compose.yml -p mysql-replication up

3.ソースとレプリカのIPアドレスを確認

ソースとレプリカのIPアドレスを確認しておきます。

ソースのIPアドレスは、レプリカ側でのレプリケーション設定、
レプリカのIPアドレスは、レプリカがソースに接続するためのユーザー作成で使用します。

% docker container exec -it mysql_source hostname -i
192.168.0.2
% docker container exec -it mysql_replica hostname -i
192.168.0.3

4.MySQLの状態を確認

TCP/IPの接続制御（skip_networking）

ソース・レプリカ間はTCP/IPで通信されます。
そのため、「skip_networking」でTCP/IPの接続が有効（OFF）になっていことを確認します。

(source) [(none)] > SHOW VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.04 sec)

その他、推奨設定

ドキュメントより、
InnoDBとトランザクションを使用したレプリケーション設定で永続性と一貫性を最大限に高めるため、
以下二つの設定が推奨されています。
・innodb_flush_log_at_trx_commit=1
・sync_binlog=1

こちらに関しては、Software Designにて丁寧に解説されていました。

推奨されている理由は、バイナリログとInnoDBのトランザクションログは別で管理されていることが起因しているようです。
InnoDBのトランザクションログは「innodb_flush_log_at_trx_commit」で書き込みタイミングを調整していますが、
バイナリログは「sync_binlog」で書き込みタイミングを制御しています。

そのため、それぞれを推奨設定にすることでトランザクションコミットの度にログ出力するようにしてタイミングを合わせます。

(source) [(none)] > SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.04 sec)
(source) [(none)] >
(source) [(none)] > SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

5.ソースのバイナリログとログ座標を確認

「SHOW」ステートメントで、バイナリログとログ座標を確認します。

(source) [(none)] > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Fileカラムにログファイルの名前が表示され、Positionカラムにファイル内のログ座標が表示されています。
こちらはレプリカ側のレプリケーション設定で必要なので控えておきます。

6.レプリケーション接続用のユーザーを作成

レプリカはMySQLのユーザー名とパスワードを使用してソースに接続します。
そのため、レプリカがソースへの接続できるユーザーアカウントを作成します。

ユーザーには、「REPLICATION SLAVE」権限を付与する必要があります。

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

7.レプリカ側でレプリケーション設定

ソースを指定

「CHANGE REPLICATION SOURCE TO」ステートメントでソースを指定します。
先に控えておいたソースのIPアドレス、通信用のユーザー、ソース側のバイナリログファイルとログ座標を指定します。

mysql> CHANGE REPLICATION SOURCE TO
     SOURCE_HOST='192.168.0.2',
     SOURCE_USER='replica',
     SOURCE_PASSWORD='replica',
     SOURCE_LOG_FILE='mysql-bin.000003',
     SOURCE_LOG_POS=157,
     GET_SOURCE_PUBLIC_KEY=1;

ハマった箇所

レプリケーション設定後、レプリカの状態を確認するとソースへの接続に失敗していました。
メッセージ内容は↓の通り。
「message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.」

(replica) [(none)] > SHOW SLAVE STATUS;
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State       | Master_Host  | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                                                                                     | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                  | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Connecting to source | 192.168.0.2 | repl        |        3306 |            60 | mysql-bin.000003 |                 157 | d47cc424594b-relay-bin.000001 |             4 | mysql-bin.000003      | Connecting       | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                   4 |             157 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          2061 | error connecting to master 'replica@192.168.0.3:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL | Replica has read all relay log; waiting for more updates |              86400 |             | 230225 15:49:53         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     0 |                   |
+----------------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.00 sec)

調べてみると、
MySQL8.0からのデフォルトでは、caching_sha2_passwordプラグインで認証されるユーザーを作成されます。
まだサーバー側にSHA2キャッシュが作られていないアカウント、かつサーバーの公開鍵を指定していない非SSLのTCP接続であることが原因でした。

そのため、「CHANGE REPLICATION SOURCE TO」ステートメントに「GET_SOURCE_PUBLIC_KEY=1」を指定して、ソースから公開鍵を取得するように指定して解消しました。

レプリケーションを開始

いよいよレプリケーションを開始します。
レプリケーションを開始するには「START SLAVE」ステートメントを実行します。

(replica) [(none)] > START SLAVE;

レプリケーションを止めたい場合は、「STOP SLAVE」で止めることも可能です。

レプリカの状態を確認

「SHOW SLAVE STATUS」ステートメントでレプリカの状態を確認します。

(replica) [(none)] > SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                  | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Waiting for source to send event | 192.168.0.2 | replica     |        3306 |            60 | mysql-bin.000003 |                 686 | ee12b4cd671d-relay-bin.000002 |           855 | mysql-bin.000003      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 686 |            1072 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | b8a164eb-b5c7-11ed-9060-0242c0a8b002 | mysql.slave_master_info |         0 |                NULL | Replica has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |                        |                     1 |                   |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.00 sec)

レプリケーション出来ていることを確認

最後にレプリケーション出来ていることを確認してみます。

ソース側でデータベースを作成してみます。

(source) [(none)] > CREATE DATABASE dbtest;
Query OK, 1 row affected (0.02 sec)

続いて、レプリカ側でデータベースが連携されていれば成功です。

(replica) [(none)] > SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dbtest             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

参考文献