MYSQLレプリケーション設定

MySQLのインストールが終了した２台がある状態より設定を開始します。
マスターとスレーブの２台がある状態からスタートです。

事前準備

事前に２台のサーバーのデータが同じである必要があります。
mysqldump等でダンプを行い、データベースが完全に同じ状態とします。

マスターの設定

設定ファイルの編集

vi /etc/mysql/my.cnf

以下のように編集

server-id       = 1
↓
server-id       = 18  (←ユニークな適当な数字へ変更)

また以下を設定ファイル内のどこかに追記(ある場合はそのままでOK)
log-bin = mysql-bin

アカウントの作成
レプリケーション用のアカウントをマスターに作成する。
このアカウントはREPLICATION SLAVE権限を持っている必要がある
以下はすべての接続を許す’repl’ユーザーを作成する例。(グローバルに出ている場合は危険です)
passwordは任意に変更する

mysqlにログイン
mysql -u root -p

すべてのSlaveの接続を許可するユーザーを作成

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'password';

マスタ上の現在のバイナリログ名とオフセット値を読み取る。

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

ロックしている間にマスタ上の現在のバイナリログ名とオフセット値を読み取る。

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      189 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

今回のような例なら　mysql-bin.000004と189　をメモしておく
Empty set (0.00 sec)と出て表示されなかった場合はFileは”"(空文字)、Positionは4となる
メモが終わったらロックを解除する。

UNLOCK TABLES;

ここで設定をさせる為にMysqlを再起動する

スレーブの設定

設定ファイルの編集

vi /etc/mysql/my.cnf

以下のように編集

server-id       = 1
↓
server-id       = 18  (←ユニークな適当な数字へ変更)

また以下を設定ファイル内のどこかに追記(ある場合はそのままでOK)
log-bin = mysql-bin

　　※マスターの番号とかぶらないように注意する！
設定後、mysqld を再起動する。

レプリケーションの開始
mysqlにログイン
　mysql -u root -p

CHANGE MASTER TO
MASTER_HOST='マスターのIPアドレス',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=189;

確認

○スレーブ側にて
mysqlへログインし

SHOW SLAVE STATUS\G

と実行する事で現在のレプリケーション状態を確認する事ができます。

*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.100.210
                 Master_User: test
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000004
         Read_Master_Log_Pos: 189
              Relay_Log_File: testcam-relay-bin.000022
               Relay_Log_Pos: 251
       Relay_Master_Log_File: mysql-bin.000004
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes

Slave_IO_StateがWaiting for master to send event
Slave_IO_RunningがYes
Slave_SQL_RunningがYes
Master_Log_FileとRead_Master_Log_Posが、
マスター側をロックした際にメモした値と同じ値

以上が確認できればおそらく正常に働いています。

レプリケーションテスト
マスター側にデータベースを作成してみます。

create database test_db;

作成できたか確認

show databases;

スレーブ側にマスターで行われた変更が反映されているかを確認します。

show databases;

その他レプリケーション関係のコマンド

SLAVEを起動

START SLAVE;

SLAVE停止

STOP SLAVE;

SLAVEの設定を読み込みなおして再起動

RESTART SLAVE;

SLAVEの設定を解放

reset slave;

トラブル

レプリケーションが止まった時
通常レプリケーションはデータの整合性が取れない場合は
エラーを吐いてそのまま停止してしまう。

もしそういった場合に停止してしまった場合は

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

slaveで↑実行するとエラー内容をスキップしてくれる為復帰するかもしれません

うまくレプリケーションが開始されない時
・ping確認
・ポートの解放 　デフォルトでは　3306番のポートを使用します
・設定変更後mysqlの再起動を行っていない

監視スクリプト

/usr/local/mysql/bin/mysql -u root -pパスワード -e 'show slave status \G'

を使用するとステータスが取れる事を利用する
検索すると似たようなスクリプトがあったので少し改造して作ってみました。

#!/bin/sh

PASSWORD="パスワード"

eval  "`/usr/local/mysql/bin/mysql -u root -p${PASSWORD} -e 'show slave status \G' | sed -ne 's/: \(.*\)/="\1"/p' `"
if [ "$?${Slave_IO_Running}${Slave_SQL_Running}" != "0YesYes" ]; then
/usr/local/mysql/bin/mysql -u root -p"${PASSWORD}" -e 'show slave status \G' |/usr/bin/mail root@hogehoge.com -s "REPLICATION DOWN "`hostname`
exit 1
fi

Slave_IO_RunningとSlave_SQL_Runningの値がyesでなければ
show slave statusで取得できるステータス一覧をメールで送信するスクリプト。
PASSWORDの部分とアラート送り先のメールアドレスを替えてください。

MySQL ではクエリに\Gを追加するとフィールドごとにリスト形式で表示するので、
これを sed でシェル変数定義に加工して、eval で展開し、フィールド名から値を取得。

これをCronに登録しておけばレプリケーションが停止している事をメールで気づく事ができます。

0/15/30/45 * * * * root /usr/local/etc/replck.sh