MySQL 5.7 GTID レプリケーション設定メモ

2017年2月8日  カテゴリー:Linux  タグ: ,
Pocket
LINEで送る

MySQLの GTID(グローバルトランザクション識別子)は、各トランザクションに世界で唯一のIDをつける機能です。これまでのレプリケーションでは、複数のスレーブサーバがある構成でスレーブがマスターに昇格した時に、スレーブ間のデータ差異を埋めなければならないという問題がありましたが、GTID を使ったレプリケーションではこの問題を根本的に解決してくれます。そこで今回は、MySQL5.7 で GTID を使ったレプリケーションを設定方法をメモしておきました。

MySQL サーバ構成

ごく単純な、マスター1台、スレーブ1台の構成です。サーバOSは CentOS7.3 (1603) MySQLのバージョンは 5.7.17 です。

MySQL5.7 GTIDレプリケーションのサーバ構成図

MySQL マスターサーバの設定

マスターサーバの my.cnf 設定

vi /etc/my.cnf

[mysqld]

# 各MySQLサーバを識別するためのIDの設定
server-id=1

# GTIDの有効化
gtid_mode=ON

# GTIDと併用できないSQL文を禁止する
enforce_gtid_consistency

# バイナリログの有効化
log_bin=mysql-bin

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

#---------- レプリケーション関連の設定はここまでです ----------#

# 基本設定
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 文字コードの設定
character-set-server=utf8
skip-character-set-client-handshake

# タイムスタンンプ関連の設定
log_timestamps=SYSTEM
explicit_defaults_for_timestamp=TRUE

# スロークエリログの設定
slow_query_log=ON
long_query_time=1.0
slow_query_log_file=/var/log/mysql/slow_query.log

log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

※ログの出力先を /var/log/mysql/ 以下に変更しています。

MySQLを再起動して、設定を反映します。

systemctl restart mysqld

レプリケーション関連設定の詳細

server-id=1
 他のMySQLサーバの server-id と、かぶらないように設定します。レプリケーションを使うときに指定できる値は 1 から 4294967295(2の32乗 - 1)です。GTID(= server_uuid:トランザクションID)を使うのであれば、server-id はいらないような気もしますが、レプリケーション使用時に server-id を一意に設定することは変わらず必要なようです。

MySQL 5.6 で server_uuid システム変数が追加されても、このセクションですでに説明したように、MySQL レプリケーションの準備と実行の一部として MySQL サーバーごとに一意の --server-id を設定する必要があることは変わりません。

17.1.4 レプリケーションおよびバイナリロギングのオプションと変数 | MySQL 5.6 リファレンスマニュアル より引用

gtid_mode=ON
 GTIDを有効にします。

enforce_gtid_consistency
 GTIDと併用できないSQL文を禁止にします。禁止されるSQL文は以下の通りです。すでに稼働しているシステムでGTIDを有効にする場合は、アプリケーション側でこのようなSQL文が使われていないか検証が必要ですね。

・CREATE TABLE ... SELECT ステートメント
・トランザクション内の CREATE TEMPORARY TABLE ステートメント
・トランザクションおよび非トランザクションテーブルの両方を更新するトランザクションまたはステートメント。

17.1.4.5 グローバルトランザクション ID のオプションと変数 | MySQL 5.6 リファレンスマニュアル より引用

log_bin=mysql-bin
 従来のレプリケーション設定と同様に、バイナリログを有効にします。バイナリログには、実行したSQLなど、DBの更新情報が含まれています。このバイナリログを、スレーブサーバに転送することによって、データ同期を実現しています。また、引数でバイナリログ名を指定できます。指定しない場合は、ホスト名がバイナリログ名の一部に使われます。

sync_binlog=1
「sync_binlog=1」にすると、バイナリログの整合性、すなわちマスター/スレーブ間の整合性を高めることができます。以前のバージョンではこの設定はサーバへの負荷が大きかったのですが、最近のバージョンで改良されたためかMySQL5.7.7からはデフォルトで「sync_binlog=1」が設定されるようになりました。(なのでこの設定は書かなくてもかまいません)
 

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

スレーブサーバ(172.16.1.117)からのみ接続を許可します。ユーザ名は、慣習的に「repl」を指定していますが、なんでもかまいません。

mysql -uroot -p

GRANT REPLICATION SLAVE ON *.* TO repl@172.16.1.117 IDENTIFIED BY '<パスワード>';

