# Binary Logging ==================================================================================== ### Binary Logging ==================================================================================== ### What is Binary Logging? Binary logging in MySQL is a crucial feature for data replication, backup, and recovery. It logs all changes made to the database, such as updates, deletions, and insertions, in a binary format. #### Purpose of Binary Logging - **Replication**: Binary logs are used to replicate data from a primary server to one or more replica servers. Changes logged on the primary server are applied to the replicas to keep them synchronized. - **Point-in-Time Recovery (PITR)**: In case of data corruption or accidental data loss, binary logs allow you to restore a backup and replay the transactions recorded in the binary logs to recover the database to a specific point in time. - **Audit Trails**: They can be used to audit and review changes made to the database over time. How Binary Logging Works - **Binary Log Files**: The changes are recorded in binary log files (with extensions like `.000001`, `.000002`, etc.) which are stored in the MySQL data directory. - **Log Events**: Each event in the binary log represents a single database change such as a query or a transaction. These events are recorded in the order they were executed. - **Binary Log Index File**: MySQL maintains an index file (typically `mysql-bin.index`) that lists all the binary log files and their sequence. ==================================================================================== ### Binary Logging Configuration ------------------------------------------------------------------------------------------------------------------------------------------------ #### Enabling Binary Logging To enable binary logging, you need to configure the MySQL server with the `log_bin` parameter in the `my.cnf` configuration file: ``` [mysqld] log_bin = /var/log/mysql/mysql-bin.log ``` ------------------------------------------------------------------------------------------------------------------------------------------------ #### Binary Logging Options Bin log options that can also be set in the my.cnf file:
expire\_logs\_days = 7Defines how many days to retain binary log files before automatic deletion.
binlog\_format = format format: ROW STATEMENT MIXED Determines the format of the binary logs. The available formats are: ROW:Logs the actual changes at the row level. STATEMENT: Logs each SQL statement that modifies data. MIXED: Uses a combination of statement and row formats.
max\_binlog\_size = 100MSets the maximum size of a binary log file before a new file is created.
------------------------------------------------------------------------------------------------------------------------------------------------ #### Binary Log Management ##### Viewing Binary Logs Use the `SHOW BINARY LOGS` or `SHOW MASTER LOGS` command to list the binary log files. ``` SHOW BINARY LOGS; ``` ##### Examining Binary Log Content Use the `mysqlbinlog` utility to examine the content of binary log files ``` mysqlbinlog /var/log/mysql/mysql-bin.000001 ``` ##### Purging Binary Logs To manually delete old binary logs and free up space, use the `PURGE BINARY LOGS` statement. ``` PURGE BINARY LOGS TO 'mysql-bin.000010'; ``` Or, you can purge logs older than a specific date: ``` PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00'; ``` ==================================================================================== ### Data Recovery From Binary Logs ------------------------------------------------------------------------------------------------------------------------------------------------

Binary Logs are just transactional changes made to a database. They don't include a full copy of the database itself. In order to replay binary logs, you'll need a backup copy of the database to replay onto. See mysql backups.

**1. Identify the Binary Logs to Use** Determine the range of binary logs that contain the transactions you need to replay. You can list all binary logs with: ``` SHOW BINARY LOGS; ``` **2. Obtain a Backup** Obtain the newest backup/dump of the database prior to the time that we need to recover to. **3. Import the backup into MySQL** ``` mysql -u root -p < /path/to/backup.sql ``` **4. Apply Binary Logs** A. Identify the start and end points within the binary logs. You might need to apply all transactions or up to a specific point in time or transaction. ``` mysqlbinlog --start-datetime="2024-06-28 10:00:00" --stop-datetime="2024-06-29 14:00:00" /path/to/mysql-bin.000001 /path/to/mysql-bin.000002 | mysql -u root -p ``` ====================================================================================