目錄

• 1.MySQL常用日志檔案知識
• 2.錯誤日志的介紹與配置
  • 2.1.錯誤日志的介紹
  • 2.2.錯誤日志的記錄配置
  • 2.3.錯誤日志輪詢
  • 2.4.資料庫故障排查
• 3.普通查詢日志的介紹與配置
  • 3.1.普通查詢日志的介紹
  • 3.2.普通查詢日志的功能配置
  • 3.3.普通查詢日志
  • 3.4.普通查詢日志的生產使用建議
• 4.二進制日志的介紹與配置
  • 4.1.二進制日志的介紹
  • 4.2.二進制日志的作用
  • 4.3.二進制日志的配置
  • 4.4.二進制日志檔案的重繪條件
  • 4.5.二進制日志索引檔案
  • 4.6.洗掉二進制日志的方法
    • 4.6.1.設定引數自動洗掉binlog
    • 4.6.2.從最開始一直洗掉到指定的檔案位置（不含指定檔案）
    • 4.6.3.安裝時間洗掉binlog日志
    • 4.6.4.清楚所有的binlog，并從000001開始重新記錄
      • 4.6.4.1.binlog_cache_size
      • 4.6.4.2.max_binlog_size
      • 4.6.4.3.sync_binlog
  • 4.7.記錄二進制日志的三種模式
    • 4.7.1.陳述句模式
    • 4.7.2.行級模式
    • 4.7.3.混合模式
  • 4.8.企業中如何選擇二進制日志模式
  • 4.9.二進制日志的模式配置調整
  • 4.10.行級模式二進制日志實際讀取
• 5.慢查詢日志
  • 5.1.慢查詢日志介紹
  • 5.2.慢查詢日志相關引數說明
  • 5.3.慢查詢日志重要引數配置
  • 5.4.慢查詢日志的重繪方法

1.MySQL常用日志檔案知識

MySQL常用日志種類：

默認情況下，以上所有的日志都處于非激活狀態（Linux環境），當激活日志時，所有的日志都默認配置在資料檔案的目錄下，管理員也可以對上述日志進行輪詢切割，實作該功能常見的命令是mysqladmin flush-logs、mysqldump的“-F”或“--master-data”引數等，

2.錯誤日志的介紹與配置

2.1.錯誤日志的介紹

MySQL的錯誤日志用于記錄MySQL服務行程mysqld在啟動/關倍訓運行程序中遇到的錯誤資訊，

2.2.錯誤日志的記錄配置

MySQL的錯誤日志通常由mysqld或mysqld_safe程式產生，

記錄MySQL錯誤日志的配置：

1、在my.cnf組態檔中調整，注意，是在[mysqld_safe]模塊的下面進行配置，
[mysqld_safe]
log-error = /application/mysql-5.6.40/data/oldboy.err

2、在啟動MySQL服務的命令里加入記錄錯誤日志的引數，
mysqld_safe --log-error=/application/mysql-5.6.40/data/oldboy.err &

查看結果
mysql> show variables like 'log_error%';

2.3.錯誤日志輪詢

管理員可以使用命令輪詢錯誤日志：

cd /application/mysql/data/  #切換到日志目錄下
mv oldboy.err oldboy_$(date +%F).err  #將錯誤日志按天移動改名
mysqladmin flush-logs  #執行重繪日志命令

2.4.資料庫故障排查

新手安裝資料庫時，遇到資料庫無法啟動時的排查方法：

1、先清空錯誤日志檔案，然后重新啟動MySQL服務，再查看日志檔案報什么錯誤，并根據錯誤日志進行處理，
2、如果無法解決，則洗掉資料檔案，重新初始化資料庫，

在排查故障時，得到的錯誤日志是：

The error means mysqld does not have the access rights to
the directory.

該錯誤是權限問題導致的問題，可對資料目錄遞回執行權限，然后再重啟資料庫，

chown -R mysql.mysql /application/mysql/data/

3.普通查詢日志的介紹與配置

3.1.普通查詢日志的介紹

普通查詢日志的作用是記錄客戶端連接資訊，以及執行的SQL陳述句資訊，

3.2.普通查詢日志的功能配置

默認情況下普通查詢日志是關閉狀態：

mysql>show variables like 'general_log%';

可以執行在線修改的命令使其臨時生效：

mysql>set global general_log = on;
mysql>show variables like 'general_log%';

可以把引數寫入my.cnf的組態檔里的[mysqld]模塊下使其永久生效：

general_log = on
general_log_file = /application/mysql-5.6.40/data/oldboy.log

3.3.普通查詢日志

tail oldboy.log

3.4.普通查詢日志的生產使用建議

在高并發資料庫的場景下，普通查詢日志應該是關閉狀態的（默認也是關閉的），主要是因為查詢日志的資訊量很大，容易導致磁盤I/O性能問題，當訪問量不是很大，而企業又有審計執行的SQL陳述句的需求時，可以考慮開啟該功能，

4.二進制日志的介紹與配置

4.1.二進制日志的介紹

