Aurora MySQL互換で binlog を有効化して、マスターからは binlog にアクセスできるが、リードレプリカではアクセスできないことを確認した。
binlog を有効化する
マスターの binlog を確認する
mysql -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p mysql> SHOW VARIABLES LIKE 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.04 sec) mysql> show binary logs; +----------------------------+-----------+ | Log_name | File_size | +----------------------------+-----------+ | mysql-bin-changelog.000002 | 120 | +----------------------------+-----------+ 1 row in set (0.05 sec) mysql> show master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-changelog.000002 | 120 | | | | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.06 sec)
- binlog をコピーする
% mysqlbinlog --read-from-remote-server -h aurora01.cluster-******.ap-northeast-1.rds.amazonaws.com -u awsuser -p mysql-bin-changelog.000002 --result-file=mysql-bin-changelog.000002
- binlog の中身を除いてみる
% cat mysql-bin-changelog.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #171207 20:53:29 server id 1880231669 end_log_pos 120 CRC32 0x7872b99d Start: binlog v 4, server v 5.6.10-log created 171207 20:53:29 at startup ROLLBACK/*!*/; BINLOG ' uSspWg/1DhJwdAAAAHgAAAAAAAQANS42LjEwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAC5KylaEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZ25 cng= '/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
リードレプリカで binlog を確認する
- リードレプリカでは binlog は読めない。
% mysql -h aurora01-rr.*******.ap-northeast-1.rds.amazonaws.com -u awsuser -p Enter password: mysql> SHOW VARIABLES LIKE 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.31 sec) mysql> show binary logs; ERROR 1381 (HY000): You are not using binary logging mysql> show master status; Empty set (0.06 sec) mysql> exit Bye % mysqlbinlog --read-from-remote-server -h aurora01-rr.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p mysql-bin-changelog.000002 --result-file=mysql-bin-changelog.000002 Enter password: ERROR: Got error reading packet from server: Binary log is not open
環境
- Aurora MySQL 5.6.10a