MySQL日誌簡介

来源:https://www.cnblogs.com/1naonao/archive/2019/11/14/11862454.html
-Advertisement-
Play Games

一.MySQL日誌簡介 二.錯誤日誌 作用： 記錄mysql資料庫的一般狀態信息及報錯信息，是我們對於資料庫常規報錯處理的常用日誌。 預設位置： $MYSQL_HOME/data/ 開啟方式: （MySQL安裝完後預設開啟) 三.一般查詢日誌 作用： 記錄mysql所有執行成功的SQL語句信息，可以 ...


一.MySQL日誌簡介

二.錯誤日誌

作用：
記錄mysql資料庫的一般狀態信息及報錯信息，是我們對於資料庫常規報錯處理的常用日誌。

預設位置：
$MYSQL_HOME/data/

開啟方式:（MySQL安裝完後預設開啟)

#編輯配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';

三.一般查詢日誌

作用：
記錄mysql所有執行成功的SQL語句信息，可以做審計用，但是我們很少開啟。

預設位置：
$MYSQL_HOME/data/

開啟方式:（MySQL安裝完之後預設不開啟）

#編輯配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on
general_log_file=/application/mysql/data/$hostnamel.log
#查看方式
mysql> show variables like '%gen%';

四.二進位日誌

作用：
記錄已提交的DML事務語句，並拆分為多個事件（event）來進行記錄
記錄所有DDL、DCL等語句
總之，二進位日誌會記錄所有對資料庫發生修改的操作

預設是否開啟: 關閉

預設位置:$datadir

預設名字:mysql-bin

二進位日誌模式:
statement：語句模式，上圖中將update語句進行記錄（預設模式）。
row：行模式，即數據行的變化過程，上圖中Age=19修改成Age=20的過程事件。
mixed：以上兩者的混合模式。
企業推薦使用row模式

優缺點:

statement模式：

優點：簡單明瞭，容易被看懂，就是sql語句，記錄時不需要太多的磁碟空間。
缺點：記錄不夠嚴謹。

row模式：(記錄sql語句的變化過程)

優點：記錄更加嚴謹。
缺點：有可能會需要更多的磁碟空間，不太容易被讀懂。

binlog的作用:
1）如果我擁有資料庫搭建開始所有的二進位日誌，那麼我可以把數據恢復到任意時刻
2）數據的備份恢復
3）數據的複製

二進位日誌的管理操作實戰

開啟方式

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row

註意:在mysql5.7中開啟binlog必須要加上server-id。

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin                #也可以絕對路徑
binlog_format=row              #日誌模式
server_id=1                    #mysql5.7中必須要加

二進位日誌的操作

#物理查看
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql      285 Mar  6  2017 mysql-bin.000001
#命令行查看
mysql> show binary logs;
mysql> show master status;
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000007';

事件介紹

1）在binlog中最小的記錄單元為event
2）一個事務會被拆分成多個事件（event）

事件（event）特性

1）每個event都有一個開始位置（start position）和結束位置（stop position）。
2）所謂的位置就是event對整個二進位的文件的相對位置。
3）對於一個二進位日誌中，前120個position是文件格式信息預留空間。
4）MySQL第一個記錄的事件，都是從120開始的。

row模式下二進位日誌分析及數據恢復

 
#查看binlog信息
mysql> show master status;
#創建一個binlog庫
mysql> create database binlog;
#使用binlog庫
mysql> use binlog
#創建binglog_table表
mysql> create table binlog_table(id int);
#查看binlog信息
mysql> show master status;
#插入數據1
mysql> insert into binlog_table values(1);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#插入數據2
mysql> insert into binlog_table values(2);
#插入數據3mysql> insert into binlog_table values(3);
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#刪除數據1
mysql> delete from binlog_table where id=1;
#查看binlog信息
mysql> show master status;
#提交
mysql> commit;
#更改數據2為22
mysql> update binlog_table set id=22 where id=2;
#查看binlog
mysql> show master status;
#提交
mysql> commit;
#查看binlog信息
mysql> show master status;
#查看數據
mysql> select * from binlog_table;
#刪表
mysql> drop table binlog_table;
#刪庫
mysql> drop database binlog;