二進制日志的作用是記錄資料庫里的資料被修改的SQL陳述句，一般為DDL和DML陳述句，例如含有insert、update、delete、create、drop、alter等關鍵字的陳述句，

4.2.二進制日志的作用

二進制日志最重要的作用有2個，

第一個是記錄MySQL資料的增量資料，用來做增量資料庫恢復，沒有二進制日志功能，MySQL的備份將無法完整還原資料，

第二個是實作主從復制功能，

4.3.二進制日志的配置

vi /etc/my.cnf
[mysqld]
log_bin = mysql-bin

show variables like '%log_bin';  #查看binlog是否開啟

實作在開啟binlog功能的前提下，臨時不記錄binlog

set session sql_log_bin = OFF;  #臨時停止記錄binlog
create database oldgirl;  #建庫測驗
show binary logs;  #查看binlog檔案串列及位置點
system mysqlbinlog mysql-bin.000005|grep "oldgirl"  #過濾binlog檔案，沒有記錄binlog
set session sql_log_bin = ON;  #開啟記錄binlog
drop database oldgirl;  #洗掉資料庫
system mysqlbinlog mysql-bin.000005|grep "oldgirl"  #繼續過濾，發現記錄了binlog

這個功能常用于在用戶使用mysql恢復資料時不希望恢復的資料SQL記錄到binlog里的情況，

4.4.二進制日志檔案的重繪條件

1、資料庫重啟會自動重繪binlog為新檔案，
2、執行“mysqldump -F”或“mysqladmin flush-logs”會將binlog重繪為新檔案，
3、binlog檔案達到1G左右時，會自動重繪binlog為新檔案，
4、人為配置切割及調整，

binlog最大值控制引數及默認大小查看方法：
show variables like 'max_binlog_size';

4.5.二進制日志索引檔案

除了很多按序列生產的binlog檔案串列之外，還有一個索引檔案mysql-bin.index，

索引檔案的檔案名和binlog檔案一樣，只是擴展名為index，

binlog索引檔案的控制引數為：

show variables like 'log_bin_index';

4.6.洗掉二進制日志的方法

binlog日志很重要，不能隨意清除，

首先，要確定什么時候可以洗掉binlog，

理論上每天的資料庫全備時刻以前的binlog都是無用的，但是作業中我們會根據需要保留3-7天的本地binlog檔案，

洗掉方式：

4.6.1.設定引數自動洗掉binlog

引數設定為：

show variables like 'expire_logs_days';
set global expire_logs_days = 7;  #設定洗掉7天前的日志

vi /etc/my.cnf
[mysqld]
expire_logs_days = 7

4.6.2.從最開始一直洗掉到指定的檔案位置（不含指定檔案）

這種方法一般用于處理臨時的需求：

cp mysql-bin.* /tmp

登錄到資料庫執行：

show binary logs;
purge binary logs to 'mysql-bin.000002';
show binary logs;

4.6.3.安裝時間洗掉binlog日志

用于處理臨時的需求：

ls -l --time-style=long-iso mysql-bin*

洗掉“2020-05-19 10:24”以前的binlog檔案：

PURGE MASTER LOGS BEFORE '2020-05-19 10:24';
system ls -l --time-style=long-iso mysql-bin*

4.6.4.清楚所有的binlog，并從000001開始重新記錄

reset master指令可以清楚資料庫所有的binlog檔案，并從000001開始重新記錄；

reset master;
system ls -l --time-style=long-iso mysql-bin*;

binlog相關引數的設定和優化思路，

查看binlog相關的引數：

show variables like 'binlog_%';
show variables like '%log_bin%';

4.6.4.1.binlog_cache_size

二進制日志快取是資料庫為每一個客戶連接分配的記憶體空間，對于事務引擎來說，適當調整該引數會獲得更好的性能，該引數的默認值為：

show variables like '%binlog_cache%';

4.6.4.2.max_binlog_size

該引數用于設定binlog日志的最大大小，默認為1G，但是該值并不能嚴格控制binlog的大小，若binlog大小接近1G，而此時又在執行一個較大的事務，那么為了保證事務的完整性，資料庫不會做日志重繪動作，而是直到該事務的日志全部記錄進入當前binlog日志后才會進行重繪，該引數的默認值查詢結果為：

show variables like '%max_binlog_size%';

4.6.4.3.sync_binlog

這個引數的作用是控制binlog什么時候同步到磁盤，對資料庫來說，這是很重要的引數，它不僅會影響資料庫的性能，還會影響資料庫資料的完整性，

sync_binlog引數的具體說明：

1、sync_binlog=0表示在事務提交之后，資料庫不會將binlog_cache中的資料重繪到磁盤，而是讓檔案系統自行決定什么時候來做重繪或者在快取滿了之后才重繪到磁盤，
2、sync_binlog=n表示每進行n次事務提交之后，資料庫都會進行一次將快取資料強制重繪到磁盤的操作，

該引數默認的設定是0：
show variables like '%sync_binlog%';

