CODEWITHSUNDEEP
mysqlamazon-web-servicesrds
Matej
Matej

Reputation: 9815

Amazon RDS mysqbinlog "Could not find first log file name in binary log index file"

Preamble:

We use RDS at AWS to manage our MySQL database. I know that RDS creates binary logs used for replication.. I want to harvest these binary logs and store them on S3, so that at any time, i can restore to an off-site database and replay the actions.

The Problem:

I span up a new RDS instance, and tried the following.

root@aws_test_server:/root# mysqlbinlog -h testing.xxxxxxxx.eu-west-1.rds.amazonaws.com -u myuser -p --read-from-remote-server -t mysql-bin-changelog.000002
Enter password:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ERROR: Got error reading packet from server: Could not find first log file name in binary log index file
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
root@aws_test_server:/root#

So the binary log retrieves fine, however see the Error within the binary log.. What does this mean?

I googled for about 2 days and could find no mention of this error [anywhere].

My questions:

  1. Is this a good idea? (backing up binary logs to S3..)
  2. Can it be done in a better way? (~infinite point-in-time db recovery)
  3. What can I do about the error?

PS: this is happening for the production DB and this test DB

PS2: I note that this is not directly a programming question, however is related to it. Please do not move/close from SO to somewhere like serverfault. Thanks

Upvotes: 2

Views: 6440

Answers (2)

JamesHoux
JamesHoux

Reputation: 3477

I had this exact same error but couldn't get anywhere from searching or trying other suggested fixes. I had to get an AWS technician to help. The solution was to set the parameter 'binlog_checksum' to NONE. It defaults to CRC32.

From the technician:

binlog_checksum is used to write checksums for the events by setting the binlog_checksum system variable. When reading back from the binary log,the source uses the event length by default, but can be made to use checksums if available by enabling the master_verify_checksum system variable.The replication I/O thread on the replica also verifies events received from the source. You can cause the replication SQL thread to use checksums if available when reading from the relay log by enabling the slave_sql_verify_checksum system variable.

To set a parameter, go to your RDS instance. Navigate to Configuration and look for the Parameter group it belongs to. Go to the parameter group and search for "binlog_checksum".

Upvotes: 1

gray
gray

Reputation: 381

Encountered the same problem today. Amazon RDS normally purges a binary log as soon as possible, to increase binlog retention time, you should run query

call mysql.rds_set_configuration('binlog retention hours', 24);

Upvotes: 3

Related Questions