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

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

Contents

1 レプリケーションの基礎知識
2 実際の構築手順(ローカルで実行できる環境)
- 2.1 dockerでの構築
- 2.2 DB側の構築手順

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

定義

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

構成

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

メリット

参照性能の向上

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

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

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

１何らかの更新処理(マスターを参照)
↓
2 スレーブからデータを取得

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

可用性の向上

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

仕組み

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

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

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

dockerでの構築

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

docker-compose.yml

version: '3'
services:
  #phpは使わなくていいかも・・・・
  php7:
    container_name: "php7"
    ports:
      - "8080:80"
    build:
        context:    "./docker/php7"
        dockerfile: "Dockerfile"
    volumes:
        - .:/var/www/html
    networks:
      db_net:
        ipv4_address: 192.168.192.2

  master_db:
    image: mysql:5.7
    container_name: "master_db"
    ports:
      - "3306:3306"
    environment:
        - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    volumes:
        - "./docker/master_db/my.cnf:/etc/mysql/conf.d/master.cnf"
        - "./docker/master_db/data/:/docker-entrypoint-initdb.d/"
    networks:
      db_net:
        ipv4_address: 192.168.192.3

  slave_db:
    image: mysql:5.7
    container_name: "slave_db"
    ports:
      - "13306:3306"
    environment:
        - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    volumes:
        - "./docker/slave_db/my.cnf:/etc/mysql/conf.d/slave.cnf"
        - "./docker/slave_db/data/:/docker-entrypoint-initdb.d/"
    networks:
      db_net:
        ipv4_address: 192.168.192.4

networks:
  db_net:
    driver: bridge
    ipam:
      config:
      - subnet: 192.168.192.0/24

version: '3'

services:

#phpは使わなくていいかも・・・・

php7:

container_name: "php7"

ports:

- "8080:80"

build:

context: "./docker/php7"

dockerfile: "Dockerfile"

volumes:

- .:/var/www/html

networks:

db_net:

ipv4_address: 192.168.192.2

master_db:

image: mysql:5.7

container_name: "master_db"

ports:

- "3306:3306"

environment:

- MYSQL_ALLOW_EMPTY_PASSWORD=yes

volumes:

- "./docker/master_db/my.cnf:/etc/mysql/conf.d/master.cnf"

- "./docker/master_db/data/:/docker-entrypoint-initdb.d/"

networks:

db_net:

ipv4_address: 192.168.192.3

slave_db:

image: mysql:5.7

container_name: "slave_db"

ports:

- "13306:3306"

environment:

- MYSQL_ALLOW_EMPTY_PASSWORD=yes

volumes:

- "./docker/slave_db/my.cnf:/etc/mysql/conf.d/slave.cnf"

- "./docker/slave_db/data/:/docker-entrypoint-initdb.d/"

networks:

db_net:

ipv4_address: 192.168.192.4

networks:

db_net:

driver: bridge

ipam:

config:

- subnet: 192.168.192.0/24

./docker/master_db/my.cnf

[mysqld]
# 各MySQLサーバを識別するためのIDの設定
server-id=1
# バイナリログの有効化
log_bin=mysql-bin

#これ以降はいつも設定している設定

character-set-server=utf8
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
innodb_strict_mode=0

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

[mysqld]

# 各MySQLサーバを識別するためのIDの設定

server-id=1

# バイナリログの有効化

log_bin=mysql-bin

#これ以降はいつも設定している設定

character-set-server=utf8

innodb_file_format = Barracuda

innodb_file_per_table = 1

innodb_large_prefix

innodb_strict_mode=0

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

./docker/slave_db/my.cnf

[mysqld]
# 各MySQLサーバを識別するためのIDの設定
server-id=2

# 更新を禁止する（スレーブ用）
read_only

character-set-server=utf8
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
innodb_strict_mode=0

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

[mysqld]

# 各MySQLサーバを識別するためのIDの設定

server-id=2

# 更新を禁止する（スレーブ用）

read_only

character-set-server=utf8

innodb_file_format = Barracuda

innodb_file_per_table = 1

innodb_large_prefix

innodb_strict_mode=0

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

https://github.com/umanari145/db_replication

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

DB側の構築手順

マスター側設定

起動後手順

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

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

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

+---------------------------------------------------------------+
| Grants for repl_user@192.168.192.4                            |
+---------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.192.4' |
+---------------------------------------------------------------+

+---------------------------------------------------------------+

| Grants for repl_user@192.168.192.4 |

+---------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.192.4' |

+---------------------------------------------------------------+

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

mysqldump --single-transaction -uroot -p example
   &gt; master_db.sql

1 2	mysqldump --single-transaction -uroot -p example > master_db.sql

スレーブ側の設定

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

change master to
   master_host='192.168.192.3',
   master_user='repl_user',
   master_password='repl_pass',
   master_log_file='mysql-bin.000004',
   master_log_pos= 154;

change master to

master_host='192.168.192.3',

master_user='repl_user',

master_password='repl_pass',

master_log_file='mysql-bin.000004',

master_log_pos= 154;

スレーブ側のスタート START SLAVE;
誤って設定した場合は
STOP SLAVE IO_THREAD;
STOP SLAVE;
RESET SLAVE;

確認 SHOW SLAVE STATUS; 下記のようになっていればOK

(略)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(略)

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

確認

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

create table member (id int not null auto_increment primary key, name varchar(100) not null);
insert into member (name) values ('yamada'),('tanaka'),('watanabe');

1 2	create table member (id int not null auto_increment primary key, name varchar(100) not null); insert into member (name) values ('yamada'),('tanaka'),('watanabe');

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