MySQL/MariaDBレプリケーション

トップ > MySQL/MariaDB > MySQL/MariaDBレプリケーション

レプリケーションとは

MySQL/MariaDB マスタサーバの DB を 2台目, 3台目のスレーブに配布する仕組みです。マスタは読み書きが可能。スレーブは読み込みのみ可能です。読み込みに利用するサーバ台数を増やして負荷分散したり、マスタに障害が発生した際のリカバリサーバとして利用することができます。おおよそ、下記の流れで動作します。

  1. マスタはマスタのバイナリログに処理の内容を記録する。
  2. スレーブ側のI/Oスレッドが、マスタからバイナリログを取得する。
  3. スレーブ側のSQLスレッドが、取得したバイナリログをスレーブ側DBに反映する。

前提

下記の前提で手順を説明します。

マスタ側の設定

my.cnf に下記の設定をしておきます。

[mysqld]
server-id = 1
log-bin

レプリケーションが使用するユーザを作成します。

mysql> CREATE USER 'repl'@'192.168.1.3' IDENTIFIED BY 'repl9999';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.3';

データベースをバックアップします。対象とるデータベースのエンジンは InnoDB で、ダンプ中に CREATE TABLE, ALTER TABLE, DROP TABLE などの操作は行われないようにしてください。

# mysqldump -u root -proot9999 --all-databases \
  --flush-logs --single-transaction --master-data=2 > master.db

スレーブ側の設定

my.cnf に下記の設定をしておきます。server-id では、関係するサーバ群で一意となる数値を指定してください。スレーブ動作中にスレーブDBへの書き込みが発生してしまうとレプリケーションがエラー停止してしまうので、read_only により誤更新を防ぎます。

[mysqld]
server-id=2
log-bin
read_only

バックアップをスレーブにリストアします。

# mysql -u root -proot9999 < master.db

マスタからバックアップファイルを取得し、バイナリログ名とポジション番号を調べます。

# grep "CHANGE MASTER TO" master.db
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000005', MASTER_LOG_POS=1277;

スレーブの設定を行います。

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.2',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl9999',
-> MASTER_LOG_FILE='mariadb-bin.000005',
-> MASTER_LOG_POS=1277;

スレーブを開始します。

mysql> START SLAVE;

マスタからスレーブにフェイルオーバーする

◆ マスタ側の作業

全テーブルをREAD LOCKし、書き込みプロセスが残っていないことを確認します。system userのプロセスは残っていて構いません。また、マスタ側のバイナリファイル名とポジション番号を確認します。

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW PROCESSLIST;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |     1583 |              |                  |
+-------------------+----------+--------------+------------------+

DBをロックしたまま、別の端末からマスタを停止します。

# systemctl stop mariadb
◆ スレーブ側の作業

スレーブ側の状況を確認します。

mysql> SHOW SLAVE STATUS\G
 Slave_IO_State: Reconnecting ...      再接続しようとしている
 Slave_IO_Running: Connecting ...      I/Oスレッドは接続しようとしている
 Slave_SQL_Running: Yes                SQLスレッドは正常動作している
 Exec_Master_Log_Pos: 1583 ...         最後に書き込んだポジション番号
 Last_IO_Errno: 2003                   最後に発生したI/Oエラーの番号
 Last_IO_Error: error reconnecting ... 最後に発生したI/Oエラーの内容

スレーブをI/Oスレッド、SQLスレッドの順に停止し、マスタ情報をリセットします。

mysql> STOP SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;
mysql> RESET MASTER;

my.cnf の read_only をコメントアウトします。

[mysqld]
server-id = 2
log-bin
# read_only

MariaDBをマスタとして再起動します。

# systemctl restart mariadb

Dockerを用いた検証手順

Dockerを用いて検証した手順を下記に示します。

