What is a Binlog?
A binlog (binary log) is a transaction log used by MySQL and MariaDB to record all changes made to the database. It is crucial for replication, point-in-time recovery, and auditing.
Key Features of Binlog
- Records Database Changes – Logs INSERT, UPDATE, DELETE statements, but not SELECT statements.
- Supports Replication – Used to synchronize changes between master and replica databases.
- Point-in-Time Recovery – Enables database restoration up to a specific transaction in case of failure.
Types of Events in a Binlog
- DML Events: INSERT, UPDATE, DELETE
- DDL Events: CREATE, ALTER, DROP
- Transaction Control: BEGIN, COMMIT, ROLLBACK
Binlog Formats
- Statement-Based Logging (SBR) – Logs SQL statements (prone to non-deterministic issues).
- Row-Based Logging (RBR) – Logs actual row changes (more reliable for replication).
- Mixed Logging (MBR) – Uses both SBR and RBR depending on the operation.
Common Binlog Commands
- Enable binlog:
log_bin = mysql-bin(in MySQL config) - Check binlog status:
SHOW BINARY LOGS; - View binlog contents:
mysqlbinlog mysql-bin.000001