聊聊資料庫~6.SQL運維中篇

鯤逸鵬發表於2019-06-15
資料庫SQL運維

上篇回顧：https://www.cnblogs.com/dotnetcrazy/p/10810798.html#top

1.6.5.MySQL日誌相關

本文的測試環境：MySQL5.7.26、MariaDB5.5.60、MySQL8.0.16

PS：版本查詢select version();

1.MySQL常用日誌

伺服器層日誌（儲存引擎層有自己的日誌）

日誌型別 描述
error_log(錯誤日誌) 記錄MySQL啟動、執行或停止時出現的問題
general_log(常規日誌) 記錄所有傳送給MySQL的請求（耗效能）
slow_query_log(慢查日誌) 記錄符合條件的查詢（eg：超過10s、沒有使用索引等）
binary_log(二進位制日誌) 記錄全部有效的資料修改日誌（老版本資料庫不會開啟）
relay_log(中繼日誌) 用於主從複製，臨時儲存主從同步的二進位制日誌（增量複製）

知識擴充套件：https://blog.csdn.net/zhang123456456/article/details/72811875

實時檢視檔案：tail -f /var/log/mysqld.log

tail -f 用於監視檔案增長（預設是末尾10行）

2.error_log（錯誤日誌）

一般記錄MySQL執行錯誤和和未授權的訪問

  • 老版：log_error + log_warnings
  • 常用：log_error + log_error_verbosity
  • 新版：log_error + log_error_verbosity + log_error_services

查詢MySQL配置：show variables like '%log_error%';

SQL查詢可以這麼幹：

-- Ubuntu下預設是：`/var/log/mysql/error.log`
-- CentOS下預設是：`/var/log/mysqld.log` | `/var/log/mariadb/mariadb.log`
select @@log_error; -- 儘可能和Data分開儲存

-- 0：不記錄警告資訊，1：告警資訊寫入錯誤日誌，2：各類告警資訊都寫入（eg:網路故障和重連資訊）
select @@log_warnings; -- MySQL8中已經移除（MySQL5.7預設是2，MariaDB5.5.60預設是1）

-- 錯誤級別（1：Error，2：Error、Warning，3：Error、Warning、Info
select @@log_error_verbosity; -- MySQL8預設是2，MySQL5.7預設是3
-- PS：從MySQL 5.7.2開始，首選`log_error_verbosity`系統變數

-- 預設是`log_filter_internal; log_sink_internal`
select @@log_error_services; -- MySQL8.0中新增

PS：其實MySQL在市面上有很多成熟解決方案（基本上都是基於5.6、5.7的）

這也是為什麼我開篇主推MySQL5.7系列和MariaDB5.5.60（很多時候不是不用最新DB，而是架構依賴做不到啊）

知識擴充：https://www.cnblogs.com/kerrycode/p/8973285.html

MySQL8.0新增引數：log_error_services

日誌服務元件：

日誌服務元件名 描述
log_sink_internal 預設的日誌輸出元件（依賴log_error）
log_filter_internal 預設的日誌過濾元件（依賴log_error_verbosity）
log_sink_json 將錯誤日誌輸出到json檔案
log_sink_syseventlog 將錯誤日誌輸出到系統日誌檔案

PS：log_filter_internal：過濾錯誤資訊（達不到級別的不記錄）

日記格式一般是這樣的：UTC時間戳 程式id [日誌級別] [錯誤程式碼] [由什麼產生的日誌（Server or Client）] 詳細資訊

eg：2019-05-19T09:54:11.590474Z 8 [Warning] [MY-010055] [Server] IP address '192.168.36.144' could not be resolved: Name or service not known

一般log_sink_json用的比較多：

官方文件參考：https://dev.mysql.com/doc/refman/8.0/en/error-log-json.html

PS：第一次使用需要安裝一下json元件：install component 'file://component_log_sink_json';

常用設定：set persist log_error_services='log_filter_internal;log_sink_json';

時間戳相關的小知識點

上面的時間預設是UTC的時間戳，和我們是有時差的，這個時間戳可以通過設定log_timestamps來本地化：

-- 查詢
select @@log_timestamps; -- MySQL5.7新增

-- 從8開始,可通過SET PERSIST命令將全域性變數的修改持久化到配置檔案中
set persist log_timestamps='SYSTEM'; -- 需要root許可權

PS：set persist生成的配置檔案路徑在：/var/lib/mysql/mysqld-auto.cnf

autoconfig.png

3.general_log（常規日誌）

以前開發除錯的時候基本上都是會開啟的，上線後關閉（系統V1初期的時候也會開啟一段時間）

現在開發可以使用go-sniffer來抓包檢視客戶端執行的SQL

