レプリケーションのゆるーいお話 その3
あけましておめでとうございます。
今回は前回できなかったレプリケーションを実際に構築するところまで行ってみます。
なお今回はすでに稼働しているサービスからデータを取得しSlaveを増やす、いわゆるスケールアウト的なものは紹介せずMaster,Slaveどちらも0の状態からレプリケーション構築をすること考えます。
では早速構築していきましょう。
OS : Ubuntu 18.04.3 LTS MySQL : 8.0.18
なお今回はmysql-shellのsandobox機能を使ってローカルホストに複数のインスタンスを用意しています。 これは非常に便利でポートを指定するだけでMySQLインスタンスをローカル環境に短時間で構築してくれるのでちょっとした機能を試したいなというときはおすすめです。
instance1 localhsot:3300 instance2 localhsot:3301 instance3 localhsot:3302
共通事項
レプリケーション用ユーザの作成
レプリケーションユーザ作成はどのようなトポロジーでも必要になります。
CREATE USER repluser IDENTIFIED WITH mysql_native_password BY 'repl'; GRANT REPLICATION SLAVE ON *.* TO repluser;
MySQL8.0からデフォルトの認証形式がmysql_native_passwordからcaching_sha2_passwordに変更されています。 セキュリティを考えるとcaching_sha2_passwordのほうが望ましいのですが、別途設定しないといけない部分が増えてしまいますので今回はmysql_native_passwordでユーザを作成します。
instance1>CREATE USER repluser IDENTIFIED WITH mysql_native_password BY 'repl'; Query OK, 0 rows affected (0.01 sec) instance1>GRANT REPLICATION SLAVE ON *.* TO repluser; Query OK, 0 rows affected (0.00 sec)
MasterとSlave間でデータの不整合が起きている状態が嫌なのでSlaveにも同様にユーザを作成しておきます。
Masterの更新進捗具合の確認
Master側で下記コマンドを実行しますることでMasterがどの程度binlogを適用したかが確認できます。
SHOW MASTER STATUS;
CHANGE MASTERおよびSTART/STOP SLAVEコマンド
レプリケーションを準備するためにはSlaveで下記のようなCHANGE MASTERコマンドを実行します。
CHANGE MASTER TO MASTER_HOST = '', MASTER_PORT = , MASTER_USER = '', MASTER_PASSWORD = '', MASTER_LOG_FILE = '', MASTER_LOG_POS = ;
※構成するレプリケーションによってはパラメータが変わります。
CHANGE MASTERだけではまだレプリケーションは開始されません。
START SLAVEコマンドを実行することでレプリケーションを開始できます。
START SLAVE;
レプリケーションを止めたい場合は下記のコマンドを実行します。
STOP SLAVE;
レプリケーションの状態を確認する
下記コマンドでレプリケーションの状態を確認できます。
SHOW SLAVE STATUS\G
レプリケーションの構築
まずはinstance1(Master) --> instance2(Slave)という構成を作ります。
最初にMasterでSHOW MASTER STATUSを実行してみます。
実行結果は次のようになりました。
instance1>SHOW MASTER STATUS; +----------------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+------------------------------------------+ | LAPTOP-CONIPJL6-bin.000003 | 1527 | | | b149d06e-2e03-11ea-a964-94b86d82d803:1-6 | +----------------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
Fileはbinary log(binlog)のファイル名、Positionはそのbinlog内でのポジション(オフセットのようなものでどこまで更新が適用されるかを表す)、Executed_Gtid_SetはGTID(Global Transaction ID)と呼ばれるサーバごとで重複しない文字列とそのサーバがどこまで更新を適用させたかを示したものを表しています。
今回の場合GTIDがb149d06e-2e03-11ea-a964-94b86d82d803で残りの1-6の部分がどこまで更新を適用させたかを示しています。
実はレプリケーションにはbinlogポジションを明示する従来のレプリケーションと、GTIDを使うGTIDレプリケーションと呼ばれるものがあります。 最近のMySQLでは後者が標準になっています。
はたしてGTIDレプリケーションが標準になりつつある今、binlogポジションを明示するレプリケーションを従来と呼ぶのはいささか問題がありそうですが便宜上そう呼ぶようにします。
従来の(binlogポジションを明示する)レプリケーションを構成する
まずは従来のレプリケーションを構築してみます。
SlaveでCHANGE MASTERを実行してみます。
instance2>CHANGE MASTER TO -> MASTER_HOST = 'localhost', -> MASTER_PORT = 3300, -> MASTER_USER = 'repluser', -> MASTER_PASSWORD = 'repl', -> MASTER_LOG_FILE = 'LAPTOP-CONIPJL6-bin.000003', -> MASTER_LOG_POS = 1527 -> ; Query OK, 0 rows affected, 1 warning (0.08 sec)
（ここのWarningはパスワードを平文で送信してしまっていることによるものです）
各パラメータの意味は下記のとおりです。
MASTER_HOST : Masterのホスト名
MASTER_PORT : Masterのポート番号
MASTER_USER : レプリケーション用ユーザ名
MASTER_PASSWORD : レプリケーション用ユーザのパスワード
MASTER_LOG_FILE : Master側のbinary log名(SHOW MASTER STATUSのFile列)
MASTER_LOG_POS : Master側のbinary logポジション(SHOW MASTER STATUSのPosition列)
これだけではまだレプリケーションは開始されていません。
Slave側で実行します。
instance2>START SLAVE; Query OK, 0 rows affected (0.01 sec)
レプリケーションの状態を確認する
Slaveの状況を確認してみます。
instance2>SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repluser Master_Port: 3300 Connect_Retry: 60 Master_Log_File: LAPTOP-CONIPJL6-bin.000003 Read_Master_Log_Pos: 1527 Relay_Log_File: LAPTOP-CONIPJL6-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: LAPTOP-CONIPJL6-bin.000003 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: 1527 Relay_Log_Space: 534 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: 1 Master_UUID: b149d06e-2e03-11ea-a964-94b86d82d803 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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: c395fa40-2e03-11ea-87b6-94b86d82d803:1-2 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 (0.00 sec)
主なパラメータについて確認してみましょう。
Master_Host : Masterのホスト名
Master_User : レプリケーションしているユーザ名
Master_Port : Masterのポート番号
Connect_Retry : Masterに接続できない場合Slaveはこの値(単位は秒)だけSleepして再接続を試みる
Master_Log_File : IOスレッドが現在読み込んだbinary logファイル名
Read_Master_Log_Pos : IOスレッドが現在読み込んだbinary logのポジション
Relay_Log_File : SQLスレッドが現在実行しているリレーログ名
Relay_Log_Pos : SQLスレッドが現在実行しているリレーログのポジション
Relay_Master_Log_File : SQLスレッドが現在実行しているbinary logファイル名
Slave_IO_Running : IOスレッドのステータス、Yesであれば正常に動いている
Slave_SQL_Running : SQLスレッドのステータス、Yesであれば正常に動いている
Last_Errno : エラーが起きている場合そのエラー番号
Last_Error : エラーが起きている場合そのエラー内容
Exec_Master_Log_Pos : SQLスレッドがどこまでMaster側の内容を読み込んだかそのポジション
Relay_Log_Space : リレーログが消費しているスペース、SQLスレッドがIOスレッド比べて極端に遅れている場合この値が大きくなりがち
Seconds_Behind_Master : Slaveがどの程度Masterから遅れているかを示す、あくまでも目安なので600と表示されていても遅延解消に10分かかるわけではない(正常値は0)
Last_IO_Errno : IOスレッドがエラーとなっている場合そのエラー番号
Last_IO_Error : IOスレッドがエラーとなっている場合そのエラー内容
Last_SQL_Errno : SQLスレッドがエラーとなっている場合そのエラー番号
Last_SQL_Error : SQLスレッドがエラーとなっている場合そのエラー内容
Last_IO_Error_Timestamp : IOスレッドがエラーとなった時刻
Last_SQL_Error_Timestamp : SQLスレッドがエラーとなった時刻
スクリプトなどでSlaveが正常に動いているのかチェックしたい場合は各スレッドのRunning列、Seconds_Behind_Masterあたりを見ておいたほうがよさそうです。
GTIDレプリケーションを構成する
次にGTIDレプリケーションを構成してみます。
一旦先ほど構成したレプリケーションはRESET SLAVE ALLできれいさっぱり消しておきます。
もう1回Master側でSHOW MASTER STATUSを実行します。
instance1>SHOW MASTER STATUS; +----------------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+------------------------------------------+ | LAPTOP-CONIPJL6-bin.000003 | 1527 | | | b149d06e-2e03-11ea-a964-94b86d82d803:1-6 | +----------------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
今回使う値はExecuted_Gtid_Setです。
この値からinstance1のサーバがどこまでトランザクションを実行したのかがわかります。
次にSlave側でMaster側で取得したExecuted_Gtid_Setを自身のgtid_purgedにセットします。
この値はbinlogからパージされたすべてのトランザクションの集合を表します。
instance2>SHOW VARIABLES LIKE 'gtid_purged'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_purged | | +---------------+-------+ 1 row in set (0.00 sec) instance2> instance2>SET GLOBAL gtid_purged = 'b149d06e-2e03-11ea-a964-94b86d82d803:1-6'; Query OK, 0 rows affected (0.01 sec) instance2> instance2>SHOW VARIABLES LIKE 'gtid_purged'; +---------------+------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------+ | gtid_purged | b149d06e-2e03-11ea-a964-94b86d82d803:1-6 | +---------------+------------------------------------------+ 1 row in set (0.00 sec)
この操作はSlave側にどこからレプリケーションを開始させればいいのか教える操作なのです。
なおgtid_purgedをセットするときに次のようなエラーが出る場合はSlave側でRESET MASTERをしてから実行します。
ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the new value must be a superset of the old value
gtid_purgedをセットしたのでレプリケーションを構築できます。
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_PORT = 3300, MASTER_USER = 'repluser', MASTER_PASSWORD = 'repl', MASTER_AUTO_POSITION = 1 ;
先ほどと違い、MASTER_LOG_FILE,MASTER_LOG_POSが無くMASTER_AUTO_POSITIONが代わりに出てきました。
MASTER_AUTO_POSITIONを使うとbinlogポジションを自動で識別しレプリケーションを開始することができます。
とその前にMasterにだけ適当に更新クエリを実行してみましょう。
instance1>CREATE DATABASE repl_test; Query OK, 1 row affected (0.03 sec) instance1>USE repl_test; Database changed instance1> instance1>CREATE TABLE t1(a INT PRIMARY KEY); Query OK, 0 rows affected (0.05 sec) instance1>SHOW MASTER STATUS; +----------------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+------------------------------------------+ | LAPTOP-CONIPJL6-bin.000003 | 1920 | | | b149d06e-2e03-11ea-a964-94b86d82d803:1-8 | +----------------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
2つのトランザクション(DDL)を実行してExecuted_Gtid_Setが1-6から1-8に増えていることがわかります。
改めてSlave側でレプリケーションを構築し起動させてみます。
instance2>CHANGE MASTER TO -> MASTER_HOST = 'localhost', -> MASTER_PORT = 3300, -> MASTER_USER = 'repluser', -> MASTER_PASSWORD = 'repl', -> MASTER_AUTO_POSITION = 1 -> ; Query OK, 0 rows affected, 1 warning (0.09 sec) instance2>START SLAVE; Query OK, 0 rows affected (0.01 sec) instance2>SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repluser Master_Port: 3300 Connect_Retry: 60 Master_Log_File: LAPTOP-CONIPJL6-bin.000003 Read_Master_Log_Pos: 1920 Relay_Log_File: LAPTOP-CONIPJL6-relay-bin.000002 Relay_Log_Pos: 823 Relay_Master_Log_File: LAPTOP-CONIPJL6-bin.000003 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: 1920 Relay_Log_Space: 1033 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: 1 Master_UUID: b149d06e-2e03-11ea-a964-94b86d82d803 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave 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: b149d06e-2e03-11ea-a964-94b86d82d803:7-8 Executed_Gtid_Set: b149d06e-2e03-11ea-a964-94b86d82d803:1-8 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) instance2>SHOW TABLES FROM repl_test; +---------------------+ | Tables_in_repl_test | +---------------------+ | t1 | +---------------------+ 1 row in set (0.02 sec)
しっかりレプリケーションできているようです。
Slaveを増やす場合
Slaveを増やしたい場合は同じ要領でレプリケーションを構築、開始させていきます。
instance3>SET GLOBAL gtid_purged = 'b149d06e-2e03-11ea-a964-94b86d82d803:1-6'; Query OK, 0 rows affected (0.05 sec) instance3>CHANGE MASTER TO -> MASTER_HOST = 'localhost', -> MASTER_PORT = 3300, -> MASTER_USER = 'repluser', -> MASTER_PASSWORD = 'repl', -> MASTER_AUTO_POSITION = 1 -> ; Query OK, 0 rows affected, 1 warning (0.09 sec) instance3> instance3>START SLAVE; Query OK, 0 rows affected (0.01 sec) instance3>SHOW TABLES FROM repl_test; +---------------------+ | Tables_in_repl_test | +---------------------+ | t1 | +---------------------+ 1 row in set (0.04 sec)
なおそのMasterにSlaveが接続しているかは次のコマンドで確認できます。
instance1>SHOW SLAVE HOSTS; +-----------+-----------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+-----------+------+-----------+--------------------------------------+ | 3 | 127.0.0.1 | 3302 | 1 | cca43008-2e03-11ea-ab2a-94b86d82d803 | | 2 | 127.0.0.1 | 3301 | 1 | c395fa40-2e03-11ea-87b6-94b86d82d803 | +-----------+-----------+------+-----------+--------------------------------------+ 2 rows in set (0.00 sec)
今回はこの程度にしておきます・・・
