FreeBSD8.0 WEBサーバ構築ノート
MySQLのレプリケーション(複製)

MySQLサーバ5.1のレプリケーション(複製)機能のご紹介です
ページ応答速度・ページランク取得タグ ⇒ 応答速度 & Google PageRank


MySQLのレプリケーション(複製)概要

   MySQLサーバー5.1では、マスターサーバーの複製として複数のスレーブサーバーを構成するレプリケーション機能があります。 このレプリケーション機能は、以下のような目的で使用できます。 ぜひ、レプリケーション機能を活用しましょう。

MySQLのレプリケーション方式

   一般的なレプリケーションの方式としては、マスター・スレーブ方式とマルチ・マスター方式があります。 また、同期方式と非同期方式があります。 MySQLのレプリケーションは、マスター・スレーブ方式の非同期方式です。 マスターはスレーブ情報は保持せず、スレーブがマスター情報を保持しています。 そして、マスターは、定期的に接続するスレーブに更新情報を渡します。 一時的に、スレーブサーバーが停止したり、マスターとの通信トラブルが発生しても、サーバーや通信が復旧すれば中断したところから自動的に再開してくれます。

MySQL マスターサーバーの設定方法

   まず、以下のステップでマスターサーバーの設定を行います。

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

   スレーブサーバーからアクセスするユーザとして、REPLICATION SLAVE権限のみを持つユーザをマスターに作成します。 IPアドレス192.168.1.0/24(192.168.1.0~192.168.1.255)に存在するスレーブサーバーから、 ユーザ名を"replication"、パスワードを"password"で接続してレプリケーションを行う場合は、 以下のMySQLコマンドでレプリケーションユーザを作成します。
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.07 sec)

2.バイナリーログおよびサーバーIDの確認

   マスターサーバーのバイナリログには更新系のクエリのみが記録され、レプリケーションだけではなく更新分のバックアップに使用されます。 デフォルトでは、 /usr/local/etc/my.cnf の [mysqld] の項目に次のような行がありますので確認して下さい。
表1 マスターサーバーの /usr/local/etc/my.cnf の設定
[mysqld]
・・・
log-bin=mysql-bin
・・・
server-id = 1
・・・
set-variable = expire_logs_days=7 <-- 7日以前のバイナリログは削除される
これにより、mysqlデータベースのホーム(ここでは/usr/local/mysql)に、バイナリログさ作成されます。 そして、スレーブから要求されるとバイナリログが渡されます。 また、古いバイナリログを消去する煩わしさから逃れるために、表1では7日以前のバイナリログを削除するよう設定しています。 バイナリーログおよびサーバーIDの確認方法は以下の通りです。
表2 マスターサーバに作成されたバイナリーログ
# ls -l
・・・
-rw-rw----1mysqlmysql991083May 10 11:50mysql-bin.000024
-rw-rw----1mysqlmysql1073775454May 12 09:53mysql-bin.000025
-rw-rw----1mysqlmysql1073741923May 13 19:59mysql-bin.000026
-rw-rw----1mysqlmysql984284583May 15 13:37mysql-bin.000027
-rw-r-----1mysqlmysql513May 13 19:59mysql-bin.index
・・・
MySQLのステートメントでもバイナリログが確認できます。
表3 MySQLのステートメントによるバイナリログの確認
mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000027 | 987232269 | | |
+------------------+-----------+--------------+------------------+
さらに、サーバーIDは、マスターサーバーには必ず必要で、MySQLのステートメントでも確認できます。
表4 MySQLのステートメントによるサーバーIDの確認
mysql> SHOW VARIABLES LIKE 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+

