MySQL系列之十一日志記錄

Posted on 2021-07-03 by WalkonNet

系列教程

MySQL系列之開篇 MySQL關系型數據庫基礎概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二多實例配置
MySQL系列之三基礎篇
MySQL系列之四 SQL語法
MySQL系列之五視圖、存儲函數、存儲過程、觸發器
MySQL系列之六用戶與授權
MySQL系列之七 MySQL存儲引擎
MySQL系列之八 MySQL服務器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務隔離實現並發控制
MySQL系列之十一日志記錄
MySQL系列之十二備份與恢復
MySQL系列之十三 MySQL的復制
MySQL系列之十四 MySQL的高可用實現
MySQL系列之十五 MySQL常用配置和性能壓力測試

一、SQL命令歷史

~/.mysql_history 記錄瞭在mysql中執行的命令歷史

二、事務日志

transaction log：事務型存儲引擎自行管理和使用

在一個事務提交後還沒有存到磁盤的情況下會記錄到事務日志，這個時候如果系統斷電，再次開機後會自動將已提交的事務重做（redo log）寫入磁盤；如果事務還沒有提交的情況下斷電重啟後會執行撤銷操作（undo log）

相關變量

MariaDB [school]> SHOW VARIABLES LIKE 'innodb_log%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| innodb_log_block_size     | 512        | -- 塊大小
| innodb_log_buffer_size    | 8388608    | -- 緩存大小
| innodb_log_file_size      | 1073741824 | -- 每個日志文件大小
| innodb_log_files_in_group | 3          | -- 日志組成員個數，也就是有幾個文件
| innodb_log_group_home_dir | ./         | -- 事務文件路徑，相對於數據目錄
+---------------------------+------------+

innodb_log_file_size默認大小為5M，innodb_log_files_in_group個數默認2個；強烈建議調大這倆個值；

如果事務操作的數據大於事務日志文件總大小，在執行ROLLBACK時：可以撤銷，但是數據文件會被占用，需要使用OPTIMIZE TABLE命令來釋放數據空間；

使用TRUNCATE刪除表記錄可以縮小占用空間，DELETE命令不會釋放磁盤空間，如果需要釋放需要使用OPTIMIZE命令從新整理碎片來釋放空間。

註意：修改innodb_log_file_size時需要先刪除原來的日志文件

三、錯誤日志

錯誤日志中記錄項目：

mysqld啟動和關閉過程中輸出的事件信息
mysqld運行中產生的錯誤信息
event scheduler運行一個event時產生的日志信息
在主從復制架構中的從服務器上啟動從服務器線程時產生的信息

相關變量

MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error';  #錯誤日志文件的路徑
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+

log_warnings=1|0 默認值1(是)：是否記錄警告信息至錯誤日志文件

四、查詢日志

記錄用戶的操作日志，一般不建議啟用

MariaDB [school]> SHOW VARIABLES LIKE 'general_log%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log      | OFF         | -- 開啟關閉通用日志，默認關閉
| general_log_file | centos7.log | -- 通用日志文件的文件名，/var/lib/mysql/HOSTNAME.log
+------------------+-------------+

MariaDB [school]> SHOW VARIABLES LIKE 'log_output';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  | -- 以什麼方式存放日志，(TABLE|FILE|NONE)
+---------------+-------+

mysql.general_log：表存放查詢日志，前提是以表方式存放的

五、慢查詢日志

記錄執行查詢時長超出指定時長的操作

1、慢查詢相關變量

MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%';    
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| slow_query_log      | OFF              | -- 開啟或關閉慢查詢日志
| slow_query_log_file | centos7-slow.log | -- 慢查詢日志文件
+---------------------+------------------+
MariaDB [school]> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 | -- 慢查詢的閥值，單位秒，查詢時間超過10則記錄到慢查詢日志
+-----------------+-----------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_slow_rate_limit | 1     | -- 多少次查詢才記錄，mariadb特有
| log_slow_verbosity  |       | -- 記錄內容的詳細程度（Query_plan,explain）
+---------------------+-------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   | -- 記錄沒有利用索引查詢，而使用全文掃描的語句；默認關閉，建議開啟
+-------------------------------+-------+

log_slow_filter ：根據查詢結果過濾

admin
filesort
filesort_on_disk
full_join
full_scan
query_cache
query_cache_miss
tmp_table
tmp_table_on_disk

2、補充：利用 `profiling` 跟蹤查詢語句所用的詳細時間

MariaDB [school]> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   | -- 開啟或關閉，默認關閉
+---------------+-------+
MariaDB [school]> SET profiling=ON;  #開啟查詢語句跟蹤功能
MariaDB [school]> SHOW profiles;  #查詢語句執行的時間列表
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.00024497 | SELECT * FROM students                                     |
|        2 | 0.00038528 | SELECT stuid,name,age FROM students WHERE stuid = 2        |
+----------+------------+------------------------------------------------------------+

MariaDB [school]> SHOW profile FOR query 2;  #查詢指定編號的SQL語句的詳細執行過程
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000035 |
| Opening tables       | 0.000003 |
| After opening tables | 0.000006 |
| query end            | 0.000003 |
| closing tables       | 0.000002 |
| freeing items        | 0.000011 |
| updating status      | 0.000006 |
| cleaning up          | 0.000001 |
+----------------------+----------+

