MySQL日誌管理
MySQL日誌簡介
錯誤日誌
# 預設是開啟：開啟
# 預設路徑及檔名：
- 原始碼和二進位制安裝：datadir/
$hostname
.err
- yum安裝：/var/log/mysql.log
# 是否可以修改：可以
作用：檢視MySQL啟動時的報錯找[Error]
# 修改日誌路徑
[root@db02 ~]
# vim /etc/my.cnf
[mysqld]
log_error=/tmp/err.log
mysql> show variables like
'log_error'
;
常規日誌
# 預設是開啟：否
# 預設路徑及檔名：datadir/$hostname.err
# 是否可以修改：可以
# 作用：記錄MySQL的常規操作
# 修改日誌路徑（一般來說不會開啟）
[root@db02 world]
# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/tmp/zls.log
二進位制日誌(binlog)
# 預設是否開啟：否
root@localhost:(none)>show variables like
'log_bin'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
# 預設日誌路徑及檔名：datadir/xxx.000001
# 是否可以修改：可以
# 作用：
1.記錄已結束的DML事務語句，並拆分為多個事件(event)來進行記錄
2.記錄所有DDL、DCL等語句
3.總之，二進位制日誌會記錄所有對資料庫、表發生修改的操作
# 如何修改日誌路徑
# 以下修改方式為 mysql5.6
[root@db02 ~]
# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
[root@db02 ~]
# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin
[root@db02 ~]
# vim /etc/my.cnf
[mysqld]
log-bin=/tmp/zls-bin
# MySQL5.7修改方式
MySQL想要開啟binlog必須配置server_id
server_id=1
log-bin=mysql-bin
mysql-bin.index：MySQL二進位制日誌binlog的索引檔案，有幾個binlog就會記錄幾個binlog
二進位制日誌的工作模式
# statment：語句模式（MySQL5.6 預設的工作模式）
mysql> show variables like
'binlog_format'
;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
將所有的語句，記錄binlog中
優點：通俗易懂，佔用磁碟空間小
缺點：不嚴謹
# row: 行級模式（MySQL5.7的預設工作模式）
root@localhost:(none)>show variables like
'binlog_format'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
將所有的語句及變化過程，記錄在binlog中
優點：嚴謹
缺點：不易懂，佔用磁碟空間大
# mixed：混合模式(瞭解)
語句模式和行級模式的混合模式
自己判斷，什麼時候只記錄語句，什麼時候記錄語句和變化過程
工作模式如何修改
[root@db02 data]# vim /etc/my.cnf
[mysqld]
log-bin
=/application/mysql/data/mysql-bin
binlog_format
=row或者（statment）
如何檢視二進位制日誌
# 檢視行級模式
[root@db01 mysql]
# mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
# 庫內檢視當前有幾個binlog日記及大小
root@localhost:(none)>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 2479 |
+------------------+-----------+
# 庫內檢視binlog日誌事件
root@localhost:(none)>show binlog events
in'mysql_bin.000001'
;
事件介紹
-
在binlog中最小的記錄單元為event
-
一個事務會被拆分為多個事件（event）
事件的特性
-
每個event都有一個開始位置（start position）和結束位置（stop position）
-
所謂的位置就是event對整個二進位制檔案的相對位置
-
對一個二進位制日誌中，前120個position是檔案格式資訊預留空間
# MySQL5.6
在MySQL5.6中，一個新的binlog起始位置點事120,120是系統資訊預留空間
其實120，就是該檔案的大小
143是空的binlog日誌，裡面沒有任何SQL語句
# MySQL5.7
在MySQL5.7中，一個新的binlog起始位置點是154，154是系統資訊預留空間
其實154，就是該檔案大小
177是空binlog，裡面沒有任何SQL語句執行
二進位制日誌資料故障恢復
#檢視binlog資訊
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 154 |
+------------------+----------+
# 建立binlog資料庫
mysql[(none)]> create database binlog;
# 檢視位置點
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 319 |
+------------------+----------+
# 建立一張表
mysql[(none)]> use binlog
mysql[binlog]> create table tb1(
id
int);
# 檢視位置點
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+
# 插入資料
mysql[binlog]> insert into tb1 values(1),(2),(3);
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+
mysql[binlog]> commit;
# 查看錶資訊
mysql[binlog]> select * from tb1;
+------+
|
id
|
+------+
| 1 |
| 2 |
| 3 |
+------+
# 修改資料
mysql[binlog]> update tb1
setid
=10
whereid
=1;
mysql[binlog]> commit;
mysql[binlog]> select * from tb1;
+------+
|
id
|
+------+
| 10 |
| 2 |
| 3 |
+------+
# 檢視位置資訊
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1018 |
+------------------+----------+
mysql[binlog]> delete from tb1
whereid
=2;
mysql[binlog]> commit;
# 檢視位置點
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1276 |
+------------------+----------+
mysql[binlog]> select * from tb1;
+------+
|
id
|
+------+
| 10 |
| 3 |
+------+
## 刪除表
mysql[binlog]> drop table tb1;
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1461 |
+------------------+----------+
## 刪除庫
mysql[binlog]> drop database binlog;
mysql[(none)]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1615 |
+------------------+----------+
如何恢復
# 1.檢視binlog，找到起始位置和結束位置點
[root@db01 mysql]
# mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
起始位置點：219
+------+
|
id
|
+------+
| 10 |
| 2 |
| 3 |
+------+
結束位置點：1018
# 2.擷取binlog
mysqlbinlog --start-position=219 --stop-position=1018 mysql_bin.000001 > /tmp/binlog.sql
# 3.匯入擷取資料
[root@db04 data]
# mysql </tmp/binlog.sql
# 4.查詢資料
mysql[binlog]> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| tb1 |
+------------------+
1 row
inset
(0.00 sec)
mysql[binlog]> select * from tb1;
+------+
|
id
|
+------+
| 10 |
| 2 |
| 3 |
+------+
存在問題
如果誤刪除的庫是10年前建立的
解決方案：
全量備份，配合binlog的增量部分
使用者使用資料是穿插使用的，binlog也不是一個庫一個庫記錄的
解決方案
只需要過濾出，被刪除的資料庫相關binlog中的SQL語句
使用-d指定資料庫擷取binlog
[root@db04 data]
# mysqlbinlog -d zls2 -vvv --base64-output=decode-row mysql_bin.000001
重新整理binlog
# 重新整理binlog
1.重啟資料庫會自動重新整理binlog
2.當binlog大小達到1G時，會自動刷新出下一個binlog
3.手動執行 flush logs;
4.使用mysqladmin flush-log
[root@db01 mysql]
# mysqladmin -uroot -p'456' flush-log
5.使用MySQLdump做備份時，可以重新整理binlog
[root@db02 data]
# mysqldump -A -F > /tmp/full.sql
刪除binlog
原則：
在儲存能力範圍內，能保留多少binlog就保留多少binlog
# 根據生存時間刪除日誌
# 臨時生效
set
global expire_logs_days = 7;
# 永久生效
[root@db01 data]
# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
# 刪除指定時間段binlog
purge binary logs before now() - interval 3 day;
# 指定binlog名字刪除，之前binlog都刪除
root@localhost:(none)>purge binary logs to
'mysql_bin.000002'
;
# 重置binlog，刪除所有binlog
mysql> reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
慢日誌(慢查詢日誌)
# 預設是否開啟：否
root@localhost:(none)>show variables like
'slow_query_log'
;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
# 預設路徑檔名：datadir/￥hostname-slow.log
# 是否可以修改：可以
# 作用
- 記錄執行的比較慢的SQL語句
# 修改日誌路徑
[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
# 不使用索引的SQL語句是否記錄到慢查詢日誌
log_queries_not_using_indexes
------------------------------------------------------------
# 查詢檢查返回少於該引數指定行的SQL不被記錄到慢查詢日誌
min_examined_row_limit=1000（雞肋）
檢視慢日誌查詢
[root@db02 data]
# mysqldumpslow
-s：指定如何排序
c：按照記錄次數
t：按照時間排序
r：按照返回記錄排序
l：按照查詢時間排序
ac：按照記錄次數 倒序排序
at：按照時間排序 倒序排序
ar：按照返回記錄 倒序排序
al：按照查詢時間 倒序排序
-t：top N
-g：指定正則表示式
[root@db02 data]
# mysqldumpslow -s t -t 10 db02-slow.log
percona 慢查詢工具
# percona下載地址
wget http://test.driverzeng.com/MySQL_Package/percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db02 ~]
# yum localinstall -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db02 ~]
# pt-query-digest /application/mysql/data/db02-slow.log
慢日誌視覺化介面
Anemometer基於pt-query-digest將MySQL慢查詢視覺化
httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日誌分析工具下載
httpss://github.com/box/Anemometer 視覺化程式碼下載
連結：https://www.cnblogs.com/wangchengww/p/16595659.html
（版權歸原作者所有，侵刪）
文末福利
即將步入2025年，不少小夥伴在考慮來年的工作方向。
僅目前來說，傳統運維衝擊年薪30W+的轉型方向就是SRE&DevOps崗位。
為了幫助大家早日擺脫繁瑣的基層運維工作，給大家整理了一套【2024最新運維資料】高階運維工程師必備技能資料包（文末一鍵領取），內容有多詳實豐富看下圖！
共有 20 個模組
······
以上所有資料獲取請掃碼
識別上方二維碼
備註：2024最新運維資料
100%免費領取
（是掃碼領取，不是在公眾號後臺回覆，別看錯了哦）
