今日はなにの日。

気になったこと勉強になったことのメモ。

トップ > データベース > 今日は、Docker Composeを使ってMySQL8.0のレプリケーションを構成してみるの日。

今日は、Docker Composeを使ってMySQL8.0のレプリケーションを構成してみるの日。

データベース SQL MySQL Docker docker-compose

目次

とある日

MySQLのレプリケーション組んだことないな.....やってみよう。

ってことで、ただMySQL8.0でレプリケーション構成を作成する話です。

レプリケーションとは

レプリケーションを使用すると、ある MySQL データベースサーバー (ソースと呼ばれる) のデータを、複数の MySQL データベースサーバー (レプリカと呼ばれる) にコピーできます。 レプリケーションはデフォルトで非同期です。ソースから更新を受信するためにレプリカを永続的に接続する必要はありません。 構成に応じて、すべてのデータベース、選択したデータベース、さらにデータベース内の選択したテーブルを複製できます。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17 レプリケーション

「レプリケーション=複製」

復数のサーバでデータのコピーを行うように構成することを指します。

メリット

  • スケールアウトソリューション:複数のレプリカに負荷を分散して、パフォーマンスを向上
  • データセキュリティ:レプリカはレプリケーションプロセスを一時停止できるため、対応するソースデータを破損させることなくレプリカでバックアップサービスを実行可能
  • アナリティクス:ライブデータはソースで作成できますが、情報の分析はソースのパフォーマンスに影響を与えることなくレプリカで実行可能
  • 長距離データ分散:レプリケーションを使用すると、ソースに永続的にアクセスせずに、リモートサイトで使用するデータのローカルコピーを作成可能

やってみる

↓以下MySQLドキュメントを見ながらやっていきます。（あまり親切ではなかったので公式以外の記事も参考にしました）

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17 レプリケーション

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定

環境

  • Docker Compose version v2.7.0
  • MySQL 8.0.30

ついでなので、MySQL最新バージョンでやってみます。

環境準備

docker composeを使ってソースとレプリカのMySQLを構築します。

レプリケーションの手順について理解したいので、Dockerfileは作成せず、一つ一つコマンド等を実行してやっていきます。

version: '3'
services:
  source_mysql:
    image: mysql:8.0.30-debian
    container_name: source_mysql
    ports:
      - "3306:3306"
    environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_DATABASE: test
        MYSQL_USER: docker
        MYSQL_PASSWORD: docker
  replica_mysql:
    image: mysql:8.0.30-debian
    container_name: replica_mysql
    ports:
      - "6612:3306"
    environment:
        MYSQL_ROOT_PASSWORD: root
    depends_on:
      - source_mysql

準備ができたら起動させて、ソースサーバに接続します。

docker-compose up -d
docker-compose exec source_mysql bash

起動確認

PS C:\Users> docker ps
CONTAINER ID   IMAGE                  COMMAND                  CREATED         STATUS         PORTS                               NAMES
d00873e6e15a   mysql:8.0.30-debian    "docker-entrypoint.s…"   2 hours ago     Up 3 seconds   33060/tcp, 0.0.0.0:6612->3306/tcp   replica_mysql
b3b20a871fba   mysql:8.0.30-debian    "docker-entrypoint.s…"   2 hours ago     Up 3 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   source_mysql
faaacf261e1d   kindest/node:v1.21.1   "/usr/local/bin/entr…"   11 months ago   Up 34 hours    127.0.0.1:40115->6443/tcp           kind-control-plane

バイナリロギングを有効にする

色々と参考にした記事や公式でも本手順が一番最初に来ています。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定

ですが、デフォルトで有効になっているので必要ありませんでした。

バイナリロギングはデフォルトで有効になっています (log_bin システム変数は ON に設定されています)。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.4.4 バイナリログ

mysql>  select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

一意なserver_id を構成する

システム変数を変更していきます。

各レプリカには、server_id システム変数で指定された一意のサーバー ID が必要です。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.2 レプリカ構成の設定

