MySQL のレプリケーション設定手順をまとめました。

環境

概要

MySQL のレプリケーションでは、レプリケーション元のサーバーを “Source” と呼び、レプリケーション先のサーバーを “Replica” と呼びます (以前は “Master” と “Slave” と呼ばれていましたが、奴隷制度を連想させる単語として変更されました)。

設定は簡単で、必須となる 2つの設定項目 server_idlog_bin を設定するだけでレプリケーションを開始することができます。ただそれだと本番環境では心もとないので、この記事では少し細かい部分も説明したいと思います。

一般的に、データベースのレプリケーション方式には “物理レプリケーション” と “論理レプリケーションがあります。

物理レプリケーションはファイルの物理構成がそのまま Replica にコピーされる方式なので、Source と Replica のファイルはバイト単位で全く同じになりますが、論理レプリケーションではトランザクションログの内容が Replica で復元されるという方式のため、トランザクションログの内容によっては Source と Replica でデータが同一にならない可能性があります。

MySQL では “論理レプリケーション” のみサポートされていますので、Source と Replica のデータが同一になるようトランザクションログの内容を設定するのが推奨されます。

MySQL のトランザクションログは binlog と呼ばれていて、ログに書き出される内容は binlog_formatbinlog_row_image という設定項目で指定することができます。

binlog_format と binlog_row_image について

binlog_format は binlog に書き出されるトランザクションログの形式を指定する設定項目です。”STATEMENT”, “ROW”, “MIXED” の 3つの値から選択することができます。

Source と Replica で同一の値を維持するには ROWMIXED を使用する必要があります。個人的には全て ROW で出力される方が安全と感じるため、今回も ROW を設定します。

binlog_formatROW を指定する場合、binlog_row_image でどの程度詳細な情報をトランザクションログに記録するかを指定することができます。binlog_row_image には “MINIMAL”、”FULL”、”NOBLOB” のいづれかを設定できます。

トランザクションログサイズを節約するには MINIMAL の方が良いですが、トランザクションログの完全性を保証するには FULL の方が推奨されますので、今回は FULL を設定します。

その他の設定項目について

その他 レプリケーションおよび binlog に関する設定項目で代表的なものは以下となります。

手順

今回、Source サーバーと Replica サーバーの IP アドレスは以下とします。

MySQL のインストール (Source, Replica 共通)

MySQL のダウンロードページから APT パッケージをダウンロードし、MySQL をインストールします。

※執筆時点のパッケージは https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb でした。

Sourceサーバー および Replica サーバー

# APT パッケージをダウンロード
$ cd ~
$ mkdir mysql && cd mysql
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb
---
# ここでインストールするバージョンなどを選択できます。今回はデフォルトのまま OK としました。
---

# MySQL をインストール
$ sudo apt update
$ sudo apt install mysql-server -y

# 任意で MySQL secure installation を実行して初期設定
$ sudo mysql_secure_installation
---
# ここでパスワードポリシーの強制やテストテーブルの削除などを行えます。
# 必須の手順ではないので任意で実行してください。
---

# リモートアクセスを許可。
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
---
# 以下の設定が記述されていた場合はコメントアウトします。記述されていなければ対応不要。
# bind_address  = 127.0.0.1
---

また、トランザクションログファイルの出力先フォルダを作成しておきます。

Sourceサーバー および Replica サーバー

$ sudo mkdir /var/lib/mysql/mysql-bin
$ sudo chown mysql:mysql /var/lib/mysql/mysql-bin
$ sudo chmod 750 /var/lib/mysql/mysql-bin

1). Source サーバーの設定

1-1). Source サーバーのコンフィグ設定

Source サーバー

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
---
server_id = 1
log_bin = mysql-bin/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
expire_logs_days = 7
---

$ sudo systemctl restart mysql

1-2). レプリケーション用ユーザーの作成

MySQL では Replica サーバーから Source サーバーに binlog を問い合わせしてレプリケーションを行うので、Replica サーバーから接続する際に使用するユーザーを Source サーバーに作成しておく必要があります。