3.マスターデータベースのスナップショットの取得

   レプリケーション開始前には、スレーブサーバーのデータベースの内容を、マスターサーバーの内容と同じにしておく必要があります。 マスターサーバーのMySQLは停止できないことが多いですので、ここでは停止しないでコピーを作成する方法について説明します。
  1. 更新系クエリを禁止する
  2.    まず、マスターデータベースのコピーを作成中はマスターデータベースが更新されないように設定します。 MySQLにログインし、次のステートメントで更新系クエリを禁止します。
    mysql> FLUSH TABLES WITH READ LOCK;
    但し、対話型のMySQLクライアントを終了すると、更新系クエリの禁止が解除されてしまいますので、以下の処理は SSH の別ウィンドウで行う必要があります。
  3. tarコマンドで、マスターデータベースをコピーする
  4.    マスターデータベースを全てコピーはするには、/usr/local/mysql フォルダの内容を階層ごと以下のようにコピーします。
    # cd /usr/local/mysql
    # tar cvf mysql.tar .
    ただし、かなり大きなディスクの空き領域が必要ですし、時間もかかってしまいます。 また、データーベースごとにスレーブサーバーに振り分けるとより、より効率的に運営することができますし、時間の節約にもなります。 例えば、"neolux"というデータベースのコピーを作成するには以下のようなコマンドになります。
    # cd /usr/local/mysql/neolux
    # tar cvf neolux.tar .
  5. マスターサーバーのMySQLステータスを調べる
  6. 以下のようにマスターサーバーのMySQLステータスを調べ、現在のバイナリーログファイル名とポジションをメモしておきましょう。 この情報は、スレーブサーバでレプリケーションを有効にする際に必要です。
    mysql> SHOW MASTER STATUS\g;
    +------------------+-----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+-----------+--------------+------------------+
    | mysql-bin.000031 | 137353304 | | |
    +------------------+-----------+--------------+------------------+
    1 row in set (0.00 sec)
  7. 更新系クエリを許可する
  8. 更新系クエリを禁止した SSH のウィンドウで、更新系クエリを許可します。
    mysql> UNLOCK TABLES;

MySQL スレーブサーバーの設定方法

1.スレーブサーバーを停止し、マスターサーバーでコピーしたファイルをスレーブサーバーで展開する

  1. スレーブサーバーを停止する
  2.    スレーブサーバーにはデータが揃っていないため、いつでも停止できるという前提で説明します。
    # /usr/local/etc/rc.d/mysql-server stop
  3. MySQLのファイル・ディレクトリを全て消去あるいは一部消去する
  4.    マスターデータベースを全てレプリケーションするには、MySQLのファイル・ディレクトリを全て消去します。
    cd /usr/local/mysql(スレーブサーバーのMySQLディレクトリ)
    # rm -fr *
    マスターデータベースの一部のデータベースのみレプリケーションするには、該当するデータベースファイルのみ消去します (それに先立って、phpMyAdminなどを使用してレプリケーションする空のデータベースを作成しておく必要があります)。 例えば、"neolux"というデータベースのみレプリケーションするには、以下のようなコマンドで消去します。
    # cd /usr/local/mysql/neolux(データベース"neolux"のディレクトリ)
    # rm -fr *
  5. マスターサーバーからデーターベースのコピーを取得し展開する
  6.    マスターデータベースを全てレプリケーションする場合には、以下のようなコマンドで、 マスターデータベースのコピー /usr/local/mysql/mysql.tar を取得して、展開します。
    # cd /usr/local/mysql(スレーブサーバーのMySQLディレクトリ)
    # ftp 192.168.1.110(マスターのIpアドレス)
    (ユーザ・パスワードを入力)
    ftp> cd /usr/local/mysql(マスターサーバーのMySQLディレクトリ)<--ディレクトりの権限の制約で FTP で取得できない場合には別ディレクトを経由する当の工夫が必要
    ftp> get mysql.tar
    ftp> bye
    # tar xvf mysql.tar
    マスターデータベースを全て展開した場合には不要なファイルも展開されます。 バイナリログやエラーログはスレーブサーバーでは必要ないので削除します。
    # rm mysql-bin.*(ファイル名称はマスターのmy.cnf設定により異なります)
    # rm neolux002.neoluxinc.com.err
    # rm neolux002.neoluxinc.com.pid
    一部のデータベースのみレプリケーションする場合には、該当するデータベースファイルのコピーを取得し、展開します。 例えば、"neolux"というデータベースのみレプリケーションするには、 データベースのコピー /usr/local/mysql/neolux.tar を取得し、展開します。
    # cd /usr/local/mysql/neolux(スレーブサーバーの"neolux"というデータベースのディレクトリ)
    # ftp 192.168.1.110(マスターのIpアドレス)
    (ユーザ・パスワードを入力)
    ftp> cd /usr/local/mysql/neolux(マスターサーバーの"neolux"というデータベースのディレクトリ)
    ftp> get neolux.tar
    ftp> bye
    # tar xvf neolux.tar

