MySQL: How long do binlogs last?
I have a mysql slave that I am trying to replicate to a mysql master instance.
I migrated data after a week or so from a master instance. At that time, I called SHOW MASTER STATUS
on the host and got the 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)
This bitlog is not the one that was there a week ago.
Can I no longer run b / c replication so the bitlog I'm trying to set up is rotated? Is there a variable that I can check to see how long I have before I can no longer start replicating?
Edit:
Also after reading several mysql docs I found a command that should display 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 I wrote last week is not listed there, so my question still remains ...
Edit 2:
This is AWS RDS, but I found a stored procedure that lists the rental 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)
It says that bingologists are saved for 24 hours. The database I'm trying to replicate to is taking more than 24 hours to roll over when it's ready for replication, the replication logs it needs to access have already been deleted ...
Edit 3:
Found here :
Log file size
MySQL slow query log, error log, and total log file sizes are limited to no more than 2% of the allocated database instance storage space. To maintain this threshold, logs are automatically rotated every hour, and log files older than 24 hours are deleted. If the total log file size exceeds the threshold after deleting the old log files, then the largest log files are removed until the log file no longer exceeds the threshold.
source to share
For specific AWS RDS instances, you can find the storage lengths of the 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)
Since I am trying to migrate our current RDS MySQL instance to Amazon Aurora, I must first migrate the database and then start replicating for any updates that happened during the migration. Since the migration takes more than 24 hours, I need to set a longer window than the default one provided by Amazon by default, which apparently I can do with the following stored procedure :
Amazon RDS usually purges the binary log as soon as possible, but the binary log should be available on the instance to which mysqlbinlog. To specify the number of hours for RDS to retain the log binary, use the mysql.rds_set_configuration stored procedure and specify a period with sufficient time to load the logs. After you set the retention period, monitor the storage usage for the database instance to ensure that the stored binary logs do not take up too much storage space.
In this example, the retention period is kept up to 1 day:
call mysql.rds_set_configuration('binlog retention hours', 24);
It looks like I need to hold the hold time on my master and do another migration, my current one can no longer be replicated properly.
source to share