「repl」ユーザが作成されたことを確認します。

SELECT user,host FROM mysql.user;
(略)
| repl | 172.16.1.117 |

マスターDBのダンプ

レプリケーションの開始時のみ、手動でマスターのDB全体を、スレーブにコピーする必要がありますので、マスターのDBをダンプします。

mysqldump -u root -p \
--single-transaction \
--flush-logs \
--master-data=2 \
--all-databases \
--hex-blob \
--triggers \
--routines \
--events > master_db.sql

ダンプしたファイルをスレーブサーバにコピーしておきます。

scp master_db.sql root@172.16.1.117:

最後に、スレープサーバからの接続に使う 3306/tcp ポートを開いておきます。

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

マスターサーバでの作業は以上です。続いてスレーブサーバの設定です。

MySQL スレーブサーバの設定

スレーブサーバの my.cnf 設定

スレーブサーバ特有の設定以外は、マスターサーバの設定と同じにしておくことをオススメします。

vi /etc/my.cnf

[mysqld]

# 各MySQLサーバを識別するためのIDの設定
server-id=2

# 更新を禁止する
read_only

# スレーブSQLスレッドによって実行される更新をバイナリログに書き込む
log_slave_updates

# リレーログファイルの名前
relay_log=mysql-relay-bin

# クラッシュセーフなレプリケーション設定
relay_log_info_repository=TABLE
relay_log_recovery=ON
relay_log_purge=ON

( gtid_mode=ON 以降の設定はマスターサーバと同じです)

# GTIDの有効化
gtid_mode=ON

MySQLを再起動して、設定を反映します。

systemctl restart mysqld

スレーブサーバ特有の設定の詳細

read_only
 read_only を設定するとレプリケーションによる更新以外を禁止することができます。ただし、SUPER権限を持ったユーザ(rootなど)の更新は許可されます。read_only と合わせて、MySQL5.7.8で導入された「super_read_only」を設定すれば、SUPER権限を持ったユーザの更新さえも禁止することができます。ただ、これを設定してしまうと、ダンプしたデータの読み込みができなくなるなど設定作業に弊害があるので、正常にレプリケーションが稼働してから設定するのがよいかと思います。

log_slave_updates
 スレーブSQLスレッドによって実行される更新をバイナリログに書き込みます。この設定は「マスター -> スレーブ -> スレーブ」のようにスレーブサーバを多段構成する場合に必要になります。ただ、多段構成に設定しない場合でも、スレーブがマスターに昇格する場面に備えて設定しておくのが良いと思います。

relay_log=mysql-relay-bin
 リレーログ名を指定します。デフォルトではホスト名がリレーログ名の一部に使われるため、サーバの移行などでホスト名が変わるとリレーログを見つけられなくなります。そのためリレーログ名を指定することが推奨されています。

relay_log_info_repository=TABLE
relay_log_recovery=ON
relay_log_purge=ON
 クラッシュセーフスレーブの設定です。マスター/スレーブサーバの障害発生時に、復旧作業の手間を大幅に軽減できます。relay_log_recovery を「ON」にした場合、relay_log_purge を「OFF」にしてはいけないため、念のため「relay_log_purge=ON」を明示的に書いています。( relay_log_purge のデフォルト値は「ON」です)

マスターDBのダンプの読み込み

ダンプしたデータを読み込む前に、スレーブサーバの「gtid_executed」をクリアする必要があるため、RESET MASTER コマンドを実行します。

mysql -u root -p

RESET MASTER;

マスターサーバでダンプしたデータを、スレーブに読み込みます。

mysql -u root -p < master_db.sql

ちなみに「gtid_executed」をクリアしないで、ダンプしたデータを読み込もうとすると以下のようなエラーが発生します。

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

レプリケーションの設定

GTIDを使用したレプリケーションでは、マスターのバイナリログファイル名(MASTER_LOG_FILE)や開始位置(MASTER_LOG_POS)を設定する必要はありません。GTIDベース自動ポジショニング(MASTER_AUTO_POSITION=1)を使用するように指定するだけでOKです。

mysql -u root -p

CHANGE MASTER TO
 MASTER_HOST='172.16.1.116',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='<パスワード>',
 MASTER_AUTO_POSITION=1;

MASTER_HOST
 マスターサーバのIPアドレスを指定します。

MASTER_PORT
 マスターサーバの MySQL の待受ポート番号を指定します。ポート番号を変更していなければ「3306」です。

