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

JSON型の検索

複数のタグなどを入れる場合、JSON型のカラムやデータなどを入れることがあるかと思いますが、ここから検索できるということが昨日わかりましたので、メモしておきます。 SQLServerで発見しましたが、 …

no image

JPAまとめ

JPAわけわかんねーと思い、触りはじめてから3か月ぐらいたちます。 いまだに細かい部分はよくわかっていないことは多いのですが、全体像というか何となくどういうものかはわかり始めてきたいのでまとめたいと思 …

no image

SQL問題

今までやったSQL問題などのまとめ。定期的にやる予定です・・ 自分用なのでテーブルデータとかあったりなかったりいい加減です(汗) SQLドリル 問題1 nameとageで構成されたテーブルがあるとして …

no image

MySQLのメモリ設定

実務で結構サーバーをヘビーに使われるお客さんがいて、SQLが遅くなったりとしています。 なんとか対策しないといけないので、いろいろと調査をいます。 それで今回はMySQLのメモリ問題など。 MySQL …

no image

NOT EXISTSの利用

引き続きNOT EXISTSの利用です。 思った以上に使えますね・・・がムズイ。 今回は下記のようなテーブル(seats)があるとします。 ケースとしては新幹線の座席番号で空は席が空いている状態、占は …

アーカイブ