お金をかけずにサーバーの勉強をしよう - MySQLのレプリケーション -

MySQLのデータベースのデータをリアルタイムでコピってレプリカデータベースを作ります。

「MySQLインストール」で作った MySQLデータベースのレプリカを別ノードにある MySQLに作ります。

この作業の事を「レプリケーション」って言いますね。

冗長化と負荷分散のためにやることなのでこれ単品で設計するものではないのですが、設定自体は簡単なので覚えておきましょう。

レプリケーションのマニュアルはこちら。(今はまだ英語のしかありません)
Chapter 19 Replication

基本的にこれに沿って設定するんですが、多分最初はサッパリ訳が分からないでしょうが「実践が先 / 理論は後」で良いと思います。

１．環境

ここではオーソドックスに片方がソースノードでもう片方がデスティネーションノードとなる 2台構成にします。

役割	マシン名
ソースノード	UbuntuServer2404-2
デスティネーションノード	UbuntuServer2404-3

両方とも Ubuntu Server 24.04に MySQL 8.4をインストールしています。

Ubuntu Server 24.04のインストールは「Ubuntu 24.04 Server インストール」に、 MySQL 8.4のインストールとDB作成は「MySQLインストール」に書いています。

ソースノードは「MySQLインストール」で作った環境そのままで、中に [subrotest]データベースがある状態になっています。

デスティネーションノードも同様に作っておきます。

各々の関係はこのような感じです。
関係図

２．ソースノード設定

ソースノードで実行されたDBの更新内容が書かれた「バイナリログ」というデータをデスティネーション側で取り込み、ソースノードの更新をデスティネーションでなぞることによってデータが同じになるという寸法です。
Oracleの Data Guardと同じ考え方ですね。

デスティネーションノードの MySQLからソースノードの MySQLを見られるように、ソースノードにレプリケーション専用のユーザーを作り、そのユーザーにレプリケーション権限を与えます。

ソースノードの [UbuntuServer2404-2]サーバーでの作業です。

subro@UbuntuServer2404-2:~$ mysql -u root -p
Enter password: MySQLの[root]ユーザーのパスワードを入れる
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'replication'@'UbuntuServer2404-3' identified by 'Abcd1234-';　　← [UbuntuServer2404-3]サーバーからのみ利用することができるようになります。
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replication'@'UbuntuServer2404-3';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

次は MySQL同士でお互いを別な存在と認識できるよう「サーバーID」という通し番号を付けるのと、デフォルトではONになっていないバイナリログ出力をONにします。

[/etc/mysql/mysql.conf.d/mysqld.cnf]ファイルの最後に以下の 2行を追加します。

server-id=1
log-bin=replication-bin

server-id	レプリケーションに関わる MySQL間で一意に付ける番号。 1 から 4294967295まで初期値は 1
log-bin	バイナリログファイルを作る名前は任意初期値は「ホスト名-bin」付けるの推奨

MySQLを再起動します。

subro@UbuntuServer2404-2:~$ sudo systemctl restart mysql

何も出ませんが、再起動できています。

再起動が済むとデータディレクトリ下に設定したバイナリログファイルができていました。