恢複數據到delete之前

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog來查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表結構就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表結構就是id=2
#結果
update binlog.binlog_table set id=22 where id=2;

#截取二進位日誌
查看二進位日誌後，發現delete語句開始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013

#臨時關閉binlog
mysql> set sql_log_bin=0;
#執行sql文件
mysql> source /tmp/binlog.sql
#查看刪除的庫
mysql> show databases;
#進binlog庫
mysql> use binlog
#查看刪除的表
mysql> show tables;
#查看表中內容
mysql> select * from binlog_table;

思考，存在問題：

資料庫或表被誤刪除的是很久之前創建的（一年前）
如果基於binlog全量恢復，成本很高
1）可以用備份恢復+短時間內二進位日誌，恢復到故障之前
2）非官方方法，binlog2sql，binlog取反，類似於Oracle的flushback
3）延時從庫

如果同一時間內和故障庫無關的資料庫都有操作，在截取binlog時都會被截取到
想一個辦法過濾出來？
1）grep？
其他過濾方案？
1）-d 參數接庫名

模擬數據

#為了讓大家更清晰看到新的操作
#刷新一個新的binlog
mysql> flush logs;
#創建db1、db2兩個庫
mysql> create database db1;
mysql> create database db2;

#庫db1操作
mysql> use db1
#創建t1表
mysql> create table t1(id int);
#插入5條數據
mysql> insert into t1 values(1),(2),(3),(4),(5);
#提交
mysql> commit;

#庫db2操作
mysql> use db2
#創建t2表
mysql> create table t2(id int);
#插入3條數據
mysql> insert into t2 values(1),(2),(3);
#提交
mysql> commit;

