MySQL中的幾類日誌檔案介紹
關於Mysql日誌
splunk內建了兩種mysql的日誌，分別是mysqld以及mysql_error
mysqld_error
Standard mysql error log標準的mysql錯誤日誌
050818 16:19:29 InnoDB: Started; log sequence number 0 43644 /usr/libexec/mysqld: ready for connections. Version: '4.1.10a-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
mysqld
Standard MySQL query log; also matches the MySQL binary log following conversion to text標準的mysql查詢日誌；也能匹配Mysql二進位制日誌
53 Query SELECT xar_dd_itemid, xar_dd_propid, xar_dd_value FROM xar_dynamic_data WHERE xar_dd_propid IN (27) AND xar_dd_itemid = 2
Mysql日誌的配置
MySQL有以下幾種日誌：
- 錯誤日誌： -log-err
- 查詢日誌： -log
- 慢查詢日誌: -log-slow-queries
- 更新日誌: -log-update
- 二進位制日誌： -log-bin
預設情況下，所有日誌建立於mysqld資料目錄中。通過重新整理日誌，你可以強制 mysqld來關閉和重新開啟日誌檔案（或者在某些情況下切換到一個新的日誌）。當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，出現日誌重新整理
1. 錯誤日誌
用--log-error[=file_name]選項來指定mysqld儲存錯誤日誌檔案的位置。如果沒有給定file_name值，mysqld使用錯誤日誌名 host_name.err並在資料目錄中寫入日誌檔案。如果你執行FLUSH LOGS，錯誤日誌用-old重新命名字尾並且mysqld建立一個新的空日誌檔案。(如果未給出--log-error選項，則不會重新命名）。
如果不指定--log-error，或者(在Windows中)如果你使用--console選項，錯誤被寫入標準錯誤輸出stderr。通常標準輸出為你的終端。
2. 通用查詢日誌
用--log[=file_name]或-l [file_name]選項啟動它。如果沒有給定file_name的值，預設名是host_name.log。
3. 慢速查詢日誌
用--log-slow-queries[=file_name]選項啟動時，mysqld 寫一個包含所有執行時間超過long_query_time秒的SQL語句的日誌檔案.如果沒有給出file_name值，預設未主機名，字尾為 -slow.log。如果給出了檔名，但不是絕對路徑名，檔案則寫入資料目錄。
4. 更新日誌
用--log-update[=file_name]選項啟動,不推薦使用.
常用命令
是否啟用了日誌
mysql>show variables like 'log_%';
怎樣知道當前的日誌
mysql> show master status;
顯示二進位制日誌數目
mysql> show master logs;
看二進位制日誌檔案用mysqlbinlog
shell>mysqlbinlog mail-bin.000001
或者shell>mysqlbinlog mail-bin.000001 | tail
在配置檔案中指定log的輸出位置.
Windows：Windows 的配置檔案為 my.ini，一般在 MySQL 的安裝目錄下或者 c:\Windows 下。
Linux：Linux 的配置檔案為 my.cnf ，一般在 /etc 下。
在linux下：
# 在[mysqld] 中輸入 #log log-error=/usr/local/mysql/log/error.log log=/usr/local/mysql/log/mysql.log
long_query_time=2 log-slow-queries= /usr/local/mysql/log/slowquery.log
windows下:
# 在[mysqld] 中輸入 #log log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" long_query_time=2 log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"
開啟慢查詢
long_query_time =2 --是指執行超過多久的sql會被log下來，這裡是2秒 log-slow-queries= /usr/local/mysql/log/slowquery.log --將查詢返回較慢的語句進行記錄 log-queries-not-using-indexes = nouseindex.log --就是字面意思，log下來沒有使用索引的query log=mylog.log --對所有執行語句進行記錄
windows下開啟mysql日誌：
在[mysql]下加入這些（基本上等於加在最後面）：
log-error= #Enter a name for the query log file. Otherwise a default name will be used. #注:（寫成txt檔案editplus可以及時過載,不過有時要放在C盤下editplus才可以及時過載） log= c:/mysql_query.log.txt #Enter a name for the slow query log file. Otherwise a default name will be used. log-slow-queries= #Enter a name for the update log file. Otherwise a default name will be used. log-update= #Enter a name for the binary log. Otherwise a default name will be used. log-bin=
日誌解析
MySQL日誌：
主要包含：錯誤日誌、查詢日誌、慢查詢日誌、事務日誌、二進位制日誌；日誌是mysql資料庫的重要組成部分。日誌檔案中記錄著mysql資料庫執行 期間發生的變化；也就是說用來記錄mysql資料庫的客戶端連線狀況、SQL語句的執行情況和錯誤資訊等。當資料庫遭到意外的損壞時，可以通過日誌檢視文 件出錯的原因，並且可以通過日誌檔案進行資料恢復。
錯誤日誌
在mysql資料庫中，錯誤日誌功能是預設開啟的。並且，錯誤日誌無法被禁止。預設情況下，錯誤日誌儲存在mysql資料庫的資料檔案中。錯誤日誌檔案通常的名稱為hostname.err。其中，hostname表示伺服器主機名。
錯誤日誌資訊可以自己進行配置的，錯誤日誌所記錄的資訊是可以通過log-error和log-warnings來定義的，其中log-err是定義是否 啟用錯誤日誌的功能和錯誤日誌的儲存位置，log-warnings是定義是否將警告資訊也定義至錯誤日誌中。預設情況下錯誤日誌大概記錄以下幾個方面的 資訊：伺服器啟動和關閉過程中的資訊（未必是錯誤資訊，如mysql如何啟動InnoDB的表空間檔案的、如何初始化自己的儲存引擎的等等）、伺服器執行 過程中的錯誤資訊、事件排程器執行一個事件時產生的資訊、在從伺服器上啟動伺服器程式時產生的資訊。
下面我們來定義mysql錯誤日誌的功能：
一般而言，日誌級別的定義沒有回話變數都只是在全域性級別下進行定義。
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /mydata/data/stu18.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /mydata/data/stu18.magedu.com.err |定義錯誤日誌
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 是否把警告資訊寫入錯誤日誌中
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /mydata/data/stu18-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+-----------------------------------+
其中，log_error可以直接定義為檔案路徑，也可以為ON|OFF；log_warings只能使用1|0來定義開關啟動。
更改錯誤日誌位置可以使用log_error來設定形式如下：
[root@stu18 data]# vim /etc/my.cnf [mysqld] Log_error=DIR/[filename]
解析：其中，DIR引數指定錯誤日誌的路徑filename引數是錯誤日誌的名稱，沒有指定該引數時預設為主機名。重啟mysql伺服器即可生效。
檢視mysql錯誤日誌：
[root@stu18 data]# tail -20 stu18.magedu.com.err 130813 15:30:50 InnoDB: Starting shutdown... 130813 15:30:51 InnoDB: Shutdown completed; log sequence number 1630920 130813 15:30:51 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 130813 15:30:52 mysqld_safe mysqld from pid file /mydata/data/stu18.magedu.com.pid ended 130813 15:30:53 mysqld_safe Starting mysqld daemon with databases from /mydata/data 130813 15:30:54 InnoDB: The InnoDB memory heap is disabled #禁用了InnoDB memory的堆功能。 130813 15:30:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins #Mutexes（互斥量）和rw_locks（行級鎖）是GCC編譯的是InnoDB內建的。 130813 15:30:54 InnoDB: Compressed tables use zlib 1.2.3 #預設壓縮工具是zlib 130813 15:30:55 InnoDB: Initializing buffer pool, size = 128.0M #InnoDB引擎的緩衝池（buffer pool）的值大小 130813 15:30:55 InnoDB: Completed initialization of buffer pool 130813 15:30:55 InnoDB: highest supported file format is Barracuda. 130813 15:30:57 InnoDB: Waiting for the background threads to start 130813 15:30:58 InnoDB: 5.5.33 started; log sequence number 1630920 130813 15:30:58 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130813 15:30:58 [Note] - '0.0.0.0' resolves to '0.0.0.0'; #0.0.0.0會反解主機名，這裡反解失敗 130813 15:30:58 [Note] Server socket created on IP: '0.0.0.0'. 130813 15:30:58 [Note] Event Scheduler: Loaded 0 events #事件排程器沒有任何事件，因為沒有裝載。 130813 15:30:58 [Note] /usr/local/mysql/bin/mysqld: ready for connections. #mysql啟動完成等待客戶端的請求。
Version: '5.5.33-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution #建立一個本地sock用於本地連線。
刪除錯誤日誌：
在mysql5.5.7之前：資料庫管理員可以刪除很長時間之前的錯誤日誌，以保證mysql伺服器上的硬碟空間。mysql資料庫中，可以使用 mysqladmin命令開啟新的錯誤日誌。mysqladmin命令的語法如下：mysqladmin –u root –pflush-logs也可以使用登入mysql資料庫中使用FLUSHLOGS語句來開啟新的錯誤日誌。
在mysql5.5.7之後：伺服器將關閉此項功能。只能使用重新命名原來的錯誤日誌檔案，手動沖洗日誌建立一個新的：方式如下：
[root@stu18 data]# mv stu18.magedu.com.err stu18.magedu.com.err.old [root@stu18 data]# mysqladmin flush-logs [root@stu18 data]# ls hellodb myclass mysql-bin.000003 mysql-bin.index stu18.magedu.com.pid ibdata1 mysql mysql-bin.000004 performance_schema ib_logfile0 mysql-bin.000001 stu18.magedu.com.err test ib_logfile1 mysql-bin.000002 stu18.magedu.com.err.old
更多資訊請查閱官方文件：http://dev.mysql.com/doc/refman/5.5/en/error-log.html
查詢日誌：
預設情況下查詢日誌是關閉的。由於查詢日誌會記錄使用者的所有操作，其中還包含增刪查改等資訊，在併發操作大的環境下會產生大量的資訊從而導致不必要的磁碟IO，會影響mysql的效能的。如若不是為了除錯資料庫的目的建議不要開啟查詢日誌。
檢視查詢日誌是否開啟：
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF #定義查詢日誌是否開啟 |
| general_log_file | /mydata/data/stu18.log #定義查詢日誌的檔案地址名稱 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF #是否開啟日誌 （若開啟則表示開啟所有的日誌功能） |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /mydata/data/stu18.magedu.com.err |
| log_output | FILE #日誌的輸出的位置 |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /mydata/data/stu18-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+-----------------------------------+
41 rows in set (0.00 sec)
擴充解析：日誌的輸出位置一般有三種方式：file(檔案)，table(表)，none(不儲存)；其中前兩個輸出位置可以同時定義，none表 示是開啟日誌功能但是記錄日誌資訊。file就是通過general_log_file |/mydata/data/stu18.log 等方式定義的，而輸出位置定義為表時檢視日誌的內容方式為：
mysql> use mysql; #在此資料庫中 Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log | #這個就是查詢日誌的表輸出位置
| help_category |
| help_keyword |
| help_relation |
| help_topic
……………… |
+---------------------------+
慢查詢日誌
慢查詢日誌是用來記錄執行時間超過指定時間的查詢語句。通過慢查詢日誌，可以查詢出哪些查詢語句的執行效率很低，以便進行優化。一般建議開啟，它對伺服器效能的影響微乎其微，但是可以記錄mysql伺服器上執行了很長時間的查詢語句。可以幫助我們定位效能問題的。
檢視慢查詢日誌的定義：
mysql> SHOW GLOBAL VARIABLES LIKE '%log%';
| slow_query_log | OFF #定義慢查詢日誌的
| slow_query_log_file |/mydata/data/stu18-slow.log #輸出方式為file（檔案）時定義慢查詢日誌的位置
啟動和設定慢查詢日誌：
1、通過配置檔案my.cnf中的log-slow-queries選項可以開啟慢查詢日誌；形式如下：
[root@stu18 data]# vim /etc/my.cnf
[mysqld]
slow_query_log=1
log-slow-queries [= DIR/[filename] ]
其中，DIR引數指定慢查詢日誌的儲存路徑；filename引數指定日誌的檔名，生成日誌檔案的完成名稱為filename- slow.log。如果不指定儲存路徑，慢查詢日誌預設儲存到mysql資料庫的資料檔案下，如果不指定檔名，預設檔名為hostname- slow.log。
2、通過登入mysql伺服器直接定義，方式如下：
首先要有全域性許可權；然後執行mysql>set global slow_query_log=1;
時間預設超過多少的稱為慢查詢日誌？
一般都是通過long_query_time選項來設定這個時間值，時間以秒為單位，可以精確到微秒。如果查詢時間超過了這個時間值（預設為10秒），這個查詢語句將被記錄到慢查詢日誌中。檢視伺服器預設時間值方式如下：
mysql> SHOW GLOBAL VARIABLES LIKE 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+ 1 row in set (0.04 sec)
註釋：其中這個慢查詢時間並不是只表示語句自身執行超過10秒還包含由於其他資源被徵用造成阻塞的查詢執行時間或其他原因等都被記錄到慢查詢中。所以這個慢查的時長表示從查詢開始到查詢結束中間包含可能的任何原因所經歷的所有時間。
測試是否可以記錄日誌：
mysql> set globalslow_query_log=1; #開啟慢查詢日誌
Query OK, 0 rowsaffected (0.35 sec)
mysql> setsession long_query_time=0.001; #更改時間（當前session中，退出則重置）
Query OK, 0 rowsaffected (0.00 sec)
mysql> set globallong_query_time=0.001; #更改時間（全域性中，重啟服務則重置）
mysql> SHOWVARIABLES LIKE 'long%'; #查詢定義時間
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time |0.001000 |
+-----------------+----------+
1 row in set (0.00sec)
mysql> showglobal variables like "%slow%"; #檢視慢查詢日誌開啟狀態
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
|log_slow_queries | ON |
|slow_launch_time | 2 |
|slow_query_log | ON |
|slow_query_log_file | /mydata/data/stu18-slow.log |
+---------------------+-----------------------------+
4 rows in set (0.03sec)
檢視慢查詢日誌：
mysql> use mysql
mysql> selectuser,host,password from user where user="root";
+------+------------------+----------+
| user | host | password |
+------+------------------+----------+
| root |localhost | |
| root |stu18.magedu.com | |
| root |127.0.0.1 | |
| root | ::1 | |
+------+------------------+----------+ 4 rows in set (0.08sec) #查詢時間為0.08
mysql> systemmore /mydata/data/stu18_slow.log #查詢慢查詢日誌記錄資訊
/usr/local/mysql/bin/mysqld,Version: 5.5.33-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
>>>>>>>>>>>>>>>>部分已省略>>>>>>>>>>>>>> # Time: 13100723:46:33 # User@Host:root[root] @ localhost [] # Query_time:0.108459 Lock_time: 0.000216 Rows_sent:4 Rows_examined: 6
SETtimestamp=1381160793;
selectuser,host,password from user where user="root";
事務日誌
事務日誌（InnoDB特有的日誌）可以幫助提高事務的效率。使用事務日誌，儲存引擎在修改表的資料時只需要修改其記憶體拷貝，再把改修改行為記錄到 持久在硬碟上的事務日誌中，而不用每次都將修改的資料本身持久到磁碟。事務日誌採用追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序I/O，而不 像隨機I/O需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快得多。事務日誌持久以後，記憶體中被修改的資料在後臺可以慢慢的刷回到磁 盤。目前大多數的儲存引擎都是這樣實現的，我們通常稱之為預寫式日誌，修改資料需要寫兩次磁碟。
如果資料的修改已經記錄到事務日誌並持久化，但資料本身還沒有寫回磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動恢復這部分修改的資料。具有的恢復方式則視儲存引擎而定。
檢視事務日誌的定義：
mysql> SHOWGLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+-----------------------------------+
| Variable_name | Value |
+-----------------------------------------+-----------------------------------+
| innodb_flush_log_at_trx_commit | 1 #在事務提交時innodb是否同步日誌從緩衝到檔案中1表示事務以提交就同步不提交每隔一秒同步一次，效能會很差造成大量的磁碟I/O；定義為2表示只有在事務提交時才會同步但是可能會丟失整個事務 |
|innodb_locks_unsafe_for_binlog |OFF |
| innodb_log_buffer_size | 8388608 |
|innodb_log_file_size |5242880 |
| innodb_log_files_in_group | 2 #至少有兩個 |
|innodb_log_group_home_dir |./ #定義innodb事務日誌組的位置 |
|innodb_mirrored_log_groups |1 #表示對日誌組做映象 |
每個事務日誌都是大小為5兆的檔案：
[root@stu18 data]#ls -lh -rw-rw---- 1 mysqlmysql 5.0M Oct 7 23:36 ib_logfile0 -rw-rw---- 1 mysqlmysql 5.0M Aug 12 01:06 ib_logfile1
二進位制日誌
二進位制日誌也叫作變更日誌，主要用於記錄修改資料或有可能引起資料改變的mysql語句，並且記錄了語句發生時間、執行時長、操作的資料等等。所以說通過二進位制日誌可以查詢mysql資料庫中進行了哪些變化。一般大小體積上限為1G。
二進位制開啟狀態：
mysql> showglobal variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON | #已開啟
|log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
二進位制日誌相關的引數：
mysql> showglobal variables like "%log%";
-
sql_log_bin ={ON|OFF}
用於控制會話級別二進位制日誌功能的開啟或關閉。預設為ON，表示啟用記錄功能。使用者可以在會話級別修改此變數的值，但其必須具有SUPER許可權。
-
binlog_cache_size =32768
預設值32768 Binlog Cache用於在開啟了二進位制日誌（binlog）記錄功能的環境，是MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時快取binlog資料的記憶體區域。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是 特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我 們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大 小不夠而使用臨時檔案（binlog_cache_disk_use）來快取了。
-
binlog_stmt_cache_size= 32768
當非事務語句使用二進位制日誌快取，但是超出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句。
-
log_bin = mysql-bin
指定binlog的位置，預設在資料目錄下。
-
binlog-format= {ROW|STATEMENT|MIXED}
指定二進位制日誌的型別，預設為MIXED。如果設定了二進位制日誌的格式，卻沒有啟用二進位制日誌，則MySQL啟動時會產生警告日誌資訊並記錄於錯誤日誌中。
-
sync_binlog = 10
設定多久同步一次二進位制日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位制每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位制日誌同步，否則，每個事務的提交會引起二進位制日誌同步
-
max_binlog_cache_size= {4096 .. 18446744073709547520}
二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取，其上限由max_binlog_stmt_cache_size決定。
-
max_binlog_stmt_cache_size= {4096 .. 18446744073709547520}
二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取
-
expire_log_days ={0..99}
設定二進位制日誌的過期天數，超出此天數的二進位制日誌檔案將被自動刪除。預設為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時。
二進位制日誌定義方式：
其一、log_bin可以直接定義為檔案路徑，也可以為ON|OFF。
其二、通過編輯my.cnf中的log-bin選項可以開啟二進位制日誌；形式如下：
[root@stu18 ~]#my.cnf [mysqld] log-bin [=DIR \ [filename]]
其中，DIR引數指定二進位制檔案的儲存路徑；filename引數指定二級制檔案的檔名，其形式為filename.number，number 的形式為000001、000002等。每次重啟mysql服務或執行mysql> flush logs;都會生成一個新的二進位制日誌檔案，這些日誌檔案的number會不斷地遞增。除了生成上述的檔案外還會生成一個名為 filename.index的檔案。這個檔案中儲存所有二進位制日誌檔案的清單又稱為二進位制檔案的索引。
[root@stu18 ~]# cd /mydata/data/
[root@stu18 data]#ls -lh
-rw-rw---- 1 mysqlmysql 14K Aug 13 15:30 mysql-bin.000001
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:05 mysql-bin.000002
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:06 mysql-bin.000003
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:07 mysql-bin.000004
-rw-rw---- 1 mysqlmysql 150 Aug 13 17:39 mysql-bin.000005
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000006
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000007
-rw-rw---- 1 mysqlmysql 126 Aug 13 19:05 mysql-bin.000008
-rw-rw---- 1 mysqlmysql 107 Aug 13 19:05 mysql-bin.000009
-rw-rw---- 1 mysqlmysql 353 Oct 7 23:40 mysql-bin.000010
-rw-rw---- 1 mysqlmysql 190 Oct 7 20:43 mysql-bin.index
[root@stu18 data]#cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
如果說我們向某個表的某個欄位插入一個資料而這個資料為當前時間(日期時間型)；過段時間將此二進位制檔案應用到另一臺伺服器上資料就會變動從而導致資料的不一致性所以說對於這種非確定性的資料使用預設的語句定義並不是可靠的；
二進位制日誌中常用的定義格式：
- 1、語句(statement)：預設的記錄格式；
- 2、行(row)：定義的並非資料本身而是這一行的資料是什麼；
- 3、混合模式(mixed)：交替使用行和語句、由mysql伺服器自行判斷。
其中基於行的定義格式資料量會大一些但是可以保證資料的精確性。
檢視二進位制日誌：
二進位制日誌的定義方式為二進位制格式；使用此格式可以儲存更多的資訊，並且可以使寫入二進位制日誌的效率更高。但是不能直接使用檢視命令開啟並檢視二進位制日誌。
mysql> showbinary logs; #顯示當前伺服器使用的二進位制檔案及大小
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001| 13814 |
| mysql-bin.000002| 150 |
| mysql-bin.000003| 150 |
| mysql-bin.000004| 150 |
| mysql-bin.000005| 150 |
| mysql-bin.000006| 126 |
| mysql-bin.000007| 126 |
| mysql-bin.000008| 126 |
| mysql-bin.000009| 107 |
| mysql-bin.000010| 353 |
+------------------+-----------+ 10 rows in set (0.07sec)
mysql> showmaster logs; #顯示主伺服器使用的二進位制檔案及大小
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001| 13814 |
| mysql-bin.000002| 150 |
| mysql-bin.000003| 150 |
| mysql-bin.000004| 150 |
| mysql-bin.000005| 150 |
| mysql-bin.000006| 126 |
| mysql-bin.000007| 126 |
| mysql-bin.000008| 126 |
| mysql-bin.000009| 107 |
| mysql-bin.000010| 353 |
+------------------+-----------+ 10 rows in set (0.02sec)
mysql> showmaster status; #當前使用的二進位制檔案及所處位置
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010| 353 | | |
+------------------+----------+--------------+------------------+ 1 row in set (0.00sec)
小擴充套件：二進位制日誌的記錄位置：通常為上一個事件執行結束時間的位置，每一個日誌檔案本身也有自己的後設資料所以說對於當前版本的mysql來說二進位制的開始位置通常為107；
mysql> flushlogs;
Query OK, 0 rowsaffected (0.23 sec)
注意：flush logs一般只會滾動中繼日誌和二進位制日誌。
mysql> showmaster status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011| 107 | | |
+------------------+----------+--------------+------------------+ 1 row in set (0.0sec)
檢視當前二進位制檔案的資訊：
mysql> createdatabase yong;
Query OK, 1 rowaffected (0.12 sec)
mysql> createtable yong.tb1 (id int,name char(20));
Query OK, 0 rowsaffected (0.44 sec)
mysql> insertinto yong.tb1 values(1,'tom');
Query OK, 1 rowaffected (0.14 sec)
mysql> showmaster status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011| 479 | | |
+------------------+----------+--------------+------------------+ 1 row in set (0.0sec)
檢視二進位制日誌資訊的命令：
SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysql> showbinlog events\G #檢視所有的二進位制資訊 ***************************87. row ***************************
Log_name: mysql-bin.000001 Pos: 13580
Event_type: Query
Server_id: 1
End_log_pos: 13688 Info: use `hellodb`; /*!40000 ALTERTABLE `toc` DISABLE KEYS */ ***************************88. row ***************************
Log_name: mysql-bin.000001 Pos: 13688
Event_type: Query
Server_id: 1
End_log_pos: 13795 Info: use `hellodb`; /*!40000 ALTERTABLE `toc` ENABLE KEYS */ ***************************89. row ***************************
Log_name: mysql-bin.000001 Pos: 13795
Event_type: Stop
Server_id: 1
End_log_pos: 13814 Info:
89 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011'; #檢視指定日誌的二進位制資訊
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011| 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlogver: 4 |
| mysql-bin.000011 |107 | Query | 1 | 190 | create database yong |
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,'tom') |
| mysql-bin.000011 |452 | Xid | 1 | 479 | COMMIT /* xid=103 */ |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
6 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011' from 190; #從指定的事件位置開始
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name | Pos | Event_type | Server_id |End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,'tom') |
| mysql-bin.000011 |452 | Xid | 1 | 479 | COMMIT /* xid=103 */ |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
4 rows in set (0.00sec)
mysql> showbinlog events in 'mysql-bin.000011' from 190 limit 3; #指定偏移量(不是語句，是事件)
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name | Pos | Event_type | Server_id |End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000011 |190 | Query | 1 | 293 | create table yong.tb1 (idint,name char(20)) |
| mysql-bin.000011 |293 | Query | 1 | 357 | BEGIN |
| mysql-bin.000011 |357 | Query | 1 | 452 | insert into yong.tb1values(1,'tom') |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
3 rows in set (0.00sec)
命令列下檢視二進位制日誌：
由於無法使用cat等方式直接開啟並檢視二進位制日誌；所以必須使用mysqlbinlog命令。但是當正在執行mysql讀寫操作時建議不要使用此開啟正在使用的二進位制日誌檔案；若非要開啟可flush logs。mysqlbinlog命令的使用方式：
[root@stu18 data]#mysqlbinlog mysql-bin.000017 #必須在資料目錄下 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET@@session.max_insert_delayed_threads=0*/; /*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4 #事件開始處
#131009 0:25:59 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.33-log created 131009 0:25:59
# Warning: thisbinlog is either in use or was not closed properly. BINLOG '
FzJUUg8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#131009 0:26:36 server id 1 end_log_pos 192 Query thread_id=12 exec_time=0 error_code=0 #131009 0:26:36年月日的簡寫方式；end_log_pos事件結束處；thread_id=12 哪個會話執行緒建立的此語句；exec_time=0 執行時長單位為秒；error_code=0 錯誤程式碼0表示沒有 SET TIMESTAMP=1381249596/*!*/; #預設資訊(環境設定)
匯出此資料庫的資訊：
[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql
匯入此資料庫的資訊：
[root@stu18 data]#mysql < a.sql
刪除二進位制日誌資訊：
二進位制日誌會記錄大量的資訊（其中包含一些無用的資訊）。如果很長時間不清理二進位制日誌，將會浪費很多的磁碟空間。但是，刪除之後可能導致資料庫崩潰時無 法進行恢復，所以若要刪除二進位制日誌首先將其和資料庫備份一份，其中也只能刪除備份前的二進位制日誌，新產生的日誌資訊不可刪(可以做即時點還原)。也不可 在關閉mysql伺服器之後直接刪除因為這樣可能會給資料庫帶來錯誤的。若非要刪除二進位制日誌需要做如下操作：匯出備份資料庫和二進位制日誌檔案進行壓縮歸 檔儲存。刪除二進位制檔案的方法如下：
1、刪除所有的二進位制日誌（不可效仿）：
使用RESET MASTER語句可以刪除所有的二進位制日誌。該語句的形式如下：
mysql> resetmaster;
Query OK, 0 rowsaffected (0.17 sec)
mysql> showbinary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001| 107 |
+------------------+-----------+ 1 row in set (0.04sec)
解析：首先不建議在生產環境下使用此操作；刪除所有的二進位制日誌後，Mysql將會重新建立新的二進位制日誌。新二進位制日誌的編號從000001開始。
2、根據檔案或時間點來刪除二進位制日誌：
語法形式：
mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }
其中TO'log_name'表示把這個檔案之前的其他檔案都刪除掉，也可使用BEFORE datetime_expr指定把哪個時間之前的二進位制檔案刪除了。
mysql> PURGEBINARY LOGS TO 'mysql-bin.000007'; Query OK, 0 rowsaffected (0.11 sec) mysql> showbinary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000007| 150 |
| mysql-bin.000008| 150 |
| mysql-bin.000009| 150 |
| mysql-bin.000010| 150 |
| mysql-bin.000011| 150 |
| mysql-bin.000012| 150 |
| mysql-bin.000013| 150 |
| mysql-bin.000014| 150 |
| mysql-bin.000015| 150 |
| mysql-bin.000016| 150 |
| mysql-bin.000017| 483 |
+------------------+-----------+ 11 rows in set (0.04sec)
[root@stu18 data]#cat mysql-bin.index
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
由此可以看出這種清理二進位制日誌檔案的方式是非常合理的，不會導致資料庫的錯誤發生。
mysql> PURGEBINARY LOGS BEFORE '13-10-19 10:26:36'; #使用時間來刪除二進位制日誌
Query OK, 0 rowsaffected (0.05 sec)
到此關於二進位制的知識就解析完了，其中若有錯誤不足之處請指出！謝謝！注意二進位制日誌和錯誤日誌很重要重
About Me
.............................................................................................................................................
● 本文整理自網路
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
.............................................................................................................................................
● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。
小麥苗的微信公眾號 小麥苗的QQ群 小麥苗的微店
.............................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2142929/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 探究MySQL中的日誌檔案MySql
- MySQL資料庫中的日誌檔案---(4)配置日誌檔案輸出路徑MySql資料庫
- MySQL資料庫中的日誌檔案---(1)錯誤日誌MySql資料庫
- 【MySQL日誌】MySQL日誌檔案初級管理MySql
- MySQL資料庫中的日誌檔案---(3)慢查詢日誌MySql資料庫
- MySQL資料庫中的日誌檔案---(2)普通查詢日誌MySql資料庫
- MySQL中幾種常見的日誌MySql
- mysql 資料儲存檔案及6類日誌MySql
- mysql的日誌檔案詳解MySql
- MySQL中的日誌檔案 你全都瞭解嗎？MySql
- mysql配置檔案中mysqld_safe介紹MySql
- MySQL InnoDB日誌檔案配置MySql
- MySQL 日誌檔案 說明MySql
- mysql二進位制日誌格式介紹MySql
- 告警日誌介紹
- MySQL資料庫中的日誌檔案---(5)系統日誌表的特點MySql資料庫
- mysql二進位制日誌的引數介紹MySql
- 簡單介紹MySQL列印死鎖日誌的方法MySql
- 重做日誌檔案中的SCN
- 重新整理 mysql 基礎篇————— 介紹mysql日誌[二]MySql
- jclouds日誌工具介紹Cloud
- mysql 主從日誌檔案mysql-bin檔案清除方法MySql
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- 介紹幾個好用的工具類
- Linux系統日誌分為哪幾種?日誌檔案包括幾列內容?Linux
- 【Oracle日誌】- 日誌檔案重建Oracle
- 日誌檔案
- js中關於定義類的幾種方式介紹JS
- 清除SQL Server日誌的方法介紹SQLServer
- 日誌檔案的管理
- 日誌檔案的大小
- MySQL技術內幕之“日誌檔案”MySql
- oracle日誌分析從列表中移去一個日誌檔案Oracle
- 對日誌檔案組和日誌檔案組成員的管理
- oracle中移動控制檔案、資料檔案、日誌檔案Oracle
- 幾個重要的RedHat Linux核心檔案介紹(轉)RedhatLinux
- 刪除日誌檔案組與日誌檔案成員
- 日誌收集之filebeat使用介紹