MySQL レプリケーションの設定

Rev.11を表示中。最新版はこちら。

作成中

MySQLのレプリケーション機能を使ってMaster,Slave構成のレプリケーションを行う時のメモ。

冗長化

MasterがダウンしたときにSlaveを使用することで、ダウン時間を短くできる。

負荷分散(読み込み系のクエリのみ)

SELECTクエリをSlave側にも処理させることで、負荷を分散できる。UPDATEなどの更新系のクエリはMasterで処理しなければならない。

SlaveサーバーでのDBバックアップ

DBのバックアップをスレーブ側で行える。バックアップの際は、不完全なデータとならないように書き込みをロックして行う必要があるが、Slave側でバックアップを取ることでMasterでデータベースの書き込みをロックする必要がなくなる。

1.設定手順 (Master-Slave構成)

1.1 Master側の設定の確認

Master側ではバイナリログを採取しておく必要があるので、Master側のmy.cnfにlog-binの設定が入っていることを確認する(デーモン起動時のオプションで指定してあってもよい)。また、ついでにserver-idが設定されていることを確認する。

[mysqld]
log-bin      <== バイナリログを取ることを指定
server-id=1  <== 識別用ID。任意の値。他のサーバーと重複しないようにする。

設定されていない場合は、my.cnfを修正してMaster側のmysqldを再起動しておく。

1.2 アカウントの作成

SlaveからMasterへ接続する際のアカウントをMaster側に用意しておく必要がある。このアカウントはREPLICATION SLAVE権限を持っている必要がある。

以下は10.0.0.2(SlaveのIPアドレス)から接続を許す'repl'ユーザーを作成する例。

(Master側で)
mysql> GRANT REPLICATION SLAVE ON *.* TO
   repl@10.0.0.2 IDENTIFIED BY 'password';

1.3 データベースのバックアップ

MasterのデータベースをSlaveにコピーする。

コピーの際、データベースが更新されないように書き込みをロックする。

(Master側で)
mysql> FLUSH TABLES WITH READ LOCK;

現在のバイナリログの状態を確認(ロック中に行うこと)。

(Master側で)
mysql> SHOW MASTER STATUS;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| tank-bin.000001 |       79 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

現在のバイナリログのファイル名と位置が表示されるので、記録しておく。これらは、Slaveを起動する時に必要になる。

Master側のデータベースをバックアップする。データベースは書き込みのロックがされているのでtarでアーカイブすればよい。以下ではfriendDBのみバックアップしている。全てのデータベースをレプリケーションしたい場合は、全データベースをバックアップしておけばよい。

(Master側で)
# cd /var/lib/mysql/               <== MySQLのデータディレクトリへ移動
# tar cvf ~/friendDB.tar friendDB  <== データベースをバックアップ

データベースのバックアップが完了してしまえば、書き込みのロックは解除すればよい。

(Master側で)
mysql> UNLOCK TABLES;

1.4 データベースのコピー

1.3でバックアップしたデータベースSlave側に持っていく。1.3で作成したtarファイルをftpなどでSlave側へ持っていき、MySQLのデータディレクトリに展開すればよい。ここで、展開したファイルはMaster側と同じパーミッションにしておくとよい。

(Slave側で)
# cd /var/lib/mysql/
# tar xvf ~tomita/friendDB.tar
パーミッションを確認しておくこと。

1.5 Slave側の設定

Slave側のmy.cnfにserver-idを設定する。

[mysqld]
server-id=2  <== 識別用ID。任意の値。他のサーバーと重複しないようにする。

設定後、Slave側のmysqldを再起動する。

1.6 レプリケーションの開始

Slave側でMasterへ接続するためのパラメータ(ホスト名やユーザー名)を設定する。

(Slave側で)
mysql> CHANGE MASTER TO
        MASTER_HOST='10.0.0.1',  <== Masterのホスト名/IPアドレス
        MASTER_USER='repl',      <== Master接続に使用するユーザー名
        MASTER_PASSWORD='password', <== パスワード
        MASTER_LOG_FILE='tank-bin.000001', <==1.3で確認したFile
        MASTER_LOG_POS=79;       <== 1.3 で確認したPosition

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

(Slave側で)
mysql> START SLAVE;

この時点で、1.3で書き込みロックを解除してからのMasterデータベースの変更はSlave側にも反映される。これ以降、Masterで発生した変更はSlaveにも通知されてデータベースの内容が同期される。Slave側のmysqldを落として再起動した場合でも、その間の変更分は自動的に同期される。

同期ができていない場合は、Slave側のエラーファイルにログがでているはずなので確認すること。

MasterとSlaveで文字コードの設定などが違っていたりすると、Masterに接続できなかったりするので注意。

060612 15:36:17 [ERROR] The slave I/O thread stops because master and slave have different values for the COLLATION_SERVER global variable. The values must be equal for replication to work
060612 15:36:17 [ERROR] Slave I/O thread exiting, read up to log 'tank-bin.000001', position 79

2. 関連コマンド

2.1 START SLAVE/STOP SLAVE (Slave用)

START SLAVEでレプリケーションを開始し、STOP SLAVEでレプリケーションを停止する。

STOP SLAVEをすると、Master側のデータベース変更を受け取らなくなりデータベースが不一致になる。再度、START SLAVEを行うと、Masterへ接続し前回レプリケーションしていた時からの変更を受け取りデータベースを同期させる。

2.2 SHOW MASTER STATUS (Master用)

バイナリログの状態(ファイル名や現在位置)を表示する。

2.4 SHOW BINLOG EVENTS (Master用)

バイナリログの中身を表示。

2.3 SHOW SLAVE STATUS (Slave用)

Slaveの情報を表示する。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.0.0.1
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: tank-bin.000001
                <略>