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