0

I have created a snapshot of Aurora MYSQL database and then restore it. Let's call it clone

Here is the output of the command SHOW BINARY LOGS on the master database

enter image description here

Here is the output of the command SHOW BINARY LOGS on the clone database

enter image description here

As you can see there are 2 extra binlog files on the clone which I don't want. I want to have binlog identical to the master.

How can I delete the last 2 bing logs from the clone database?

There is nothing special in these bin log files

mysql-bin-changelog.603814

enter image description here

mysql-bin-changelog.603815

enter image description here

Improve this question

1 Answer 1

Reset to default
2

"I want to have binlog identical to the master."

Why would you want this? The binlogs are already not identical between these two instances, even besides the last two, because the filenames and sizes are different.

Binlogs start new files if FLUSH LOGS is run, or if MySQL Server restarts, or the file reaches the max binlog size. The number of files and their sizes are bound to be out of sync between the source instance and its replicas. I would guess in this case that restoring the snapshot caused two restarts.

It's not a problem for binlog files to have different sizes. As long as they contain the same changes, they can be stored in few files or many files, and they don't have to be the same.

To apply changes in binlogs, you must have a contiguous set of changes since the last snapshot. If you were to purge the last two files, you could never use the binlogs for replication or point-in-time recovery, at least not past mysql-bin-changelog.603813.

Purging the binlog files older than the latest snapshot is common and recommended, but not the newest binlog files. In fact, PURGE BINARY LOGS will never remove the latest binlog file, because the MySQL Server is still using it.

One solution for you is to set the expire_logs_days in the parameter group to a modest value such as 2 or 3, then wait that number of days for the current set of binlogs to expire. After that, the small binlog files will be gone.

Improve this answer
4
  • Thanks for the detailed answer. Yes, I don't want to use mysql-bin-changelog.603813 . I just wanted to reset the bin log on the client at position mysql-bin-changelog.603813 . is that possible?
    – Mubbashar
    Commented Jun 18, 2021 at 5:01
  • I don't understand what you want to achieve. Are you trying to restore data to an earlier state?
    – Bill Karwin
    Commented Jun 18, 2021 at 15:32
  • Let me tell you what I want to achieve. I am trying to do a replication from RDS to Cloud SQL. I have followed the following steps 1. Create a snapshot of the production RDS DB 2. Create an instance of production DB using a snapshot 3. Create a DNS record rds-cloud.xyz.com and point it to the newly created instance of Production DB 4. This instance will be only used for Cloud SQL initial transfer job so this will not fail as there will no activity on this instance 5. Once the initial transfer is done I had to update the DNS record to point to our production DB
    – Mubbashar
    Commented Jun 18, 2021 at 17:25
  • But problem is that the newly created snapshot has 2 extra binlog and I can not use it for the initial transfer job. I have tried all the methods of creating Cloud SQL replica of my RDS but everything is failed.
    – Mubbashar
    Commented Jun 18, 2021 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.