MySQL的日志基礎知識及基本操作學習教程

2024-07-24 13:08:12

字體：大中小

來源：轉載

供稿：網友

這篇文章主要介紹了MySQL的日志基礎知識學習教程,包括MySQL中日志相關常用的服務器變量說明,極力推薦!需要的朋友可以參考下

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

查詢日志：

默認情況下查詢日志是關閉的。由于查詢日志會記錄用戶的所有操作，其中還包含增刪查改等信息，在并發操作大的環境下會產生大量的信息從而導致不必要的磁盤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.00sec)

查看當前二進制文件的信息：

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.00sec)

查看二進制日志信息的命令：

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)

PS：MySQL中日志相關常用的服務器變量說明：

expire_logs_days={0..99}

設定二進制日志的過期天數，超出此天數的二進制日志文件將被自動刪除。默認為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日志時。作用范圍為全局，可用于配置文件，屬動態變量。

general_log={ON|OFF}

設定是否啟用查詢日志，默認值為取決于在啟動mysqld時是否使用了--general_log選項。如若啟用此項，其輸出位置則由--log_output選項進行定義，如果log_output的值設定為NONE，即使用啟用查詢日志，其也不會記錄任何日志信息。作用范圍為全局，可用于配置文件，屬動態變量。

general_log_file=FILE_NAME

查詢日志的日志文件名稱，默認為“hostname.log"。作用范圍為全局，可用于配置文件，屬動態變量。

binlog-format={ROW|STATEMENT|MIXED}

指定二進制日志的類型，默認為STATEMENT，建議更改為MIXED。如果設定了二進制日志的格式，卻沒有啟用二進制日志，則MySQL啟動時會產生警告日志信息并記錄于錯誤日志中。作用范圍為全局或會話，可用于配置文件，且屬于動態變量。

log={YES|NO}

是否啟用記錄所有語句的日志信息于一般查詢日志(general query log)中，默認通常為OFF。MySQL 5.6已經棄用此選項。

log-bin={YES|NO}

是否啟用二進制日志，如果為mysqld設定了--log-bin選項，則其值為ON，否則則為OFF。其僅用于顯示是否啟用了二進制日志，并不反應log-bin的設定值。作用范圍為全局級別，屬非動態變量。

log_bin_trust_function_creators={TRUE|FALSE}

此參數僅在啟用二進制日志時有效，用于控制創建存儲函數時如果會導致不安全的事件記錄二進制日志條件下是否禁止創建存儲函數。默認值為0，表示除非用戶除了CREATE ROUTING或ALTER ROUTINE權限外還有SUPER權限，否則將禁止創建或修改存儲函數，同時，還要求在創建函數時必需為之使用DETERMINISTIC屬性，再不然就是附帶READS SQL DATA或NO SQL屬性。設置其值為1時則不啟用這些限制。作用范圍為全局級別，可用于配置文件，屬動態變量。

log_error=/PATH/TO/ERROR_LOG_FILENAME

定義錯誤日志文件。作用范圍為全局或會話級別，可用于配置文件，屬非動態變量。

log_output={TABLE|FILE|NONE}

定義一般查詢日志和慢查詢日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，默認為TABLE。如果組合中出現了NONE，那么其它設定都將失效，同時，無論是否啟用日志功能，也不會記錄任何相關的日志信息。作用范圍為全局級別，可用于配置文件，屬動態變量。

log_query_not_using_indexes={ON|OFF}

設定是否將沒有使用索引的查詢操作記錄到慢查詢日志。作用范圍為全局級別，可用于配置文件，屬動態變量。

log_slave_updates

用于設定復制場景中的從服務器是否將從主服務器收到的更新操作記錄進本機的二進制日志中。本參數設定的生效需要在從服務器上啟用二進制日志功能。

log_slow_queries={YES|NO}

是否記錄慢查詢日志。慢查詢是指查詢的執行時間超出long_query_time參數所設定時長的事件。MySQL 5.6將此參數修改為了slow_query_log。作用范圍為全局級別，可用于配置文件，屬動態變量。

log_warnings=#

設定是否將警告信息記錄進錯誤日志。默認設定為1，表示啟用;可以將其設置為0以禁用;而其值為大于1的數值時表示將新發起連接時產生的“失敗的連接”和“拒絕訪問”類的錯誤信息也記錄進錯誤日志。

long_query_time=#

設定區別慢查詢與一般查詢的語句執行時間長度。這里的語句執行時長為實際的執行時間，而非在CPU上的執行時長，因此，負載較重的服務器上更容易產生慢查詢。其最小值為0，默認值為10，單位是秒鐘。它也支持毫秒級的解析度。作用范圍為全局或會話級別，可用于配置文件，屬動態變量。

max_binlog_cache_size{4096 .. 18446744073709547520}