ソースサーバ

server-id = 1001適当な値を設定します。

docker環境でviが使えなかったのでvimで編集しました。

apt-get update
apt-get install vim

/etc/mysql/my.cnfに追加します。

root@b3b20a871fba:/# cat /etc/mysql/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id = 1001

Docker Desktopでボタン押して再起動しました。

再起動して値を確認します。

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1001  |
+---------------+-------+
1 row in set (0.01 sec)

レプリカサーバ

ソースサーバと同じように設定します。

もちろん値は変える必要があるので変えてます。

root@d00873e6e15a:/# cat /etc/mysql/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id = 2

確認します。

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.05 sec)

レプリケーション用ユーザーを作成する

レプリカがソースにアクセスするためのユーザを作成します。

ソースにユーザを作成するので間違えないようにしましょう。（過去の自分へ）

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定

MySQL8.0だとデフォルト認証が変わっているので明示的にmysql_native_passwordを一応指定しています。

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

実行

mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.04 sec)

ソースサーバロックする

同期を取るタイミングを合わせるためにロックします。

FLUSH TABLES WITH READ LOCK ステートメントを実行してすべてのテーブルおよびブロック書込みステートメントをフラッシュします。

mysql>  FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

次のステートメントを使用して、出力された値をコピーします。

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3005196 | No        |
| binlog.000002 |       180 | No        |
| binlog.000003 |       878 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      878 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

コピーした値はレプリカで接続する際に使用します。

ついでに、ロックしたテーブルを解除するステートメントは以下です。

UNLOCK TABLES;

レプリカを構成する

準備が整ったので、レプリカをスタートさせます。

レプリカサーバで以下手順を実行します。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定

構成する前にソースのIPアドレスが必要なので取得します。

mysqlreplication_defaultの値はそれぞれの環境で異なるので変更ください。

b3b20a871fbaの値はdocker psで表示されるCONTAINER IDです。

PS C:\Users> docker inspect -f '{{.NetworkSettings.Networks.mysqlreplication_default.IPAddress}}' b3b20a871fba
172.27.0.2

取得した値を使用してステートメントを構成します。

  • SOURCE_HOST:ソースとするサーバのIPアドレス
  • SOURCE_USER:作成したユーザ名
  • SOURCE_PASSWORD:作成したユーザのパスワード
  • SOURCE_LOG_FILE:show binary logsで取得した最新のLog_nameの値を指定
  • SOURCE_LOG_POS:SHOW MASTER STATUSで取得したPositionの値を設定
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='172.27.0.2',
SOURCE_USER='repl',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='binlog.000003',
SOURCE_LOG_POS=1484;

実行ができたら、start replicaでレプリカを開始します。

mysql> start replica;
Query OK, 0 rows affected (0.13 sec)

レプリケーション動作確認する

動作確認はshow replica statusを実行します。

ここまで順調にできていれば以下の結果になると思います。

mysql> show replica status \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.27.0.2
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000003
          Read_Source_Log_Pos: 878
               Relay_Log_File: d00873e6e15a-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000003
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 878
              Relay_Log_Space: 540
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1001
                  Source_UUID: bf099b00-1619-11ed-b895-0242ac1b0002
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.03 sec)

SQL実行してみる

↓のSQLを実行してソースとレプリカでレプリケーションが構築されていることを確認します。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.19 InnoDB と MySQL レプリケーション

ソースサーバでのSQL実行

mysql> create database test;
Query OK, 1 row affected (0.10 sec)

mysql> use test
Database changed
mysql> CREATE TABLE fc1 (
    ->     i INT PRIMARY KEY,
    ->     j INT
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE TABLE fc2 (
    ->     m INT PRIMARY KEY,
    ->     n INT,
    ->     FOREIGN KEY ni (n) REFERENCES fc1 (i)
    ->         ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.26 sec)

mysql>  INSERT INTO fc1 VALUES (1, 1), (2, 2);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  SELECT * FROM fc1;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.05 sec)

