目次
- 目次
- とある日
- レプリケーションとは
- やってみる
- レプリケーションエラー
- 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).
- 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のレプリケーション組んだことないな.....やってみよう。
ってことで、ただMySQL8.0でレプリケーション構成を作成する話です。
レプリケーションとは
レプリケーションを使用すると、ある MySQL データベースサーバー (ソースと呼ばれる) のデータを、複数の MySQL データベースサーバー (レプリカと呼ばれる) にコピーできます。 レプリケーションはデフォルトで非同期です。ソースから更新を受信するためにレプリカを永続的に接続する必要はありません。 構成に応じて、すべてのデータベース、選択したデータベース、さらにデータベース内の選択したテーブルを複製できます。
「レプリケーション=複製」
復数のサーバでデータのコピーを行うように構成することを指します。
メリット
- スケールアウトソリューション:複数のレプリカに負荷を分散して、パフォーマンスを向上
- データセキュリティ:レプリカはレプリケーションプロセスを一時停止できるため、対応するソースデータを破損させることなくレプリカでバックアップサービスを実行可能
- アナリティクス:ライブデータはソースで作成できますが、情報の分析はソースのパフォーマンスに影響を与えることなくレプリカで実行可能
- 長距離データ分散:レプリケーションを使用すると、ソースに永続的にアクセスせずに、リモートサイトで使用するデータのローカルコピーを作成可能
やってみる
↓以下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
バイナリロギングを有効にする
色々と参考にした記事や公式でも本手順が一番最初に来ています。
- ソースで、バイナリロギングが有効になっていることを確認し、一意のサーバー ID を構成する必要があります。 これには、サーバーの再起動が必要となる場合があります。 セクション17.1.2.1「レプリケーションソース構成の設定」を参照してください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2 バイナリログファイルの位置ベースのレプリケーションの設定
ですが、デフォルトで有効になっているので必要ありませんでした。
バイナリロギングはデフォルトで有効になっています (
log_binシステム変数は ON に設定されています)。
mysql> select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
一意なserver_id を構成する
システム変数を変更していきます。
各レプリカには、
server_idシステム変数で指定された一意のサーバー ID が必要です。
ソースサーバ
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)
レプリケーション用ユーザーを作成する
レプリカがソースにアクセスするためのユーザを作成します。
ソースにユーザを作成するので間違えないようにしましょう。（過去の自分へ）
- 必要に応じて、レプリケーション用のバイナリログを読み取るときに、ソースとの認証中にレプリカで使用する別のユーザーを作成します。 セクション17.1.2.3「レプリケーション用ユーザーの作成」を参照してください。
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)
ソースサーバロックする
同期を取るタイミングを合わせるためにロックします。
- データスナップショットを作成したり、レプリケーションプロセスを開始したりする前に、ソースで現在の位置をバイナリログに記録するようにしてください。 レプリカがイベントの実行を開始するバイナリログ内の場所を認識できるように、レプリカを構成するときにこの情報が必要になります。 セクション17.1.2.4「レプリケーションソースのバイナリログ座標の取得」を参照してください。
FLUSH TABLES WITH READ LOCK ステートメントを実行してすべてのテーブルおよびブロック書込みステートメントをフラッシュします。
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.02 sec)
次のステートメントを使用して、出力された値をコピーします。
show binary logs:サーバー上のバイナリログファイルを一覧表示します。- Log_nameの最新の名前コピーする
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.7.1 SHOW BINARY LOGS ステートメント
SHOW MASTER STATUS:ソースサーバーのバイナリログファイルに関するステータス情報を提供します。
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;
レプリカを構成する
準備が整ったので、レプリカをスタートさせます。
レプリカサーバで以下手順を実行します。
- ホスト名、ログイン資格証明、バイナリログファイルの名前と位置など、ソースに接続するための設定でレプリカを構成します。 セクション17.1.2.7「レプリカでのソース構成の設定」を参照してください。
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が発生しました。バイナリログのインデックスファイルに最初のログファイル名が見つかりませんでした'
ソースとレプリカで初期同期が取れていなかったのでソースでデータを更新した際にエラーがでました。
解決方法は、ソースサーバのデータをリセットしてからレプリケーションをスタートさせました。
- ソースサーバ:FLUSH TABLES WITH READ LOCKを実行
- ソースサーバ:SHOW MASTER STATUSを実行
- レプリカサーバ:stop replicaでレプリカ停止
- レプリカサーバ:reset replicaでクリーンスタート
- レプリカサーバ:CHANGE REPLICATION SOURCEを値を更新して実行
- レプリカサーバ: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 8.0 リファレンスマニュアル :: 17.1.2.4 レプリケーションソースのバイナリログ座標の取得
MySQL :: MySQL 8.0 リファレンスマニュアル :: 17.1.2.7 レプリカでのソース構成の設定