-- 是否開啟常規日誌（0不開啟，1開啟）
-- 一般不開啟（效能）
select @@general_log; -- 預設為0

-- Ubuntu預設：/var/lib/mysql/ubuntuserver.log
-- CentOS預設：/var/lib/mysql/localhost.log
select @@general_log_file; -- 常規日誌的路徑

-- 日誌的儲存方式（FILE | TABLE | NONE）
select @@log_output; -- 預設是檔案儲存

簡單看一下常規日誌在資料庫中的結構：

2.常規日誌.png

臨時開啟參考：

# 開啟
set global general_log = 1;

# set [global | persist] general_log_file = '日誌路徑';

set global log_output = 'TABLE';

4.slow_query_log（慢查詢日誌）

這個是最常用的，把符合條件的查詢語句記錄在日誌中，一般都是些需要優化的SQL

PS：出現效能瓶頸的時候，或者為了優化SQL會開啟一段時間（小專案推薦直接開啟）

先看下預設值：show variables like '%slow%';、show variables like 'long%';

2.慢查詢.png

SQL查詢：

-- 是否開啟
select @@slow_query_log; -- 預設是關閉

-- CentOS：/var/lib/mysql/localhost-slow.log
-- Ubuntu：/var/lib/mysql/ubuntuserver-slow.log
select @@slow_query_log_file;

-- 條件：設定超過多少秒為慢查詢（一般設定1s）
select @@long_query_time; -- 預設是10s（支援小數：0.003)

-- PS：設定為0就會記錄所有SQL（不推薦這麼幹）

-- 條件：沒有使用索引的查詢記錄到日誌中
select @@log_queries_not_using_indexes; -- 預設是0（不開啟）

-- 記錄optimize table、analyze table和alter table的管理語句
select @@log_slow_admin_statements; -- 預設是0（不開啟）

-- 記錄由Slave所產生的慢查詢
select @@log_slow_slave_statements;

常用設定：

PS：高併發下的網際網路專案，對SQL執行時間的容忍度一般都是低於300~500ms的（long_query_time=0.05）

# 常用如下：（需要MySQL的root許可權）
set global slow_query_log = 1; # 開啟慢查詢日誌
set global long_query_time = 1; # 記錄大於1s的SQL
set global log_slow_admin_statements = 1; # 記錄管理語句
set global log_queries_not_using_indexes = 1; # 記錄沒有使用索引的SQL
# set [global | persist] slow_query_log_file = '路徑'; # 設定log路徑

設定long_query_time時，需要重新連線才能生效（不需要重啟DB）

PS：當前會話不生效，之後的會話就生效了（不想重連可以再設定下當前會話的long_query_time）

知識擴充：（chown mysql:mysql /work/log/xxx.log）

擴充套件：慢查詢工具

先簡單分析下慢查詢日誌：

# Time: 2019-05-22T21:16:28.759491+08:00
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 0.000818  Lock_time: 0.000449 Rows_sent: 5  Rows_examined: 5
SET timestamp=1558530988;
select * from mysql.user order by host; # SQL語句
  1. Time：查詢的執行時間（start_time）
  2. User@Host: root[root] @ localhost [] Id:11：執行 sql 的主機資訊
  3. Query_time：SQL查詢所耗的時間
  4. Lock_time：鎖定時間
  5. Rows_sent：所傳送的行數
  6. Rows_examined：鎖掃描的行數
  7. SET timestamp=1558530988;：SQL執行時間

現在可以說說工具了，推薦兩款：

  1. 自帶的慢日誌分析工具：mysqldumpslow
  2. MySQL工具箱（percona-toolkit）中的pt-query-digest
mysqldumpslow（精簡）

查詢最慢的10條SQL：mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log

-s 按照那種方式排序
    t: 查詢時間
    c：訪問計數
    l：鎖定時間
    r:返回記錄
    al：平均鎖定時間
    ar：平均訪問記錄數
    at：平均查詢時間
-t 返回多少條資料（可以理解為top n）
-g 可以跟上正則匹配模式，大小寫不敏感。

PS：使用mysqldumpslow的分析結果不會顯示具體完整的sql語句：

  1. 翻頁sql不一樣，效能也是不一樣的，越往後的頁數越容易出現慢查詢，而mysqldumpslow把所有翻頁sql當成一個sql了
  2. eg：select * from tb_table where uid=20 group by createtime limit 10000, 1000; ==> select * from tb_table where uid=N group by createtime limit N, N;
    • 不管你uid和limit怎麼變，mysqldumpslow認為是一樣的
pt-query-digest（推薦）

官方文件：https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

