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.