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

README.md

DockerでMySQLのレプリケーションを設定してみる

$ docker-compose build
...
$ docker-compose up -d
...
$ docker-compose ps
           Name                        Command             State    Ports  
---------------------------------------------------------------------------
mysql57sandbox_db-master_1   docker-entrypoint.sh mysqld   Up      3306/tcp
mysql57sandbox_db-slave_1    docker-entrypoint.sh mysqld   Up      3306/tcp

master側の設定

レプリケーション用ユーザの作成

mysql> CREATE USER 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)                                                                                                                                                                                                          

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

masterの状態を確認

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      548 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave側の設定

masterの情報の設定

mysql> CHANGE MASTER TO MASTER_HOST='db-master', MASTER_USER='repl', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=548; 

レプリケーションの開始

mysql> START SLAVE;                                                                                                                                                                                                                           
Query OK, 0 rows affected (0.01 sec)                                                                                                                                                                                                          

確認

実際にDB作ってみるなどして、masterで行ったSQLがslaveに反映されてるか確認

docker-compose.yml

version: '3'
services:
  db-master:
    build:
      context: .
      args:
        custom_cnf: master.cnf
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
  db-slave:
    build:
      context: .
      args:
        custom_cnf: slave.cnf
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes

Dockerfile

FROM mysql:5.7

ARG custom_cnf

COPY $custom_cnf /etc/mysql/conf.d/custom.cnf

master.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

slave.cnf

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log