MySQL のレプリケーション設定手順をまとめました。
環境
- OS: Ubuntu 20.04 LTS
- Database: MySQL 8.0
概要
MySQL のレプリケーションでは、レプリケーション元のサーバーを “Source” と呼び、レプリケーション先のサーバーを “Replica” と呼びます (以前は “Master” と “Slave” と呼ばれていましたが、奴隷制度を連想させる単語として変更されました)。
設定は簡単で、必須となる 2つの設定項目 server_id
と log_bin
を設定するだけでレプリケーションを開始することができます。ただそれだと本番環境では心もとないので、この記事では少し細かい部分も説明したいと思います。
一般的に、データベースのレプリケーション方式には “物理レプリケーション” と “論理レプリケーションがあります。
物理レプリケーションはファイルの物理構成がそのまま Replica にコピーされる方式なので、Source と Replica のファイルはバイト単位で全く同じになりますが、論理レプリケーションではトランザクションログの内容が Replica で復元されるという方式のため、トランザクションログの内容によっては Source と Replica でデータが同一にならない可能性があります。
MySQL では “論理レプリケーション” のみサポートされていますので、Source と Replica のデータが同一になるようトランザクションログの内容を設定するのが推奨されます。
MySQL のトランザクションログは binlog
と呼ばれていて、ログに書き出される内容は binlog_format
と binlog_row_image
という設定項目で指定することができます。
binlog_format と binlog_row_image について
binlog_format
は binlog に書き出されるトランザクションログの形式を指定する設定項目です。”STATEMENT”, “ROW”, “MIXED” の 3つの値から選択することができます。
STATETMENT
:- トランザクションで実行された SQL 文がそのままトランザクションログに記録されます。例えば UPDATE 文などで複数のレコードが更新された場合も、ログに記録されるのは UPDATE 文のみのためログファイルのサイズを節約することができます。
- ただし、例えば UUID() など関数で生成された値は、関数のみが記録されるため Source と Replica で異なる値が生成される可能性があります。
ROW
:- トランザクションで更新された行の値がトランザクションログに記録されます。例えば UPDATE 文で複数のレコードが更新された場合、更新された各レコードの情報がトランザクションログに記録されます。
- 関数で生成された値も、生成された値がログに記録されるため Source と Replica で同じ値が設定されます。
MIXED
:- 基本的には
STATEMENT
が使用され、STATEMENT では値が変更される可能性がある場合のみROW
が使用されます。どのような場合にROW
が使われるかは公式ドキュメントに記載されています。
- 基本的には
Source と Replica で同一の値を維持するには ROW
か MIXED
を使用する必要があります。個人的には全て ROW
で出力される方が安全と感じるため、今回も ROW
を設定します。
binlog_format
に ROW
を指定する場合、binlog_row_image
でどの程度詳細な情報をトランザクションログに記録するかを指定することができます。binlog_row_image
には “MINIMAL”、”FULL”、”NOBLOB” のいづれかを設定できます。
MINIMAL
: トランザクションログには、更新されたカラムのみ値が保存されます。更新されなかった値は保存されません。FULL
: トランザクションログに、更新された行の全てのカラムの値が保存されます。NOBLOB
:FULL
と同じですが、BLOG や TEXT 型のカラムの値はログに保存されません。
トランザクションログサイズを節約するには MINIMAL
の方が良いですが、トランザクションログの完全性を保証するには FULL
の方が推奨されますので、今回は FULL
を設定します。
その他の設定項目について
その他 レプリケーションおよび binlog に関する設定項目で代表的なものは以下となります。
server_id
:- レプリケーションを行うサーバー間で一意の数値を設定します。
log_bin
:- binlog ファイルの出力先。絶対パスか相対パスで指定します。設定した値に識別子が付いたファイル名で binlog が保存されます。
- これを設定することでトランザクションログの書き出しが有効になります。
- 相対パスを指定した場合、binlog はデータディレクトリ (Ubuntu では
/var/lib/mysql
) 以下に出力されます。
sync_binlog
:- 指定したトランザクション毎にbinlog が書き出されます。”1″ を設定すると、各トランザクションがコミットされる毎にトランザクションの内容が binlog に書き出されます。
expire_logs_days
:- binlog が自動で削除されるまでの期間を設定します。例えば “7” と設定すると、binlog が書き出されてから 7日後にその binlog ファイルが削除されます。
log_slave_updates
:- Replica 側で binlog をディスクに保存するか (ON)、保存しないか (OFF) を指定します。Replica から別の Replica へレプリケーションする場合に必要な設定です。
read_only
:- データベースを読み取り専用とするか (ON)、書き込みも可能とするか (OFF) を設定します。
- Replica で書き込みを許可するとデータの不整合が発生し得るため、通常は Replica を読み取り専用として設定します。
手順
今回、Source サーバーと Replica サーバーの IP アドレスは以下とします。
- Source サーバー: 172.16.11.10
- Replica サーバー: 172.16.11.11
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_updates
と read_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
コマンドを含めることができるため、バックアップを復元するだけで binlog 位置の指定も同時に行うことができます。REPLICATION
SOURCE TO
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 のレプリケーション設定は完了です。