MySQL常用日誌文件知識

来源:https://www.cnblogs.com/yanyanqaq/archive/2020/06/19/13164319.html
-Advertisement-
Play Games

1.MySQL常用日誌文件知識 MySQL常用日誌種類： MySQL日誌種類 解釋說明 錯誤日誌（error log） 當資料庫啟動、運行、停止時產生該日誌 普通查詢日誌（general query log） 客戶端連接資料庫執行語句時產生該日誌 二進位日誌（binary log） 當資料庫內容發生 ...


目錄

1.MySQL常用日誌文件知識

MySQL常用日誌種類：

MySQL日誌種類 解釋說明
錯誤日誌（error log） 當資料庫啟動、運行、停止時產生該日誌
普通查詢日誌（general query log） 客戶端連接資料庫執行語句時產生該日誌
二進位日誌（binary log） 當資料庫內容發生改變時產生該日誌，也被用來實現主從複製功能
中繼日誌（relay log） 從庫上收到主庫的數據更新時產生該日誌
慢查詢日誌（slow query log） SQL語句在資料庫查詢超過指定時間時產生該日誌
DDL日誌（metadata log） 執行DDL語句操作元數據時產生該目錄

預設情況下，以上所有的日誌都處於非激活狀態（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優化的前提，因此，這裡以表的形式進行說明。

慢查詢的參數及說明：

慢查詢參數 解釋說明
slow_query-log 慢查詢開啟開關，預設值是OFF*
slow-query-log-file 記錄慢查詢語句的文件，文件名形如“主機名-slow.log”*
long_query_time 記錄大於指定N秒的SQL語句，預設是10秒，也可以使用微秒單位*
log_queries_not_using_indexes 記錄沒有使用到索引的SQL語句，預設值是OFF*
min_examined_row_limit 記錄結果集大於N行的SQL語句，預設是0行*
log_slow_admin_statements 記錄管理的慢SQL語句，例如ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE、REPAIR TABLE
log_throttle_queries_not_using_indexes 限制每分鐘寫入記錄的慢SQL語句的數量，預設值為0，表示沒限制

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

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 雲原生是什麼？
    雲：雲和本地是相對的，傳統的應用跑在本地伺服器上，現在流行的應用跑在雲端；IaaS，PaaS，SaaS; 雲原生：Cloud Native, 原生表示土生土長的意思，我們在開始設計應用的時候，就考慮到應用將來是運行在雲環境中的；要充分利用雲資源的優點：彈性和分散式； 雲原生 = 微服務 + DevO ...
  • PHTunnel集成Openwrt，不到一小時？安排上！
    花生殼phtunnel嵌入Openwrt 詳細介紹如何將phtunnel封裝成一個openwrt標準組件，並編譯到自己的openwrt固件中。 phtunnel組件製作下載 下載自己的編譯平臺的二進位phtunnel文件是第一步要做的，我們可以到oray的官方github網站進行下載，根據自己的型號 ...
  • yum倉庫管理 yum-config-manager
    yum倉庫管理 yum-config-manager 簡介 # yum 主要功能是更方便的添加/刪除/更新RPM 包，自動解決包的倚賴性問題，便於管理大量系統的更新問題。 # yum 可以同時配置多個資源庫(Repository)，簡潔的配置文件（/etc/yum.conf），自動解決增加或刪除 在 ...
  • rpm部分命令解讀
    rpm部分命令解讀 rpm RedHat Package Manger 打包及安裝工具 rpm參數列表 rpm -a rpm -q < rpm package name> 解讀：查詢一個包是否被安裝 rpm -qa rpm -qa <package name> -q 使用詢問模式(query) -a ...
  • Git 居然可以用來跟女神聊天？
    Git 是用來做啥的？想必碼農朋友都知道，Git 是版本控制軟體，是軟體開發過程中團隊協作不可或缺的軟體。 但是，作為版本控制軟體的 Git ，能跟聊天工具扯上關係嗎？這二者似乎毫無關係，但腦洞大開的外國朋友 Ephi Gabay 就開發了一個 GIC ，活生生將 Git 改造成了一個聊天工具，有了 ...
  • 鍵盤俠Linux教程（四）| 常用命令
    前言 Linux命令並不可怕，只要熟悉日常的操作命令即可，其他不熟悉的命令，需要用到的時候可以查閱資料，熟能生巧。 Linux常用操作命令 命令的基本格式 命令的提示符 [root@localhost ~]# []：這是提示符的分隔符號，沒有特殊含義。 root：顯示的是當前的登錄用戶。 @：分隔符 ...
  • MySQL資料庫存儲引擎
    1.MySQL引擎概述 1.1.什麼是存儲引擎？ 資料庫表裡的數據存儲在資料庫里及磁碟上，它跟視頻格式及存儲磁碟文件系統格式的特征類似，也有很多存儲方式。 但是，對於用戶和應用程式來說，同樣一張表的數據，無論採用什麼引擎來存儲，用戶看到的數據都是一樣的。對於不同的引擎存取，引擎功能、占用的空間大小、 ...
  • MySQL資料庫字元集
    1.MySQL資料庫字元集知識 1.1.什麼是字元集 電腦只能識別0和1這樣的二進位數字，無論是處理電腦程式，還是進行科學運算，最終都要轉換為二進位數據來完成操作；例如，我們輸入一個數字“8”，電腦會將其識別成二進位數字“1000”。 但是，電腦要處理的數據不僅僅是數字，還會有字母，為了處理 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類