subro@UbuntuServer2404-2:~$ sudo ls -l /var/lib/mysql
合計 103532
-rw-r----- 1 mysql mysql  6291456  5月 12 20:11 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 14680064  5月  7 12:06 '#ib_16384_1.dblwr'
drwxr-x--- 2 mysql mysql     4096  5月 12 20:09 '#innodb_redo'
drwxr-x--- 2 mysql mysql     4096  5月 12 20:09 '#innodb_temp'
-rw-r----- 1 mysql mysql       56  5月  7 12:06  auto.cnf
-rw-r----- 1 mysql mysql      507  5月  7 12:06  binlog.000001
-rw-r----- 1 mysql mysql     1852  5月  7 13:29  binlog.000002
-rw-r----- 1 mysql mysql      181  5月  7 20:16  binlog.000003
-rw-r----- 1 mysql mysql      181  5月  8 11:22  binlog.000004
-rw-r----- 1 mysql mysql     3271  5月  8 16:48  binlog.000005
-rw-r----- 1 mysql mysql     1289  5月 12 20:09  binlog.000006
-rw-r----- 1 mysql mysql       96  5月 12 19:23  binlog.index
-rw------- 1 mysql mysql     1709  5月  7 12:06  ca-key.pem
-rw-r--r-- 1 mysql mysql     1108  5月  7 12:06  ca.pem
-rw-r--r-- 1 mysql mysql     1108  5月  7 12:06  client-cert.pem
-rw------- 1 mysql mysql     1705  5月  7 12:06  client-key.pem
-rw-r----- 1 mysql mysql     3617  5月 12 20:09  ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912  5月 12 20:09  ibdata1
-rw-r----- 1 mysql mysql 12582912  5月 12 20:09  ibtmp1
drwxr-x--- 2 mysql mysql     4096  5月  7 12:06  mysql
-rw-r----- 1 mysql mysql 26214400  5月 12 20:09  mysql.ibd
-rw-r----- 1 mysql mysql      124  5月  7 12:06  mysql_upgrade_history
drwxr-x--- 2 mysql mysql     4096  5月  7 12:06  performance_schema
-rw------- 1 mysql mysql     1705  5月  7 12:06  private_key.pem
-rw-r--r-- 1 mysql mysql      452  5月  7 12:06  public_key.pem
-rw-r----- 1 mysql mysql      158  5月 12 20:09  replication-bin.000001
-rw-r----- 1 mysql mysql       25  5月 12 20:09  replication-bin.index
-rw-r--r-- 1 mysql mysql     1108  5月  7 12:06  server-cert.pem
-rw------- 1 mysql mysql     1705  5月  7 12:06  server-key.pem
drwxr-x--- 2 mysql mysql     4096  5月  8 16:21  subrotest
drwxr-x--- 2 mysql mysql     4096  5月  7 12:06  sys
-rw-r----- 1 mysql mysql 16777216  5月 12 20:11  undo_001
-rw-r----- 1 mysql mysql 16777216  5月 12 20:11  undo_002

とりあえず、ソースノードはこれで置いておきます。

２.デスティネーションノードの設定をする

これは [UbuntuServer2404-3]サーバーの作業になります。

[/etc/mysql/mysql.conf.d/mysqld.cnf]ファイルに以下を追加します。

server-id=2
read_only

server-id	ソースが 1に対して、こちらは 2にしました。
read_only	レプリカなのでこちらで更新してはダメにしました

MySQLを再起動します。

subro@UbuntuServer2404-3:~$ sudo systemctl restart mysql

やっぱり何も出ませんが、再起動できています。

これでセッティングは完了です。

３.レプリ前にソース・デスティネーション間でデータを同じにする

レプリケーションは更新を逐次反映させていくものなので、ソース・デスティネーションのデータが同じ状態になっているところからスタートさせないといけません。

ソースノードで mysqldumpコマンドを使って作ったバックアップファイルをデスティネーションノードに適用して両者のデータの同期を行っておきます。

ソースノード側のバックアップをします。
[subrotest]データベースが対象で、中には [subrotesttable]テーブルが 1つだけ存在しています。
コマンドで以下をしています。

subro@UbuntuServer2404-2:~$ mysqldump -u root -p --flush-logs --lock-all-tables --databases subrotest > source.sql
Enter password: MySQLの[root]ユーザーのパスワードを入れる

オプション	機能
-u	MySQLのユーザー名
-p	パスワード(インタラクティブにしています)
--flush-logs	メモリ上の更新内容をディスクに反映させる
--lock-all-tables	バックアップ中に全テーブルをロックしてデータ更新をさせない
--databalses	バックアップ対象データベース指定

バックアップファイルができました。

subro@UbuntuServer2404-2:~$ ls -l source.sql
-rw-rw-r-- 1 subro subro 2119  5月 12 20:18 source.sql

