MySQL數據庫三種日志的特點和使用

下面講講關于MySQL數據庫三種日志的特點和使用，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL數據庫三種日志的特點和使用這篇文章你一定會有所受益。

1.1 mysql工具mysqlbinlog

mysqbinlog工具的作用是解析mysql的二進制binlog日志內容，把二進制的日志解析成可以在MySQL數據庫里執行的SQL語句。

1.2 MySQL的·binlog日志是什么？

MySQL數據目錄下的如下文件就是mysql的binlog日志

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.000007
mysql-bin.000008
  . . . . . .. .
提示：要想生成binlog必須在配置文件中打開log-bin功能
[root@db01 3306]# greplog-bin /data/3306/my.cnf
log-bin =/data/3306/mysql-bin

1.3 mysql binlog日志功能開啟

[root@db01 3306]# vim /data/3306/my.cnf
log-bin = /data/3306/mysql-bin

1.4 mysql的binlog日志作用是什么？

       mysql的binlog日志作用是用來記錄mysql內部增刪等對mysql數據庫有更新的內容的記錄（對數據的改動），對數據庫查詢的語句如show，select開頭的語句，不會被binlog日志記錄。用于數據庫的主從復制，以及增量恢復。

測試題：

在MySQL數據庫中，關于binlog日志，下列說法正確的是-----------（A）

A：依靠足夠長度的binlog日志和定期的全備，我們可以恢復任何時間點的單表數據。

B：以mysql主從同步為例，binlog中會記錄主數據庫的所有操作。

C：以mysql主從同步為例，binlog中會記錄主數據庫的所有查詢操作。

D：binlog通過cat和vi無法查看，但可以通過gedit查看。

1.5 mysqlbinlog工具解析binlog日志實踐

默認情況binlog日志是二進制格式的，不能使用查看文本工具的命令查看，例如：cat、vi

[root@db01 3306]# file /data/3306/mysql-bin.000001
/data/3306/mysql-bin.000001: MySQL replication log

解析指定庫的binlog日志

范例：利用mysqlbinlog-d參數解析指定庫的binlog日志

[root@db01 3306]#  oldboy /data/3306/mysql-bin.000001 -r oldboy.sql
[root@db01 3306]# ll oldboy.sql    
          
-rw-r--r-- 1 root root 4731 Aug 31 04:36 oldboy.sql
[root@db01 3306]# cat oldboy.sql

結論：mysqlbinlog工具分庫導出binlog，如果使用-d參數，那更新數據時，必須有usedatabase，才能分出指定庫的binlog，例如：

use oldboy;
insert into test values(1,’oldboy’)

下面的寫法就不行

nsert into oldboy.test values(1,’oldboy’)

官方資料

按照位置截取：精確

mysqlbinlog mysql-bin.000003 365 456 pos.sql

# 說明：指定文件的第365到456結束，是binlog文件中的“# at 365”---“# at 456”；-r是指定文件，相當于重定向，如果命令中指定開始不指定結尾就是到文件的結尾，如果不指定開始則是從文件開頭開始。

按照時間截取：模糊，不準

mysqlbinlog mysql-bin.000003 -'2016-10-8 12:12:12' '2016-10-8 12:20:20' -r time.sql

# 說明：時間在該binlog文件中的 # at456 下面即使，只指定開始時間就是到文件結尾，只指定結尾，則是從開頭開始；-r是指定文件

1.6 mysqlbinlog命令小結

mysqlbinlog命令：

1. 把binlog日志解析為SQL語句（包含位置和時間點）。

2. -d參數根據指定庫拆分binlog（拆分單表binlog可通過SQL關鍵字過濾）。

3. 通過位置參數截取部分binlog：--syart-position=365  --stop-position=456,；精確定位取部分內容。

4. 通過時間參數截取部分binlog：-stsrt-datetime='2016-10-8 12:12:12' --stop-deter=time='2016-10-8 12:20:20'，模糊截取部分內容，會丟數據。

