[初心者向け] MySQL でバイナリログレプリケーションしてみた on EC2

[初心者向け] MySQL でバイナリログレプリケーションしてみた on EC2

MySQL の勉強のため、EC2上の mysql にてバイナリログレプリケーションをやってみました。
#MySQL
すながわ

すながわ

facebook logohatena logotwitter logo
Clock Icon2024.08.12

はじめに

業務でバイナリログレプリケーションについて触れる機会があったため、試してみます。
RDS を普段触っていますが、データベースに関してバリバリの初心者です。なので今回は勉強のため EC2 に mysql を入れてみて、そこでレプリケーションの設定を行いました。
※ mysql のインストール手順については触れておりませんため、他ブログを参照してください。

構成

  • パブリックサブネットに2台の EC2 インスタンス(Amazon Linux 2023)を立て、MySQL をインストール
  • 使用した mysql バージョンは 8.0.39
mysql> status;
--------------
mysql  Ver 8.0.39 for Linux on x86_64 (MySQL Community Server - GPL)
  • プライマリとセカンダリEC2のセキュリティグループを示します。基本的にアウトバウンドはデフォルトの全て許可、インバウンドはローカルPCからの接続に必要なSSHポートと、インスタンス同士でmysqlサーバーがやり取りするためにセキュリティグループIDを指定し、許可設定を行っています。
# プライマリのセキュリティグループ
$ aws ec2 describe-security-groups --group-ids <プライマリEC2のSG>
...
            "IpPermissions": [
                {
                    "FromPort": 22,
                    "IpProtocol": "tcp",
                    "IpRanges": [
                        {
                            "CidrIp": "<My IP>/32"
                        }
                    ],
                    "Ipv6Ranges": [],
                    "PrefixListIds": [],
                    "ToPort": 22,
                    "UserIdGroupPairs": []
                },
                {
                    "FromPort": 3306,
                    "IpProtocol": "tcp",
                    "IpRanges": [],
                    "Ipv6Ranges": [],
                    "PrefixListIds": [],
                    "ToPort": 3306,
                    "UserIdGroupPairs": [
                        {
                            "GroupId": "<セカンダリEC2のセキュリティグループID>",
                          ...
                        }
                    ]
                }
            ],    
...

# セカンダリのセキュリティグループ
$ aws ec2 describe-security-groups --group-ids <セカンダリEC2のSG>
...
            "IpPermissions": [
                {
                    "FromPort": 22,
                    "IpProtocol": "tcp",
                    "IpRanges": [
                        {
                            "CidrIp": "<My IP>/32"
                        }
                    ],
                    "Ipv6Ranges": [],
                    "PrefixListIds": [],
                    "ToPort": 22,
                    "UserIdGroupPairs": []
                },
                {
                    "FromPort": 3306,
                    "IpProtocol": "tcp",
                    "IpRanges": [],
                    "Ipv6Ranges": [],
                    "PrefixListIds": [],
                    "ToPort": 3306,
                    "UserIdGroupPairs": [
                        {
                            "GroupId": "<プライマリEC2のセキュリティグループID>",
                            ...
                        }
                    ]
                }
            ]
...

やってみた

以下、参考にしたブログです。基本的にこのブログに沿って進めました。やり方が手順分けされており、また各種手順における設定の意味なども記載されています。すごくわかりやすく助かりました。
https://envader.plus/course/10/scenario/1096
一方で、自分の環境でやろうとすると思わぬところに落とし穴があってハマった部分もあったため、まとめます。

構築手順は上記ブログの通り 5 つあります。

  1. プライマリ上でレプリケーション化のための設定を行う。
  2. プライマリ上でレプリケーションに接続する専用のユーザを作る。
  3. プライマリからフルバックアップを取得する。
  4. セカンダリ上でレプリケーション化のための設定を行う。
  5. セカンダリ上でマスターとなるMySQLを指定する。

1.プライマリ上でレプリケーション化のための設定を行う。

my.cnf に以下の設定を書くことがステップ1のゴール。

[mysqld]
server-id=1
log-bin=mysql-bin-hoge

※上記のlog-binパラメータは、mysql-bin-hoge と適当な名前にしていますが、これはバイナリログファイルのファイル名になるっぽいのでわかりやすくそうしました。

