MySQL レプリケーション関連Tips

MySQL

前回のブログに引き続き、MySQLのレプリケーションについて。今回は実装レベルでのTipsについても言及。以前ハマったことのある内容を中心に書いておく。

IPアドレス/ホスト名変更対応

  • マスター、スレーブのIPアドレス、ホスト名のいずれか、または両方が変更になった場合スレーブ側で古いマスター情報を覚えてしまうケースがある。基本的には*.infoファイルやmy.cnfのレプリケーション関連パラメータをクリアすればよい。この方法でも以前の情報がクリアできない(マスター情報をInitializeできない)場合はスレーブ側で以下のコマンドを実行する。参考資料(Ver.5.1マニュアル)



mysql> reset slave;

SQLスレッドエラー対処

  • SQLスレッドが発生する典型的なケースとしては、スレーブDBのデータを直接手動で変更してしまい、その後マスターで当該データが更新された場合がある。エラーを発生させたクエリをスキップさせ、スレーブを再起動(stop slave/start slave)すれば同期が再開される。ただし、どのデータがエラー原因になったかはスレーブのエラーログまた「show slave status」などで確認する必要あり。参考資料(Ver5.1マニュアル)



mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=x;

マスターでbinlogを出力せずにクエリを実行



mysql> SET SQL_LOG_BIN=0;

テーブル名大文字/小文字区別

  • 要件次第ではあるが、一般に設計段階でテーブル名大文字/小文字では開発環境と本番環境との差異を考慮する必要がある
    • ex) 本番DB→Linux/UNIX、開発マシンDB→Windowsである場合、デフォルト設定では正常に動作しない
  • プラットフォーム間の互換性を維持(テーブル名の大文字小文字を区別しない)するため、Linux/UNIXではデフォルトの0ではなく1を指定するのがよい
    • lower_case_names_table_names=1（大文字小文字の区別ON）にする場合には、replicate-do(ignore)-dbは必ず小文字で指定。

レプリケーション対象設定

  • replicate-do-dbとreplicate-ignore-dbの併用は基本的に使用不可
  • replicate-do-dbの先を変更(修正・追加)した場合、インスタンスの停止-開始間にバッファが必要
    • 起動スクリプト改修しsleepを挿入するなどの対策が必要

DNS逆引き問題

  • DNS有効環境の場合、逆引きがうまくいかないとレプリケーション正しく機能しない場合がある(5.0.22)
  • 対策方針は以下の通り
    • そもそもの下原因であるDNSの解決(hostsファイルでの解決、DNSの修正、DNSを見に行かないようMySQLに設定追加など)
    • MySQLのバージョンアップ

スレーブ障害復旧後の同期

  • レプリケーションを構成するスレーブが長時間停止しその後復旧した場合、スレーブは自身の機能で自動同期を図るが、マスターからスレーブに大量のbinogが転送される際エラーが発生し同期できない場合がある
  • 明確な原因は不明だが、スレーブのエラーログにmax_allowed_packet関連のメッセージが記録されることためこの辺りが原因と思われる。max_allowed_packetパラメータの是正で解決できない場合には、マスターのdumpデータなどからスレーブを最新のDBに近づけてから再同期、スレーブ停止時からの差分を個別にbinlog適用(ロールフォワード)を行い最新化、などの対処が必要
    • 大規模DBの場合にはスレーブ復旧に時間がかかってしまうのが難点
    • バックアップ・リカバリでInnoDB HotBackupを採用する場合にはある程度復旧時間は削減可能