MASTER_USER
 先ほど作成した、レプリケーション用ユーザ名を指定します。

MASTER_PASSWORD
 レプリケーション用ユーザのパスワードを指定します。

MASTER_AUTO_POSITION
 上にも書きましたが、GTIDベース自動ポジショニングを使用するように「1」を指定します。従来のファイルと位置に基づくレプリケーションに戻す場合は「0」を指定するとともに、バイナリログファイル名(MASTER_LOG_FILE)と開始位置(MASTER_LOG_POS)を指定します。

設定が終わったら、以下のコマンドでレプリケーションを開始します。

START SLAVE;

パスワード保存のセキュリティ警告について

「CHANGE MASTER TO」で「MASTER_USER」や「MASTER_PASSWORD」を指定すると、MySQL5.6からは警告されるようになりました。これは『スレーブサーバの「master.info」にユーザ名やパスワードがそのまま保存されるよ!』と注意してくれています。

SHOW WARNINGS\G
(略)
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

CentOS7.3の場合「master.info」のアクセス権は、以下のように設定されているため、一般ユーザにユーザ名やパスワードが見られてしまうことはありませんが、セキュリティ的にはちょっと不安なところです。

ls -l /var/lib/mysql/master.info
-rw-r----- 1 mysql mysql 130 2月 8 5:29 /var/lib/mysql/master.info

そこで「master.info」にユーザ名やパスワードを保存しないようにするには、「CHANGE MASTER TO」では指定せずに、「START SLAVE」と合わせて指定します。

mysql -u root -p

CHANGE MASTER TO
 MASTER_HOST='172.16.1.116',
 MASTER_PORT=3306,
 MASTER_AUTO_POSITION=1;
 
START SLAVE USER='repl' PASSWORD='<パスワード>';

「START SLAVE」でユーザ名を指定する場合は MASTER_USER ではなく「USER」、パスワードも MASTER_PASSWORD ではなく「PASSWORD」オプションで指定します。

ただ、こうした場合、スレーブサーバを再起動するたびにユーザ名とパスワードを入力しなければならないので、セキュリティと運用のバランスを見て、検討する必要があるかと思います。

レプリケーションの動作確認

「SHOW SLAVE STATUS」コマンドの表示で Slave_IO_Running と Slave_SQL_Running がどちらも「Yes」になっていれば、レプリケーションが正常に動作しています。

SHOW SLAVE STATUS\G
(略)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(略)

よくあるエラーの原因と対策(UUIDの重複)

レプリケーションが開始しない原因としてよくあるのが、UUID(server-uuid)の重複です。「SHOW SLAVE STATUS」で確認すると以下ようなエラーメッセージが表示されます。

Slave_IO_Running: No
Slave_SQL_Running: Yes
(略)
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

UUIDは「/var/lib/mysql/auto.cnf」などに保存されているのですが、例えばAWSなどのクラウドサービスで、MySQL設定済のテンプレートから複数のサーバを作成すると、「/var/lib/mysql/auto.cnf」がどのサーバも同じものになるため、当然ですがUUIDが重複します。

このエラーが発生した場合は、以下の手順でUUIDを新たに生成してから、レプリケーションを開始します。

MySQLを一度停止します。

systemctl stop mysqld

UUIDが保存されている「/var/lib/mysql/auto.cnf」を削除します。

rm /var/lib/mysql/auto.cnf

MySQLを起動します。このとき新たに「/var/lib/mysql/auto.cnf」作成され、重複のないUUIDが生成してくれます。

systemctl start mysqld

一度スレーブをリセットしてから、レプリケーションを開始します。
mysql -u root -p

STOP SLAVE;
RESET SLAVE;
START SLAVE;

GTIDを使う本当のメリット

単純なレプリケーション構成の場合、GTIDを使うメリットが無いように思えますが、GTIDを使用したレプリケーションは「データを正しく同期する」というレプリケーション本来の目的を、とてもシンプルな方法で解決しようとしています。GTIDを使う本当のメリットはここにあると私は思います。

おわりに

この記事を書くのにあたって「詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)」を参考にさせて頂きました。この書籍では、GTID以外にもMySQL5.7で追加/変更された、175もの新機能や変更点が詳細に解説されています。これからMySQL5.7を使おうかなと思っている方や、既にMySQL5.7を使っている方にも、ぜひオススメの一冊です。

スポンサーリンク
Pocket
LINEで送る

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です