MySQL のレプリケーションを試してみる

はじめに

Mac のローカルで MySQL のレプリケーションを構築する機会があったので手順をまとめてみます。

レプリーションやリカバリなどを一度経験しておくと Amazon RDS などのクラウドサービスにおいても役に立つ場面があるかもしれません。

環境準備

Docker で mysql:8.0.39-debian を使用してレプリケーションを構築します。

ディレクトリ構成

├── .docker
│   ├── replica
│   │   ├── Dockerfile
│   │   └── my.cnf
│   └── source
│       ├── Dockerfile
│       ├── init.sql
│       ├── insert.sh
│       └── my.cnf
|
|── compose.yaml

ソースのリソース

Dockerfile

FROM mysql:8.0.39-debian
# 設定ファイルを配置
COPY ./my.cnf /etc/mysql/conf.d/my.cnf
# コンテナ初回起動時に実行する sql を配置
COPY ./init.sql /docker-entrypoint-initdb.d/init.sql
# レプリケーション確認用スクリプトを配置
COPY ./insert.sh /root/insert.sh 
RUN chmod u+x /root/insert.sh

my.cnf

レプリケーションを構築するソースとレプリカのサーバシステム変数 server_id は異なる値を設定する必要があります。またレプリケーションはバイナリログを使用します。MySQL 8系からデフォルトでバイナリログは有効です。

設定ファイル（ my.cnf ）を使用して server_id を明示的に指定します。

[mysqld]
server_id=1000

init.sql

コンテナ初回起動時に説明で使用するデータベース（ sample_db ）とテーブル（ sample ）の作成およびデータを挿入する SQL を発行します（ MySQL はデータベースをスキーマと呼びますが本記事ではデータベースと記載します）。
docker-entrypoint-initdb.d に配置した sql ファイルはコンテナ初回起動時に自動実行されます。

CREATE DATABASE IF NOT EXISTS `sample_db`;
USE `sample_db`;
CREATE TABLE IF NOT EXISTS sample (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO sample (name) VALUES ('Alice');

insert.sh

レプリケーション構築後はソースに加えた変更がレプリカに同期されます。
同期を確認するためにソースに 100 件データを INSERT するスクリプトを作成しておきます。

#!/bin/bash

set -eu

MYSQL_PWD=password
export MYSQL_PWD

for i in {1..100}
do 
  mysql -u root -A sample_db -e "INSERT INTO sample (name) VALUES ('Bob{i}');"
done

レプリカのリソース

Dockerfile

FROM mysql:8.0.39-debian

COPY ./my.cnf /etc/mysql/conf.d/my.cnf
RUN apt-get update \
  && apt-get install -y less 

my.cnf

[mysqld]
server_id=1001

compose.yaml

services:
  db-source:
    build:
      context: .docker/source
      dockerfile: Dockerfile
    hostname: source
    restart: always
    volumes: 
      - db-source:/var/lib/mysql
    networks:
      - db-network
    environment:
      MYSQL_ROOT_PASSWORD: password
    platform: linux/amd64
  db-replica:
    build:
      context: .docker/replica
      dockerfile: Dockerfile
    hostname: replica
    restart: always
    volumes: 
      - db-replica:/var/lib/mysql
    networks:
      - db-network
    environment:
      MYSQL_ROOT_PASSWORD: password
    platform: linux/amd64

networks:
  db-network:
    name: netowrk.local
    driver: bridge

volumes:
  db-source:
    driver: local
  db-replica:
    driver: local

Docker はサービスディスカバリを使用してサービス名でコンテナ間通信ができますが分かり易さのために hostname（ホスト）を明示的に指定しています。

レプリケーションを構築

上記 Docker 環境でポジションレプリケーションを構築します。
レプリケーション構築方法を大まかに記載することが本記事の目的なので、各コマンドの詳細は説明しません。
また本記事は MySQL キーワードと予約語を小文字で記載します。

前提

source, replica の両ホストのデータベースにホスト % ユーザー root でアカウントを作成している前提で進めます。
環境準備の compose.yaml を使用すればアカウント root@% が作成されています。

1. ソースのフルバックアップを取得

ソースのフルバックアップを取得します。

host:$ docker compose exec db-replica /bin/bash

root@replica:$ mysqldump -h source -u root -p --single-transaction --default-character-set=utf8mb4 --source-data=2 --routines --triggers --events --hex-blob --all-databases > dump.sql

2. フルバックアップからレプリケーション開始ポジションを取得

フルバックアップを dump.sql として進めます。

root@replica:$ less dump.sql | grep -A3 "Position to start replication or point-in-time recovery from"
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=157;

上記例ではレプリケーション開始ポジションはバイナリログ binlog.000002 の 157 です。

3. フルバックアップをレプリカにリストア

フルバックアップをレプリカにリストアします。

root@replica:$ mysql -h replica -u root -p < dump.sql

ホスト（ -h ）の指定は省略できますが、分かり易さのために明示しています。

4. レプリケーションを設定

レプリカで以下ステートメントを発行します。
source_log_file, source_log_pos は 2. フルバックアップからレプリケーション開始ポジションを取得 で取得した値です。

root@replica > change replication source to source_host='source', source_port=3306, source_user='root', source_password='password', source_log_file='binlog.000002', source_log_pos=157;

5. レプリケーションを開始

レプリカで以下ステートメントを発行します。

root@replica > start replica;

6. レプリケーション構築を確認

レプリカで以下ステートメントを発行します。

root@replica > show replica status\G

Rplica_IO_Running, Replica_SQL_Running が Yes なら正常にレプリケーションが構築・開始されています。

7. レプリカに同期されることを確認

ソースに 100 件データを追加します。

host:$ docker compose exec db-source /root/insert.sh

レプリカに同期されたことを確認します。

host:$ docker compose exec db-replica /bin/bash
root@replica:/# mysql -h replica -u root -p -D sample_db

mysql> select * from sample;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | Alice   |
|   2 | Bob1   |
|   3 | Bob2   |
.................
.................
| 100 | Bob99  |
| 101 | Bob100 |
+-----+---------+

レプリカに同期されていることを確認できました。

まとめ

今回は簡単ですが MySQL のレプリケーション構築の流れについて記載しました。
何かの役にたてれば幸いです。

QUARTETCOM TECH BLOG カルテットコミュニケーションズ開発部ブログ

MySQL のレプリケーションを試してみる

はじめに

環境準備

ディレクトリ構成

ソースのリソース

Dockerfile

my.cnf

init.sql

insert.sh

レプリカのリソース

Dockerfile

my.cnf

compose.yaml

レプリケーションを構築

前提

1. ソースのフルバックアップを取得

2. フルバックアップからレプリケーション開始ポジションを取得

3. フルバックアップをレプリカにリストア

4. レプリケーションを設定

5. レプリケーションを開始

6. レプリケーション構築を確認

7. レプリカに同期されることを確認

まとめ

カルテット開発部に興味はありませんか？