Binary log management in MySQL - Step-by-Step Execution

Concept Flow - Binary log management

Enable binary logging in MySQL config

↓

MySQL writes changes to binary log

↓

Binary log stores events (INSERT, UPDATE, DELETE)

↓

Use binary log for replication or recovery

↓

Manage binary logs: list, purge, expire

↓

Old logs deleted to save space

Binary log management involves enabling logs, recording data changes, using logs for replication or recovery, and cleaning old logs to save space.

Execution Sample

MySQL

SHOW BINARY LOGS;
PURGE BINARY LOGS TO 'mysql-bin.000003';
SHOW MASTER STATUS;

These commands list binary logs, delete logs up to a point, and show current log status.

Execution Table

Step	Command	Action	Result
1	SHOW BINARY LOGS;	List all binary log files	mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004
2	PURGE BINARY LOGS TO 'mysql-bin.000003';	Delete logs before mysql-bin.000003	Logs mysql-bin.000001 and mysql-bin.000002 deleted
3	SHOW BINARY LOGS;	List remaining binary log files	mysql-bin.000003 mysql-bin.000004
4	SHOW MASTER STATUS;	Show current binary log file and position	File: mysql-bin.000004 Position: 154
5	PURGE BINARY LOGS BEFORE NOW();	Delete all logs before current time	Logs mysql-bin.000003 deleted
6	SHOW BINARY LOGS;	List remaining binary log files	mysql-bin.000004
7	PURGE BINARY LOGS TO 'mysql-bin.000005';	Error: log file does not exist	ERROR 1236 (HY000): Could not find first log file name in binary log index file
8	EXIT	Stop managing logs	Session ended

💡 Stopped after error on purging non-existing log and session exit

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 5	After Step 6	Final
Binary Logs	mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004	Same	mysql-bin.000003 mysql-bin.000004	Same	mysql-bin.000004	Same	mysql-bin.000004
Current Log File	mysql-bin.000004	Same	Same	Same	Same	Same	Same
Current Position	154	Same	Same	Same	Same	Same	Same

Key Moments - 3 Insights

Why does PURGE BINARY LOGS TO 'mysql-bin.000003' delete logs mysql-bin.000001 and mysql-bin.000002?

Because PURGE BINARY LOGS TO deletes all logs before the specified log file, not including it, as shown in execution_table step 2.

What happens if you try to purge logs to a non-existing log file?

An error occurs because MySQL cannot find the specified log file in the index, as seen in execution_table step 7.

Does SHOW MASTER STATUS change after purging logs?

No, the current binary log file and position remain the same after purging, as shown in step 4 and variable_tracker.

Visual Quiz - 3 Questions

Look at the execution_table, after which step are mysql-bin.000001 and mysql-bin.000002 deleted?

A After Step 5

B After Step 3

C After Step 2

D After Step 6

💡 Hint

Check the Action and Result columns in execution_table row for Step 2

According to variable_tracker, what is the current binary log file after Step 6?

A mysql-bin.000003

B mysql-bin.000004

C mysql-bin.000002

D mysql-bin.000001

💡 Hint

Look at the Current Log File row under After Step 6 column in variable_tracker

If you run PURGE BINARY LOGS TO 'mysql-bin.000005', what will happen according to execution_table?

A An error will occur because the log file does not exist

B Only logs before mysql-bin.000005 will be deleted

C All logs will be deleted

D Nothing will happen

💡 Hint

See the Result column in execution_table step 7

Concept Snapshot

Binary log management in MySQL:
- Enable binary logging in config (my.cnf)
- Logs record data changes for replication and recovery
- Use SHOW BINARY LOGS to list logs
- Use PURGE BINARY LOGS TO to delete old logs
- SHOW MASTER STATUS shows current log and position
- Purging non-existing logs causes errors

Full Transcript

Binary log management in MySQL starts by enabling binary logging in the server configuration. The server writes all data-changing events to binary log files. These logs are used for replication and recovery. You can list all binary logs using SHOW BINARY LOGS. To save disk space, old logs can be deleted using PURGE BINARY LOGS TO 'log_name', which deletes all logs before the specified log. The current binary log file and position can be checked with SHOW MASTER STATUS. Attempting to purge logs to a non-existing log file results in an error. Managing binary logs properly ensures efficient replication and disk usage.

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)