MySQLレプリケーション(GTID使わない版)
MySQL5.6よりGTID(Global Transaction ID)でのレプリケーションが出来るようになりましたが、基本を押さえたかったのでまずはGTIDを使わないオーソドックスなレプリケーション対応手順をメモしておきます。
前提条件
*vagrantで2台の仮想環境を構築
*仮想環境のIPアドレスは、192.168.33.10(マスター)、192.168.33.11(スレーブ)
*仮想環境のCentOSのバージョンは6.7、MySQLのバージョンは5.7.12
*マスター側にDB「test_db」を作成し、DB内にテーブル「zaiko」を作成
*テーブル「zaiko」のカラム情報、レコードの内容は以下の通り
◆カラム情報
mysql> SHOW COLUMNS FROM zaiko; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | maker | text | YES | | NULL | | | type | text | YES | | NULL | | | serial | varchar(100) | YES | | NULL | | | purpose | varchar(20) | YES | | NULL | | | input_date | date | YES | | NULL | | | output_date | date | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
◆レコードの内容
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 1 | maker1 | type1 | 001 | purpose1 | 2016-05-01 | 2017-05-01 | | 2 | maker2 | type2 | 002 | purpose2 | 2016-05-02 | 2017-05-02 | | 3 | maker3 | type3 | 003 | purpose3 | 2016-05-03 | 2017-05-03 | +----+--------+-------+--------+----------+------------+-------------+ 3 rows in set (0.00 sec)
【マスタ】/etc/my.cnf変更
下記を追加します。
# レプリケーション用設定 server_id=1 log_bin=mysql_bin binlog_format=MIXED max_binlog_size=100M expire_logs_days=30 sync_binlog=1 innodb_support_xa=1 innodb_flush_log_at_trx_commit=1
| 項目 | 説明 |
|---|---|
| server_id | MySQLサーバを識別するためのID |
| log_bin | バイナリ ログ を採取するか否か。 ここにログファイル名を指定することで、有効となる |
| binlog_format | バイナリロギング形式の指定 STATEMENT：実行されたSQLに基づいてロギング ROW：行ベースに基づきロギング MIXED：基本はSTATEMENTだが特定の条件では行ベースでロギング |
| max_binlog_size | バイナリログの最大サイズ |
| expire_logs_days | バイナリログの保管期間 |
| sync_binlog | バイナリログをN回のステートメントまたはまたはトランザクションごとにフラッシュする設定 1が最も安全で、かつ最も遅い設定 |
| innodb_support_xa | XA トランザクションの 2 相コミットで InnoDB のサポートを有効にする |
| innodb_flush_log_at_trx_commit | トランザクションのCOMMIT時に、InnoDBログファイルへデータがディスクへ同期されるかどうかを指定。 指定できる値は0,1,2でデフォルトは1。 1の場合COMMITごとにログファイルへの書き込みとディスクへの同期が行われる。 0の場合はCOMMIT時には何も行われず1秒ごとにログファイルへの書き込みとディスクへの同期が行われる。 2の場合COMMITと同時にログファイルへの書き込みが行われるがディスクとの同期は1秒ごと。 |
max_binlog_sizeとexpire_logs_daysはデフォルト値でも問題ないかと思います。
sync_binlog=1,innodb_support_xa=1,innodb_flush_log_at_trx_commit=1のとき、バイナリログとInnoDBログファイルは完全に同期されます。
【マスタ】文法チェック(念のため)
# mysqld --verbose --help > /dev/null
【マスタ】mysql再起動
# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
【マスタ】レプリケーション用のユーザ作成
mysql> GRANT REPLICATION SLAVE ON *.* -> TO 'repluser'@'192.168.33.11' IDENTIFIED BY 'Repl-Pass0'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> SELECT user, host FROM mysql.user; +-----------+---------------+ | user | host | +-----------+---------------+ | repluser | 192.168.33.11 | | mysql.sys | localhost | | root | localhost | | testuser | localhost | +-----------+---------------+ 4 rows in set (0.00 sec) mysql>
ちなみに、GRANTを利用してユーザを作ることは近々出来なくなるらしいです。。。。
mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1 row in set (0.00 sec)
【マスタ】データベースのダンプ
# mysqldump -uroot -p --all-databases --master-data=2 --single-transaction --flush-logs > /tmp/dumpfile.sql Enter password:(パスワードを入力) # ls -l /tmp/dumpfile.sql -rw-r--r-- 1 root root 766735 May 24 02:23 /tmp/dumpfile.sql
| 項目 | 説明 |
|---|---|
| --all-databases | すべてのデータベースをバックアップ |
| --master-data=2 | CHANGE MASTER TOコマンドをダンプの先頭に付加。 --master-data=2：CHANGE MASTER TOがコメントアウト --maser-data=1：CHANGE MASTER TOが コメントアウトされずにダンプ |
| --single-transaction | ダンプする際に先頭にBEGINをつける (ダンプ時のデータのトランザクションの一貫性を保つことができる)。 |
| --flush-logs | ダンプを開始する前に、MySQL サーバ内のログファイルをフラッシュ |
【スレーブ】my.cnf変更
下記を追加します。
server_id=1000 report_host=slave1000 sync_binlog=0 innodb_flush_log_at_trx_commit=0
| 項目 | 説明 |
|---|---|
| report_host | レプリケーションの状態をマスターサーバなどでも確認しやすくなる |
【スレーブ】文法チェック(念のため)
# mysqld --verbose --help > /dev/null
【スレーブ】mysql再起動
# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
【スレーブ】ダンプファイルをマスターからコピー
SCPかなんかで。。。
【スレーブ】ダンプファイルをリストア
# mysql -u root -p < /tmp/dumpfile.sql Enter password:(パスワードを入力)
【スレーブ】バイナリログファイル名と開始位置を確認
# head -100 /tmp/dumpfile.sql | grep CHANGE -- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000008', MASTER_LOG_POS=154;
MASTER_LOG_FILEがバイナリログファイル名、MASTER_LOG_POGがログのポジションとなります。
【スレーブ】レプリケーションの設定
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.33.10', -> MASTER_USER='repluser', -> MASTER_PASSWORD='Repl-Pass0', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql_bin.000002', -> MASTER_LOG_POS=154, -> MASTER_CONNECT_RETRY=10;
| 項目 | 説明 |
|---|---|
| MASTER_HOST | マスタのホスト名、もしくはIPアドレス |
| MASTER_USER | マスタで作成したレプリケーション用ユーザ |
| MASTER_PASSWORD | マスタで作成したレプリケーション用ユーザのパスワード |
| MASTER_PORT | マスタのポート番号 (デフォルト値を設定しているだけなので特に設定しなくてもいいかも？) |
| MASTER_LOG_FILE | バイナリログファイル名(*) |
| MASTER_LOG_POS | バイナリログのポジション(*) |
| MASTER_CONNECT_RETRY | マスタへの再接続を行う間隔(秒) |
*先ほど確認したバイナリログファイル名とポジションを指定します。
【スレーブ】mysqld再起動
# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
【スレーブ】レプリケーションの開始
mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
【スレーブ】レプリケーションの状態確認
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.33.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql_bin.000008 Read_Master_Log_Pos: 154 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql_bin.000008 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: 154 Relay_Log_Space: 531 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: 901b3801-207a-11e6-a220-0800272020f4 Master_Info_File: /var/lib/mysql/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: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
「Slave_IO_Running」と「Slave_SQL_Running」がYesになっていれば正常です。
ちなみにエラーが起きている場合は以下のような状態になります。
*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.33.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql_bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql_bin.000002 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: 154 Relay_Log_Space: 154 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: 1045 Last_IO_Error: error connecting to master 'repluser@192.168.33.10:3306' - retry-time: 10 retries: 54 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/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: 160525 01:57:50 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: 1 row in set (0.00 sec)
上記はマスタ側のアカウント設定を誤ってマスタに接続できない状態なのですが、「Slave_IO_Running」が 「Connecting」となっており、「Last_IO_Error」にエラーメッセージが出力されています。
動作確認
実際にレプリケーションが動いているかどうかをzaikoテーブルにレコードを追加して確認してみます。
(1)レコード追加前の状態
【マスタ】
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 1 | maker1 | type1 | 001 | purpose1 | 2016-05-01 | 2017-05-01 | | 2 | maker2 | type2 | 002 | purpose2 | 2016-05-02 | 2017-05-02 | | 3 | maker3 | type3 | 003 | purpose3 | 2016-05-03 | 2017-05-03 | +----+--------+-------+--------+----------+------------+-------------+ 3 rows in set (0.00 sec)
【スレーブ】
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 1 | maker1 | type1 | 001 | purpose1 | 2016-05-01 | 2017-05-01 | | 2 | maker2 | type2 | 002 | purpose2 | 2016-05-02 | 2017-05-02 | | 3 | maker3 | type3 | 003 | purpose3 | 2016-05-03 | 2017-05-03 | +----+--------+-------+--------+----------+------------+-------------+ 3 rows in set (0.00 sec)
(2)マスタ側にレコード追加
mysql> insert into zaiko(maker,type,serial,purpose,input_date,output_date) values('maker4','type4','004','purpose4','2016-05-04','2017-05-04'); Query OK, 1 row affected (0.00 sec) mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 1 | maker1 | type1 | 001 | purpose1 | 2016-05-01 | 2017-05-01 | | 2 | maker2 | type2 | 002 | purpose2 | 2016-05-02 | 2017-05-02 | | 3 | maker3 | type3 | 003 | purpose3 | 2016-05-03 | 2017-05-03 | | 4 | maker4 | type4 | 004 | purpose4 | 2016-05-04 | 2017-05-04 | +----+--------+-------+--------+----------+------------+-------------+ 4 rows in set (0.00 sec)
(3)スレーブを確認
mysql> select * from zaiko; +----+--------+-------+--------+----------+------------+-------------+ | id | maker | type | serial | purpose | input_date | output_date | +----+--------+-------+--------+----------+------------+-------------+ | 1 | maker1 | type1 | 001 | purpose1 | 2016-05-01 | 2017-05-01 | | 2 | maker2 | type2 | 002 | purpose2 | 2016-05-02 | 2017-05-02 | | 3 | maker3 | type3 | 003 | purpose3 | 2016-05-03 | 2017-05-03 | | 4 | maker4 | type4 | 004 | purpose4 | 2016-05-04 | 2017-05-04 | +----+--------+-------+--------+----------+------------+-------------+ 4 rows in set (0.00 sec)
データの同期が取れているので、正常に動作しているようです。