◆Dockerネットワークを作成する
# docker network create --subnet 192.168.1.0/24 local_net
◆マスタ側を設定する
# mkdir ~/maria1
# docker volume create maria1
# docker run -d --name maria1 -h maria1 --net local_net -e MYSQL_ROOT_PASSWORD=root9999 -v maria1:/var/lib/mysql mariadb
# docker exec -it maria1 mysql -u root -proot9999
 ※ コンテナ起動後、ログインできるようになるまで、下記エラーでしばらく時間がかかることがある
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
mysql> CREATE USER 'repl'@'192.168.1.0/255.255.255.0'  IDENTIFIED BY 'repl9999';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.0/255.255.255.0';
mysql> QUIT
# docker cp maria1:/etc/mysql ~/maria1
# sed -i 's/\[mysqld\]/\[mysqld\]\nserver-id = 1\nlog-bin/' ~/maria1/mysql/my.cnf
# docker rm -fv maria1
# docker run -d --name maria1 -h maria1 --net local_net -v maria1:/var/lib/mysql -v ~/maria1/mysql:/etc/mysql mariadb
# docker exec -it maria1 mysql -u root -proot9999
mysql> create database sample;
mysql> use sample;
mysql> create table users ( id int, name varchar(30) );
mysql> insert into users ( id, name ) values ( 1, 'Yamada' );
mysql> insert into users ( id, name ) values ( 2, 'Suzuki' );
mysql> QUIT
# docker exec -it maria1 mysqldump -u root -proot9999 --all-databases --single-transaction --master-data=2 > ~/maria1/maria1.db
◆スレーブ側を設定する
# mkdir ~/maria2
# docker volume create maria2
# docker run -d --name maria2 -h maria2 --net local_net -e MYSQL_ROOT_PASSWORD=root9999 -v maria2:/var/lib/mysql mariadb
# docker exec -it maria2 mysql -u root -proot9999
  ※エラーとなる場合はしばらく待つ
mysql> QUIT
# docker cp ~/maria1/maria1.db maria2:/tmp
# docker exec -it maria2 /bin/bash
(コンテナの中で)
# mysql -u root -proot9999 < /tmp/maria1.db
# exit
# docker cp maria2:/etc/mysql ~/maria2
# sed -i 's/\[mysqld\]/\[mysqld\]\nserver-id = 2\nlog-bin\nread_only/' ~/maria2/mysql/my.cnf
# docker rm -fv maria2
# docker run -d --name maria2 -h maria2 --net local_net -v maria2:/var/lib/mysql -v ~/maria2/mysql:/etc/mysql mariadb
# docker inspect maria1 | grep IPAddress
  "IPAddress": "192.168.1.2",
# grep 'CHANGE MASTER TO MASTER_LOG_FILE' ~/maria1/maria1.db
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000001', MASTER_LOG_POS=1010;
# docker exec -it maria2 mysql -u root -proot9999
mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.1.2',
 -> MASTER_PORT=3306,
 -> MASTER_USER='repl',
 -> MASTER_PASSWORD='repl9999',
 -> MASTER_LOG_FILE='mysqld-bin.000001',
 -> MASTER_LOG_POS=1010;
mysql> START SLAVE;
mysql> QUIT
◆マスタでデータを更新する
# docker exec -it maria1 mysql -u root -proot9999 sample
mysql> insert into users ( id, name ) values ( 3, 'Takeda' );
mysql> QUIT
◆スレーブに反映されているか確認する
# docker exec -it maria2 mysql -u root -proot9999 sample
mysql> select * from sample.users;
mysql> QUIT
◆フェイルオーバーする(マスタ側)
# docker exec -it maria1 mysql -u root -proot9999
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW PROCESSLIST;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |     1583 |              |                  |
+-------------------+----------+--------------+------------------+
(DBをロックしたまま、別の端末から)
# docker stop maria1
◆フェイルオーバーする(スレーブ側)
# docker exec -it maria2 mysql -u root -proot9999
mysql> SHOW SLAVE STATUS\G
  Slave_IO_State: Reconnecting ...      再接続しようとしている
  Slave_IO_Running: Connecting ... 接続しようとしている
  Slave_SQL_Running: Yes
  Exec_Master_Log_Pos: 1583 ... 最後に書き込んだポジション番号(マスタと合致していればよい)
  Last_IO_Errno: 2003 ... 最後に発生したI/Oエラーの番号
  Last_IO_Error: error reconnecting ... 最後に発生したI/Oエラーの内容
mysql> STOP SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;
mysql> RESET MASTER;
mysql> QUIT
# sed -i 's/#*read_only/#read_only/' ~/maria2/mysql/my.cnf
# docker restart maria2

Copyright (C) 2019-2011 杜甫々
初版:2019年12月8日 最終更新:2021年5月7日
http://www.tohoho-web.com/ex/mysql-replication.html