Home > サーバー技術情報 > MYSQLレプリケーション設定

MYSQLレプリケーション設定

投稿日2009-11-25 (水)
更新日2010-09-22 (水) 21:29
サーバー技術情報

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

次の記事→ Poundでロードバランサを実現する
前の記事→ PHPのインストール

コメント:4

akirakoyasu 10-12-30 (木) 22:13: とても参考になりました。公開ありがとうございます。

ただ、一つだけ気になることがあります。
このコードだと「show slave status \G」を２回実行することになりませんか？（５行目と７行目です）

実行は１回だけにして、変数に入れるなどした方がよいのではないかと思うのですが、いかがでしょう。
akirakoyasu 10-12-30 (木) 22:17: 補足：
すいません、言葉足らずでした。「監視スクリプト」の項についてのことです。
エーエイチレフサーバー管理担当 11-01-19 (水) 16:22: コメントありがとうございます！
確かに、変数にしてしまった方がその時の状態を確認する事にもなりますしいいですね。

また機会をみて検証させて頂き、変更させていただきたいと思います。

ありがとうございます！
mat 11-12-31 (土) 21:23: この記事をみてレプリケーションの設定ができました！ありがとうございます。

監視スクリプトも利用させていただきます。

トラックバック:5

この記事のトラックバックURL: http://www.ahref.org/tech/server/server-tips/546.html/trackback
この記事へのリンク: エーエイチレフ linuxサーバー技術情報からの記事 MYSQLレプリケーション設定

pingback from Aiks Blog - MySQLレプリケーション　スレーブ監視スクリプト 10-06-17 (木) 14:54: [...] MYSQLレプリケーション設定 [...]
pingback from MySQLレプリケーション動作監視スクリプトを作ってみた « 沖縄人による舞ブログ 10-09-02 (木) 23:24: [...] MYSQLレプリケーション設定 [...]
trackback from Akira Koyasu's WebLog 10-12-30 (木) 22:03: MySQLのレプリケーションを監視するシェルスクリプト（２）

という感じで書いてはみたものの、改めて検索してみたらもっとカッコイイ書き方があった。「金槌を持つ人にはすべてが釘に見える」とはよく言ったもので、前回はついawkで書いてし…
pingback from メモ帳 » Mysqlレプリケーション 11-01-19 (水) 11:23: [...] 《参考サイト》現場指向のレプリケーション詳説 MySQL レプリケーションの設定大規模システムを支えるMySQLのレプリケーション機能 MYSQLレプリケーション設定 [...]
pingback from レプリケーションの設定 | マジですCAR 11-12-31 (土) 23:05: [...] 参考サイト１ [...]