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
更多相關文章
  • 字元串的MD5值 下麵這個例子是字元串 hello 的MD5值 命令解析 echo 預設是帶換行符做結尾的 echo n 可以去掉換行符 md5sum 列印或檢查MD5(128位)校驗和 文件的MD5值 下麵這個例子是文件 a.txt 的MD5值 命令解析 md5sum 列印或檢查MD5(128位) ...
  • Linux關鍵的根文件介紹 /etc:配置文件 /home:用戶的家目錄。每一個用戶的家目錄通常預設為/home/用戶名 /root:管理員的家目錄 /lib:庫文件 靜態庫:.a 動態庫:.dll .so /lib/modules:內核模塊文件 /media:掛載點目錄(常應用於移動設備) /mn ...
  • 電腦操作系統鎖機制. 在多線程編程中,操作系統引入了鎖機制。通過鎖機制,能夠保證在多核多線程環境中,在某一個時間點上,只能有一個線程進入臨界區代碼,從而保證臨界區中操作數據的一致性。 所謂的鎖,可以理解為記憶體中的一個整型數,擁有兩種狀態:空閑狀態和上鎖狀態。加鎖時,判斷鎖是否空閑,如果空閑,修改為 ...
  • 監控SQLServer資料庫 SSMS執行相關SQL SQL模板命名規則 Zabbix客戶端導入模板 添加SQLServer監控圖形 SQLServer伺服器關聯模板 監控思科Cisco防火牆交換機 監控SQLServer資料庫 SSMS執行相關SQL SQL模板命名規則 Zabbix客戶端導入模板 ...
  • 這段時間一直在學習mysql資料庫。項目組一直用的是oracle,所以對mysql的瞭解也不深。本文主要是對mysql鎖的總結。 Mysql的鎖主要分為3大類: 表級鎖:存儲引擎為Myisam。鎖住整個表,特點是開銷小,加鎖快,鎖定力度大,發生鎖衝突的概率最高,併發度最低。 頁級鎖:存儲引擎為BDB ...
  • 恢復內容開始 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) ...
  • 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設置了主鍵 ...
  • [TOC] 一,準備環境 基本配置如下: 初始化四台 虛擬機,配置如下: 修改系統時區 為方便使用建議如下配置: 安裝 " " 插件; 設置 行號; 安裝 插件服務; 安裝 插件服務並加以配置,方便文件上傳下載; 在 上安裝 客戶端; 在 上安裝 客戶端。 需要的軟體: 在虛擬機做如下步驟:創建目錄 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...