mysql —日誌記錄

標籤：updating   font   schedule   efi   進位   注意   mys   資料   事件   

日誌

交易記錄： transaction log

中繼日誌： reley log錯誤記錄檔： error log

通用日誌： general log

慢查詢日誌： slow query log

二進位日誌： binary log

交易記錄

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

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

相關變數

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. 在主從複製架構中的從伺服器上啟動從伺服器線程時產生的資訊

相關變數

log_warnings=1|0 預設值1(是)：是否記錄警告資訊至錯誤記錄檔檔案

查詢日誌

記錄使用者的動作記錄，一般不建議啟用

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

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

慢查詢日誌

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

MariaDB [hellodb]> show variables like ‘slow_query%‘;+---------------------+------------------+| Variable_name       | Value            |+---------------------+------------------+| slow_query_log      | OFF              |--開啟或關閉慢查詢日誌| slow_query_log_file | centos7-slow.log |--慢查詢記錄檔+---------------------+------------------+2 rows in set (0.00 sec)MariaDB [hellodb]> SHOW VARIABLES LIKE ‘long_query_time‘;+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |--慢查詢的閥值，單位秒，查詢時間超過10則記錄到慢查詢日誌+-----------------+-----------+1 row in set (0.00 sec)MariaDB [hellodb]> SHOW VARIABLES LIKE ‘log_slow%‘;+---------------------+------------------------------+| Variable_name | Value || log_slow_rate_limit | 1 |-- 多少次查詢才記錄，mariadb特有| log_slow_verbosity | |-- 記錄內容的詳細程度（Query_plan,explain）+---------------------+------------------------------+4 rows in set (0.00 sec)MariaDB [hellodb]> SHOW VARIABLES LIKE ‘log_queries_not_using_indexes‘;+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |-- 記錄沒有利用索引查詢，而使用全文掃描的語句；預設關閉，建議開啟+-------------------------------+-------+1 row in set (0.00 sec)

log_slow_filter ：根據查詢結果過濾adminfilesortfilesort_on_diskfull_joinfull_scanquery_cachequery_cache_misstmp_tabletmp_table_on_disk

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

MariaDB [hellodb]> show variables like ‘profiling‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| profiling     | OFF   |-- 開啟或關閉，預設關閉+---------------+-------+1 row in set (0.00 sec)MariaDB [hellodb]> SET profiling=ON; #開啟查詢語句跟蹤功能Query OK, 0 rows affected (0.00 sec)MariaDB [hellodb]> SHOW profiles; #查詢語句執行的時間列表+----------+------------+---------------------------------+| Query_ID | Duration   | Query                           |+----------+------------+---------------------------------+|        1 | 0.00006851 | SET profiling=ON                ||        2 | 0.00049469 | show variables like ‘profiling‘ |        |+----------+------------+---------------------------------+3 rows in set (0.00 sec)

MariaDB [hellodb]> 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 為二進位日誌索引檔案

記錄二進位日誌的三種方式：

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

MariaDB [hellodb]> show variables like ‘binlog_format‘;+---------------+-----------+| Variable_name | Value     |+---------------+-----------+| binlog_format | STATEMENT |--記錄方式，系統預設基於語句方式+---------------+-----------+1 row in set (0.00 sec)

SET binlog_format=‘ROW|STATEMENT|MIXED‘; -- 修改二進位日誌記錄方式

二進位日誌的相關變數

開啟二進位日誌

MariaDB [(none)]> show variables like ‘sql_log_bin‘;-- 是否記錄二進位日誌，預設ON+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    |+---------------+-------+1 row in set (0.00 sec)

由於SHOW VARIABLES LIKE ‘log_bin‘;不支援動態修改，所以編輯設定檔vim /etc/my.cnf

建立mkdir /data/mysqllogs/

修改所有者所有組chown -g mysql.mysql /data/mysqllogs/

 指定檔案位置；預設OFF，表示不啟用二進位日誌功能，上述兩項都開啟才可

MariaDB [(none)]> show variables like ‘max_binlog_size‘;+-----------------+------------+| Variable_name   | Value      |+-----------------+------------+| max_binlog_size | 1073741824 |-- 單個二進位記錄檔的最大體積，到達最大值會自動滾動，預設為1G+-----------------+------------+1 row in set (0.00 sec)MariaDB [(none)]> show variables like ‘sync_binlog‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| sync_binlog   | 0     |-- 設定是否啟動二進位日誌即時同步磁碟功能，預設0，由作業系統負責同步處理記錄到磁碟+---------------+-------+1 row in set (0.00 sec)MariaDB [(none)]> show variables like ‘expire_logs_days‘;+------------------+-------+| Variable_name    | Value |+------------------+-------+| expire_logs_days | 0     |-- 二進位日誌可以自動刪除的天數。 預設為0，即不自動刪除+------------------+-------+1 row in set (0.00 sec)

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 -- 查看二進位檔案中的指定內容

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 顯示詳細資料

[[email protected] 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=0use `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命令可以直接匯入資料，實現了備份還原的功能

二進位日誌的管理

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; -- 手動觸發日誌滾動

 

mysql —日誌記錄