Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: How long are binlogs retained?

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.

like image 786
jcroll Avatar asked Aug 07 '15 15:08

jcroll


People also ask

Where are MySQL bin logs stored?

The MySQL binary logs and index files are saved in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. We can change the default location of the binary logs.

What are Binlogs in MySQL?

The mysqlbinlog command displays the log file contents for all databases that are a part of the system. This command can be modified to display the events that have occurred only for a particular database using -d or -database option. These options are followed by the database name for the which the logs are required.

How do I know if MySQL Binlog is enabled?

to know if MySQL binary log is enabled through SQL command, you can use show variables command. show variables like 'yourPatternValue'; In place of 'yourPatternValue', you can use log_bin to check the binary log is enabled using SQL command show.

How do I purge Binlogs in MySQL?

To safely purge binary log files, follow this procedure: On each replica, use SHOW REPLICA STATUS to check which log file it is reading. Obtain a listing of the binary log files on the source with SHOW BINARY LOGS . Determine the earliest log file among all the replicas.


1 Answers

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.

like image 132
jcroll Avatar answered Oct 06 '22 00:10

jcroll