MySQL 5.7 を用いてレプリケーション設定を行います。

検証用の環境を構築 (mysqld_mutil)

検証のため、ここでは mysqld_mutil を用いて複数の Port で mysqld を起動します。公式ページはこちらです。yum repository を追加した場合は PATH が通った状態で mysqld_multi コマンドが利用できます。

$ which mysqld_multi
/usr/bin/mysqld_multi

コマンドは Perl スクリプトです。

$ file /usr/bin/mysqld_multi 
/usr/bin/mysqld_multi: a /usr/bin/perl script text executable

mysqld_multi --help を参考に my.cnf を変更します。

[mysqld]
# 本項目は mysqld_multi コマンドからは参照されません。
port=3306
socket=/tmp/mysql.sock
datadir=/data/mysql
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
# 本項目は mysqld_multi コマンドからは参照されません。
socket=/tmp/mysql.sock

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin

[mysqld1]
port=13306
socket=/tmp/mysql_master.sock
datadir=/data/mysql_master
log-error=/var/log/mysqld_master.log
pid-file=/var/run/mysqld/mysqld_master.pid

[mysqld2]
port=13307
socket=/tmp/mysql_slave.sock
datadir=/data/mysql_slave
log-error=/var/log/mysqld_slave.log
pid-file=/var/run/mysqld/mysqld_slave.pid

datadir を初期化します。

sudo mkdir -p /data/mysql_master
sudo mkdir -p /data/mysql_slave
sudo /usr/sbin/mysqld --no-defaults --initialize --user=mysql --datadir=/data/mysql_master
sudo /usr/sbin/mysqld --no-defaults --initialize --user=mysql --datadir=/data/mysql_slave
sudo /usr/bin/mysql_ssl_rsa_setup --no-defaults --datadir=/data/mysql_master
sudo /usr/bin/mysql_ssl_rsa_setup --no-defaults --datadir=/data/mysql_slave
sudo chown -R mysql: /data/mysql_master
sudo chown -R mysql: /data/mysql_slave

以下のコマンドで起動します。

sudo mysqld_multi start

起動したことを確認します。

sudo mysqld_multi report

root パスワードの変更

確認した初期パスワードでログインします。

mysql -uroot -S /tmp/mysql_master.sock -p
mysql -uroot -S /tmp/mysql_slave.sock -p

以下のコマンドでパスワードを変更します。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

パスワードなしでログインできるようにするためには /etc/my.cnf を編集して再起動します。

sudo vim /etc/my.cnf
skip-grant-tables  ← [mysqld1], [mysqld2] セクションに追記
sudo mysqld_multi stop  ← 初回は停止しないため sudo pkill -9 mysql
sudo mysqld_multi start

ただし skip-grant-tables による影響で GRANT が使用できなくなります。skip-grant-tables をコメントアウトして再起動してから root のパスワードを空に設定することでパスワード入力なし、かつ GRANT が使用可能な状態になります。

sudo mysqld_multi stop
sudo vim /etc/my.cnf
sudo mysqld_multi start
mysql -uroot -S /tmp/mysql_master.sock -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql -uroot -S /tmp/mysql_slave.sock -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';

パスワードが失効しない設定であることを確認

mysql 5.7 には、一定期間が経過するとパスワードを失効させてログインできなくする機能があります。不要な場合は、以下のコマンド結果が 0 であることを確認して無効になっていることを確認します。

select @@default_password_lifetime;

レプリケーションユーザーの作成

$ mysql -uroot -S /tmp/mysql_master.sock
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replpass';
mysql> SHOW GRANTS FOR 'repl'@'%';

スレッドの種類について

repl ユーザーでログインした状態でレプリケーションが開始されると master のマスタースレッドから slave の I/O スレッドに binlog が転送され、I/O スレッドによって relaylog に書き込まれます。転送モードには gtid_mode が OFF と ON の二種類があります。slave の SQL スレッドは relaylog から読み出した SQL を実行します。SHOW SLAVE STATUS\G で確認される Seconds_Behind_Master は SQL スレッドの遅れを表現しています。

GTID を用いたレプリケーション設定

MySQL 5.6 から導入された GTID は既定では gtid_mode = OFF になっています。これは MASTER_LOG_FILE および MASTER_LOG_POS を利用したレプリケーションを行うモードです。設定方法は MySQL 5.5 と同じです。以降、本ページでは GTID を用いた非同期レプリケーション設定を対象とします。公式ページはこちらです。

mysql> SHOW VARIABLES LIKE '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

my.cnf の編集

GTID を用いたレプリケーション設定を my.cnf に追記します。オンラインでも変更可能ですが、手順がやや複雑なため本ページでは停止してオフラインで変更しています。

$ sudo mysqld_multi stop
$ sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
$ sudo vim /etc/my.cnf

/etc/my.cnf

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin

[mysqld1]
port=13306
socket=/tmp/mysql_master.sock
datadir=/data/mysql_master
log-error=/var/log/mysqld_master.log
pid-file=/var/run/mysqld/mysqld_master.pid
# レプリケーションサーバー間で重複しない ID
server_id=100001
# GTID モード有効化
gtid_mode=ON 
# GTID 互換のない SQL の実行を禁止 (GTID では SQL に制約がつきます)
enforce_gtid_consistency=ON
# binlog 出力設定 (後者二つは master では無意味ですが slave と同じ設定にしたいため)
log_bin=mysql-bin
log_slave_updates
relay_log=mysqld-relay-bin
# sync_binlog=0 # ディスク使用率を下げたい場合 (ただし、予期せぬクラッシュ時に binlog に書き出されないトランザクションが発生し得ります)