scpコマンドでデスティネーションノードにファイルをコピりました。

デスティネーションノードで取り込みます。

subro@UbuntuServer2404-3:~$ mysql -u root -p < source.sql
Enter password: MySQLの[root]ユーザーのパスワードを入れる

何も出ませんが、上手く行っているようです。
テーブルが 1つ、その中に 1件のデータがあるはず。

確認してみます。

subro@UbuntuServer2404-3:~$ mysql -u root -p
Enter password: MySQLの[root]ユーザーのパスワードを入れる
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use subrotest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from subrotesttable;
+--------+
| name   |
+--------+
| Tanaka |
+--------+
1 row in set (0.00 sec)
終わらせないでこのままにしておきます。

想定通りの結果になりました。

データの同期は完了です。

４.レプリケーション開始

ソースノードで延々と続くバイナリログのうちどの位置まで同期が済んでいるかを確認します。

ssubro@UbuntuServer2404-2:~$ mysql -my -p
〜〜〜　略　〜〜〜
mysql> SHOW BINARY LOG STATUS;　　← 以前と変数名が変わってるので注意。
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| replication-bin.000002 |      158 |              |                  |                   |　　← この内容を覚えておきます。
+------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

＝＝＝＝＝ここからは分かってないですがとりあえずできたので＝＝＝＝＝
8.04からユーザー認証プラグインのデフォルトが [mysql_native_password] から [caching_sha2_password] に変わっています。

今回レプリケーションのためにソースノードに作った MySQLの [replication]ユーザーの認証もこれで行われます。

8.0の時と同じように start replicaコマンドを実行したところ、[/var/log/mysql/error.log]ファイルに、以下のエラーが出てレプリケーションができませんでした。

