目的

sysbench を使って擬似的にサービス稼動状態を再現した形で MySQL のマスター移行を ダンプ + 移行予定のマスターへのリストア及びレプリケーション という形で練習する。そのための手順を記す。

事前準備

MySQL または MariaDB をインストールする。以後は両方共 MySQL として扱う形で説明する。

MySQL Community Server
- https://dev.mysql.com/downloads/mysql/
MariaDB Server
- https://mariadb.com/kb/ja/yum/

sysbench をインストールする

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

本来はベンチマークツールであるが、ここでは oltp_insert_only を使って常にデータを作り続けるために使う。これによりサービス稼動状態を擬似的に再現出来る。

MySQL の設定

マスターの my.cnf に以下を追加

# mariadb だと mariadb-bin.[number]
[server]
server-id=123
log-bin
# 以下は GTID 有効の場合に必要
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates

レプリカ（マスター予定）の my.cnf に以下を追加

[server]
server-id=456
# 以下は GTID 有効の場合に必要
log-bin
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates
# クラッシュセーフなレプリカを作るには以下を入れる
relay-log-recovery = on
relay-log-info-repository = TABLE

sysbench の実行

最初に prepare を実行する。ここでの件数は 100 万件とした。

sysbench oltp_read_write prepare \
  --db-driver=mysql \
  --mysql-user=$USER \
  --mysql-password=$PASS \
  --mysql-db=test \
  --table_size=1000000

prepare 実行完了後バックグラウンドで実行する。これで常にデータが作り続けられる。ここでは rate を毎秒 10件とした。

nohup sysbench oltp_insert run \
  --rate=10 \
  --time=0 \
  --db-driver=mysql \
  --mysql-user=$USER \
  --mysql-password=$PASS \
  --mysql-db=test &

ダンプとリストア

予め my.cnf を作成してユーザ名とパスワードをそこから読むようにする。

[client]
user = $USER
password = $PASSWORD

ダンプ先に対して mysqldump を実行しダンプをとってレプリカに移す。

ダンプ先はマスターだとサービスに影響があるので稼働中ならマスターからレプリケーションしてる（それもレプリケーション遅延起こしても問題ない）サーバを使うべきだが、停止メンテが可能かつダンプ時間的に問題ないならマスターからダンプする。

# ユーザとパスワードは ~/.my.cnf から取ってくる前提
mysqldump $DB --master-data=2  --single-transaction > dump.sql

ダンプしたファイルをレプリカ先にうつし、リストアする。

# ユーザとパスワードは ~/.my.cnf から取ってくる前提
mysql -D $DB < dump.sql

ダンプ先に対して mysqldump を実行し、パイプを経由してレプリカの mysql に直接リストアすることも出来る。

※ その場合は GCP でいう内部 IP を経由し、インターネット上にトラフィックを流さないこと

# パイプ先のユーザとパスワードは ~/.my.cnf から取ってくる前提
mysqldump $DB -h $HOST -u $USER -p$PASS --master-data=2  --single-transaction | mysql -D $DB

上記の mysqldump の実行には以下のグローバル権限が必要なのでお忘れなく。

RELOAD
SUPER/REPLICATION CLIENT

レプリケーション

レプリカからダンプした先をマスターとみなしてレプリケーションを実行する。

change master to
  master_host = '$HOST',
  master_user = '$USER',
  master_password = '$PASS',
  master_log_file = '$LOG_FILE',
  master_log_pos = '$LOG_POS';
start slave;

あとはレプリケーションが正常に働き、 Seconds_Behind_Master が 0 になることを確認する。

show slave status \G

もし Seconds_Behind_Master が 0 にならず追いつかない場合はまず何が原因かを調べる必要があるが、マルチスレッドスレーブの有効化を対策の選択肢として入れたほうがよいだろう。

レプリケーション中にエラーになったら

MariaDB がレプリケーション先の場合 OFF 扱いにされるので、レプリカの GTID を on_permissive にして Anonymous GTID を受け入れるようにする。

set global gtid_mode = on_permissive;

もしレプリケーション中にエラーになった場合、かつ show slave status の結果を見てスキップして問題ないなら以下を実行する。

set global sql_slave_skip_counter = 1;
start slave;

ちなみに GTID = ON の場合は sql_slave_skip_counter を変更する方法ではなく mysqlslavetrx を使う ※要 Python

レプリカのマスター昇格

ダンプ先の書き込みが止まったらレプリカをマスターに昇格する。その後ダンプ先の mysqld を停止させる

stop slave;
reset slave all;
reset master;
set global gtid_mode = on;