[mysqld2]
port=13307
socket=/tmp/mysql_slave.sock
datadir=/data/mysql_slave
log-error=/var/log/mysqld_slave.log
pid-file=/var/run/mysqld/mysqld_slave.pid
# レプリケーションサーバー間で重複しない ID
server_id=100002
# GTID モード有効化
gtid_mode=ON
# GTID 互換のない SQL の実行を禁止 (GTID では SQL に制約がつきます)
enforce_gtid_consistency=ON
# binlog 出力 (GTID モードでは slave も binlog 出力が代替手段を設定しない限り必要。relay_log の名称も指定しておくとホスト名の変更の影響を受けないため安心)
log_bin=mysql-bin
log_slave_updates
relay_log=mysqld-relay-bin
# sync_binlog=0 # ディスク使用率を下げたい場合 (ただし、予期せぬクラッシュ時に binlog に書き出されないトランザクションが発生し得ります)

起動して設定を確認します。

sudo mysqld_multi start
sudo mysqld_multi report
mysql -uroot -S /tmp/mysql_master.sock
mysql -uroot -S /tmp/mysql_slave.sock

mysql> SHOW VARIABLES LIKE '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT @@server_id;
mysql> SHOW MASTER LOGS;
mysql> SHOW BINLOG EVENTS;

ダンプファイルの取得およびリストア

master から mysqldump コマンドでダンプを取得します。ただし、mysql.gtid_executed は除外します。

mysqldump --master-data=2 --single-transaction --skip-lock-tables --all-databases --triggers --routines --events --ignore-table=mysql.gtid_executed -uroot -S /tmp/mysql_master.sock | gzip > master.dump.gz

slave にリストアします。

zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock

レプリケーションの開始

slave にログインします。

mysql -uroot -S /tmp/mysql_slave.sock

以下のコマンドで master に向けます。

CHANGE MASTER TO
    MASTER_HOST = '127.0.0.1',
    MASTER_PORT = 13306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'replpass',
    MASTER_AUTO_POSITION = 1;

GTID においては MASTER_LOG_FILE および MASTER_LOG_POS を確認する作業は不要です。

$ zgrep '^-- CHANGE MASTER TO' master.dump.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

レプリケーションを開始します。

START SLAVE;

GTID レプリケーション開始後の状態確認

SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SHOW BINLOG EVENTS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
SHOW RELAYLOG EVENTS;

GTID は event を発生させたサーバーの UUID : トランザクション ID という構成の文字列です。slave は既に適用した GTID を自身の binlog から知ることができるため、同じ GTID の SQL を重複実行することを回避できます。例えば以下の例では UUID c91b2678-a905-11e6-8592-0800276b1cdd の master で発生したトランザクション ID 1 の event が slave でも実行済みであることを表現しています。

$ mysql -uroot -S /tmp/mysql_master.sock -e 'select @@server_uuid'
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| c91b2678-a905-11e6-8592-0800276b1cdd |
+--------------------------------------+

$ mysql -uroot -S /tmp/mysql_slave.sock -e 'select @@GLOBAL.GTID_EXECUTED'
+----------------------------------------+
| @@GLOBAL.GTID_EXECUTED                 |
+----------------------------------------+
| c91b2678-a905-11e6-8592-0800276b1cdd:1 |
+----------------------------------------+

レプリケーション情報をリセットして最初からやりなおす

GTID レプリケーションが有効な状態でリストアを実行すると、安全のためエラーが出るようになっています。

$ zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

ダンプファイル内に記載されている以下のクエリによって、ダンプ取得時において最後に実行された SQL の GTID を SET しようとするためです。

SET @@GLOBAL.GTID_PURGED='c91b2678-a905-11e6-8592-0800276b1cdd:1';

スレーブのデータが本当に不要であり、最初からやりなおしたい場合は以下のコマンドでリセットしてからリストアします。

レプリケーションの停止

STOP SLAVE;

binlog の全削除 (GTID をどこまで実行したかという情報を破棄)

SHOW MASTER STATUS;
RESET MASTER;  ← バイナリログががすべて削除されます。
SHOW MASTER STATUS;

リストア

zcat master.dump.gz | mysql -uroot -S /tmp/mysql_slave.sock

レプリケーションの開始ポジションを再設定

RESET SLAVE;

CHANGE MASTER TO
    MASTER_HOST = '127.0.0.1',
    MASTER_PORT = 13306,
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'replpass',
    MASTER_AUTO_POSITION = 1;

レプリケーションの開始

START SLAVE;

ダンプファイル内には TABLE の DROP は記載されていますが DB の DROP がないため、過去の DB スキーマが残存している状態で CREATE DATABASE が実行されるとエラーになります。

Last_SQL_Error: Error 'Can't create database 'mydb2'; database exists' on query. Default database: 'mydb2'. Query: 'create database mydb2'

手動で DROP してから再度開始すれば復旧しますが、そもそも DROP してからリストアしたほうが master の状態と実行履歴も含めて完全に同じになるため好ましいです。

DROP DATABASE mydb2;
START SLAVE;