5. -r指定文件名，相當于重定向。

6. 解析ROW級別binlog日志的方法

2. MySQL數據庫服務日志

2.1 錯誤日志（error.log）介紹與調整

1. 錯誤日志（error.log）介紹

  MySQL的錯誤日志記錄MySQL服務進程mysql在啟動/關閉或運行過程中遇到的錯誤信息。

2. 錯誤日志（error.log）實踐

法1：在配置文件中調整方法，當然可以在啟動時加入啟動參數

[mysqld_safe]           
log-error=/data/3306/mysql_oldboy3306.err

法2：啟動MySQL命令里加入：

mysql_safe --detaults-file=/data/3306/my.cnf  &

在數據庫里也可以看到

mysql> show variables like '%log_error%';
+---------------+---------------------------------+
| Variable_name |      Value                      |
+---------------+---------------------------------+
| log_error     |  |
+---------------+---------------------------------+
1 row in set (0.00 sec)

如果mysql數據庫起不起來排查步驟

1. 將日志文件備份，然后清空日志文件，并重啟數據庫，查看報錯。

2. 如果是多實例的話講多實例目錄下的目錄權限chown -R mysql.....。

3. 查看是否有管理mysql數據庫的用戶。

2.2 普通查詢日志（genera log）介紹與調整（工作中不用）

1. 普通查詢日志（generalquery log）介紹

   普通查詢日志（general querylog），記錄客戶端連接信息和執行的SQL語句信息（增刪改查，全部記錄）。工作中不用，會消耗IO性能，

2. 普通查詢日志generalquery log）調整

mysql> show variables like 'general_log%';              
+-------------------------+--------------------------+
| Variable_name           |Value                     |
+-------------------------+--------------------------+
| general_log             | ON                       |
| general_log_file        | /data/3306/data/db01.log |
+-------------------------+--------------------------+
2 rows in set (0.00 sec)

臨時生效：

mysql> set global general_log_file ='/data/3306/data/db01.log';            
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'general_log%';              
+-------------------------+--------------------------+
| Variable_name           |Value                     |
+-------------------------+--------------------------+
| general_log             | OFF                      |
| general_log_file        | /data/3306/data/db01.log |
+-------------------------+--------------------------+
2 rows in set (0.00 sec)

永久生效（配置文件中配置）：

[root@db01 3306]# grep gene /data/3306/my.cnf
general_log = on                                 
general_log_file = /data/3306/data/db01.log

實際的普通查詢日志示例：

2.3 慢查詢日志（slow query log）介紹與調整 *****

1. 慢查詢日志介紹：

  慢查詢日志，記錄執行時間超出指定值的SQL語句

2. 慢查詢日志的調整

long_query_time = 1                             
log-slow-queries = /data/3306/slow.log          
log_queries_not_using_indexes

慢查詢的設置，對于數據庫SQL的優化非常重要

[root@db01 /]# egrep "quer"/data/3306/my.cnf|tail -3
long_query_time = 1
log-slow-queries = /data/3306/slow.log
log_queries_not_using_indexes

利用慢查詢進行優化解決方案

1. 開啟慢查詢參數

long_query_time = 1                                  
log-slow-queries = /data/3306/slow.log
log_queries_not_using_indexes

2. 慢查詢日志切割腳本

[root@db01 /]# vim /server/scripts/cut_slow_log.sh
#!/bin/bash
cd /data/3306 &&\
/bin/mv slow.log slow.log.$(date +%F) &&\
mysqladmin -uroot -poldboy123 -S/data/3306/mysql.sock flush-log
[root@db01 scripts]# tail -2 /var/spool/cron/root
# cut mysql slow log
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh>/dev/null 2>&1

3. 使用工具mysqlsla分析慢查詢，定時給相關人員信箱

使用explain優化SQL語句（select語句）的基本流程  * * * * *