二進定日志緩存空間大小，5.5.9及以后的版本僅應用于事務緩存，其上限由max_binlog_stmt_cache_size決定。作用范圍為全局級別，可用于配置文件，屬動態變量。

max_binlog_size={4096 .. 1073741824}

設定二進制日志文件上限，單位為字節，最小值為4K，最大值為1G，默認為1G。某事務所產生的日志信息只能寫入一個二進制日志文件，因此，實際上的二進制日志文件可能大于這個指定的上限。作用范圍為全局級別，可用于配置文件，屬動態變量。

max_relay_log_size={4096..1073741824}

設定從服務器上中繼日志的體積上限，到達此限度時其會自動進行中繼日志滾動。此參數值為0時，mysqld將使用max_binlog_size參數同時為二進制日志和中繼日志設定日志文件體積上限。作用范圍為全局級別，可用于配置文件，屬動態變量。

innodb_log_buffer_size={262144 .. 4294967295}

設定InnoDB用于輔助完成日志文件寫操作的日志緩沖區大小，單位是字節，默認為8MB。較大的事務可以借助于更大的日志緩沖區來避免在事務完成之前將日志緩沖區的數據寫入日志文件，以減少I/O操作進而提升系統性能。因此，在有著較大事務的應用場景中，建議為此變量設定一個更大的值。作用范圍為全局級別，可用于選項文件，屬非動態變量。

innodb_log_file_size={108576 .. 4294967295}

設定日志組中每個日志文件的大小，單位是字節，默認值是5MB。較為明智的取值范圍是從1MB到緩存池體積的1/n，其中n表示日志組中日志文件的個數。日志文件越大，在緩存池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度。作用范圍為全局級別，可用于選項文件，屬非動態變量。

innodb_log_files_in_group={2 .. 100}

設定日志組中日志文件的個數。InnoDB以循環的方式使用這些日志文件。默認值為2。作用范圍為全局級別，可用于選項文件，屬非動態變量。

innodb_log_group_home_dir=/PATH/TO/DIR

設定InnoDB重做日志文件的存儲目錄。在缺省使用InnoDB日志相關的所有變量時，其默認會在數據目錄中創建兩個大小為5MB的名為ib_logfile0和ib_logfile1的日志文件。作用范圍為全局級別，可用于選項文件，屬非動態變量。

innodb_support_xa={TRUE|FLASE}

存儲引擎事務在存儲引擎內部被賦予了ACID屬性，分布式(XA)事務是一種高層次的事務，它利用“準備”然后“提交”(prepare-then-commit)兩段式的方式將ACID屬性擴展到存儲引擎外部，甚至是數據庫外部。然而，“準備”階段會導致額外的磁盤刷寫操作。XA需要事務協調員，它會通知所有的參與者準備提交事務(階段1)。當協調員從所有參與者那里收到“就緒”信息時，它會指示所有參與者進行真正的“提交”操作。

此變量正是用于定義InnoDB是否支持兩段式提交的分布式事務，默認為啟用。事實上，所有啟用了二進制日志的并支持多個線程同時向二進制日志寫入數據的MySQL服務器都需要啟用分布式事務，否則，多個線程對二進制日志的寫入操作可能會以與原始次序不同的方式完成，這將會在基于二進制日志的恢復操作中或者是從服務器上創建出不同原始數據的結果。因此，除了僅有一個線程可以改變數據以外的其它應用場景都不應該禁用此功能。而在僅有一個線程可以修改數據的應用中，禁用此功能是安全的并可以提升InnoDB表的性能。作用范圍為全局和會話級別，可用于選項文件，屬動態變量。

relay_log=file_name

設定中繼日志的文件名稱，默認為host_name-relay-bin。也可以使用絕對路徑，以指定非數據目錄來存儲中繼日志。作用范圍為全局級別，可用于選項文件，屬非動態變量。

relay_log_index=file_name

設定中繼日志的索引文件名，默認為為數據目錄中的host_name-relay-bin.index。作用范圍為全局級別，可用于選項文件，屬非動態變量。

relay-log-info-file=file_name

設定中繼服務用于記錄中繼信息的文件，默認為數據目錄中的relay-log.info。作用范圍為全局級別，可用于選項文件，屬非動態變量。

relay_log_purge={ON|OFF}

設定對不再需要的中繼日志是否自動進行清理。默認值為ON。作用范圍為全局級別，可用于選項文件，屬動態變量。

relay_log_space_limit=#

設定用于存儲所有中繼日志文件的可用空間大小。默認為0，表示不限定。最大值取決于系統平臺位數。作用范圍為全局級別，可用于選項文件，屬非動態變量。

slow_query_log={ON|OFF}

設定是否啟用慢查詢日志。0或OFF表示禁用，1或ON表示啟用。日志信息的輸出位置取決于log_output變量的定義，如果其值為NONE，則即便slow_query_log為ON，也不會記錄任何慢查詢信息。作用范圍為全局級別，可用于選項文件，屬動態變量。