MySQLでレプリケーションの設定をする

はじめに

昔、MySQLのレプリケーションをするシステムがあったのだが、その頃は知識も浅く、手順通りにやっていたので一から実践できるように試してみる。

環境

Windows 10 Pro (操作PC)
Docker Desktop 4.21.1 (114176)
MySQL 8.1.0

準備

今回、検証するにあたって、Docker を使用し、実際にレプリケーションできるかを試す。
下記のリポジトリに、データベースの勉強用リポジトリとして色々入れているので一緒に試せるようにした。
https://github.com/katsuobushiFPGA/db-study

レプリケーションの設定

構成

rep_primary - プライマリー
rep_secondary - セカンダリー

とする。

プライマリの設定

rep_primary の extra.cnf に下記を設定した。

[mysqld]
log-bin = mysql-bin
server-id = 1

これで、バイナリログが有効になり、 server_id を 1に設定する。

セカンダリの設定

[mysqld]
character-set-server=utf8mb4
log-bin = mysql-bin
server-id = 2

同じく、バイナリログを有効にし、 server_id を 2に設定する。

コンテナの起動

docker compose up -d --build

でビルド+コンテナ起動をする。

NAME                          IMAGE                       COMMAND                  SERVICE             CREATED             STATUS              PORTS
database-specialist-db-1      database-specialist-db      "docker-entrypoint.s…"   db                  4 seconds ago       Up 3 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp
database-specialist-rep_p-1   database-specialist-rep_p   "docker-entrypoint.s…"   rep_p               4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:13306->3306/tcp
database-specialist-rep_s-1   database-specialist-rep_s   "docker-entrypoint.s…"   rep_s               4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:23306->3306/tcp

プライマリーは 13306, セカンダリーは23306でアクセスができる。

プライマリーでダンプを取得する

docker compose exec rep_p bash

MySQLにrootでログインをする。

mysql -u root -proot

レプリケーション用のユーザを作成する。

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; 
 ON *.* TO 'repl'@'%';Query OK, 0 rows affected (0.03 sec)

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

テーブルロックをする。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

プライマリの状態を確認する。

show master status;

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

※ File と Position は控えておく。

mysql のコンソールから抜け、ダンプを取得する。

mysqldump -u root -p --all-databases --lock-all-tables > /root/primary_alldatabases.dump

テーブルのロックは解除する。

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

そうしたら Docker のコンテナからも抜ける。

docker cp でコピーをするので、 docker ps でコンテナIDを確認する。

docker ps

CONTAINER ID   IMAGE                             COMMAND                  CREATED         STATUS                    PORTS                                                          NAMES
8a31c3dce4e0   database-specialist-rep_p         "docker-entrypoint.s…"   6 minutes ago   Up 6 minutes              33060/tcp, 0.0.0.0:13306->3306/tcp                             database-specialist-rep_p-1
09c947c71829   database-specialist-db            "docker-entrypoint.s…"   6 minutes ago   Up 6 minutes              0.0.0.0:3306->3306/tcp, 33060/tcp                              database-specialist-db-1
b1c624d6d0c4   database-specialist-rep_s         "docker-entrypoint.s…"   6 minutes ago   Up 6 minutes              33060/tcp, 0.0.0.0:23306->3306/tcp                             database-specialist-rep_s-1

8a31c3dce4e0 なので、 docker cp でホストにダンプをコピーする。

docker cp 8a31c3dce4e0:/root/primary_alldatabases.dump .

これでコピーができた。

セカンダリにダンプを取り込む

ダンプデータを取り込むため、あらかじめコンテナ内にダンプを入れておく。

docker cp primary_alldatabases.dump  b1c624d6d0c4:/root

セカンダリのコンテナに入る。

docker compose exec rep_s bash

ダンプデータを取り込む。

mysql -u root -proot < /root/primary_alldatabases.dump

mysql コンソールに入る。

mysql -u root -proot

プライマリの情報を登録する。

mysql> change master to
    -> master_host = 'rep_p',
    -> master_user = 'repl',
    -> master_password = 'password',
    -> master_log_file = 'mysql-bin.000003',
    -> master_log_pos = 660;

Query OK, 0 rows affected, 8 warnings (0.05 sec)

項目	設定値	備考
master_host	rep_p	プライマリのホスト
master_user	repl	レプリケーションユーザ名
master_password	password	レプリケーションユーザのパスワード
master_log_file	mysql-bin.000003	show master statusでの確認したバイナリログ
master_log_pos	660	show master statusでの確認したバイナリログの位置

レプリケーションを開始する

セカンダリのコンテナに入り、mysqlコンソールで下記を実行する。

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

ステータスの確認

show slave status \G;

できていそうか。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: rep_p
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 844
               Relay_Log_File: b1c624d6d0c4-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Connecting
            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: 844
              Relay_Log_Space: 157
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2061
                Last_IO_Error: Error connecting to source 'repl@rep_p:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             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: 10
                  Master_Bind: 
      Last_IO_Error_Timestamp: 230826 05:03:47
     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.00 sec)

ERROR: 
No query specified

動作検証

トラブルシューティング

レプリケーション後に下記のようなエラーが出る。

mysql> show slave status \G;

Last_IO_Errno: 2061
Last_IO_Error: Error connecting to source 'repl@rep_p:3306'. This was attempt 8/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

これは、エラー内容の通り、caching_sha2_password でエラーが起きているみたい。
mysql_native_password でレプリケーション用のユーザを作成することでエラーは解決した。

参考

第 17 章レプリケーション | MySQL 8.0 リファレンスマニュアル https://dev.mysql.com/doc/refman/8.0/ja/replication.html
MySQLのレプリケーション設定
https://bellsoft.jp/blog/system/detail_655
MySQLでレプリケーションを組んでみる
https://qiita.com/shotaTsuge/items/87e872e6522e6cd8cea9
認証プラグインを指定してユーザーを作成する
https://www.javadrive.jp/mysql/user/index9.html

おわりに

実際にレプリケーションの作業を実施してみた。
意外と難しくなく、簡単にレプリケーションの設定ができたと思う。
今度は、障害時の復旧手順などもやってみたい・・・。