2.マスターサーバーのMySQLへ接続できることを確認する

   スレーブサーバーからネットワークを経由してマスターサーバーのMySQLへ接続してみます。
# mysql -h 192.168.1.110 -u replication -ppassword <-- ユーザ名"replication"、パスワード"password"でマスターサーバー192.168.1.110へ接続する。
または、
# mysql -h neolux002.neoluxinc.com -u replication -ppassword <-- ユーザ名"replication"、パスワード"password"でマスターサーバー neolux002.neoluxinc.comへ接続する (この接続にはネームサーバーの設定が必要です)。
または、
# mysql -h neolux002 -u replication -ppassword

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
ユーザ replication には "REPLICATION" 権限しかありませんので、データーベース information_schema しか見えませんが、接続できています。

3.スレーブサーバーの /usr/local/etc/my.cnf を設定する

   MySQL のデフォルト設定では、マスターサーバーですので、スレーブサーバーを認識させるために
[mysqld]
・・・
#log-bin=mysql-bin             <-- コメント化
・・・ server-id = 2        <-- マスターあるいは他のスレーブと重複しない番号
#--------------以下の3行はMySQL コマンドでも設定可能なのでここでは省略
#master-host = neolux002.neoluxinc.com    <-- マスターサーバー名
#master-user= replication          <-- レプリケーション・ユーザー名
#master-password = password          <-- レプリケーション・ユーザーのパスワード
特定のデーターベースのみレプリケーションする場合には、[mysqld] に以下の行を追加します (ここでは データベース名 database1, database2, database3 のみレプリケーションの対象にセットします)。 全てのデーターベースをレプリケーションする場合にはこの記述は必要ありません。
replicate-do-db = database1
replicate-do-db = database2
replicate-do-db = database3

4.スレーブサーバーの MySQL を起動しマスターの設定を行う

   以下に示す通り、スレーブサーバーの MySQL を起動し、MySQL コマンドにより、スレーブサーバーの設定を行い、 スレーブサーバーを再起動します。
# /usr/local/etc/rc.d/mysql-server start
# mysql -ppassword
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'neolux002.neoluxinc.com',
-> MASTER_USER = 'replication',       <-- レプリケーション・ユーザー名
-> MASTER_PASSWORD = 'password',       <-- レプリケーション・ユーザーのパスワード
-> MASTER_LOG_FILE = 'mysql-bin.000027',   <-- スナップショットを取得した時のマスターのログファイル名
-> MASTER_LOG_POS = 987232269;        <-- スナップショットを取得した時のマスターのログファイルポジション
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
最後にスレーブサーバーを再起動します。
# /usr/local/etc/rc.d/mysql-server restart

スレーブサーバーの動作状況を確認する

  
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: neolux002.neoluxinc.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000027
Read_Master_Log_Pos: 992093235
Relay_Log_File: neolux001-relay-bin.000002
Relay_Log_Pos: 158250
Relay_Master_Log_File: mysql-bin.000031
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 988332388
Relay_Log_Space: 44740341
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5965
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
各変数の大体は意味はできると思いますが、各行の意味は概ね以下の通りです。

スレーブサーバーエラーのチェックポイント

スレーブのSQLスレッドが止まる場合の対処方法

   MySQLのデフォルト設定では、SQLクエリでエラーが発生すると、以下のようにSQLスレッドは停止してしまいます。
