LoginSignup
1
0

More than 3 years have passed since last update.

MySQLのレプリケーション機能を使う

Posted at

動機

社内の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 repl@172.16.222.124 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;

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

1
0
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
1
0