MySQL Binary Logs

Giriş

Açıklaması şöyle

In MySQL, the changes that occur within the database are logged simultaneously. The MySQL BinLog (Binary Logs) is responsible for handling these updates and hence provide a description of such events to indicate the changes made to the database being used. These can be the changes made to the data or an operation like new table creation, etc. They even provide information on statements that could have lead to a potential change.

The MySQL BinLogs serve two important purposes:

• Replication: When working on a master server, the binary logs contain a record of the changes that have occurred. These records are sent to the slave servers to help them execute those events & make the same data changes that were made on the master server. For more information on implementing replication, you can look into the replication manual.
• 
  
• Data Recovery: Some recovery operations require using binary logs. Once the backup is restored, the recorded events are re-executed and this brings the database up to date from the time of backup.

Binary Log Formatı

3 formattan birisi kullanılıyor

1. Statement-Based Logging

2. Row-Based Logging

3. Mixed Logging

Binary log içeriğini görmek için mysqlbinlog komutu kullanılır

1. Statement-Based Logging

Açıklaması şöyle

When statement-based logging is enabled, statements are logged to the binary log exactly as they were executed.

Örnek

Şöyle yaparız. Burada içeriğin SQL cümlesi olduğu görülebilir.

mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005 

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

2. Row-Based Logging

Açıklaması şöyle

When row-based logging is enabled, DML statements are not logged to the binary log. Instead, each insert, update, or delete performed by the statement for each row is logged to the binary log separately. DDL statements are still logged to the binary log.

3. Mixed Logging

Açıklaması şöyle

When mixed logging is enabled, the server uses a combination of statement-based logging and row-based logging. Statement-based logging is used by default, but when the server determines a statement may not be safe for statement-based logging, it will use row-based logging instead. 

Binary Log Formatını Görmek

Şu komutlardan herhangi birisini yaparız

SELECT @@global.binlog_format;

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

SELECT variable_value FROM information_schema.global_variables

WHERE variable_name='binlog_format';

Binary Log Formatını Değiştirmek

Örnek - SQL

Şöyle yaparız

-- Global
SET GLOBAL binlog_format='ROW';

-- Session için 
SET SESSION binlog_format='ROW';

Örnek - Konfigürasyon

Şöyle yaparız

binlog_format=ROW

Binary Log'un çalıştığını kontrol etmek

Örnek

Şöyle yaparız

mysql> show variables like '%bin%';
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 38152                |
| binlog_format                   | MIXED                |
| innodb_locks_unsafe_for_binlog  | OFF                  |
| log_bin                         | ON                   |
| log_bin_trust_function_creators | OFF                  |
| log_bin_trust_routine_creators  | OFF                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 419430400            |
| sql_log_bin                     | ON                   |
| sync_binlog                     | 0                    |
+---------------------------------+----------------------+
10 rows in set (0.00 sec)

Binary Dosyalarını Silmek

PURGE BINARY LOGS ile elle silinebilir

Konfigürasyon Dosyasına otomatik silme eklenebilir.