2024-05-12T12:19:07.176099Z 19 [ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'replication@UbuntuServer2404-2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

デスティネーションノードからソースノードにアクセスする時に、パスワードが平文のまま回線を流れていってしまうのを防ぐために、SSL/TLS通信にしなさいということのようです。

しかし調べていくうちに、[caching_sha2_password] は暗号化されたパスワードをメモリにキャッシュしているので、SSL/TLS通信できるクライアントプログラムので一度接続してパスワードをキャッシュさせてやると OKになるそうな。

これには以下のブログを参考にさせて頂きました。
MySQL8.0を再起動するとアプリからつながらなくなる理由

それで、作業的にはこの時点で一度デスティネーションノードの [UbuntuServer2404-3]サーバーから mysqlコマンドでソースノードの MySQLに [replication]ユーザーでログインだけしてやってから以降の作業をすると、確かにレプリケーションされるようになりました。

全部の検証が終わってからですが「でもメモリのキャッシュ内容が無くなったらまたダメになっちゃうのでは？」と不安になり、試しにソースノードの MySQLを再起動してみたのですが、レプリケーションは継続して行われていました。

この点気になってドキュメントを調べて、該当箇所を見つけました。
観念を理解するのに英語だと辛かったので Ver.8.0の日本語版ドキュメントのものです。
6.4.1.2 SHA-2 プラガブル認証のキャッシュ

こちらの「SHA-2 プラガブル認証のキャッシュ操作」という段落に以下の記述がありますので引用します。

キャッシュのクリア操作は、後続のクライアント接続の認証要件に影響します。ユーザーアカウントごとに、次のいずれかの操作後のユーザーの最初のクライアント接続では、セキュアな接続 (TLS 資格証明、Unix ソケットファイルまたは共有メモリーを使用して TCP を使用して確立) または RSA キーペアベースのパスワード交換を使用する必要があります:

アカウントの作成後。

アカウントのパスワード変更後。

アカウントの RENAME USER の後。

FLUSH PRIVILEGES の後。

FLUSH PRIVILEGES はキャッシュ全体をクリアし、caching_sha2_password プラグインを使用するすべてのアカウントに影響します。その他の操作では、特定のキャッシュエントリがクリアされ、操作の一部であるアカウントにのみ影響します。

ユーザーが正常に認証されると、アカウントに影響する別のキャッシュクリアイベントが発生するまで、アカウントはキャッシュに入力され、後続の接続にセキュアな接続または RSA キーペアは必要ありません。 (キャッシュを使用できる場合、サーバーはクリアテキストパスワード転送を使用せず、セキュアな接続を必要としないチャレンジレスポンスメカニズムを使用します。)

何となく分かったものの、運用上は問題が出そうな気も…。

でも本当は公開鍵を使わせるのが正しいやり方なんでしょうけど、LAN内で使うだけの場合はどうしよう…。
＝＝＝＝＝ここまで＝＝＝＝＝

デスティネーションノードに以下の情報を設定します。
上で覚えておいた情報を利用します。

ソースノードのマシン名か IPアドレス
レプリケーション開始時のバイナリログファイルの名前
バイナリログ内のレプリケーション開始位置

mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='UbuntuServer2404-2',SOURCE_LOG_FILE='replication-bin.000002',SOURCE_LOG_POS=158;　　← 以前とオプション名が変わってるので注意。
Query OK, 0 rows affected (0.02 sec)

レプリケーションを開始します。

mysql> START REPLICA USER='replication' password='Abcd1234-';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

※[change] の時に user/password を設定することもできるのですが、実際にやってみると「セキュアでない」と怒られます。
設定した値は見えてしまうからですかね。
[start] の方でやるとこの問題を回避できるようです。

これでレプリケーションが開始されているはずです。

デスティネーション側の MySQLのログを見てみると

subro@UbuntuServer2404-3:~$ tail /var/log/mysql/error.log
〜〜〜　略　〜〜〜
2024-05-12T13:19:54.919823Z 23 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'replication@UbuntuServer2404-2:3306' with server_uuid=d241e34d-0c1e-11ef-955d-000c29fa645f, server_id=1. Starting replication from file 'replication-bin.000002', position '158'.

開始されているっぽいです。

ソースノードで [subrotesttable]テーブルにデータを挿入してみます。
(ログイン作業は省略します)

mysql> use subrotest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> insert into subrotesttable values('Suzuki');
Query OK, 1 row affected (0.01 sec)

mysql> select * from subrotesttable;
+--------+
| name   |
+--------+
| Tanaka |
| Suzuki |
+--------+
2 rows in set (0.00 sec)

データが挿入されました。

デスティネーションノードに跳ねているでしょうか。

mysql> use subrotest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from subrotesttable;
+--------+
| name   |
+--------+
| Tanaka |
| Suzuki |
+--------+
2 rows in set (0.00 sec)

跳ねています！　＼(^o^)／
データのレプリケーションができています。

＝＝＝＝＝＝＝＝＝＝
ポリコレ対応でソース側を表す言葉が「source」になるのかと思いきや、そうでもなくハマりました。

英語マニュアルをさらってやっと「BINARY LOG」って文言を見つけましたです。

某用語自体がヤバくて、マニュアルに分かりやすい用語対比表を書くのも無理なんですかねぇ。

とか言いながら GRANT文ではまだ使ってますし…。

このページで書いているレプリケーションのやり方は非同期ですので、ソースとデスティネーション間にタイムラグが出ます。
また、障害のタイミングによってはソース/デスティネーション間でデータの差異が出ることがあります。

デスティネーションにデータが同期されてからソース側のデータが書き換わる安全なやり方もありますが、処理が遅くなります。

安全性と速度はトレードオフの関係にあるので、どれがベストとか正しいとか言うことは無く、どういう使い方をしたいのかによって設計が変わってきますね。

MySQLでのレプリケーションは複雑そうでいて、やってみるとかなりシンプルな事が分かります。

OSSのデータベースでこんな事ができてしまうのですから、使わない手はないですね。

当ページは、とほほさんのブログを参考にさせて頂きました。
MySQL/MariaDBレプリケーション

2024年5月22日に出るようです。