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時需要先刪除原來的日志文件

三、錯誤日志

錯誤日志中記錄項目：

1. mysqld啟動和關閉過程中輸出的事件信息

2. mysqld運行中產生的錯誤信息

3. event scheduler運行一個event時產生的日志信息

4. 在主從復制架構中的從服務器上啟動從服務器線程時產生的信息

相關變量

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 ：主從復制架構中，從服務器用于保存從主服務器的二進制日志中讀取到的事件

以上是“MySQL中日志記錄的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！