バイナリログファイルのベース名を指定するには、--log-bin[=base_name]オプションを使用します。 --log-bin オプションを指定しない場合、MySQL はバイナリログファイルのデフォルトのベース名として binlog を使用します。
https://dev.mysql.com/doc/refman/8.0/ja/binary-log.html

そのため、まずは自分の環境において my.cnf がどこにあるか探す。help オプションを付けるとデフォルトでどこから読み込まれているか表示される。

$ mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
...

ちなみに grep ですぐ探せます。

$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

my.cnf は複数ファイルで設定されている場合、後勝ちで読み込まれる。(参考：日々の覚書: my.cnfのパラメータ優先順位)
上記結果を見る限り、以下の順序で読み込まれていることがわかる。

1. /etc/my.cnf 
2. /etc/mysql/my.cnf 
3. /usr/etc/my.cnf 
4. ~/.my.cnf

上記のどの my.cnf に設定を入れたらいいのかわからなかったため、以下のように力任せで調べた。

$ sudo cat /etc/my.cnf
...
[mysqld]
...
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

$ sudo cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: No such file or directory
$ sudo cat /usr/etc/my.cnf
cat: /usr/etc/my.cnf: No such file or directory
$ sudo cat ~/.my.cnf
cat: /home/ec2-user/.my.cnf: No such file or directory

デフォルトでは、/etc/my.cnf しか存在しなかったため、ここに設定を入れた。

$ sudo cat /etc/my.cnf
[mysqld]
...
server-id=1
log-bin=mysql-bin-hoge

変更後は以下コマンドで再起動。(ここはそれぞれの環境においてやり方は異なると思う。）

$ sudo systemctl restart mysqld

2. プライマリ上でレプリケーションに接続する専用のユーザを作る。

プライマリ側 EC2 インスタンスの mysql サーバーにログインして、レプリケーション用のユーザーを作成する。

まず、ルートユーザーでログイン。

$ mysql -u root -p

ちなみにルートユーザーでログインする際に使用するパスワードはMySQL公式ドキュメントのインストールマニュアル(Linux版)に従い、以下のように探した。

$ sudo grep 'temporary password' /var/log/mysqld.log
2024-07-28T02:22:05.010999Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <temp pass>

ログイン後は、念の為ルートユーザーのパスワードを変更しておく。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sunaGawa!000';

その後、レプリケーション用のユーザーを作成。

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'replPass!000';

ちゃんと作成できているか確認。

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| repl             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

作成したユーザー repl の現在の権限を確認。

mysql> SHOW GRANTS FOR repl;
+--------------------------------------+
| Grants for repl@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `repl`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)

下記記事によると、上記の結果は何も権限が無い状態のよう。

GRANT USAGE ON . TO ユーザー名 と表示されました。これはグローバルレベルで USAGE という権限が設定されていることを示しています。この USAGE という権限は「何も権限がない」という権限です。つまり新しく作成したユーザーは、すべてのデータベースを対象に何も権限がない状態となっています。
MySQL | ユーザーに権限を設定する(GRANT文)

そのため、作成したユーザー repl にレプリケーション用の権限を付与する。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

変更を反映させる。

mysql> FLUSH PRIVILEGES;

ステップ4でプライマリのフルバックアップをセカンダリ側に反映させるが、そこでちゃんと反映できているかの確認のため、今時点で適当なデータベースとして test_db を作っておく。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

3. プライマリからフルバックアップを取得する。

プライマリ mysql サーバーがある EC2 上で以下を実行し、フルバックアップファイルを取得。

$ mysqldump -u root -p --all-databases --master-data > dump.sql

フルバックアップをセカンダリ側の EC2 インスタンスへ転送する。
そのため、プライマリ側にある dump.sql を一度自分のローカルPCに持ってきて、それをセカンダリ側EC2にアップロードする手順を踏む。
(プライマリからセカンダリに scp で直接送るにはセキュリティグループとか秘密鍵とか色々考慮しないといけなかったため、このやり方にした。もっといい方法ありそう。)

プライマリEC2 にある dump.sql をローカルPCへ落とす。(ローカルPCでコマンド実行。)

$ scp -P 22 -i <秘密鍵> ec2-user@<プライマリEC2のパブリックIP>:/home/ec2-user/dump.sql .