mysql> SELECT * FROM fc2;
+---+------+
| m | n    |
+---+------+
| 1 |    1 |
| 3 |    1 |
| 2 |    2 |
+---+------+
3 rows in set (0.07 sec)

レプリカサーバで確認

Database changed
mysql>  SELECT * FROM fc1;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.04 sec)

データが有ったので、複製が確認できました。

レプリケーションエラー

起こったエラーについてまとめます。

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

mysql> show replica status \G;
*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 172.27.0.2
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: source1-bin.001
          Read_Source_Log_Pos: 878
               Relay_Log_File: d00873e6e15a-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: source1-bin.001
           Replica_IO_Running: No
          Replica_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_Source_Log_Pos: 660
              Relay_Log_Space: 157
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1001
                  Source_UUID: bf099b00-1619-11ed-b895-0242ac1b0002
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp: 220807 07:23:19
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.03 sec)

ERROR:
No query specified

↓翻訳したもの

致命的なエラーです。マスターとスレーブのMySQLサーバーIDが等しいため、スレーブのI/Oスレッドが停止する。レプリケーションが機能するには、これらのIDが異なる必要がある（または、スレーブで --replicate-same-server-id オプションを使用する必要があるが、これは必ずしも意味をなさない。）

ソースサーバのIPアドレスを指定したつもりが、レプリカサーバを指定していたため自己参照となりサーバIDが重複した。

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.27.0.2
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000003
          Read_Source_Log_Pos: 1096
               Relay_Log_File: d00873e6e15a-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000003
           Replica_IO_Running: Yes
          Replica_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1049
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1096. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 878
              Relay_Log_Space: 758
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1049
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1096. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1001
                  Source_UUID: bf099b00-1619-11ed-b895-0242ac1b0002
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 220807 07:40:36
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.02 sec)

↓翻訳したもの

バイナリログからデータを読み込む際に、マスターから致命的なエラー1236が発生しました。バイナリログのインデックスファイルに最初のログファイル名が見つかりませんでした'

ソースとレプリカで初期同期が取れていなかったのでソースでデータを更新した際にエラーがでました。

解決方法は、ソースサーバのデータをリセットしてからレプリケーションをスタートさせました。

  1. ソースサーバ:FLUSH TABLES WITH READ LOCKを実行
  2. ソースサーバ:SHOW MASTER STATUSを実行
  3. レプリカサーバ:stop replicaでレプリカ停止
  4. レプリカサーバ:reset replicaでクリーンスタート
  5. レプリカサーバ:CHANGE REPLICATION SOURCEを値を更新して実行
  6. レプリカサーバ:start replicaでレプリカ実行

教訓

  • それ以降のデータはコピーされるがそれ以前はコピーされない
  • slaveからreplicaに変わっている（エイリアスが作成されている）
  • ソースとレプリカのどちらで必要な作業なのか把握する

〆

これから、この環境で色々と遊ぼうかなと思ってます。

準同期レプリケーションや、レプリケーションでできることのetc...を試してみたいなと思います。

https://twitter.com/mysql_jp/status/1552847630715105282?s=20&t=rYf_4ZdQj9jzlGJWezyNLA

参考記事

https://twitter.com/mysql_jp/status/1552847630715105282?s=20&t=e-Cdf5yf0KD_wwqEGWMOug

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17 レプリケーション

【docker-composeでMysqlのMaster/Slave構成を作ってみた】 - Qiita

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.2 レプリケーションの実装

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.1 レプリケーションソース構成の設定

MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.4.4 バイナリログ

MySQL | 認証プラグインを指定してユーザーを作成する

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.4 レプリケーションソースのバイナリログ座標の取得

MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.7 レプリカでのソース構成の設定

mysqlのレプリケーションを使う(5) エラー1236の対処 | レンタルサーバー・自宅サーバー設定・構築のヒント

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' - Qiita