skillup

技術ブログ

Database

MySQLのレプリケーションについて

投稿日:

MySQLのレプリケーションについて調べたことなどを。

レプリケーションの基礎知識

定義

  • DBを複製して、別サーバーでも管理できるようにすること
  • バックアップと違い、リアルタイムで変更を反映したDBをもつ

構成

  • サーバーは一般的にマスター(わかりやすくいうとメイン)とスレーブ(サブ)に分割される
  • マスターはメインのDB主に、データの変更と参照両方ができ、スレーブに変更内容を転送、複数のスレーブが持てる。
  • スレーブはマスターの変更内容を受け取り、基本的には参照のみに使われることが多い。

メリット

参照性能の向上

重いSELECT文などがある場合はスレーブを参照、更新系はマスターをみるなどとすることで負荷を適宜に分散させることができる。

注:基本的に一瞬でスレーブには転送されるのですが、それでもほんの少しラグが起こります。

以前、レプリケーションを使ったDBを使っている時

1 何らかの更新処理(マスターを参照)

2  スレーブからデータを取得

とした時にスレーブに変更が反映されていない・・ということがありました。ほんの少しですがラグがあったので注意しましょう。

可用性の向上

マスター障害時にマスターをスレーブに昇格するなどして、障害時の復旧コストを下げることができる

仕組み

マスターのバイナリログをスレーブに転送することで実現することができる。

MySQL入門 レプリケーション編

実際の構築手順(ローカルで実行できる環境)

dockerでの構築

ローカルではdockerを使うのが一番やりやすいと思います。

docker-compose.yml

./docker/master_db/my.cnf

./docker/slave_db/my.cnf

https://github.com/umanari145/db_replication

docker-composeでお手軽mysqlのmaster-slave構成

DB側の構築手順

マスター側設定

起動後手順

  1. masterの設定が正常に読み込まれているか状態の確認 SHOW MASTER STATUS 下記のような形式のデータが入っているはず(値自体は変わってくる)

  1. 任意のデータベース作成 CREATE DATABASE example
  2. スレーブの設定 GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.192.4' IDENTIFIED BY 'repl_pass'
  3. 権限確認 SHOW GRANTS FOR 'repl_user'@'192.168.192.4'で下記のレコードがあることを確認

  1. マスター側のダンプデータの作成

スレーブ側の設定

  1. スレーブのDB作成 CREATE DATABASE example
  2. マスター側のダンプデータのリストア mysql -uroot -p example < master_db.sql
  3. スレーブ側の設定 マスター側の操作で設定したホスト、ユーザー、パスワードを設定

  1. スレーブ側のスタート START SLAVE;
    誤って設定した場合は
    STOP SLAVE IO_THREAD;
    STOP SLAVE;
    RESET SLAVE;
  1. 確認 SHOW SLAVE STATUS; 下記のようになっていればOK

確認

下記のようなSQLをマスター側に流し、スレーブに反映があるかいなか

あぱーブログ MySQL 5.6 マスター/スレーブサーバの設定メモ

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

MySQLでtext型が大量にあるもののリストア 

MySQLでのリストアについて。 先日実務でtext型のカラムが複数あるテーブルを読もうとしたら下記エラーがでてこけました。

なにやら …

no image

cakePHPでの直SQL

今回はCakePHPにて直のSQLを書く方法を。 cakePHPにて大概の処理はもともと備わっているコマンドでなんとかなりますが、まれに直SQLを書いたほうがらくなこともあります。 書き方その1 [c …

no image

アンチパターン 連鎖倒産+エラー監視+データのバックアップ

本日は主にインフラの設計的なことに関して。 Contents1 連鎖倒産1.1 デメリット1.2 対策2 エラー監視2.1 デメリット2.2 対策3 データのバックアップ体制3.1 デメリット3.2 …

no image

joinとeager loading

フレームワークでデータをORMがらみでjoinするときのネタ。 自分の場合はLaravel。他のフレームワークでも考え方は通じるものあるかと・・ Contents1 通常のjoin2 ループの中で取得 …

no image

SQL基礎 case式について

case式に関して。 集約系の関数では複雑な処理を一気に行うことができる。 case式は1列のみ有効。複数の列に対して行うことはできない。 case ~ when ・・・thenではwhenが評価され …

アーカイブ