I have a mysql slave which I am attempting to replicate a master mysql instance.
I migrated the data a week or so from the production master instance. At the time I invoked SHOW MASTER STATUS
on the master and got a binlog name and position. Now when I run SHOW MASTER STATUS
I get:
mysql> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.039446 | 120 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.05 sec)
That binlog is not the same one that was there a week ago.
Can I no longer start replication b/c the binlog I am trying to target was rotated? Is there a variable I can check out to see how long I "have" before I can no longer begin replication?
Edit:
Also doing some more reading through the mysql docs and I found a command that should list all binary logs:
mysql> SHOW BINARY LOGS;
+----------------------------+-----------+
| Log_name | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.039456 | 479 |
| mysql-bin-changelog.039457 | 120 |
+----------------------------+-----------+
2 rows in set (0.07 sec)
Again the binary log that I wrote down last week is not listed there so my question still remains...
Edit 2:
This is AWS RDS specific but I found a stored procedure that lists the rentention hours:
mysql> call mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name | value | description |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours | NULL | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
Here it says the binglogs are retained for 24 hours. The database I am trying to replicate takes over 24 hours to migrate meaning by the time it is ready for replication the replication logs it needs to access are already deleted...
Edit 3:
Found here:
Log File Size
The MySQL slow query log, error log, and the general log file sizes are constrained to no more than 2% of the allocated storage space for a DB instance. To maintain this threshold, logs are automatically rotated every hour and log files older than 24 hours are removed. If the combined log file size exceeds the threshold after removing old log files, then the largest log files are deleted until the log file size no longer exceeds the threshold.
For AWS RDS specific instances you can find the retention lengths of binary logs using the following stored procedure:
mysql> call mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name | value | description |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours | NULL | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
As I am trying to migrate our current MySQL RDS instance to Amazon Aurora I must first migrate the database and then begin replication for any updates that happened during the migration window. Because the migration takes over 24 hours I need to set a longer window than the default 24 window provided by Amazon which apparently I can do with the following stored procedure:
Amazon RDS normally purges a binary log as soon as possible, but the binary log must still be available on the instance to be accessed by mysqlbinlog. To specify the number of hours for RDS to retain binary logs, use the mysql.rds_set_configuration stored procedure and specify a period with enough time for you to download the logs. After you set the retention period, monitor storage usage for the DB instance to ensure that the retained binary logs do not take up too much storage.
This example sets the retention period to 1 day:
call mysql.rds_set_configuration('binlog retention hours', 24);
Looks like I need to up the retention time on my master and do another migration, my current one can no longer be properly replicated.