Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

株式会社スピードリンクジャパンAdvent Calendar 2023

Day 22
@suzuko24(すずこ)

【MySQL】レプリケーションの設定をしろって言われたんです。

Last updated at Posted at 2023-12-21

レプリケーション設定の備忘録

目次

  1. レプリケーションとは
  2. レプリケーションのメリット
  3. レプリケーションの設定を入れてみる

後輩ちゃん「上司からMySQLのレプリケーション設定を組み込んでくれって言われたんですけど、レプリケーションってなんですか？」

すずこ「レプリカ（複製）をつくることだよ」
すずこ「簡単に言うと、一つのMySQLデータベース（マスター）の変更が、
他のMySQLデータベース（スレーブ）に自動的に反映される仕組みのことをレプリケーションっていうんだよ～」

多様性の観点からマスター（master）/スレーブ（slave）から、メイン（main）/レプリカ（replica）と表現されるケースもある

レプリケーションとは

レプリケーションは、データベースの情報を複製して複数の場所で同じデータを利用できるようにする仕組み。
データベースの可用性を向上させ、冗長性を確保することができる。


後輩ちゃん「... 複製するメリットがいまいち分からないのですが、なんのためにレプリケーション設定を入れるのですか？」

すずこ「例えば、データベースサーバーが1台の場合、サーバーに障害が発生したらどうなる？」

後輩ちゃん「アプリケーションの利用ができなくなる...？」

すずこ「その通り！レプリケーションを導入することで、1台のサーバーに頼らない冗長性を確保できるんだよ。」

後輩ちゃん「冗長性って、具体的にどういうことですか？」

すずこ「冗長性とは、冗長な構成を持つことでシステムやサービスの可用性や信頼性を向上させること。」
すずこ「具体的には、マスターサーバーと1つ以上のスレーブサーバーを持つことによって、マスターが故障した場合でもスレーブが引き続きサービスを提供できるようにするんだ。」

後輩ちゃん「なるほど、冗長な構成を持つことで障害が発生してもサービスを止めずに続けられるってことですね。」
すずこ「そうゆうこと！他にもメリットあるよ！」

レプリケーションのメリット

  • 冗長性
    • 障害対策：マスターサーバーに障害が発生した場合、スレーブが引き続きサービスを提供することができる
    • フェイルオーバー機能：マスターサーバーの障害時には、自動的に別のスレーブが昇格して新たなマスターとなり、シームレスにサービスを継続できる
  • スケーラビリティ
    • 読み取り専用レプリケーション：スレーブを読み取り専用として活用することで、マスターサーバーが書き込みトランザクションに専念し、同時に多くの読み取り操作に対応できる
  • バックアップ
    • リアルタイム同期：バックアップとしてのスレーブは、マスターサーバーのデータとリアルタイムで同期しているため、マスターサーバーのデータが破損した場合、スレーブのデータを使用してほぼ即座に復旧できる
    • ポイントインタイムリカバリ(PITR)：スレーブはバイナリログを使用してマスターサーバーと同期しているため、バイナリログの変更履歴を利用することで、特定の時点までデータを戻すことができる

レプリケーションの設定を入れてみる

ローカル環境を作る

後輩ちゃん「レプリケーションを実現するためには、少なくとも2つのデータベースサーバーが必要になりますね」
後輩ちゃん「設定手順を作成するにあたり、Dockerで2台のMySQLコンテナを立てて試してみます」

マスター、スレーブのMySQLのバージョンは同一であることが前提

docker-compose.yml (参考というほどのものでもないけど)
docker-compose.yml
version: "2"
services:
  master-db:
    container_name: master
    image: mysql:5.7.37
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: slj
      MYSQL_USER: slj
      MYSQL_PASSWORD: sljsljslj
    ports:
      - "3306:3306"

  slave-db: 
    container_name: slave
    image: mysql:5.7.37
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: slj
      MYSQL_USER: slj
      MYSQL_PASSWORD: sljsljslj
    ports:
      - "3307:3306"

※dockerコマンド関連（コンテナの起動の仕方など）は本記事には記載しません、悪しからず。

レプリケーション設定の作業サマリ

  1. マスターデータベースの書き込みを一時停止
  2. マスターデータベースのデータをエクスポート
  3. マスターデータベースにレプリケーション用ユーザーの作成
  4. マスターデータベースにバイナリログの有効化設定
  5. マスターデータベース再起動
  6. バイナリログの状態確認
  7. スレーブデータベースにデータをインポート
  8. スレーブデータベースにレプリケーション用ユーザーの作成
  9. スレーブデータベースにバイナリログの有効化設定
  10. スレーブデータベースの再起動
  11. スレーブデータベースにマスターデータベースの登録をする
  12. スレーブデータベースでレプリケーションの開始
  13. スレーブデータベースのレプリケーション設定の確認
  14. マスターデータベースの書き込みを再開