分析慢查詢日誌：pt-query-digest /var/lib/mysql/localhost-slow.log

  1. 使用tcppdump捕獲MySQL協議資料，然後報告最慢的查詢：
    • tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
    • pt-query-digest --type tcpdump mysql.tcp.txt
  2. 檢視來自遠端程式列表上最慢的查詢：
    • pt-query-digest --processlist h=ip

安裝可以參考：https://github.com/lotapp/awesome-tools/blob/master/README.md#4%E8%BF%90%E7%BB%B4

PS：percona-toolkit的常用工具我也在裡面簡單說了下，對應文件也貼了

other

PS：還有一款mysqlsla我沒用過，所以貼個參考文章，感興趣的同志自己研究下

https://www.cnblogs.com/fengchi/p/6187099.html

知識擴充：https://www.cnblogs.com/fengchi/p/6187099.html

5.binary_log（二進位制日誌）

上節主要說了通用日誌和慢查日誌，今天說下二進位制日誌：

二進位制日誌算是最常用的了，主要就是記錄對資料庫的修改，然後就是主從複製用的比較多（比如增量備份）

PS：記錄了修改操作，那麼衍生出的場景就是：增量備份和恢復（基於時間點的備份和恢復）

PS：MySQL日誌主要分為這兩類：（互不干擾）

  1. 服務層日誌（和使用儲存引擎無關）
    • 通用日誌、慢查詢日誌、二進位制日誌
  2. 儲存引擎層日誌
    • eg：innodb的重做日誌(redo log)和回滾日誌(undo log)

Q：那什麼樣的修改會記錄下來呢？

A：記錄所有對MySQL資料庫的修改事件（包括增刪改查事件和對錶結構修改的事件），而且只記錄已經成功執行的事件（失敗的不會記錄）

這麼說可能有點抽象，熟悉SQLServer的同志看個圖就秒懂：

3.二進位制日誌.png

5.1.二進位制日誌格式
引數 說明
STATEMENT 基於段的格式，記錄執行資料修改時候所執行的SQL語句
ROW 基於行的格式，記錄增刪改查操作所修改行的資訊（每修改一行就會有一條資訊）
MIXED 基於行和端的混合格式，根據SQL語句由系統決定是基於段還是基於行的日誌格式記錄

檢視方式：show variables like 'binlog_format';

  1. binlog_format=statement：基於段的記錄格式（老版本的預設值）
    1. 優點：記錄量較小，節約磁碟和網路IO（單條操作Row更節約）
    2. 缺點：必須記錄上下文資訊來保證語句在從伺服器上執行結果與主伺服器相同
      • 但是如果使用了uuid()、user()等結果非確定的函式，可能會造成MySQL主從不一致
    3. 日誌檢視：mysqlbinlog /var/lib/mysql/binlog.0000xx | more（不用指定引數）
  2. binlog_format=row：基於行的記錄格式（5.7以後的預設值）
    1. 優點：可以避免MySQL複製中出現的主從不一致的問題（主從更安全）
      • PS：沒有備份的時候可以通過分析row格式的二進位制日誌來反向恢復
    2. 缺點：記錄日誌量較大（順序寫入）
      • 現在增加了新引數來優化：binlog_row_image=[full|minimal|noblob]
    3. 日誌檢視：mysqlbinlog -vv /var/lib/mysql/binlog.0000xx | more
  3. binlog_format=mixed：基於行和端的混合格式（推薦）
    • PS：資料量大小由所執行的SQL決定（非確定性函式越多，行數越多）

PS：DDL操作（create、drop、alter）的時候都是基於段方式來記錄log

如果一條一條記錄，表有上億資料，我就修改某列的狀態值，那不得瘋？

對binlog_row_image=[FULL|MINIMAL|NOBLOB]的補充說明：

PS：檢視方式：show variables like 'binlog_row_image'

  1. 預設是full：完整
    • 記錄修改行的全部內容
  2. noblob：就是在full記錄的基礎上對大文字列的優化
    • 沒有對text或者blob列修改就不記錄該列
  3. minimal：簡單記錄，只記錄修改的那一列
    • PS：這個要特別注意一點，雖然容量小了，但是一旦誤操作，很難恢復的（不知道原來內容）
推薦使用

一般使用binlog_format=mixed混合格式 or binlog_format=row + binlog_row_image=minimal

PS：如果對安全性要求特別高，推薦使用binlog_format=row + binlog_row_image=full（不怕誤操作）

這個和SQLServer的日誌恢復模式有點類似，我貼下圖你們可以對比參考：

3.容量.png

5.2.二進位制日誌配置

上面雖然說完了二進位制日誌的常用3種格式，但老版本預設都是不啟用二進位制日誌的，咋辦？

