Binary Logs in Mysql

Binary Logs are a useful feature in MySQL. Mainly intended for master / slave replication setups and point-in-time recovery they contain records of all changes to the MySQL database, including schema alterations and table creations, but also INSERT, UPDATE and DELETE statements.

# Enable binary log in Mysql
- Edit file my.cnf
[mysqld]
# Other options
# go here
## Replication
server-id=0
binlog-format = STATEMENT
expire_logs_days = 14
log-bin = /var/lib/mysql/bin-log
# Checking If Binary Logs Are Enabled
mysql> SHOW VARIABLES LIKE 'log_bin';
# Finding The Binary Logs
mysql> SHOW VARIABLES LIKE 'log_bin_basename';
# List of binary log files on the server.
mysql> SHOW BINARY LOGS;

# How Binary Logs Are Rotated
mysql> SHOW VARIABLES LIKE 'max_binlog_size';
The default value is 1073741824 bytes… around 1G, which is also the maximum value.
# Binary Log Formats
mysql> SHOW VARIABLES LIKE 'binlog_format';
There are 3 options for binary log format: Row,Statement, Mixed
- Statement-based - Logs the actual SQL queries that are executed
- Row-based - Logs events that indicate how table rows are changed
- Mixed - Uses statement-based by default, but under certain circumstances switches to row-based
# Reading The Binary Logs
#mysqlbinlog --no-defaults --start-datetime="2019-05-23 14:47:26" --stop-datetime="2019-05-23 14:50:52" /usr/local/mysql/bin-log.000002 | mysql -u root -p