動機

社内のMySQLサーバーのホットスタンバイを作ろうと思った。サービス停止時間が長引くと自分の寿命が縮みそうなので・・・。

環境

マスター・スレーブ共にOpenSUSE Leap 15.1です。マスター側は物理サーバーで、スレーブ側は仮想サーバー(Hyper-V)です。

それぞれのIPアドレスは、マスターが172.16.0.1/16, スレーブが172.16.222.124/16です。

mysql -Vは
mysql Ver 15.1 Distrib 10.2.25-MariaDB, for Linux (x86_64) using EditLine wrapper

構築手順

マスター側設定

サーバー設定

とりあえず、以下の設定を行います。設定は

MySQL 5.6 マスター/スレーブサーバの設定メモ|あぱーブログ

を参考させて頂きました。

[mysqld]
# サーバーのID　重複しないようにする
server-id=1
# バイナリログの有効化（バイナリログとは、更新作業を行ったログが記録されるログ。これがないとレプリケーションできない）
log_bin=mysql-bin

# バイナリログをコミットと同時にディスクに書込む
sync_binlog=1

# クラッシュセーフなレプリケーション設定（ストレージエンジンがInnoDBでないと動かないとの情報あり）
relay_log_info_repository=TABLE
relay_log_recovery=ON

設定後、MySQLを再起動します

systemctl restart mysqld

スレーブ側からのレプリケーションのみに使用するユーザーを作成します。

GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY '<パスワード>';

データベースの出力

スレーブに流し込むSQLを出力します。

mysqldump -u root -p \
--all-databases \
--events \
--single-transaction \
--flush-logs \
--master-data=2 \
--hex-blob \
--default-character-set=utf8mb4 > master_db.sql

一応、それぞれのオプションを説明しておきます

all-databases

すべてのDB（ユーザー等含む）をバックアップします

events

MySQL 5.1.8から追加された"events"テーブルも含めます（これをしないとワーニングが出る）

single-transaction

ダンプ処理をトランザクションで囲む（DB全体をロックする？）

flush-logs

バイナリログを一度削除して、新しいファイルを作る

master-data=2

CHANGE MASTER TO句を含める。=2にした場合は、バイナリログの読み出し開始位置をコメントとして出力する

hex-blob

バイナリ部分を16進数の形式でダンプする（そのままダンプするとただの文字列としてダンプしてしまう）

default-character-set=utf8mb4

デフォルトの文字コード設定。これは各自自分のDBに合わせれば良いと思いますが、あまり検証していないので確証は持てません。

当たり前の事ですが、このコマンドはサービスを停止して実行するか、社内システムなら業務時間外に実行してください。

ダンプが完了したら、sqlファイルをスレーブ側に転送しておきます。

スレーブ側設定

my.cnf

# サーバーIDの設定（マスターとかぶらないようにする）
server-id=2

# 更新を禁止する（スレーブ用）
read_only

上記を追記して

systemctl restart mysql

でリスタートします。

ダンプしたファイルの確認

ダンプしたファイルを確認し、バイナリログのファイル名と開始位置を確認します。

head -n 100 master_db.sql | grep CHANGE

実行結果は、例えば

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=342;

こんな感じで表示されます。

これを頼りに、以下のSQLを実行します。

CHANGE MASTER TO
 MASTER_HOST='172.16.0.1',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='<パスワード>',
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=342;

この後

START SLAVE;

このクエリーを発行すると、レプリケーションが開始されます。

SHOW SLAVE STATUS\G

と入力し、表示された一覧に

Slave_IO_Running: Yes

と表示されていればOKです。

トラブルシューティング

Slave_IO_Runnigがconnectingのまま

ファイアウォールの設定で3306ポートを弾いてる可能性があるので、確認してください。

Slave_IO_RunnigがNO

整合性が取れてない状態なので、もう一度やり直します。

mysql -u root -p < master_db.sql

でもう一度DBをリストアし直して、一度

MariaDB [(none)]> STOP SLAVE;

でスレーブを停止し

CHANGE MASTER TO
 MASTER_HOST='172.16.0.1',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='<パスワード>',
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=342;

としたあと

START SLAVE;

で、再度スレーブを開始してください。