PS：如果是MariaDB可以去示例配置中檢視：ls /usr/share/mysql/ |grep .cnf（CentOS）

驗證下：

MySQL8之前：cat /etc/mysql/mysql.conf.d/mysqld.cnf（UbuntuServer）

3.binlog.png

MySQL8：cat /etc/my.cnf |grep log（CentOS）
3.binlog2.png

Q：有些人可能疑惑了，為什麼用show variables like 'log_bin';查詢出來的結果和配置檔案中不大一樣啊？

PS：一般配置項中的引數都可以使用show variables like 'xx'來查詢對應的值

3.log_bin.png

A：那是因為5.7之後版本分成了兩個引數：log_bin和log_bin_basename：

PS：配置檔案的log_bin=xxx相當於命令中的log_bin和log_bin_basename

mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
開啟演示

MariaDB開啟binlog圖示：（CentOS）

4.MariaDB開啟binlog.jpg

MySQL5.7演示：（UbuntuServer）

4.UbuntuServer下MySQL5.7演示.jpg

配置檔案中修改：（show variables like 'binlog_format';：檢視當前binlog基於什麼格式）

# 伺服器標識
server-id=1 # 單機MariaDB可不開啟

# 開啟binlog並設定路徑
# 不指定路徑則預設在資料目錄下
log_bin=binlog # 這個代表以binlog開頭的檔案

# binlog採用ROW|MIXED格式
# binlog_format=MIXED # 5.7預設是ROW

先看下檔案字首（log_bin=binlog）的概念，一張圖就懂：

4.檔案字首.png

PS：如果log_bin只是指定一個名字，那麼預設路徑一般都是在資料檔案的資料夾中

配置檔案一般都會寫，eg:datadir=/var/lib/mysql，或者通過show variables like 'datadir';也可以查詢到

雖然和SQLServer檔案組不是一個概念，但有些相似 ==> log可以多個也可以動態調整
3.多日誌檔案.png

5.3.ROW模式下記錄SQL

Q：雖然ROW記錄能保證主從資料安全，但我們排查問題的時候往往需要知道SQL，而用段的記錄方式又不合適，咋辦？

A：有個新引數可以解決：binlog_rows_query_log_events，開啟後就可以記錄sql了

檢視方式：show variables like 'binlog_row%';

mysql> show variables like 'binlog_row%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_row_image             | FULL  |
| binlog_rows_query_log_events | OFF   |
+------------------------------+-------+
2 rows in set (0.01 sec)
binlog演示

顯示binlog列表：show binary logs;

重新整理一份新的binlog：flush logs;（現在開始的二進位制日誌就記錄在這個新檔案中）

5.新建binlog.jpg

binlog現在是空的：(-vv：把二進位制格式的日誌顯示為能讀懂的字串)

mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS /var/lib/mysql/binlog.000006

5.新建binlog2.png

現在簡單摸擬幾個SQL操作，然後看看binlog：

6.sql.png

檢視下binlog日誌：(線上開發一般都是FULL模式，主要是防止程式設計師修改SQL的時候不加條件等誤操作)

FULL模式就是這樣，該行資料全部記錄（修改部分其實就綠色框的地方）

6.binlog.jpg

想要binlog中記錄SQL就開啟binlog_rows_query_log_events：

PS：像這種操作，如果模式選混合模式，binlog中會記錄SQL的

臨時開啟下binlog_rows_query_log_events（如果你有需要可以配置檔案設定一下）

PS：MySQL8可通過set persist命令將全域性變數的修改持久化到配置檔案中

6.sql2.png

效果如下：

6.binlog2.png

5.4.二進位制日誌的清除
  1. 自動清除
    • 配置檔案中設定時間：expire_logs_days = 30
  2. 手動清除
    • 刪除指定編號之前的日誌：purge binary logs to 'binlog.000006';
    • 刪除指定時間之前的日誌：purge binary logs before '2019-06-15 14:14:00';

已經23:23了，我們快速演示下：

MySQL命令列中執行命令：

7.刪除.png

檔案列表：

7.檔案.png

5.5.二進位制日誌與主從

這個把運維篇講完會繼續說，運維篇結束後會有個高階篇（架構），這邊就簡單提下二進位制格式對主從複製的影響：

  1. 基於SQL語句的複製（SBR）
    • 二進位制日誌格式使用的是statement格式（5.7前的預設）
  2. 基於行的複製（RBR）
    • 二進位制日誌格式使用的是基於行的日誌格式
  3. 混合模式
    • 根據實際在上面兩者中切換

貼個課後擴充文章：https://www.cnblogs.com/gujianzhe/p/9371682.html

下級預估：備份與恢復、監控

相關文章