MySQL5 [レプリケーション]

レプリケーションとは複数のデータベースの内容を同期させるメカニズムです。マスター・スレーブの関係で構成され、 マスターへの変更は即座にスレーブにも反映されます。
更新日 2016-02-13

概要

仕組みはシンプルです。データベースは本来、変更系のクエリ文の実行によってのみ 変化していくものなので、同じクエリをそっくり真似して実行すれば出来上がるデータベースも 全く同じになるという訳です。
具体的にはスレーブ用として別に準備したMySQL を使って、マスター側のMySQL データベースに問い合わせを行い、 バイナリログに保存されている過去に実行された変更系のクエリをそのままスレーブサイドでも実行します。 以後スレーブ側は常々マスターを監視し、新規に実行されたクエリを即座にスレーブにおいても実行します。
mysqldump と違い、差分更新という点で実に効率的です。スレーブは別のマスターとしても振舞えるため、何台もの チェーンを構築する事もできます。デフォルトでポート3306 を使ってTCP で動きます。

設定

マスター側の準備

スレーブ側から接続されるときに使うユーザーを専用に作ります。今回はユーザー'user_replication'、パスワード 'pass_replication' とし、LAN からの接続に限定しておきます。
GRANT REPLICATION SLAVE ON *.* TO user_replication@'192.168.1.0/255.255.255.0'
 IDENTIFIED BY 'pass_replication';

データベースの初期同期

マスター側のバイナリログがまだ設定していない、もしくはどの段階から記録されているか不明瞭な為、 現時点での同期は常にフルバックアップに頼らざるえません。mysqldump コマンドを使ってデータベースの 内容をテキスト(UTF8) として出力します。
// マスターにおいてバックアップファイルに出力
mysqldump -u root -p --all-databases > /all.db

// /all.db をスレーブに転送

// スレーブにおいてバックアップファイルより再構築
mysql -u root -p < /all.db

マスター側のmy.cnf の編集

マスター側のmy.cnf の[mysqld] ディレクティブにてserver-id とlog-bin を設定します。server-id は サーバーの識別値であり、レプリケーションを行う上でユニークでなければなりません。今回は適当に100 に しています。log-bin と書くだけで有効なバイナリログ出力ディレクティブですが、さらにバイナリログの 書き出しファイル名としてのプレフィックスもつけれます。
[mysqld]
log-bin=mysql-bin
server-id=100
設定を有効にする為にMySQL を再起動します。

スレーブ側のmy.cnf の編集

同じようにスレーブ側でもserver-id を割り当てます(マスターとは違うユニークな値を)。
[mysqld]
server-id=101

マスターの持つバイナリログのファイル名と位置を確認する。

マスター側のMySQL 端末にログインし、更新系のコマンドを受け付けないようにしておいて現時点での バイナリログのファイル名と位置を確認します。
// マスター側
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000005 |      120 |              |                  |
 +------------------+----------+--------------+------------------+
次にスレーブ側を設定します。それまで更新系コマンドのロックを継続します。ロックを短時間で済ませたい 場合は、次のスレーブ側の操作もある程度準備しておくと良いでしょう。

スレーブ側でCHANGE MASTER TO 文を発行する。

CHANGE MASTER TO 文はレプリケーションの設定を行うコマンドです。マスターのあるホスト名(アドレス)、 レプリケーション専用のユーザー名およびパスワード、先ほど確認した最新のバイナリログファイル名に 位置を設定します。続いてスレーブ機能を有効にするSTART SLAVE 文を実行します。
// スレーブ側
CHANGE MASTER TO
MASTER_HOST='192.168.24.6',
MASTER_PORT=3306,
MASTER_USER='user_replication',
MASTER_PASSWORD='pass_replication',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=120;

START SLAVE;
この段階でレプリケーションは有効になりました。スレーブはマスターを常に監視しています。マスター側の 更新ロックを忘れずに解いておきましょう。(MASTER_HOST、MASTER_USER、MASTER_PASSWORD は再設定時は省く事もできる)
// マスター側
UNLOCK TABLES;

動作確認

マスターでテーブルの一つでも試しに製作してみてください。スレーブにも反映される事が確認できれば 成功です。レプリケーションは常にマスターからスレーブへの一方通行なので、くれぐれもスレーブ側で 更新系のクエリを発行しない事!誤ると整合性を失います。

トラブルシューティング

スレーブを別のスレーブのマスターにする際の注意点

スレーブが記録するバイナリログは、マスター時のソレとは別になります。このままだと別のスレーブのマスターとなっても 更新が反映されません。起動オプションに --log-slave-updates オプションを付けてスレーブを起動すると、更新の処理が バイナリログにも記録されるようになります。
vi /etc/rc.d/init.d/mysql

 # Give extra arguments to mysqld with the my.cnf file. This script
 # may be overwritten at next upgrade.

 pid_file=$server_pid_file
 $bindir/mysqld_safe  --log-slave-updates --datadir=$datadir ・・・

レプリケーションの対象としたいデータベースを追加する

まずマスターにロックをかけて、新規に追加したいデータベースの内容をマスターからスレーブにコピーしておきます。 次にスレーブ側の/etc/my.cnf にてreplicate-do-db を追加します。スレーブのMySQL を再起動して、CHANGE MASTER TO を 行い、start slave ならびにマスターのロック解除を行います。
// 特定のデータベースをレプリケーションの対象から外す場合
replicate-ignore-db=ast_db

スレーブの/etc/my.cnf を編集して再起動するとレプリケーションが動かない

通常の再起動では、レプリケーションは直ちに正しく再開されます。これは/etc/my.cnf よりもレプリケーションの最新の進捗情報を 残したmaster.info ファイルと relay-log.info を優先して参照しているからです。しかし/etc/my.cnf に変更が加えられている場合には、 そちらが優先されてレプリケーションの最新の進捗情報も破棄されます。実に面倒ですが、CHANGE MASTER TO 等をやり直さなければ いけません。

スレーブ側に対するデータベース、テーブルの追加は可能

レプリケーションはあくまでもマスター側で実行されたクエリをスレーブでも実行するだけなので、 スレーブ側においてデータベースを新規に作成したりテーブルを構築する事も可能です。
もちろんレプリケーションされているテーブルに対して行えば、マスターとスレーブの同期は損なわれます。

auto.cnf について

auto.cnf はデータディレクトリに自動で作成される設定ファイルで、server-uuid を保持しています。仮想環境下で マスターの複製をスレーブとした場合、値がバッティングするので問題アリです。削除しておきます。

レプリケーションが止まった場合

マスターで実行したSQL がスレーブにおいてエラーになると、レプリケーションのSQL スレッドが停止する事があります。以下の手順で 再実行させます。
SHOW SLAVE STATUS\G;
// Slave_SQL_Running: No

// エラーを確認し、スキップして継続
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
エラーの度に止まります。その場合は上記のスキップを繰り返してください。

GTID によるレプリケーション

my.conf の編集

スレーブにコピー

// Slave
 STOP SLAVE;
 RESET MASTER;
 RESET SLAVE;

 CHANGE MASTER TO
 MASTER_HOST='192.168.24.6',
 MASTER_PORT=3306,
 MASTER_USER='user_replication',
 MASTER_PASSWORD='pass_replication',
 MASTER_AUTO_POSITION=1;

// Master 
mysqldump -u root -p --all-databases > /all.db

// Slave
mysql -u root -p < /all.db

START SLAVE;