二進位日誌簡單介紹 MySQL的二進位日誌（binary log）是一個二進位文件，主要用於記錄修改數據或有可能引起數據變更的MySQL語句。二進位日誌（binary log）中記錄了對MySQL資料庫執行更改的所有操作，並且記錄了語句發生時間、執行時長、操作數據等其它額外信息，但是它不記錄SELE... ...
二進位日誌簡單介紹
MySQL的二進位日誌（binary log）是一個二進位文件，主要用於記錄修改數據或有可能引起數據變更的MySQL語句。二進位日誌（binary log）中記錄了對MySQL資料庫執行更改的所有操作，並且記錄了語句發生時間、執行時長、操作數據等其它額外信息，但是它不記錄SELECT、SHOW等那些不修改數據的SQL語句。二進位日誌（binary log）主要用於資料庫恢復和主從複製，以及審計（audit）操作。
官方文檔關於二進位日誌（binary log）的介紹如下：
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:
· For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
· Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
The binary log is not used for statements such as SELECT or SHOW that do not modify data. To log all statements (for example, to identify a problem query), use the general query log. See Section 5.4.3, “The General Query Log”.
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
二進位日誌狀態查看
系統變數log_bin的值為OFF表示沒有開啟二進位日誌（binary log）。ON表示開啟了二進位日誌（binary log）
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |+---------------+-------+
| log_bin | ON |+---------------+-------+
1 row in set (0.00 sec)
mysql>
查看當前伺服器所有的二進位日誌文件
mysql> show binary logs;+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 478421400 |
| mysql-bin.000005 | 9653 |
| mysql-bin.000006 | 340631484 |
+------------------+-----------+
3 rows in set (0.00 sec)
當然你還可以使用下麵命令查看
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 478421400 |
| mysql-bin.000005 | 9653 |
| mysql-bin.000006 | 340631484 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql>
查看當前二進位日誌文件狀態
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 373655406 | | | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
二進位日誌開啟方法
查看系統變數log_bin，如果其值為OFF，表示沒有開啟二進位日誌（binary log），如果需要開啟二進位日誌，則必須在my.cnf中[mysqld]下麵添加log-bin [=DIR\[filename]] ，DIR參數指定二進位文件的存儲路徑；filename參數指定二級制文件的文件名。 其中filename可以任意指定，但最好有一定規範。系統變數log_bin是靜態參數，不能動態修改的（因為它不是Dynamic Variable）。如下所示：
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |+---------------+-------+
| log_bin | OFF |+---------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin=mysql_bin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>
1：修改my.cnf，在[mysqld]下麵增加log_bin=mysql_bin_log，重啟MySQL後，你就會發現log_bin變為了ON，二進位日誌（binary log）預設放在數據目錄下（系統變數datadir下），如下所示：
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |+---------------+-------+
| log_bin | ON |+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |+---------------------------------+------------------------------------+
| log_bin | ON || log_bin_basename | /var/lib/mysql/mysql_bin_log |
| log_bin_index | /var/lib/mysql/mysql_bin_log.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)
mysql>
2：如果在my.cnf裡面只設置log_bin，但是不指定file_name，然後重啟資料庫。你會發現二進位日誌文件名稱為${hostname}-bin 這樣的格式。如下所示：
[mysqld]
log_bin
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |+---------------------------------+------------------------------------+
| log_bin | ON || log_bin_basename | /var/lib/mysql/DB-Server-bin |
| log_bin_index | /var/lib/mysql/DB-Server-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+------------------------------------+
6 rows in set (0.00 sec)
mysql>
3：當然你可以可以指定二進位日誌的路徑位置，如下所示：
log_bin=/mysql/bin_log/mysql_binlog
二進位日誌切換方法
使用命令flush logs切換二進位日誌，如下所示：
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| DB-Server-bin.000002 | 120 | | | |
+----------------------+----------+--------------+------------------+-------------------+
