Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

mysql.rds_next_master_log

Changes the replication master log position to the start of the next binary log on the master. Use this procedure only if you are receiving replication I/O error 1236 on a Read Replica.

Syntax

Copy
CALL mysql.rds_next_master_log( curr_master_log );

Parameters

curr_master_log

The index of the current master log file. For example, if the current file is named mysql-bin-changelog.012345, then the index is 12345. To determine the current master log file name, run the SHOW SLAVE STATUS command and view the Master_Log_File field.

Usage Notes

The mysql.rds_next_master_log procedure must be run by the master user.

Warning

Call mysql.rds_next_master_log only if replication fails after a failover of a Multi-AZ DB instance that is the replication source, and the Last_IO_Errno field of SHOW SLAVE STATUS reports I/O error 1236.

Calling mysql.rds_next_master_log may result in data loss in the Read Replica if transactions in the source instance were not written to the binary log on disk before the failover event occurred. You can reduce the chance of this happening by configuring the source instance parameters sync_binlog = 1 and innodb_support_xa = 1, although this may reduce performance. For more information, see Working with PostgreSQL, MySQL, and MariaDB Read Replicas.

The mysql.rds_next_master_log procedure is available in these versions of Amazon RDS MySQL:

  • MySQL 5.5 version 5.5.33 and later

  • MySQL 5.6 version 5.6.13 and later

  • MySQL 5.7 version 5.7.10 and later

Examples

Assume replication fails on an Amazon RDS Read Replica. Running SHOW SLAVE STATUS\G on the replica returns the following result:

Copy
*************************** 1. row *************************** Slave_IO_State: Master_Host: myhost.XXXXXXXXXXXXXXX.rr-rrrr-1.rds.amazonaws.com Master_User: MasterUser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin-changelog.012345 Read_Master_Log_Pos: 1219393 Relay_Log_File: relaylog.012340 Relay_Log_Pos: 30223388 Relay_Master_Log_File: mysql-bin-changelog.012345 Slave_IO_Running: No 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: 30223232 Relay_Log_Space: 5248928866 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin-changelog.013406' at 1219393, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 67285976

The Last_IO_Errno field shows that the instance is receiving I/O error 1236. The Master_Log_File field shows that the file name is mysql-bin-changelog.012345, which means that the log file index is 12345. To resolve the error, you can call mysql.rds_next_master_log with the following parameter:

Copy
CALL mysql.rds_next_master_log(12345);