六、二進制日志

記錄已提交事務導致數據改變或潛在導致數據改變的SQL語句，通過“重放”日志文件中的事件來生成數據副本，不依賴存儲引擎類型。

開啟二進制日志，默認是關閉的，二進制日志和數據分開存放

開啟記錄二進制文件的功能：在my.cnf的[mysqld]下加入log_bin[=/path/somefile]

默認二進制日志在數據庫目錄下，mariadb-bin.000001 為二進制日志數據文件，mariadb-bin.index 為二進制日志索引文件

1、記錄二進制日志的三種方式：

基於語句的記錄方式，每一條改變數據的語句記錄為一條語句，節省空間，系統默認為此模式，但是不推薦使用，會有隱藏的風險
基於行的記錄方式，每一行的改變都會將使其改變的語句記錄為一條語句，日志量會很大，但是對數據的安全保護非常高
混合模式：mixed, 讓系統自行判定該基於哪種方式進行

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';  
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT | -- 記錄方式，系統默認基於語句方式
+---------------+-----------+

SET binlog_format='ROW|STATEMENT|MIXED'; -- 修改二進制日志記錄方式

2、二進制日志的相關變量

MariaDB [(none)]> SHOW MASTER|BINARY LOGS; -- 查看mariadb自行管理使用中的二進制日志文件列表
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000002 |       290 |
| mariadb-bin.000003 |       264 |
| mariadb-bin.000004 |    529038 |
| mariadb-bin.000005 |       245 |
+--------------------+-----------+
MariaDB [(none)]> SHOW MASTER STATUS; -- 查看使用中的二進制日志文件
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000005 |      245 |              |                  |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\G -- 查看二進制文件中的指定內容

MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; -- 是否記錄二進制日志，默認ON
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; -- 指定文件位置；默認OFF，表示不啟用二進制日志功能，上述兩項都開啟才可
MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 | -- 單個二進制日志文件的最大體積，到達最大值會自動滾動，默認為1G
+-----------------+------------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';    
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     | -- 設定是否啟動二進制日志即時同步磁盤功能，默認0，由操作系統負責同步日志到磁盤
+---------------+-------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     | -- 二進制日志可以自動刪除的天數。 默認為0，即不自動刪除
+------------------+-------+

3、mysqlbinlog命令

二進制日志的客戶端命令工具
–start-position=# 指定開始位置
–stop-position=# 指定結束位置
–start-datetime=(YYYY-MM-DD hh:mm:ss) 指定開始時間
–stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定結束時間
–base64-output=decode-row
-v |-vv |-vvv |-vvvv 顯示詳細信息

[root@centos7 mysql]# mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v
# at 528864
#180611 20:59:46 server id 1  end_log_pos 528992        Query   thread_id=29    exec_time=0     error_code=0
use `school`/*!*/;
SET TIMESTAMP=1528721986/*!*/;
INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M')  #改變數據的SQL語句
/*!*/;

事件發生的日期和時間：180611 20:59:46
事件發生的服務器標識：server id 1
事件的結束位置：end_log_pos 528992
事件的類型：Query
事件發生時所在服務器執行此事件的線程的ID：thread_id=29
語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0
錯誤代碼：error_code=0
事件內容： 
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID

使用mysqlbinlog mariadb-bin.000001 > file.sql命令重定向到SQL文件中，再使用mysql -uroot -p < file.sql命令可以直接導入數據，實現瞭備份還原的功能

4、二進制日志的管理

MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002'; -- 刪除到02，註意：02不刪除
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; -- 刪除2018-01-23之前的日志
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
MariaDB [(none)]> RESET MASTER; -- 刪除所有二進制日志，index文件重新記數
MariaDB [(none)]> FLUSH LOGS; -- 手動觸發日志滾動

七、中繼日志

relay log ：主從復制架構中，從服務器用於保存從主服務器的二進制日志中讀取到的事件

總結

本篇文章就到這裡瞭，希望可以給你帶來一些幫助，也希望您能夠多多關註WalkonNet的更多內容！

MySQL系列之十一日志記錄

系列教程

一、SQL命令歷史

二、事務日志

三、錯誤日志

四、查詢日志

五、慢查詢日志

1、慢查詢相關變量

2、補充：利用 `profiling` 跟蹤查詢語句所用的詳細時間

六、二進制日志

1、記錄二進制日志的三種方式：

2、二進制日志的相關變量

3、mysqlbinlog命令

4、二進制日志的管理

七、中繼日志

總結

推薦閱讀：

發佈留言取消回覆

近期文章

系列教程

一、SQL命令歷史

二、事務日志

三、錯誤日志

四、查詢日志

五、慢查詢日志

1、慢查詢相關變量

2、補充：利用 profiling 跟蹤查詢語句所用的詳細時間

六、二進制日志

1、記錄二進制日志的三種方式：

2、二進制日志的相關變量

3、mysqlbinlog命令

4、二進制日志的管理

七、中繼日志

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

2、補充：利用 `profiling` 跟蹤查詢語句所用的詳細時間

發佈留言取消回覆