レプリケーション設定の作業詳細

1. マスターデータベースの書き込みを一時停止

レプリケーション設定をする前に、マスターデータベース上の書き込みを一時停止することで安全に設定を入れる。

データベース
> FLUSH TABLES WITH READ LOCK;
2. マスターデータベースのデータをエクスポート

マスターデータベースの現在の状態をエクスポートし、そのデータを元にレプリケーションを開始する。

ターミナル
$ mysqldump -u ユーザー名 -p パスワード --host=ホスト名 --port=ポート番号 --databases データベース名 > ダンプファイル.sql
3. マスターデータベースにレプリケーション用ユーザーの作成

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

データベース
# ユーザーの作成
> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
# レプリケーション権限の設定
> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 作成したユーザーの確認
> SELECT User, Host, Plugin FROM mysql.user;
4. マスターデータベースにバイナリログの有効化設定

バイナリログを有効にして、マスターデータベースで発生した変更をバイナリログに記録する。
これにより、スレーブデータベースが変更を追跡することができる。

my.cnf
[mysqld]
# Binary log settings
server-id=1001 #ユニークなIDを設定
log-bin=mysql-bin #バイナリログの設定
expire_logs_days=7 #バイナリログの保持期間
5. マスターデータベースの再起動

my.cnfの変更が反映されるように、マスターデータベースを再起動する。

6. バイナリログの状態確認

バイナリログの有効化が正常に設定されているか確認する。

データベース
> SHOW MASTER STATUS;
※出力結果（File, Position）をメモしておくこと

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
7. スレーブデータベースにデータをインポートする

マスターデータベースからエクスポートしたデータをスレーブデータベースにインポートする。

ターミナル
mysql -u ユーザー名 -p データベース名 < ダンプファイル.sql
8. スレーブデータベースにレプリケーション用ユーザーの作成

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

データベース
# ユーザーの作成
> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
# レプリケーション権限の設定
> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 作成したユーザーの確認
> SELECT User, Host, Plugin FROM mysql.user;
9. スレーブデータベースにバイナリログの有効化設定

バイナリログを有効にし、マスターデータベースの変更を追跡できるようする。

my.cnf
[mysqld]
# Binary log settings
server-id=1002 #ユニークなIDを設定（マスターデータベースと異なるID）
log-bin=mysql-bin #バイナリログの設定
expire_logs_days=7 #バイナリログの保持期間
read only # 読み取り専用レプリケーションの場合
10. スレーブデータベースの再起動

my.cnfの変更が反映されるように、スレーブデータベースを再起動する。

11. スレーブデータベースにマスターデータベースの登録をする

スレーブデータベースに、マスターデータベースの情報を登録する。

データベース
※下記マスターデータベースの情報を設定する
※バイナリログ = 6. バイナリログの状態確認で確認したバイナリログの情報

> CHANGE MASTER TO
    MASTER_HOST = '[IPアドレス/ホスト名]',
    MASTER_USER = '[レプリケーションユーザー]',
    MASTER_PASSWORD = '[レプリケーションユーザーのパスワード]',
    MASTER_PORT = [ポート番号],
    MASTER_LOG_FILE = '[バイナリログのファイル名]',
    MASTER_LOG_POS = [バイナリログのポジション]
    ;
12. スレーブデータベースででレプリケーションの開始

レプリケーションプロセスを開始。

データベース
> START SLAVE;
13. スレーブデータベースのレプリケーション設定の確認

レプリケーションの設定が正しく行われているかを確認。

データベース
> SHOW SLAVE STATUS\G

※この辺りを確認しておけばOK
...
Slave_IO_State: Waiting for master to send event
Master_Host: master-db
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 899
Relay_Log_File: 9bc602b450ce-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes 
Slave_SQL_Running: No
...

Slave_IO_Running：マスターのバイナリログを読み取るためのI/Oスレッドが実行しているかどうか
Slave_SQL_Running：SQLスレッドが起動しているかどうか

14. マスターデータベースの書き込みを再開

マスターデータベースの書き込みを再開する。

データベース
> UNLOCK TABLES;

後輩ちゃん「マスターデータベースにデータの挿入したら、スレーブデータベースにも反映されていました！」
すずこ「今回はシンプルなレプリケーションの設定をしたんだけど、他にも双方向レプリケーションや、非同期レプリケーションもあるんだよ～！」
後輩ちゃん「そうなんですね！異なるレプリケーション手法も理解して、適切なデータベース設計ができるようになりたいです！」

うむ。

13
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?