Binary log management in MySQL

Introduction

Binary logs keep a record of all changes made to the database. Managing them helps keep the database safe and running smoothly.

When you want to recover data after a crash or mistake.

When you need to copy changes from one database to another.

When you want to track who changed what and when.

When you want to save space by removing old logs.

When you want to check what changes happened recently.

Syntax

MySQL

SHOW BINARY LOGS;
SHOW MASTER STATUS;
PURGE BINARY LOGS TO 'log_name';
PURGE BINARY LOGS BEFORE 'date';

Use SHOW BINARY LOGS to list all binary log files.

PURGE commands help delete old logs to save space.

Examples

Lists all binary log files currently stored.

MySQL

SHOW BINARY LOGS;

Shows the current binary log file and position.

MySQL

SHOW MASTER STATUS;

Deletes all binary logs before 'mysql-bin.000010'.

MySQL

PURGE BINARY LOGS TO 'mysql-bin.000010';

Deletes all binary logs created before June 1, 2024.

MySQL

PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

Sample Program

This sequence shows all binary logs, shows the current log status, deletes logs before 2024, then shows the remaining logs.

MySQL

SHOW BINARY LOGS;
SHOW MASTER STATUS;
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
SHOW BINARY LOGS;

Important Notes

Binary logs are important for backup and replication.

Deleting logs too early can cause problems if you need to restore data.

Always check current log status before purging logs.

Summary

Binary logs record all database changes for recovery and replication.

Use SHOW commands to view logs and status.

Use PURGE commands to safely remove old logs and save space.

Practice

1. What is the primary purpose of the MySQL binary log?

easy

A. To store user login credentials securely

B. To record all changes to the database for recovery and replication

C. To manage database schema versions

D. To optimize query execution plans

Solution

1. Step 1: Understand what binary logs do

   Binary logs keep a record of all changes made to the database, such as inserts, updates, and deletes.

2. Step 2: Identify the main uses of binary logs

   These logs are used for data recovery after crashes and for replicating changes to other servers.

3. Final Answer:

   To record all changes to the database for recovery and replication -> Option B

4. Quick Check:

   Binary logs = record changes for recovery and replication [OK]

Hint: Binary logs track changes for recovery and replication [OK]

Common Mistakes:

• Confusing binary logs with user authentication logs
• Thinking binary logs store query execution plans
• Assuming binary logs manage schema versions

2. Which of the following commands correctly shows the list of binary log files in MySQL?

easy

A. SHOW BINARY LOG FILES;

B. SHOW BINLOG FILES;

C. SHOW LOGS BINARY;

D. SHOW BINARY LOGS;

Solution

1. Step 1: Recall the correct syntax to list binary logs

   The correct command to list binary log files is SHOW BINARY LOGS;.

2. Step 2: Verify other options for syntax correctness

   Options B, C, and D are not valid MySQL commands and will cause syntax errors.

3. Final Answer:

   SHOW BINARY LOGS; -> Option D

4. Quick Check:

   List binary logs = SHOW BINARY LOGS; [OK]

Hint: Use SHOW BINARY LOGS; to list binary log files [OK]

Common Mistakes:

• Using incorrect command syntax like SHOW BINLOG FILES;
• Mixing up command word order
• Adding extra words causing syntax errors

3. Given the following MySQL commands executed in order:
SHOW BINARY LOGS;
Output:
File: mysql-bin.000001
File: mysql-bin.000002
File: mysql-bin.000003

Then running:
PURGE BINARY LOGS TO 'mysql-bin.000003';
What binary log files remain after the purge?

medium

A. mysql-bin.000001, mysql-bin.000002

B. Only mysql-bin.000003

C. mysql-bin.000003 and later

D. All binary logs are deleted

Solution

1. Step 1: Understand PURGE BINARY LOGS TO command

   This command deletes all binary logs before the specified file, but not the file itself.

2. Step 2: Apply to given files

   Logs mysql-bin.000001 and mysql-bin.000002 are deleted; mysql-bin.000003 and any later files remain.

3. Final Answer:

   mysql-bin.000003 and later -> Option C

4. Quick Check:

   PURGE BINARY LOGS TO 'file' keeps that file and after [OK]

Hint: PURGE BINARY LOGS TO keeps the named file and after [OK]

Common Mistakes:

• Assuming the named file is deleted too
• Thinking all logs are deleted
• Confusing PURGE BINARY LOGS TO with PURGE BINARY LOGS BEFORE

4. You run the command PURGE BINARY LOGS BEFORE 'mysql-bin.000005'; but get an error: "ERROR 1236 (HY000): Could not purge binary logs". What is the most likely cause?

medium

A. The specified log file does not exist or is not in the binary log index

B. You need to restart the MySQL server before purging

C. The binary log feature is disabled in MySQL

D. You must use PURGE BINARY LOGS TO instead of BEFORE

Solution

1. Step 1: Understand error cause for PURGE BINARY LOGS BEFORE

   This error usually happens if the specified log file is missing or not recognized in the binary log index.

2. Step 2: Check other options

   Restarting server is not required; binary log must be enabled to run command; PURGE BINARY LOGS BEFORE is valid syntax.

3. Final Answer:

   The specified log file does not exist or is not in the binary log index -> Option A

4. Quick Check:

   Missing log file = purge error [OK]

Hint: Check log file exists before purging [OK]

Common Mistakes:

• Restarting server unnecessarily
• Confusing PURGE BINARY LOGS BEFORE with TO
• Ignoring binary log enablement status

5. You want to free disk space by deleting old binary logs but keep all logs generated after a specific date '2024-01-01 00:00:00'. Which approach correctly achieves this?

hard

A. Find the binary log file generated just after the date, then run PURGE BINARY LOGS TO 'that_file';

B. Run PURGE BINARY LOGS BEFORE 'mysql-bin.000001'; to delete all logs before the first file

C. Disable binary logging temporarily, delete all files manually, then re-enable logging

D. Use SHOW BINARY LOGS; and delete files older than the date manually from disk

Solution

1. Step 1: Identify the binary log file after the target date

   Use the binary log index or SHOW BINARY LOGS; to find the first file generated after '2024-01-01 00:00:00'.

2. Step 2: Use PURGE BINARY LOGS TO command

   Run PURGE BINARY LOGS TO 'that_file'; to safely delete all logs before that file, keeping newer logs.

3. Step 3: Avoid unsafe manual deletion

   Manual deletion or disabling logging risks data loss or replication errors.

4. Final Answer:

   Find the binary log file generated just after the date, then run PURGE BINARY LOGS TO 'that_file'; -> Option A

5. Quick Check:

   Use PURGE BINARY LOGS TO after date file [OK]

Hint: Purge logs safely by targeting file after date [OK]

Common Mistakes:

• Deleting files manually risking corruption
• Disabling binary logging to delete files
• Purging logs before the first file (no effect)