【MySQL】リードレプリカのDB環境構築体験
はじめに
案件によって様々な環境に触れる機会があるかと思います。
システムを使用するユーザー数やモジュール数、要求される要件によって多種多様な環境が求められており、何もかも同じ条件や環境の案件などほぼ存在しないでしょう。
私が最近経験した案件でMySQLで
- マスター/スレーブ方式
- リードレプリカ構成
を採用し、運用していました。
実際にローカル環境に準備して、色々操作してみて知見を深めてみたので
その内容を記載します。
用語解説
DBに関する用語になります。
マスター/スレーブ方式
その名前の通りで主人と奴隷のような関係からこのような用語になりました。
-
マスター(Master)
基本的にメインで使用されるDB。
常時稼働しており、システムとメインでやりとりを行う。 -
スレーブ(Slave)
マスターの複製(レプリケーション)として使用されるDB。
マスターでの障害発生時や負荷分散を目的としている。
この構成によるメリットとしては
・MySQLの標準機能のため、特別な準備等は必要ない。
・DBサーバーで障害が起こった際、バックアップを行えている。
・非同期で複製を行っているが、ラグはほとんどない。
※注意
「マスター/スレーブ」という用語は世間では差別用語として嫌われ、使わないようにしている企業や国などがあります。
その場合は「メイン/スタンバイ」、「マスター/レプリカ」などと言い換えられていますが内容としては変わりません。
リードレプリカ構成
こちらもその名前の通りで、システムが処理として行う参照(SELECT処理=Read)をスレーブDBでのみ行い、マスターDBの負荷を分散させる構成になります。
一般的にWEBやデスクトップアプリなどやバッチなどのシステムにおいて参照処理は更新処理よりはるかに多いこともあり、参照と更新を分けるだけで負荷を分散しマシンへの負担を軽減させることができます。
また、マスター対スレーブは１：ｎの関係が成り立つのでユーザー数や処理が多い場合はさらにスレーブＤＢを増やして負荷分散を行うことが可能となります。
やってみよう
簡単にですが、自分のローカル上にDBサーバーを２つ構築し実際に「マスター/スレーブ構成」はどのようなものか体験してみます。
- スタート：マスター/スレーブって何か知る
- ゴール：マスター/スレーブ構成をローカルに完成させる
マスター/スレーブ環境を使ったリードレプリカ構成に関しては実際に自分でやってみてください。
前提条件
・Dockerが使用できること(Dockerで環境作ります。)
・MySQLに関して知識があること
環境の準備
Dockerを使用してMySQLのコンテナを２つ作成します。
まずはこのようなディレクトリ構成を用意します。
mysql-docker [D]
├docker-compose.yml [F]
├db1 [D]
│ ├my.cnf [F]
│ └DDL [D]
└db2 [D]
├my.cnf [F]
└DDL [D]
[D]：ディレクトリ
[F]：ファイル
〇docker-compose.yml ↓
version: '3.3'
services:
db1:
container_name: "db1"
restart: always
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: homestead
MYSQL_USER: homestead
MYSQL_PASSWORD: secret
TZ: 'Asia/Tokyo'
ports:
- 33061:3306
command: 'mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci'
volumes:
- db-store1:/var/lib/mysql
- ./db1/my.cnf:/etc/mysql/conf.d/my.cnf
- ./db1/sql:/docker-entrypoint-initdb.d
- ./db1/DDL:/etc/ddl
- ./logs/db1:/var/log/mysql
db2:
container_name: "db2"
restart: always
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: homestead
MYSQL_USER: homestead
MYSQL_PASSWORD: secret
TZ: 'Asia/Tokyo'
ports:
- 33062:3306
command: 'mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci'
volumes:
- db-store2:/var/lib/mysql
- ./db2/my.cnf:/etc/mysql/conf.d/my.cnf
- ./db2/sql:/docker-entrypoint-initdb.d
- ./db2/DDL:/etc/ddl
- ./logs/db2:/var/log/mysql
phpmyadmin1:
image: phpmyadmin/phpmyadmin
environment:
- PMA_ARBITRARY=1
- PMA_HOSTS=mysql
- PMA_USER=homestead
- PMA_PASSWORD=secret
links:
- db1:mysql
ports:
- "8081:80"
phpmyadmin2:
image: phpmyadmin/phpmyadmin
environment:
- PMA_ARBITRARY=1
- PMA_HOSTS=mysql
- PMA_USER=homestead
- PMA_PASSWORD=secret
links:
- db2:mysql
ports:
- "8082:80"
volumes:
db-store1:
db-store2:
※中身確認用にphpadminを２つ用意してます。
必要なければ削除してください。
〇db1/my.cnf ↓
[mysqld]
server-id=101
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log
[client]
default-character-set=utf8mb4
〇db2/my.cnf ↓
[mysqld]
server-id=102
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log
read_only
[client]
default-character-set=utf8mb4
環境の構築
1. 起動します。
docker-compose up -d
2. 起動確認します。
$ docker-compose ps
Name Command State Ports
--------------------------------------------------------------------------------------------------------
db1 docker-entrypoint.sh mysql ... Up 0.0.0.0:33061->3306/tcp, 33060/tcp
db2 docker-entrypoint.sh mysql ... Up 0.0.0.0:33062->3306/tcp, 33060/tcp
mysql-docker_phpmyadmin1_1 /docker-entrypoint.sh apac ... Up 0.0.0.0:8081->80/tcp
mysql-docker_phpmyadmin2_1 /docker-entrypoint.sh apac ... Up 0.0.0.0:8082->80/tcp
3. MySQLへ接続してみます。
DB1へ
docker-compose exec db1 bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
DB2へ
docker-compose exec db2 bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD} ${MYSQL_DATABASE}'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
PHPADMINで
DB1→
http://localhost:8081/
DB2→
http://localhost:8082/
※「db1コンテナ内からdb2内のMySQLへの接続」「db2コンテナ内からdb1内のMySQLへの接続」確認などもしておくとGood
MySQLの設定
1. レプリケーション用のユーザーを作成します。
マスタ側へ接続
docker-compose exec db1 bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD} '
ユーザー作成
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
※本来は'%'はホストを指定する方が良い
2. マスターDBのダンプの取得
docker-compose exec db1 bash -c 'mysqldump -u root -proot --all-databases --flush-logs --single-transaction --master-data > /etc/ddl/master.db'
「mysql-docker\db1\DDL」にmaster.dbが出力されていることを確認する。
3. スレーブDBへダンプのリストア
ダンプファイルをコピー
cp db1/DDL/master.db db2/DDL/
リストアの実行
docker-compose exec db2 bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD} < /etc/ddl/master.db'
※ダンプファイルの１行目に
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
が存在する場合は削除するとダンプファイルをリストアできる
4. スレーブDBの設定
master.dbをエディタで開き、以下の文言で検索する。
「CHANGE MASTER TO」
検索した結果、以下の文言が見つかる
「CHANGE MASTER TO MASTER_LOG_FILE='binlog.XXXXXX', MASTER_LOG_POS=YYY;」
スレーブDBへ接続する
docker-compose exec db2 bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD} '
以下のSQLに当てはめ、実行する
mysql> CHANGE MASTER TO
-> MASTER_HOST='db1',
-> MASTER_PORT=33061,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='binlog.XXXXXX',
-> MASTER_LOG_POS=YYY;
スレーブ(レプリケーション)の開始
mysql> START SLAVE;
スレーブのステータス確認
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: db1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000006
Read_Master_Log_Pos: 1444
Relay_Log_File: 74ee0d826a91-relay-bin.000002
Relay_Log_Pos: 1609
Relay_Master_Log_File: binlog.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1444
Relay_Log_Space: 1825
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 3b7acad3-5bd1-11ec-bfc2-0242ac160002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
エラーが出ていないことを確認できればOK
レプリケーションの確認
マスタDB側へサンプルとして以下のSQLを実行する。
CREATE TABLE `test_table1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`test_text` text COLLATE utf8_unicode_ci NOT NULL,
`test_varchar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`test_int` int(11) NOT NULL,
`created_at` timestamp DEFAULT NULL,
`updated_at` timestamp DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DB1の結果(phpadmin)
DB2の結果(phpadmin)
DB2の方に「test_table1」が作成されていれば完了。
さいごに
普段開発しか行っていない人はあまりDBの構成等は考える機会は少ないかと思われます。
大企業になれば専用のインフラチームが存在し、その部隊が上記のような構成の発案から保守まで請け負うパターンが多いです。
少し前までだとサーバーを用意しなければこういった構成を構築し、個人として試すことはできず技術として自分にインプットできませんでした。
※コストも時間もかかり手を出しにくかった
今現在ではDockerのようなコンテナ技術(簡易的な仮想環境)が発展したことで誰でも簡単に試すことができるようになりました。
もし「Dockerを勉強してなかった」「Dockerを勉強する機会がなかった」などあればこの機会に勉強しても良いかもしれませんね。
Discussion