MySQL Binary Log

Understanding the content and function of MySQL binary logs is critically important, especially in database management and disaster recovery scenarios. This article provides detailed information on what binary logs are, how they are used, and how they are managed.

1. What is a Binary Log and Why is it Used?

A binary log is a log file that records data changes occurring on the MySQL server. It is used for two main purposes:

PITR (Point In Time Recovery): Binary logs are needed to restore the database to a specific point in time.

Replication: They enable the transfer of changes made on the master server to replica servers.

Additionally, in some scenarios, binary logs can be used to access previous versions of updated records.

2. Location of Binary Log Files

By default, binary log files are located within the data directory. However, this location can be changed:

Note: The binary log directory must be owned by the mysql user.

3. Disabling Binary Logs

In some cases, binary logging is not desired. For example, when you want to quickly restore a backup taken with mysqldump:

This parameter disables binary logging.

4. Binary Log File Size

By default, the binary log file size is 1GB. However, since large transactions can exceed this limit, it can be adjusted with the following parameter:

max_binlog_size = 500

max_binlog_size = 500

max_binlog_size = 500

If a transaction does not fit into one file, a larger file is created for integrity.

5. Memory Usage of Binary Logs

Binary logs use memory, especially for transactions that have not yet been committed.

binlog_cache_size: Transactions are held in memory until they are committed.

Large Transactions: For transactions affecting a large number of rows, you may need to increase the binlog_cache_size value.

6. Retention Period of Binary Logs

Older Versions: Use the expire_logs_days parameter.

Newer Versions: The binlog_expire_logs_seconds parameter is preferred.

Controlling the retention period of binary logs is important for managing disk space.

7. Binary Log Formats

The binlog_format parameter is used to determine how binary logs are stored:

ROW: Stores the binary versions of the rows that have changed. It is a secure method but uses more disk space.

STATEMENT: Stores SQL statements. It may cause incompatibilities with procedure calls.

MIXED: A hybrid method that combines both formats.

DDL Commands: All DDL (CREATE, ALTER, DROP) commands are always stored as STATEMENT.

8. Reading Binary Logs: mysqlbinlog

The primary tool used to view and process the contents of binary logs is:

mysqlbinlog /binlog/server-log.0000001

mysqlbinlog /binlog/server-log.0000001

mysqlbinlog /binlog/server-log.0000001

This command converts the binary log file into text format, allowing you to examine the data.

Use Cases:

PITR: Completing missing changes after a backup.
Data Recovery: Recovering lost data.

9. Use of Binary Logs in Replication

MySQL replication occurs by transferring binary logs generated on the master server to replica servers. Replication is not possible without binary logs.

10. PITR (Point In Time Recovery)

A database backup and the binary log files for the desired time range are required. After restoring the backup, missing operations are completed using binary logs:

mysqlbinlog /binlog/server-log.0000001

mysqlbinlog /binlog/server-log.0000001

mysqlbinlog /binlog/server-log.0000001

11. Potential Issues: Non-Deterministic Queries

STATEMENT Format: Non-deterministic operations (e.g., NOW(), RAND()) can lead to data consistency issues.

ROW Format: Since it records the full details of affected rows, it is more reliable.

Examples of Problematic Queries:

DELETE FROM sales WHERE sale_date < NOW() - INTERVAL 1 MONTH;
UPDATE sales SET update_user = @user WHERE id=100;
DELETE FROM sales ORDER BY RAND() LIMIT 1

DELETE FROM sales WHERE sale_date < NOW() - INTERVAL 1 MONTH;
UPDATE sales SET update_user = @user WHERE id=100;
DELETE FROM sales ORDER BY RAND() LIMIT 1

DELETE FROM sales WHERE sale_date < NOW() - INTERVAL 1 MONTH;
UPDATE sales SET update_user = @user WHERE id=100;
DELETE FROM sales ORDER BY RAND() LIMIT 1

Logging such queries in ROW format helps ensure data consistency.

Conclusion

MySQL binary logs play a critical role in database management and data security. With proper configuration and management, it is possible to prevent data loss and maintain healthy replication.