- 我的MYSQL學習心得（1） ：簡單語法
- 我的MYSQL學習心得（2） ：資料型別寬度
- 我的MYSQL學習心得（3） ： 檢視欄位長度
- 我的MYSQL學習心得（4） ： 資料型別
- 我的MYSQL學習心得（5） ： 運算子
- 我的MYSQL學習心得（6） ： 函式
- 我的MYSQL學習心得（7） ： 查詢
- 我的MYSQL學習心得（8） ： 插入 更新 刪除
- 我的MYSQL學習心得（9） ： 索引
- 我的MYSQL學習心得（10） ： 自定義儲存過程和函式
- 我的MYSQL學習心得（11） ： 檢視
- 我的MYSQL學習心得（12） ： 觸發器
- 我的MYSQL學習心得（13） ： 許可權管理
- 我的MYSQL學習心得（14） ： 備份和恢復
這一篇《我的MYSQL學習心得（十五）》將會講解MYSQL的日誌
MYSQL裡的日誌主要分為4類，使用這些日誌檔案，可以檢視MYSQL內部發生的事情。
分別是
1、錯誤日誌：記錄mysql服務的啟動、執行、停止mysql服務時出現的問題
2、查詢日誌：記錄建立的客戶端連線和執行的語句
3、二進位制日誌：記錄所有更改資料的語句，可以用於資料複製
4、慢查詢日誌：記錄所有執行時間超過long_query_time的所有查詢或不使用索引的查詢
預設情況下，所有日誌建立於mysql資料目錄中。通過重新整理日誌，可以強制mysql關閉和重新開啟日誌檔案（或者在某些情況下切換到
一個新的日誌）。當執行一個FLUSH LOGS語句或執行mysqladmin flush-logs 或mysqladmin refresh 時，將重新整理日誌
如果使用mysql複製功能，在複製伺服器上可以維護更多日誌檔案，這種日誌稱為接替日誌
其他日誌功能會降低mysql資料庫的效能。例如，在查詢非常頻繁的mysql資料庫系統中，如果開啟了通用查詢日誌和慢查詢日誌，
mysql資料庫會花費很多時間記錄日誌。同時，日誌會佔用大量的磁碟空間
二進位制日誌
二進位制日誌就是我們經常說的binlog，主要記錄mysql資料庫的變化。
二進位制日誌以一種有效的格式，並且是事務安全的方式包含更新日誌中可用的所有資訊。
二進位制日誌包含關於每個更新資料庫的語句的執行時間資訊。他不包含沒有修改任何資料的語句，例如select語句
使用二進位制日誌的最大目的是最大可能地恢復資料庫，因為二進位制日誌包含備份後進行的所有更新
1、啟動和設定二進位制日誌
預設情況下，二進位制日誌是關閉的，可以通過修改mysql的配置檔案來啟動和設定二進位制日誌
my.ini中[mysqld]組下面有幾個設定是關於二進位制日誌的：
|
1 2 3 |
log-bin[=PATH/[FILENAME]] expire_logs_days=10 max_binlog_size=100M |
log-bin定義開啟二進位制日誌；path表明日誌檔案所在的目錄路徑；
filename指定了日誌檔案的名稱，如檔案的全名是filename.0001，filename.0002等
除了上述檔案之外，還有一個成為filename.index的檔案，檔案內容為所有日誌的清單，可以使用記事本開啟該檔案
filename.index檔案的內容，joe是我的計算機名，當前只有一個binlog檔案：.\joe-bin.000001
|
1 |
.\joe-bin.000001 |
expire_logs_days定義了mysql清除過期日誌的時間，即二進位制日誌自動刪除的天數。
預設值為0，表示“沒有自動刪除”。當mysql啟動或重新整理二進位制日誌時可能刪除該檔案
max_binlog_size定義了單個檔案的大小限制，如果二進位制日誌寫入的內容大小超出給定值，日誌就會發生滾動
（關閉當前檔案，重新開啟一個新的日誌檔案）。不能將該變數設定為大於1GB或小於4096位元組。預設值是1GB
如果正在使用大事務 ，二進位制日誌檔案大小還可能超過max_binlog_size的定義大小。
在my.ini配置檔案中的[mysqld]組下，新增以下幾個引數與引數值
|
1 2 3 4 |
[mysqld] log-bin expire_logs_days=10 max_binlog_size=100M |
新增完畢之後，關閉並重啟mysql服務程式，即可開啟二進位制日誌，然後可以通過SHOW VARIABLES語句來查詢日誌設定
使用show VARIABLES 語句檢視日誌設定
|
1 |
show VARIABLES LIKE '%log_%'; |
可以看到log_bin為ON，max_binlog_size為104857600位元組，換算為MB為100MB
MYSQL重新啟動之後，就可以看到新產生的檔案字尾為.000001和.index的兩個檔案，檔名稱預設為主機名稱
如果想改變日誌檔案的目錄位置，可以修改my.ini中log-bin引數
例如：
|
1 2 |
[mysqld] log-bin="D:\mysql\log\binlog" |
關閉並重啟mysql服務之後，新的二進位制日誌將出現在”D:\mysql\log\binlog”路徑下
提示：資料庫檔案最好不要和日誌檔案放在同一個磁碟上，這樣當資料庫檔案所在磁碟發生損壞的時候，可以使用日誌來恢復資料
2、檢視二進位制日誌
mysql二進位制日誌是經常用到的。當mysql建立二進位制日誌檔案時，首先建立一個以filename為名稱，以index為字尾的檔案；
再建立一個以filename為名稱，以“.000001”為字尾的檔案。當mysql服務重新啟動一次，以“.000001”為字尾的檔案會增加一個，
並且字尾名加1遞增；如果日誌長度超過了max_binlog_size的上限（預設是1GB）也會建立一個新的日誌檔案
show binary logs語句可以檢視當前二進位制日誌檔案個數和檔名。mysql二進位制日誌並不能直接檢視，如果要檢視日誌內容，
可以通過mysqlbinlog命令檢視
使用show binary logs語句檢視二進位制日誌檔案個數和檔名
|
1 |
SHOW BINARY LOGS; |
可以看到，當前有兩個二進位制日誌檔案，因為我把mysql服務重啟了一次，日誌檔案的個數和mysql服務啟動的次數相同。
每啟動一次mysql服務，將會產生一個新的日誌檔案
使用mysqlbinlog檢視二進位制日誌
mysqlbinlog是一個單獨的exe，需要在命令列裡執行我們把binlog檔案裡面的內容匯出到binlog.txt
|
1 |
mysqlbinlog "D:\Program Files (x86)\MySQL\MySQL Server5.5\data\joe-bin.000002" >c:\binlog.txt |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #140731 7:49:30 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.20-log created 140731 7:49:30 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ioTZUw8BAAAAZwAAAGsAAAABAAQANS41LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACKhNlTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; |
3、刪除二進位制日誌
mysql的二進位制日誌可以配置自動刪除，同時mysql也提供了安全的手動刪除二進位制日誌的方法
刪除所有的二進位制日誌檔案使用RESET MASTER;
|
1 |
RESET MASTER; |
執行該語句，所有二進位制日誌將被刪除，mysql 會重新建立二進位制日誌，新的日誌副檔名將重新從000001開始編號
只刪除部分二進位制日誌檔案使用PURGE MASTER LOGS;
|
1 |
PURGE MASTER LOGS; |
語法如下
|
1 2 |
PURGE {MASTER | BINARY} LOGS TO 'log_name' PURGE {MASTER | BINARY} LOGS BEFORE 'date' |
第一種方法指定檔名，執行該命令將刪除檔名編號比指定檔名編號小的所有日誌檔案
第二種方法指定日期，執行該命令將刪除指定日期以前的所有日誌檔案
使用PURGE MASTER LOGS;刪除建立時間比binlog.000003早的所有日誌檔案
首先，為了演示語句操作過程，準備多個日誌檔案，讀者可以對mysql服務進行多次重啟
例如這裡有10個日誌檔案
執行刪除命令
|
1 |
PURGE MASTER LOGS TO "joe-bin.000003"; |
執行完成後，使用 show BINARY logs; 檢視二進位制日誌
可以看到joe-bin.000001和joe-bin.000002兩個日誌檔案被刪除了
使用 PURGE MASTER LOGS 刪除2013年3月30日前建立的所有日誌檔案，執行命令如下
|
1 |
PURGE MASTER LOGS BEFORE '20130330' |
執行完畢之後，2013年3月30日前的日誌檔案都被刪除，但2013年3月30日的日誌會被保留
4、檢視二進位制日誌裡的操作記錄
|
1 |
show binlog events; |
比如想檢視某一個二進位制日誌裡面的記錄，但又不想用mysqlbinlog，可以使用show binlog events
比如我想檢視’joe-bin.000006’這個binlog檔案的內容，執行如下命令
|
1 |
show binlog events in 'joe-bin.000006'; |
內容如下
|
1 2 3 4 5 6 |
Log_name: joe-bin.000006 Pos: 202 Event_type: Query Server_id: 1 End_log_pos: 304 Info: use `test`; insert into bin(name) values ('orange') |
可以看到’joe-bin.000006’這個binlog檔案記錄了哪些SQL命令
如果想知道binlog檔案的建立時間，就需要mysqlbinlog工具來檢視
|
1 2 3 4 5 6 |
C:\ProgramData\MySQL\MySQL Server 5.5\data>mysqlbinlog mysql_bin.000001 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #131015 16:35:56 server id 1 end_log_pos 106 |
其中131015為日誌建立時間，即2013年10月15日
5、使用二進位制日誌還原資料庫
如果mysql伺服器啟用了二進位制日誌，在資料庫出現意外丟失資料時，可以使用mysqlbinlog工具從指定的時間點開始
（例如，最後一次備份）直到現在，或另外一個指定的時間點的日誌中恢復資料
要想從二進位制日誌恢復資料，需要知道當前二進位制日誌檔案的路徑和檔名。一般可以從配置檔案（即my.cnf或者my.ini，檔名取決於mysql
伺服器的作業系統）中找到路徑
mysqlbinlog恢復資料的語法如下：
|
1 |
mysqlbinlog [option] filename |mysql -uuser -ppass |
option是一些可選項，filename是日誌檔名
比較重要的兩對option引數是
–start-datetime、–stop-datetime
–start-position、–stop–position
–start-date、–stop-date可以指定恢復資料庫的起始時間點和結束時間點
–start-position、–stop–position可以指定恢復資料的開始位置和結束位置
使用mysqlbinlog恢復mysql資料庫到2014年7月2日15：27：48時的狀態，執行下面命令
|
1 |
mysqlbinlog --stop-datetime="2014-7-2 15:27:48 " D:\mysql\log\binlog\binlog.000008 |mysql -u user -p password |
該命令執行成功後，會根據binlog.000008日誌檔案恢復2014年7月2日15：27：48前的所有操作。
這種方法對誤操作的刪除資料比較有效
6、暫時停止二進位制日誌
如果在mysql的配置檔案配置啟動了二進位制日誌，mysql會一直記錄二進位制日誌，修改配置檔案，可以停止二進位制日誌，
但是需要重啟mysql資料庫。mysql提供了暫時停止二進位制日誌的功能。通過 SET SQL_LOG_BIN 語句可以使mysql暫停或者啟動二進位制日誌
語法如下
|
1 |
SET sql_log_bin={0|1} |
執行下面語句將暫停二進位制日誌
|
1 |
SET sql_log_bin=0; |
執行下面語句將恢復記錄二進位制日誌
|
1 |
SET sql_log_bin=1; |
實際上，binlog檔案有點類似於SQLSERVER的ldf檔案，大家都儲存了資料庫的操作日誌，都可以根據這個日誌來恢復資料庫
但是又有不同，mysql的binlog可用不開啟，因為mysql的redo日誌放在ib_logfile開頭的檔案裡面，而undo日誌跟資料檔案是放在一起的
所以這一點跟SQLSERVER很不一樣
在複製的時候，MYSQL一定要開啟binlog功能，slave讀取binlog，而SQLSERVER的訂閱端讀取釋出端的ldf檔案
所以剛才說：binlog檔案有點類似於SQLSERVER的ldf檔案
錯誤日誌
錯誤日誌檔案包含了當mysqld啟動和停止時，以及伺服器在執行過程中發生任何嚴重錯誤時的相關資訊。
在MYSQL中，錯誤日誌也是非常重要的，mysql將啟動和停止資料庫資訊以及一些錯誤資訊記錄到錯誤日誌中
1、啟動和設定錯誤日誌
在預設情況下，錯誤日誌會記錄到資料庫的資料目錄下。如果沒有在配置檔案中指定檔名，則檔名預設為hostname.err。
例如：mysql所在伺服器主機名為mysql-db，記錄錯誤資訊的檔名為mysql-db.err。如果執行了FLUSH LOGS，錯誤日誌檔案會重新載入
錯誤日誌的啟動和停止以及日誌檔名，都可以通過修改my.ini（或者my.cnf）來配置。錯誤日誌的配置項是log-error。
在[mysqld]下配置log-error，在啟動錯誤日誌。如果需要指定檔名，則配置項如下：
|
1 2 3 |
[mysqld] log-error=[path/[file_name]] |
path為日誌檔案所在的目錄路徑，filename為日誌檔名。修改配置項後，需要重啟mysql服務才生效
2、檢視錯誤日誌
通過錯誤日誌可以監視系統的執行狀態，便於及時發現故障，修復故障。mysql錯誤日誌是以文字檔案形式儲存的，可以使用文字編輯器直接
檢視mysql錯誤日誌
如果不知道日誌檔案的儲存路徑，可以使用 show variables; 語句檢視錯誤日誌的儲存路徑。
語句如下
|
1 |
show variables LIKE 'log_error'; |
使用記事本檢視mysql錯誤日誌
通過上面 show variables LIKE ‘log_error’; 的語句檢視到錯誤日誌的路徑，然後用記事本開啟該檔案
我們可以看到錯誤日誌內容如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
140705 16:41:17 [Note] Plugin 'FEDERATED' is disabled. 140705 16:41:17 InnoDB: The InnoDB memory heap is disabled 140705 16:41:17 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140705 16:41:17 InnoDB: Compressed tables use zlib 1.2.3 140705 16:41:17 InnoDB: Initializing buffer pool, size = 2.0G 140705 16:41:18 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file E:\MYSQL DataBase\ibdata1 did not exist: InnoDB: a new database to be created! 140705 16:41:18 InnoDB: Setting file E:\MYSQL DataBase\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 140705 16:41:18 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 213 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 140705 16:41:21 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 213 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback segment(s) active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 140705 16:41:23 InnoDB: Waiting for the background threads to start 140705 16:41:24 InnoDB: 1.1.8 started; log sequence number 0 140705 16:41:24 [Note] Event Scheduler: Loaded 0 events 140705 16:41:24 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140705 23:44:14 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140705 23:44:14 [Note] Event Scheduler: Purging the queue. 0 events 140705 23:44:14 InnoDB: Starting shutdown... 140705 23:44:15 InnoDB: Shutdown completed; log sequence number 1595675 140705 23:44:15 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140706 8:17:09 [Note] Plugin 'FEDERATED' is disabled. 140706 8:17:09 InnoDB: The InnoDB memory heap is disabled 140706 8:17:09 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140706 8:17:09 InnoDB: Compressed tables use zlib 1.2.3 140706 8:17:09 InnoDB: Initializing buffer pool, size = 2.0G 140706 8:17:10 InnoDB: Completed initialization of buffer pool 140706 8:17:10 InnoDB: highest supported file format is Barracuda. 140706 8:17:14 InnoDB: Waiting for the background threads to start 140706 8:17:15 InnoDB: 1.1.8 started; log sequence number 1595675 140706 8:17:16 [Note] Event Scheduler: Loaded 0 events 140706 8:17:16 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140706 14:05:35 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140706 14:05:35 [Note] Event Scheduler: Purging the queue. 0 events 140706 14:05:35 InnoDB: Starting shutdown... 140706 14:05:36 InnoDB: Shutdown completed; log sequence number 1603322 140706 14:05:37 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140718 21:47:03 [Note] Plugin 'FEDERATED' is disabled. 140718 21:47:03 InnoDB: The InnoDB memory heap is disabled 140718 21:47:03 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140718 21:47:03 InnoDB: Compressed tables use zlib 1.2.3 140718 21:47:03 InnoDB: Initializing buffer pool, size = 2.0G 140718 21:47:03 InnoDB: Completed initialization of buffer pool 140718 21:47:03 InnoDB: highest supported file format is Barracuda. 140718 21:47:04 InnoDB: Waiting for the background threads to start 140718 21:47:05 InnoDB: 1.1.8 started; log sequence number 1603322 140718 21:47:06 [Note] Event Scheduler: Loaded 0 events 140718 21:47:06 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140719 20:02:45 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140719 20:02:45 [Note] Event Scheduler: Purging the queue. 0 events 140719 20:02:46 InnoDB: Starting shutdown... 140719 20:02:47 InnoDB: Shutdown completed; log sequence number 1603332 140719 20:02:48 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140719 20:04:20 [Note] Plugin 'FEDERATED' is disabled. 140719 20:04:20 InnoDB: The InnoDB memory heap is disabled 140719 20:04:20 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140719 20:04:20 InnoDB: Compressed tables use zlib 1.2.3 140719 20:04:20 InnoDB: Initializing buffer pool, size = 2.0G 140719 20:04:20 InnoDB: Completed initialization of buffer pool 140719 20:04:20 InnoDB: highest supported file format is Barracuda. 140719 20:04:21 InnoDB: Waiting for the background threads to start 140719 20:04:22 InnoDB: 1.1.8 started; log sequence number 1603332 140719 20:04:23 [Note] Event Scheduler: Loaded 0 events 140719 20:04:23 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140720 1:39:36 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140720 1:39:37 [Note] Event Scheduler: Purging the queue. 0 events 140720 1:39:40 InnoDB: Starting shutdown... 140720 11:17:29 [Note] Plugin 'FEDERATED' is disabled. 140720 11:17:29 InnoDB: The InnoDB memory heap is disabled 140720 11:17:29 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140720 11:17:29 InnoDB: Compressed tables use zlib 1.2.3 140720 11:17:29 InnoDB: Initializing buffer pool, size = 2.0G 140720 11:17:29 InnoDB: Completed initialization of buffer pool 140720 11:17:29 InnoDB: highest supported file format is Barracuda. 140720 11:17:37 InnoDB: Waiting for the background threads to start 140720 11:17:38 InnoDB: 1.1.8 started; log sequence number 1603332 140720 11:17:39 [Note] Event Scheduler: Loaded 0 events 140720 11:17:39 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140720 13:40:21 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140720 13:40:21 [Note] Event Scheduler: Purging the queue. 0 events 140720 13:40:22 InnoDB: Starting shutdown... 140720 13:40:23 InnoDB: Shutdown completed; log sequence number 1603332 140720 13:40:24 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140726 11:12:58 [Note] Plugin 'FEDERATED' is disabled. 140726 11:12:59 InnoDB: The InnoDB memory heap is disabled 140726 11:12:59 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140726 11:12:59 InnoDB: Compressed tables use zlib 1.2.3 140726 11:12:59 InnoDB: Initializing buffer pool, size = 2.0G 140726 11:12:59 InnoDB: Completed initialization of buffer pool 140726 11:12:59 InnoDB: highest supported file format is Barracuda. 140726 11:13:06 InnoDB: Waiting for the background threads to start 140726 11:13:07 InnoDB: 1.1.8 started; log sequence number 1603332 140726 11:13:10 [Note] Event Scheduler: Loaded 0 events 140726 11:13:10 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140727 0:34:19 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140727 0:34:20 [Note] Event Scheduler: Purging the queue. 0 events 140727 0:34:24 InnoDB: Starting shutdown... 140727 10:03:47 [Note] Plugin 'FEDERATED' is disabled. 140727 10:03:49 InnoDB: The InnoDB memory heap is disabled 140727 10:03:49 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140727 10:03:49 InnoDB: Compressed tables use zlib 1.2.3 140727 10:03:49 InnoDB: Initializing buffer pool, size = 2.0G 140727 10:03:49 InnoDB: Completed initialization of buffer pool 140727 10:03:50 InnoDB: highest supported file format is Barracuda. 140727 10:03:50 InnoDB: Waiting for the background threads to start 140727 10:03:51 InnoDB: 1.1.8 started; log sequence number 1603332 140727 10:03:52 [Note] Event Scheduler: Loaded 0 events 140727 10:03:52 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140727 14:29:56 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140727 14:29:56 [Note] Event Scheduler: Purging the queue. 0 events 140727 14:29:58 InnoDB: Starting shutdown... 140727 14:30:00 InnoDB: Shutdown completed; log sequence number 1643538 140727 14:30:00 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140801 21:10:05 [Note] Plugin 'FEDERATED' is disabled. 140801 21:10:05 InnoDB: The InnoDB memory heap is disabled 140801 21:10:05 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140801 21:10:05 InnoDB: Compressed tables use zlib 1.2.3 140801 21:10:06 InnoDB: Initializing buffer pool, size = 2.0G 140801 21:10:06 InnoDB: Completed initialization of buffer pool 140801 21:10:06 InnoDB: highest supported file format is Barracuda. 140801 21:10:09 InnoDB: Waiting for the background threads to start 140801 21:10:10 InnoDB: 1.1.8 started; log sequence number 1643538 140801 21:10:10 [Note] Event Scheduler: Loaded 0 events 140801 21:10:10 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19' socket: '' port: 3306 MySQL Community Server (GPL) 140801 22:59:19 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown 140801 22:59:19 [Note] Event Scheduler: Purging the queue. 0 events 140801 22:59:21 [Warning] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Forcing close of thread 2 user: 'root' 140801 22:59:21 InnoDB: Starting shutdown... 140801 22:59:23 InnoDB: Shutdown completed; log sequence number 1643538 140801 22:59:23 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete 140801 22:59:24 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=WIN7U-20130414Z-bin' to avoid this problem. 140801 22:59:24 [Note] Plugin 'FEDERATED' is disabled. 140801 22:59:24 InnoDB: The InnoDB memory heap is disabled 140801 22:59:24 InnoDB: Mutexes and rw_locks use Windows interlocked functions 140801 22:59:24 InnoDB: Compressed tables use zlib 1.2.3 140801 22:59:24 InnoDB: Initializing buffer pool, size = 2.0G 140801 22:59:24 InnoDB: Completed initialization of buffer pool 140801 22:59:24 InnoDB: highest supported file format is Barracuda. 140801 22:59:24 InnoDB: Waiting for the background threads to start 140801 22:59:25 InnoDB: 1.1.8 started; log sequence number 1643538 140801 22:59:26 [Note] Event Scheduler: Loaded 0 events 140801 22:59:26 [Note] E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections. Version: '5.5.19-log' socket: '' port: 3306 MySQL Community Server (GPL) |
3、刪除錯誤日誌
mysql的錯誤日誌以文字檔案的形式儲存在檔案系統中，可以直接刪除
對於mysql5.5.7以前的版本，flush logs可以將錯誤日誌檔案重新命名為filename.err_old，
並建立新的日誌檔案。但是從mysql5.5.7開始，flush logs只是重新開啟日誌檔案，並不做日誌備份和建立的操作。
如果日誌檔案不存在，mysql啟動或者執行flush logs時會建立新的日誌檔案
在執行狀態下刪除錯誤日誌檔案後，mysql並不會自動建立日誌檔案。flush logs在重新載入日誌的時候，如果檔案不存在，
則會自動建立。所以在刪除錯誤日誌之後，如果需要重建日誌檔案需要在伺服器端執行以下命令：
|
1 |
mysqladmin -u root -p flush-logs |
或者在客戶端登入mysql資料庫，執行flush logs語句
|
1 |
flush logs; |
刪除err檔案，並用flush logs語句重建log-error檔案
手動刪除檔案
手動執行 flush logs; ，err檔案恢復了
開啟err檔案，裡面什麼都沒有
通用查詢日誌
通用查詢日誌記錄了mysql的所有使用者操作，包括啟動和關閉服務、執行查詢和更新語句等
1、啟動和設定通用查詢日誌
mysql伺服器預設情況下並沒有開啟通用查詢日誌。如果需要通用查詢日誌，可以通過修改my.ini或my.cnf配置檔案來
開啟。在my.ini或my.cnf的[mysqld]組下加入log選項
形式如下
|
1 2 3 |
[mysqld] log[=path/[filename]] |
path為日誌檔案所在目錄路徑，filename為日誌檔名。如果不指定目錄和檔名，通用查詢日誌將預設儲存在mysql資料目錄中的
hostname.log檔案中。hostname是mysql資料庫的主機名
這裡在[mysqld]下面增加選項log，後面不指定引數值
|
1 2 |
[mysqld] log |
2、檢視通用查詢日誌
通用查詢日誌中記錄了用的所有操作。通過檢視通用查詢日誌，可以瞭解使用者對mysql進行的操作。通用查詢日誌是
以文字檔案形式儲存在檔案系統中的，可以使用文字編輯器直接開啟通用日誌檔案進行檢視，Windows下可以使用記事本
Linux下可以使用vim、gedit等
使用記事本檢視mysql通用查詢日誌
檔案內容如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with: TCP Port: 3306, Named Pipe: (null) Time Id Command Argument 140801 23:39:33 1 Connect root@localhost on 1 Query SHOW VARIABLES 1 Query SHOW WARNINGS 1 Query select timediff( curtime(), utc_time() ) 1 Query SHOW COLLATION 1 Query SET NAMES utf8 1 Query SET character_set_results=NULL 1 Query SELECT * FROM `emp` 140801 23:39:44 1 Query SELECT * FROM `emp` 1 Query SELECT * FROM `emp` 140801 23:39:55 1 Query USE test; SELECT * FROM `emp` 1 Init DB test |
可以看到mysql啟動資訊和使用者root連線伺服器與執行查詢語句的記錄
3、刪除通用查詢日誌
通用查詢日誌是以文字檔案的形式儲存在檔案系統中的。通用查詢日誌記錄使用者的所有操作
因此在使用者查詢、更新頻繁的情況下，通用查詢日誌會增長得很快。DBA可以定期刪除比較早的通用日誌，以節省磁碟空間
可以用直接刪除日誌檔案的方式刪除通用查詢日誌。要重新建立新的日誌檔案，可使用語句
|
1 |
mysqladmin -flush logs |
直接刪除log檔案
執行 flush logs
log檔案重新生成了
慢查詢日誌
慢查詢日誌是記錄查詢時長超過指定時間的日。慢查詢日誌主要用來記錄執行時間較長的查詢語句
通過慢查詢日誌，可以找出執行時間較長、執行效率較低的語句，然後進行優化
1、啟動和設定慢查詢日誌
mysql中慢查詢日誌預設是關閉的，可以通過配置檔案my.ini或my.cnf中的log-slow-queries選項開啟，也可以在mysql服務
啟動的時候使用–log–slow-queries[=file_name]啟動慢查詢日誌。啟動慢查詢日誌時，需要在my.ini或者my.cnf檔案中
配置long_query_time選項指定記錄閥值，如果某條查詢語句的查詢時間超過了這個值，這個查詢過程將被記錄到慢查詢日誌
檔案中。
在my.ini或者my.cnf檔案中開啟慢查詢日誌的配置如下：
|
1 2 3 4 |
[mysqld] log-slow-queries[=path/[filename]] long_query_time=n |
path為日誌檔案所在目錄路徑，filename為日誌檔名。如果不指定目錄和檔名稱，預設儲存在資料目錄中
檔名為hostname-slow.log，hostname是mysql伺服器的主機名。引數n是時間值，單位是秒。
如果沒有設定long-query_time選項，預設時間為10秒
開啟慢查詢日誌
|
1 2 3 |
[mysqld] log-slow-queries long_query_time=1 |
2、檢視慢查詢日誌
mysql的慢查詢日誌是以文字形式儲存的，可以直接使用文字編輯器檢視。在慢查詢日誌中，記錄著執行時間較長的查詢語句，
使用者可以從慢查詢日誌中獲取執行效率較低的查詢語句，為查詢優化提供重要的依據
檢視慢查詢日誌的一些引數
|
1 |
show variables like '%slow%'; |
檢視慢查詢日誌檔案裡的內容，使用文字編輯器開啟資料目錄下的WIN7U-20130414Z-slow.log檔案
|
1 2 3 4 5 6 7 8 9 |
E:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.19-log (MySQL Community Server (GPL)). started with: TCP Port: 3306, Named Pipe: (null) Time Id Command Argument # Time: 140802 0:02:29 # User@Host: root[root] @ localhost [::1] # Query_time: 7.578125 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use test; SET timestamp=1406908949; SELECT BENCHMARK (10000000,PASSWORD ('newpwd')); |
可以看到這裡記錄了一條慢查詢日誌。執行該條語句的帳戶是root @ localhost
查詢時間是Query_time: 7.578125秒
查詢語句是 SELECT BENCHMARK (10000000,PASSWORD (‘newpwd’));
該語句查詢時間大大超過了設定值1秒，因此被記錄在慢查詢日誌檔案中
BENCHMARK函式簡介：http://database.51cto.com/art/201010/229366.htm
3、刪除慢查詢日誌
和通用查詢日誌一樣，慢查詢日誌也可以直接刪除。刪除後在不重啟伺服器的情況下，需要執行
|
1 |
mysqladmin -u root -p flush logs |
重新生成日誌檔案，或者在客戶端登入到伺服器執行 flush logs; 語句重建日誌檔案
官方mysql的慢查詢日誌在這裡有一個缺陷，就是查詢閥值只能是1秒或以上，如果要設定一秒以下就無能為力了
這時候如果想找出1秒以下的慢查詢SQL，可以使用percona提供的microslow-patch來突破限制，將慢查詢時間閥值減小到毫秒級別
平時應開啟哪些日誌
日誌既會影響mysql的效能，又會佔用大量磁碟空間。因此，如果不必要，應儘可能少地開啟日誌。
根據不同的使用環境，考慮開啟不同的日誌。
例如開發環境中優化查詢效率低的語句，可以開啟慢查詢日誌，或者生產環境中發現某些SQL執行特別慢也可以開啟
如果磁碟空間不是特充足可以在高峰期間開啟，在捕獲到查詢慢的SQL之後再關閉慢查詢日誌
如果需要搭建複製環境，那麼就一定要開啟二進位制日誌，如果資料特別重要也建議開啟二進位制日誌，以便資料庫損壞的時候也可以通過二進位制日誌
挽救一部分資料
通用日誌無論在哪種情況下，一般不建議開啟
總結
本文簡單的闡述了MYSQL的日誌面的內容，MYSQL的日誌系統還是比較完善的，希望這篇文章對大家有幫助
如有不對的地方，歡迎大家拍磚o(∩_∩)o
2014-11-27補充 寫事務日誌流程