Replication に使用するユーザーには REPLICATION SLAVE 権限があれば良いのですが、ここではレプリケーションの監視コマンドを実行するのに必要な REPLICATION CLIENT 権限も一緒に付与しています。

Source サーバー

$ mysql -u root -p
mysql> create user replicationuser@'%' identified by 'password';
mysql> grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to replicationuser@'%';
mysql> flush privileges;

1-3). テスト用データベース / テーブルの作成

Source サーバー

mysql> create database testdb;
mysql> use testdb;
mysql> create table table1 (id int primary key auto_increment, name text, updated datetime);
mysql> insert into table1 (name, updated) values ('name 1', now());

mysql> select * from table1;
+----+--------+---------------------+
| id | name   | updated             |
+----+--------+---------------------+
|  1 | name 1 | 2023-01-08 10:34:06 |
+----+--------+---------------------+
1 row in set (0.00 sec)

以上で Source サーバー側の設定は完了です。

2). Replica サーバーの設定

2-1). Replica サーバーの Config 設定

Replica サーバー側の Config 設定は、Source サーバーとほぼ同じです。

異なる点は、server_id に Source サーバーとは異なる値を設定することと、任意で log_slave_updatesread_only を設定する点です。

Replica サーバー

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
---
server_id = 2  # Source サーバーとは異なる server_id を設定します。
log_bin = mysql-bin/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
sync_binlog = 1
expire_logs_days = 7

# Replica 特有の設定
log_slave_updates = ON
read_only = ON
---

$ sudo systemctl restart mysql

2-2). Source サーバーへの接続情報を設定

Replica サーバーにて、Source サーバーへの接続情報を設定します。

Replica サーバー

$ mysql -u root -p
mysql> change replication source to source_host = '172.16.11.10', source_user = 'replicationuser', source_password = 'password';

2-2). Source データベースのバックアップの取得と復元

レプリケーションを開始する前に、Source サーバーからバックアップを取得し、Replica サーバーに復元する必要があります。

バックアップ データを復元後、そのバックアップがどの時点までの binlog を含むかという情報を設定することで、Replica サーバーは次にどの binlog を取得してレプリケーションを開始すれば良いかを判断することができます。

Replica がどの binlog までのデータを保持しているかを指定するには、CHANGE REPLICATION SOURCE TO コマンドを使用します (MySQL 5.7 以前は CHANGE MASTER TO コマンドでしたが、どちらも同じコマンドです)。

Source サーバーからバックアップを取得する際に、mysqldump ツールで --source-data=1 とオプションを指定することで、バックアップの中に自動的に CHANGE REPLICATION SOURCE TO コマンドを含めることができるため、バックアップを復元するだけで binlog 位置の指定も同時に行うことができます。

Source サーバー

# Source サーバーでバックアップを取得
$ mysqldump --single-transaction --flush-logs --source-data=1 --user=root --password=password --host=localhost --databases testdb > /path/to/source-dump.sql

Replica サーバー

# Replica サーバーでバックアップを復元
$ mysql -u root -p < source-dump.sql

※なお、バックアップファイルを見ると以下のように CHANGE MASTER TO コマンドで binlog ファイルとポジションが指定されているのが確認できます。

$ less source-dump.sql
...
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157;
...

2-3). レプリケーションの開始

最後に START REPLICA コマンドを実行してレプリケーションを開始します (MySQL 5.7 以前は START SLAVE コマンドでしたが、どちらも同じコマンドです)。

Replica サーバー

$ mysql -u root -p
mysql> start replica;

# レプリケーションのステータスを表示。
mysql> show replica status \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.16.11.10
                  Source_User: replicationuser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000003
          Read_Source_Log_Pos: 468
               Relay_Log_File: ubuntu-relay-bin.000003
                Relay_Log_Pos: 684
        Relay_Source_Log_File: mysql-bin.000003
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

以上で MySQL のレプリケーション設定は完了です。