#查看binlog事件
mysql> show binlog events in 'mysql-bin.000014';
#查看db1的操作
[root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014

刪除、刷新binlog

刷新binlog日誌
1）flush logs;
2）重啟資料庫時會刷新
3）二進位日誌上限（max_binlog_size 1G上限）

刪除二進位日誌
1）原則
在存儲能力範圍內，能多保留則多保留
基於上一次全備前的可以選擇刪除

刪除方式

  • 1.根據存在時間刪除日誌
#臨時生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
  • 2.使用purge命令刪除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
purge binary logs before now() - interval 3 day;
  • 3.根據文件名刪除
PURGE BINARY LOGS TO 'mysql-bin.000010'; #之前的 刪除rinary
purge binary logs to 'mysql-bin.000010';
  • 4.使用reset master(刪除所有重新開始,相當於重置)
mysql> reset master;

五.慢查詢日誌

作用：
1）是將mysql伺服器中影響資料庫性能的相關SQL語句記錄到日誌文件
2）通過對這些特殊的SQL語句分析，改進以達到提高資料庫性能的目的

預設位置：
$MYSQL_HOME/data/$hostname-slow.log
開啟方式（預設沒有開啟）：

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否開啟慢查詢日誌
slow_query_log = 1
#指定慢日誌文件存放位置（預設在data）
slow_query_log_file=/application/mysql/data/slow.log
#設定慢查詢的閥值(預設10s)
long_query_time=0.05
#不使用索引的慢查詢日誌是否記錄到索引
log_queries_not_using_indexes
#查詢檢查返回少於該參數指定行的SQL不被記錄到慢查詢日誌
min_examined_row_limit=100（雞肋）

==註意:配置文件寫對,否則重啟不成功==

模擬慢查詢語句

#進入world庫
mysql> use world
#查看表
mysql> show tables
#將city表中所有內容加到t1表中
mysql> create table t1 select * from city;
#查看t1的表結構
mysql> desc t1;
#將t1表所有內容插入到t1表中（多插入幾次）
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#提交
mysql> commit;
#刪除t1表中id>2000的數據
mysql> delete from t1 where id>2000;
#查看慢日誌
[root@db01 ~]# cat /application/mysql/data/mysql-db01

使用mysqldumpslow命令來分析慢查詢日誌

#輸出記錄次數最多的10條SQL語句
$PATH/mysqldumpslow -s c -t 10 /database/mysql/slow-log

參數說明:
-s:
是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
-t:
是top n的意思，即為返回前面多少條的數據；
-g:
後邊可以寫一個正則匹配模式，大小寫不敏感的；

例：

#得到返回記錄集最多的10個查詢
$PATH/mysqldumpslow -s r -t 10 /database/mysql/slow-log
#得到按照時間排序的前10條裡面含有左連接的查詢語句
$PATH/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log

show process

第三方推薦（擴展）：

yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm

使用percona公司提供的pt-query-digest工具分析慢查詢日誌

[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log

有能力的可以做成可視化界面：
Anemometer基於pt-query-digest將MySQL慢查詢可視化
https://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日誌分析工具下載
https://github.com/box/Anemometer 可視化代碼下載


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

-Advertisement-
Play Games
更多相關文章
  • linux shell 命令獲取字元串/文件的MD5值
    字元串的MD5值 下麵這個例子是字元串 hello 的MD5值 命令解析 echo 預設是帶換行符做結尾的 echo n 可以去掉換行符 md5sum 列印或檢查MD5(128位)校驗和 文件的MD5值 下麵這個例子是文件 a.txt 的MD5值 命令解析 md5sum 列印或檢查MD5(128位) ...
  • Linux根文件系統介紹
    Linux關鍵的根文件介紹 /etc：配置文件 /home：用戶的家目錄。每一個用戶的家目錄通常預設為/home/用戶名 /root：管理員的家目錄 /lib：庫文件 靜態庫：.a 動態庫：.dll .so /lib/modules：內核模塊文件 /media:掛載點目錄（常應用於移動設備） /mn ...
  • 電腦操作系統鎖機制.
    電腦操作系統鎖機制. 在多線程編程中，操作系統引入了鎖機制。通過鎖機制，能夠保證在多核多線程環境中，在某一個時間點上，只能有一個線程進入臨界區代碼，從而保證臨界區中操作數據的一致性。 所謂的鎖，可以理解為記憶體中的一個整型數，擁有兩種狀態：空閑狀態和上鎖狀態。加鎖時，判斷鎖是否空閑，如果空閑，修改為 ...
  • 【第二章】Zabbix3.4監控SQLServer資料庫和H3C交換機思科Cisco防火牆交換機教程筆記
    監控SQLServer資料庫 SSMS執行相關SQL SQL模板命名規則 Zabbix客戶端導入模板 添加SQLServer監控圖形 SQLServer伺服器關聯模板 監控思科Cisco防火牆交換機 監控SQLServer資料庫 SSMS執行相關SQL SQL模板命名規則 Zabbix客戶端導入模板 ...
  • mysql的鎖機制詳解
    這段時間一直在學習mysql資料庫。項目組一直用的是oracle，所以對mysql的瞭解也不深。本文主要是對mysql鎖的總結。 Mysql的鎖主要分為3大類： 表級鎖：存儲引擎為Myisam。鎖住整個表，特點是開銷小，加鎖快，鎖定力度大，發生鎖衝突的概率最高，併發度最低。 頁級鎖：存儲引擎為BDB ...
  • SQLServer筆記
    恢復內容開始 SQL語句的執行順序 1>From 表 2>where 條件 3>group by 列 4>Having 篩選條件 5>select 5-1>被分組列,聚合函數列5-2>distinct>5-3>top6>order by 列 GROUP BY --當使用了分組語句(group by) ...
  • MySQL第四課
    CREATE TABLE biao( name VARCHAR(20) PRIMARY KEY, age INT(11) NOT NULL, sex CHAR(11)DEFAULT 1, love VARCHAR(11) );Query OK, 0 rows affected 此時name設置了主鍵 ...
  • 分享一份關於Hadoop2.2.0集群環境搭建文檔
    [TOC] 一，準備環境 基本配置如下： 初始化四台 虛擬機，配置如下： 修改系統時區 為方便使用建議如下配置： 安裝 " " 插件； 設置 行號； 安裝 插件服務； 安裝 插件服務並加以配置，方便文件上傳下載； 在 上安裝 客戶端； 在 上安裝 客戶端。 需要的軟體： 在虛擬機做如下步驟：創建目錄 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類