それをローカルPCからセカンダリ側EC2へアップロード。(ローカルPCでコマンド実行。)

$ scp -P 22 -i <秘密鍵> dump.sql ec2-user@<レプリカ側EC2のパブリックIP>:/home/ec2-user/

セカンダリ側に送られていればステップ3はOKです。

4. セカンダリ上でレプリケーション化のための設定を行う。

セカンダリ側EC2の my.cnf に以下の設定を追加し、リスタートする。(詳しいやり方は前述のステップ1を参照)

$ sudo cat /etc/my.cnf
[mysqld]
...
server-id=2

先ほどプライマリからセカンダリへ転送した dump.sql ファイルをセカンダリにて反映させる。FLUSH もしておく。

mysql> source /home/ec2-user/dump.sql
mysql> FLUSH PRIVILEGES;

ちなみに本当にちゃんと反映されているのか念の為確認。step2の最後に作成した、test_db データベースが見えるからOK。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

ここで、少し補足。
自分の環境だとフルバックアップをセカンダリへ反映させて、その後セカンダリ側のmysqlサーバーからログアウト、再度ログインしようとしたら、パスワード間違いのエラーとなり、ログインすることができなかった。
これまでセカンダリ側mysqlへは、(本ブログでは言及してなかったけれど)root ユーザーでパスワード "Replica!123" で入っていたのだが、プライマリのフルバックアップを反映させた後は、それができなかった。
もしやと思い、プライマリ側で使っていた "sunaGawa!000" のパスワードを使うと root ユーザーでセカンダリへログインできたので、同じような人がいたらプライマリの時のパスワード使うといいかも。これこういう仕様なのかな。

5. セカンダリ上でマスターとなるMySQLを指定する。

いよいよ最後のステップ。ここが一番大変だった。ただなんとかできたので、以下レポートする。

セカンダリ側に転送されてきた dump.sql を grep し、バイナリログファイル名とその位置を確認する。

$ grep "^CHANGE MASTER" dump.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-hoge.000005', MASTER_LOG_POS=856;

ちなみに、上の値が本当に合っているのか気になり、プライマリ側の mysql で以下のように実行したら、見事に一致した。しかも、ログファイル名が mysql-bin-hoge.[拡張子] になっており、ステップ1で設定した内容がちゃんと反映されていることがわかる。

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-hoge.000005 |      856 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

セカンダリのmysqlインスタンスへログインし、セカンダリから接続するプライマリの情報を設定する。

mysql> CHANGE MASTER TO MASTER_HOST="<プライマリEC2のプライベートIP>",MASTER_USER="repl",MASTER_PASSWORD="replPass!000",MASTER_LOG_FILE="mysql-bin-hoge.000005",MASTER_LOG_POS=856;

レプリケーションをスタートする。

mysql> START SLAVE;

スタート後の現在のセカンダリの状態を念の為確認。エラーが出ていた。

mysql> show slave status;
...
Error connecting to source 'repl@<プライマリEC2のプライベートIP>:3306'. This was attempt 14/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

一旦上記エラーを無視して、動作確認してみたが、プライマリ側で新規テーブルを作ってみてもうまくセカンダリへレプリケートされていなかった。
そのため、まず本エラーを解消する。ちなみにエラー文に記載の caching_sha2_password についてはAIの回答がわかりやすかったので、転記する。(情報の確実性についてはともかく、ざっくり学ぶためにAIを使うのはありかもしれないと思った。ちょっと感動したので転記した。)

Q. caching_sha2_password とは何ですか？ 小学生でもわかるように教えてください。

はい、分かりました。小学生にも分かるように説明します。

MySQLのプラグインとは、MySQLの機能を拡張したり、
カスタマイズしたりするための小さなプログラムのことです。

プラグインは、MySQLの本体に組み込まれていないので、
必要に応じて後から追加することができます。

例えば、caching_sha2_passwordは、ユーザーのパスワードを安全に保存するためのプラグインです。
このプラグインを使うと、パスワードが平文(普通の文字)ではなく、特別な方法で暗号化されて保存されます。
そのおかげで、万が一データが流出しても、パスワードが簡単に読み取られる心配がありません。

他にも、データを圧縮したり、監査ログを残したり、特殊な計算をしたりするプラグインがあります。
プラグインを使うと、MySQLの機能を自由にカスタマイズできるので便利なのです。

