MySQL:二进制日志保留多长时间？

Question

我有一个 mysql slave，我正在尝试复制一个 master mysql 实例。

我在一周左右的时间内从生产主实例中迁移了数据。当时我在 master 上调用了 SHOW MASTER STATUS 并得到了一个 binlog 名称和位置。现在，当我运行 SHOW MASTER STATUS 时，我得到:

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)

那个二进制日志与一周前的二进制日志不同。

我是否可以不再开始复制 b/c 我试图定位的 binlog 已轮换？是否有一个变量可以让我查看在我不能再开始复制之前“拥有”多长时间？

编辑:

我还仔细阅读了 mysql 文档，发现了一个应该列出所有二进制日志的命令:

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)

同样，我上周写下的二进制日志没有列在那里，所以我的问题仍然存在......

编辑 2:

这是特定于 AWS RDS 的，但我找到了一个列出保留时间的存储过程:

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 它说 binglog 会保留 24 小时。我尝试复制的数据库需要超过 24 小时的迁移时间，这意味着当它准备好复制时，它需要访问的复制日志已经被删除......

编辑 3:

找到 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.

Answer 1

对于 AWS RDS 特定实例，您可以使用以下存储过程找到二进制日志的保留长度:

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)

当我尝试将当前的 MySQL RDS 实例迁移到 Amazon Aurora 时，我必须首先迁移数据库，然后开始复制迁移窗口期间发生的任何更新。因为迁移需要超过 24 小时，所以我需要设置比亚马逊提供的默认 24 窗口更长的窗口，这显然我可以使用以下 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);

看来我需要延长我的 master 上的保留时间并进行另一次迁移，我当前的迁移无法再正确复制。