設定為0時資料庫的性能是最好的，但資料風險也是最大的，對于資料安全性要求較高的資料庫，應該調整該引數將其改為1，值得注意的是，即使引數設定為1，仍然有binlog記錄的內容與資料庫的實際內容不一致的風險，

4.7.記錄二進制日志的三種模式

MySQL使用不同的模式記錄二進制日志資訊，常見的有三種模式，

4.7.1.陳述句模式

陳述句（statement-based）模式是MySQL5.6默認的模式，簡單地說，就是每一條被修改的資料的SQL陳述句都會記錄到master的binlog中，在復制slave庫的時候，SQL行程會決議成與原來master端執行過的相同的SQL來再次執行，

該模式的優點是不需要記錄細到每一行資料的更改變化，因此，可減少binlog日志量，實際上是減少了很多，節約了磁盤I/O，提高了系統性能，

但該模式同樣有一些缺點，由于陳述句模式記錄的是執行的SQL陳述句，所以，對于某些具有特殊功能的SQL陳述句來說，就可能會導致無法在從庫上正確執行，從而導致主從庫資料不一致的問題，

例如，當特殊的函式被執行時，當觸發器、存盤程序等特殊功能被執行時，而row level模式是基于每一行來記錄變化的，所以不會出現類似的問題，

4.7.2.行級模式

簡單地說，行級（row-based）模式就是將資料被修改的每一行的情況記錄為一條陳述句，

優點：在行級模式下，binlog中可以不記錄執行的SQL陳述句的背景關系相關資訊，僅僅記錄哪一條記錄被修改，修改成什么樣了即可，所以row level的日志內容會非常清楚地記錄下每一行資料修改的細節，非常容易理解，而且不會出現某些特定情況下的存盤程序或function以及trigger的呼叫和觸發無法被正確復制的問題，

缺點：行級模式下，所有的執行陳述句都將根據修改的行來記錄，而這就可能會產生大量的日志內容，例如一條陳述句修改了100萬行，陳述句模式就用一條陳述句即可搞定，而行級模式執行之后，日志中記錄的就是100萬行的修改記錄，binlog日志的量會非常大，

4.7.3.混合模式

混合（mixed-based）模式默認采用陳述句模式記錄日志，在一些特定的情況下會將記錄模式切換為行級模式記錄，這些特殊情況包含但不限于這幾種情況，

1、當函式中包含UUID()時，
2、當表中有自增列（AUTO_INCREMENT）被更新時，
3、當執行觸發器（trigger）或者存盤程序（stored function）等特殊功能時，
4、當FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等執行時，

4.8.企業中如何選擇二進制日志模式

在互聯網公司中，使用MySQL的特殊功能比較少（存盤程序、觸發器、函式），此時可以選擇默認的陳述句模式，

如果公司較多用到MySQL的特殊功能，如存盤程序、觸發器、函式等，并且需要做主從復制請首選行級模式，次選mixed模式，

4.9.二進制日志的模式配置調整

臨時調整命令：

set global binlog_format = 'statement';
set global binlog_format = 'row';
set global binlog_format = 'mixed';

永久調整可以將“binlog_format = '模式名'”寫入到my.cnf組態檔中，并重啟服務，

4.10.行級模式二進制日志實際讀取

當在資料庫中執行陳述句：

set global binlog_format = 'row';

或者在my.cnf中加入binlog_format = 'row'配置生效后，此時如果更新或者洗掉多行資料就會發現binlog日志記錄的內容有所不同，

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001  #--base64-output=decode-rows -v以行級模式決議binlog日志，

5.慢查詢日志

5.1.慢查詢日志介紹

簡單地理解，慢查詢日志（slow query log）就是記錄執行時間超出指定值（long_query_time）或其他指定條件（例如，沒有使用到索引，結果集大于1000行）的SQL陳述句，

5.2.慢查詢日志相關引數說明

慢查詢的引數，對于資料庫SQL的優化非常重要，是SQL優化的前提，因此，這里以表的形式進行說明，

慢查詢的引數及說明：

5.3.慢查詢日志重要引數配置

企業中常見的配置慢查詢的引數：

slow-query-log = ON                                #慢查詢開啟開關
long_query_time = 2                                #記錄大于2秒的SQL陳述句
log_queries_not_using_indexes = ON                 #沒有使用到索引的SQL陳述句
slow-query-log-file = /application/mysql/slow.log  #記錄SQL陳述句的檔案
min_examined_row_limit = 800                       #記錄結果大于800行的SQL陳述句

5.4.慢查詢日志的重繪方法

在作業中，可以利用定時任務按天對慢查詢日志進行切割，然后再分析，

切割腳本：

vi /server/scripts/cut_slow_log.sh
export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
cd /application/mysql &&\
mv slow.log slow.log.$(date +%F) &&\
mysqladmin flush-log

將腳本放入定時任務，每天0點執行切割任務：

00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1

標籤：Linux

上一篇：linux 終端輸出有顏色字體

下一篇：MySQL資料庫字符集