エラーの原因を調べると、下記ブログのように caching_sha2_password の状態でSSL接続ができていないと本エラーが出る模様。詳細は下記ブログを参照。

https://yoku0825.blogspot.com/2018/10/mysql-80cachingsha2password-ssl.html

自分の環境のプライマリとレプリカについて現在のプラグインを確認する。
その結果、双方ともプラグインとして caching_sha2_password が使用されていた。(全く同じ表示だった。)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| repl             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

前述のブログ日々の覚書: MySQL 8.0のcaching_sha2_password + 非SSL接続が転けるを参考に、プライマリ側の repl ユーザーのプラグインを caching_sha2_password から mysql_native_password へ変更する。

mysql> ALTER USER 'repl'@'%' identified WITH mysql_native_password BY 'replPass!000';

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| repl             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

上記のように、プライマリ側で変更を加えたため、念の為セカンダリ側へ変更差分を反映させた。手順はステップ3, 4を再度実施した。

その後、プライマリにて、現在のバイナリログファイルの位置を確認。

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-hoge.000005 |     1665 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

セカンダリにてレプリケーション設定をやり直す。先ほどスタートさせたレプリケーションが残っている状態であったため、一度ストップ、リセットを実施後、CHANGE MASTER TO を実施した。

mysql> STOP SLAVE;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CHANGE MASTER TO MASTER_HOST="<プライマリEC2のプライベートIP>",MASTER_USER="repl",MASTER_PASSWORD="replPass!000",MASTER_LOG_FILE="mysql-bin-hoge.000005",MASTER_LOG_POS=1665;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

レプリケーションをスタートさせ、状態を確認する。先ほどのエラーは解消されていた。

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show slave status;
...
エラー文なし
...

動作確認する。プライマリ側のデータベース test_db に新しいテーブル tanomu_ugoke を作成。

mysql> select database();
+------------+
| database() |
+------------+
| test_db    |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tanomu_ugoke(id int, value varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------------+
| Tables_in_test_db    |
+----------------------+
| tanomu_ugoke         |
+----------------------+
1 row in set (0.00 sec)

レプリカ側で同テーブルが作成されているか確認。
以下の通り、適切にレプリケーションされ作成できていた。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_test_db    |
+----------------------+
| tanomu_ugoke         |
+----------------------+
1 row in set (0.00 sec)

補足

caching_sha2_password のエラーを解決後、もし以下のようなエラーが表示される場合は、プライマリインスタンス側にて FLUSH HOSTS を実行してください。mysql では同じホストから短時間に大量の接続エラーが発生すると、以降、それをブロックするような動きがあるらしいです。

エラー

mysql> show slave status;
...
Error connecting to source 'repl@<プライマリのプライベートIP>:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Host '<セカンダリのプライベートIP>' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
...

解消法

mysql> FLUSH HOSTS;
Query OK, 0 rows affected, 1 warning (0.01 sec)

まとめ

今回は EC2 に入れた MySQL 8.0 にてバイナリログレプリケーションを試しました。
やってみると案外ハマることが多く大変でした。ただ、自分でやってみることで具体的な中身がわかってくるので、やはり手を動かすことは重要だと認識しました。

本記事がmysqlを学習している方のお役に立てば幸いです。

参考記事

この記事をシェアする

facebook logohatena logotwitter logo

関連記事

MySQL8でハッシュジョインする際の内部結合・外部結合のフィルタ条件評価のタイミング

MySQL8でハッシュジョインする際の内部結合・外部結合のフィルタ条件評価のタイミング

User avatar
岩田智哉
2024.08.28
[小ネタ] Amazon Linux 2023 に MySQL をインストール->ログイン する

[小ネタ] Amazon Linux 2023 に MySQL をインストール->ログイン する

User avatar
すながわ
2024.08.17
EC2にOSSのGrafanaをインストールしてRDSと接続してみた

EC2にOSSのGrafanaをインストールしてRDSと接続してみた

User avatar
よなみね
2024.06.29
Aurora MySQL の FreeableMemory が減少したら何をすればいいのか

Aurora MySQL の FreeableMemory が減少したら何をすればいいのか

User avatar
高橋和也
2024.06.13
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.