原因：當網站出現問題，到會訪問慢，在數據庫中超過2秒就是很慢的查詢了！

思路：當用戶訪問數據慢時，就要想到是不是滿查詢引起的，所以我們要查找慢查詢，而找找慢查詢分兩種情況，第一種是比較緊急情況下使用下面緊急處理的現場抓取的方法，進入mysql數據庫將慢查詢找到，然后建立索引，這起見借助explain可以查看慢查詢語句是否經過索引，如果沒有經過索引就建立索引，第二種則是不那么緊急的情況，可以在平時就關注負載、cpu等信息，當發現有異常時時就進行檢查，這些都是可以通過修改配置文件來將慢查詢的語句輸入到sllow.log里面，然后通過分析工具進行分析，以郵件的形式發給開發人員和dba人員讓他們來介入處理。 

排查方法：首先查看web負載高不高，存儲壓力大不大，還有數據庫負載和磁盤IO，還有cpu等原因。負載高的話常規情況下是數據庫慢查詢導致的，下面是查詢數據庫慢查詢如下： 

（1） 抓慢查詢SQL語句方法

在數據庫查找慢查詢（緊急處理）

mysql> show full processlist;                            
+----+------+-----------+--------+---------+------+-------+-----------------------+
| Id | User | Host      | db     | Command | Time | State |Info                   |
+----+------+-----------+--------+---------+------+-------+-----------------------+
|  9 | root | localhost | oldboy | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+--------+---------+------+-------+-----------------------+
1 row in set (0.00sec)

說明：如果數據庫正在對外提供訪問，訪問量大時。連續執行此命令會有很多語句，建議每隔兩秒執行兩次，當發現同一個命令連續出現時，就可能是慢查詢語句。

在命令行查詢慢查詢（日常處理）

[root@db01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showfull processlist;"|egrep -vi "sleep"
Id   User   Host       db    Command  Time    State  Info
11   root   localhost   NULL   Query      0     NULL    show full processlist

說明：此命令可以說是未雨綢繆；重要不緊急，分析慢查詢日志

配置參數記錄慢查詢語句

long_query_time = 2                          
log_queries_not_using_indexes                 
log-slow-queries = /data/3306/slow.log

（2） 抓到慢查詢后，使用explain語句檢查索引執行情況（看下有沒有走索引）

explain select * from test where name='oldgirl'\G
explain select * from test where name='oldgirl'\G

提示：這兩條命令是查詢的結果是一樣的！SQL_NO_CACHE是防止有緩存

（3）對需要建索引的條件列建立索引

  大表不能高峰期建立索引，300萬記錄就是達標

（4）分析man查詢工具mysqlsla（每天早晨發郵件）。

切割慢查詢日志

1.mv切割、reload進程（和mginx日志切割一個原理）

[root@db01 3306]# vim /data/3306/my.cnf       
log-slow-queries = /data/3306/slow.log        
 
[root@db01 3306]# /data/3306/mysql restart    
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
 
[root@db01 3306]# ll slow.log                 
-rw-rw---- 1 mysqlmysql 380 Aug 27 15:59 slow.log
 
[root@db01 3306]# mv /data/3306/slow.log /opt/$(date +%F)_slow.log      
 
[root@db01 3306]# ll /opt/                                              
-rw-rw----  1 mysql mysql 190 Aug 27 16:26 2016-08-27_slow.log
 
[root@db01 3306]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-logs
                                     
[root@db01 3306]# ll /data/3306/slow.log
-rw-rw---- 1 mysqlmysql 190 Aug 27 16:28 /data/3306/slow.log

2. 寫成腳本（定時任務）

mv /data/3306/slow.log/opt/$(date +%F)_slow.log
mysqladmin -uroot -poldboy123 -S/data/3306/mysql.sock flush-logs

提示：可以將以上命令寫成腳本，每天進行切割，在使用相應的工具進行分析

對于以上MySQL數據庫三種日志的特點和使用相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。