mysql> SHOW SLAVE STATUS\G
Read_Master_Log_Pos: 592687900
・・・
Exec_Master_Log_Pos: 531720395
・・・
 Last_Error: Error 'Duplicate entry 'webike-rb:10328664' for key 'PRIMARY'' on query. Default database: 'neoluxr'. Query: 'INSERT INTO RakutenArticlesC( ID,Name,Price,ImageURL,ImageHeight,ImageWidth,AffiliateURL,AffiliateRate,MerchantId,Cat1No,Cat4No,TaxFlag,PostageFlag,CreditC ardFlag,NoStock,OrderNum,InStockDate,ReviewCount,Evaluation,Fast ) VALUES( 'webike-rb:10328664','【グリップ】アリート:ariete ハンドルバーグリップ',2794,'http://thumbnail.image.rakuten.co.jp/@0_mall/webike-rb/cabinet/img52/1510540.jpg? _ex=128x128',128,128,'http://hb.afl.rakuten.co.jp/hgc/0a0a77fa.e3e9c52f.0a0a77fb.0d6b12e4/?pc=http%3A%2F% 2Fitem.rakuten.co.jp%2Fwebike-rb%2F1510540%2F',1.0,'webike-rb','C200',110180040,0,1,1,0,44,'20100521062050',0,-1,1 )'
SQLスレッドが停止してしまう原因の殆どは、スレーブ側で誤ってDBを更新してしまった場合です。 上記の場合は、既にある行を追加しようとしたために発生したエラーですので、問題を起こしている行を削除してMySQlを再起動すれば解決できます。 また、以下の通り、SQLクエリを指定ステップ数だけスキップする設定を行い、SQLスレッドを起動する方法でも対処できます。
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
SQLクエリエラーが発生しなければSQLスレッドは停止しませんが、どうしてもSQLスレッドが停止してしまう場合には、 /usr/local/etc/my.cnf の [mysqld] に以下の行を追加することにより、SQLスレッドの停止を避けることができます。
[mysqld]
slave-skip-errors=1062      <-- データの重複エラー(1062)をスキップする
あるいは
slave-skip-errors=ALL       <-- 全てのエラーをスキップする。

スレーブサーバーのI/Oスレッドが停止した場合の対処方法

   MySQLを長期間運用し、マスターサーバーをリブートした場合などのタイミングでI/Oスレッドが停止する場合があります。
mysql> show slave status\G
・・・
Master_Log_File: mysql-bin.001430
Read_Master_Log_Pos: 145611425
・・・
Slave_IO_Running: No   <-- I/Oスレッドが停止している
Slave_SQL_Running: Yes  <-- SQLスレッドは動いている
・・・
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
そして、マスター側のMySQLのホームディレクトリ/usr/local/mysqlを見てみると、mysql-bin.001430がある場合とない場合があります。 mysql-bin.001430がある場合でも、恐らくファイル中にRead_Master_Log_Pos: 145611425が存在しないか、読めないためにエラーになっているのでしょう。 そこで、マスター側で現在のファイルと位置を確認すると以下のようになっています。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001500 | 12203324 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
過去の情報更新はあきらめ、以下の要領でスレーブ側のマスターログファイル名と位置を変更し、スレーブサーバーを動かしましょう。
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001500',MASTER_LOG_POS=12203324;
mysql> START SLAVE;
参考文献:MySQL Chapter 16. Replication
最終更新日:2013年04月20日

* 無断転載禁止。
* どのページでもご自由にリンクして下さい。
* ご意見・ご質問等がございましたら こちらからメールをご送付下さい。

FreeBSD8.0 メニュー
Sponsoredサイト
美容・健康・ダイエットサイト
脳の機能と心と体の健康
健康な心と体の栄養ブログ
占星術・風水・タロット・手相
東洋・西洋占星術
無料姓名判断で読み解く運命
タロットカードで無料占い
手相占いによる未来診断
飛星派風水で住宅開運
夢の夢占い
男と女の心理学と心理テスト
占いと健康ブログ
会社情報等
運営会社情報

プライバシー・ポリシー
通販Neo-楽天編
お楽しみサイト
Western-Eastern Astrology

ザ・ホラリー占星術

便利サイト
株式投資講座(改訂版)
脳の機能と心と体の健康
ネオラクス
株式投資講座(改訂版)
無料SEO対策 無料SEO対策
(153.121.91.33)