MySQL系列之十一 日志記錄
更新時(shí)間：2021年07月02日 17:02:12 作者：生生不息.連綿不絕
這篇文章主要介紹了MySQL日志文件詳解,本文分別講解了錯(cuò)誤日志、二進(jìn)制日志、通用查詢?nèi)罩尽⒙樵內(nèi)罩?、Innodb的在線redo日志、更新日志等日志類型和作用介紹,需要的朋友可以參考下
系列教程
MySQL系列之開篇 MySQL關(guān)系型數(shù)據(jù)庫(kù)基礎(chǔ)概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二 多實(shí)例配置
MySQL系列之三 基礎(chǔ)篇
MySQL系列之四 SQL語(yǔ)法
MySQL系列之五 視圖、存儲(chǔ)函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器
MySQL系列之六 用戶與授權(quán)
MySQL系列之七 MySQL存儲(chǔ)引擎
MySQL系列之八 MySQL服務(wù)器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務(wù)隔離實(shí)現(xiàn)并發(fā)控制
MySQL系列之十一 日志記錄
MySQL系列之十二 備份與恢復(fù)
MySQL系列之十三 MySQL的復(fù)制
MySQL系列之十四 MySQL的高可用實(shí)現(xiàn)
MySQL系列之十五 MySQL常用配置和性能壓力測(cè)試
一、SQL命令歷史
~/.mysql_history 記錄了在mysql中執(zhí)行的命令歷史
二、事務(wù)日志
transaction log：事務(wù)型存儲(chǔ)引擎自行管理和使用
在一個(gè)事務(wù)提交后還沒(méi)有存到磁盤的情況下會(huì)記錄到事務(wù)日志，這個(gè)時(shí)候如果系統(tǒng)斷電，再次開機(jī)后會(huì)自動(dòng)將已提交的事務(wù)重做（redo log）寫入磁盤；如果事務(wù)還沒(méi)有提交的情況下斷電重啟后會(huì)執(zhí)行撤銷操作（undo log）
相關(guān)變量
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 | -- 每個(gè)日志文件大小 | innodb_log_files_in_group | 3 | -- 日志組成員個(gè)數(shù)，也就是有幾個(gè)文件 | innodb_log_group_home_dir | ./ | -- 事務(wù)文件路徑，相對(duì)于數(shù)據(jù)目錄 +---------------------------+------------+
innodb_log_file_size默認(rèn)大小為5M，innodb_log_files_in_group個(gè)數(shù)默認(rèn)2個(gè)；強(qiáng)烈建議調(diào)大這倆個(gè)值；
如果事務(wù)操作的數(shù)據(jù)大于事務(wù)日志文件總大小，在執(zhí)行ROLLBACK時(shí)：可以撤銷，但是數(shù)據(jù)文件會(huì)被占用，需要使用OPTIMIZE TABLE命令來(lái)釋放數(shù)據(jù)空間；
使用TRUNCATE刪除表記錄可以縮小占用空間，DELETE命令不會(huì)釋放磁盤空間，如果需要釋放需要使用OPTIMIZE命令從新整理碎片來(lái)釋放空間。
注意：修改innodb_log_file_size時(shí)需要先刪除原來(lái)的日志文件
三、錯(cuò)誤日志
錯(cuò)誤日志中記錄項(xiàng)目：
- mysqld啟動(dòng)和關(guān)閉過(guò)程中輸出的事件信息
- mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息
- event scheduler運(yùn)行一個(gè)event時(shí)產(chǎn)生的日志信息
- 在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動(dòng)從服務(wù)器線程時(shí)產(chǎn)生的信息
相關(guān)變量
MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error'; #錯(cuò)誤日志文件的路徑 +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | log_error | /var/log/mariadb/mariadb.log | +---------------+------------------------------+
log_warnings=1|0 默認(rèn)值1(是)：是否記錄警告信息至錯(cuò)誤日志文件
四、查詢?nèi)罩?/h2>
記錄用戶的操作日志，一般不建議啟用
MariaDB [school]> SHOW VARIABLES LIKE 'general_log%'; +------------------+-------------+ | Variable_name | Value | +------------------+-------------+ | general_log | OFF | -- 開啟關(guān)閉通用日志，默認(rèn)關(guān)閉 | 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：表存放查詢?nèi)罩?，前提是以表方式存放?/p>
五、慢查詢?nèi)罩?/h2>
記錄執(zhí)行查詢時(shí)長(zhǎng)超出指定時(shí)長(zhǎng)的操作
1、慢查詢相關(guān)變量
MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%'; +---------------------+------------------+ | Variable_name | Value | +---------------------+------------------+ | slow_query_log | OFF | -- 開啟或關(guān)閉慢查詢?nèi)罩? | slow_query_log_file | centos7-slow.log | -- 慢查詢?nèi)罩疚募? +---------------------+------------------+ MariaDB [school]> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | -- 慢查詢的閥值，單位秒，查詢時(shí)間超過(guò)10則記錄到慢查詢?nèi)罩? +-----------------+-----------+ MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_slow_rate_limit | 1 | -- 多少次查詢才記錄，mariadb特有 | log_slow_verbosity | | -- 記錄內(nèi)容的詳細(xì)程度（Query_plan,explain） +---------------------+-------+ MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | -- 記錄沒(méi)有利用索引查詢，而使用全文掃描的語(yǔ)句；默認(rèn)關(guān)閉，建議開啟 +-------------------------------+-------+
log_slow_filter ：根據(jù)查詢結(jié)果過(guò)濾
- admin
- filesort
- filesort_on_disk
- full_join
- full_scan
- query_cache
- query_cache_miss
- tmp_table
- tmp_table_on_disk
2、補(bǔ)充：利用 profiling 跟蹤查詢語(yǔ)句所用的詳細(xì)時(shí)間
MariaDB [school]> SHOW VARIABLES LIKE 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | -- 開啟或關(guān)閉，默認(rèn)關(guān)閉 +---------------+-------+ MariaDB [school]> SET profiling=ON; #開啟查詢語(yǔ)句跟蹤功能 MariaDB [school]> SHOW profiles; #查詢語(yǔ)句執(zhí)行的時(shí)間列表 +----------+------------+------------------------------------------------------------+ | 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; #查詢指定編號(hào)的SQL語(yǔ)句的詳細(xì)執(zhí)行過(guò)程 +----------------------+----------+ | 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 | +----------------------+----------+
六、二進(jìn)制日志
記錄已提交事務(wù)導(dǎo)致數(shù)據(jù)改變或潛在導(dǎo)致數(shù)據(jù)改變的SQL語(yǔ)句，通過(guò)“重放”日志文件中的事件來(lái)生成數(shù)據(jù)副本，不依賴存儲(chǔ)引擎類型。
開啟二進(jìn)制日志，默認(rèn)是關(guān)閉的，二進(jìn)制日志和數(shù)據(jù)分開存放
開啟記錄二進(jìn)制文件的功能：在my.cnf的[mysqld]下加入log_bin[=/path/somefile]
默認(rèn)二進(jìn)制日志在數(shù)據(jù)庫(kù)目錄下，mariadb-bin.000001 為二進(jìn)制日志數(shù)據(jù)文件，mariadb-bin.index 為二進(jìn)制日志索引文件
1、記錄二進(jìn)制日志的三種方式：
- 基于語(yǔ)句的記錄方式，每一條改變數(shù)據(jù)的語(yǔ)句記錄為一條語(yǔ)句，節(jié)省空間，系統(tǒng)默認(rèn)為此模式，但是不推薦使用，會(huì)有隱藏的風(fēng)險(xiǎn)
- 基于行的記錄方式，每一行的改變都會(huì)將使其改變的語(yǔ)句記錄為一條語(yǔ)句，日志量會(huì)很大，但是對(duì)數(shù)據(jù)的安全保護(hù)非常高
- 混合模式：mixed, 讓系統(tǒng)自行判定該基于哪種方式進(jìn)行
MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | -- 記錄方式，系統(tǒng)默認(rèn)基于語(yǔ)句方式 +---------------+-----------+
SET binlog_format='ROW|STATEMENT|MIXED'; -- 修改二進(jìn)制日志記錄方式
2、二進(jìn)制日志的相關(guān)變量
MariaDB [(none)]> SHOW MASTER|BINARY LOGS; -- 查看mariadb自行管理使用中的二進(jìn)制日志文件列表 +--------------------+-----------+ | 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; -- 查看使用中的二進(jìn)制日志文件 +--------------------+----------+--------------+------------------+ | 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 -- 查看二進(jìn)制文件中的指定內(nèi)容
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; -- 是否記錄二進(jìn)制日志，默認(rèn)ON MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; -- 指定文件位置；默認(rèn)OFF，表示不啟用二進(jìn)制日志功能，上述兩項(xiàng)都開啟才可 MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | -- 單個(gè)二進(jìn)制日志文件的最大體積，到達(dá)最大值會(huì)自動(dòng)滾動(dòng)，默認(rèn)為1G +-----------------+------------+ MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | -- 設(shè)定是否啟動(dòng)二進(jìn)制日志即時(shí)同步磁盤功能，默認(rèn)0，由操作系統(tǒng)負(fù)責(zé)同步日志到磁盤 +---------------+-------+ MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | -- 二進(jìn)制日志可以自動(dòng)刪除的天數(shù)。 默認(rèn)為0，即不自動(dòng)刪除 +------------------+-------+
3、mysqlbinlog命令
- 二進(jìn)制日志的客戶端命令工具
- --start-position=# 指定開始位置
- --stop-position=# 指定結(jié)束位置
- --start-datetime=(YYYY-MM-DD hh:mm:ss) 指定開始時(shí)間
- --stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定結(jié)束時(shí)間
- --base64-output=decode-row
- -v |-vv |-vvv |-vvvv 顯示詳細(xì)信息
[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') #改變數(shù)據(jù)的SQL語(yǔ)句 /*!*/; 事件發(fā)生的日期和時(shí)間：180611 20:59:46 事件發(fā)生的服務(wù)器標(biāo)識(shí)：server id 1 事件的結(jié)束位置：end_log_pos 528992 事件的類型：Query 事件發(fā)生時(shí)所在服務(wù)器執(zhí)行此事件的線程的ID：thread_id=29 語(yǔ)句的時(shí)間戳與將其寫入二進(jìn)制文件中的時(shí)間差：exec_time=0 錯(cuò)誤代碼：error_code=0 事件內(nèi)容： GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID
使用mysqlbinlog mariadb-bin.000001 > file.sql命令重定向到SQL文件中，再使用mysql -uroot -p < file.sql命令可以直接導(dǎo)入數(shù)據(jù)，實(shí)現(xiàn)了備份還原的功能
4、二進(jìn)制日志的管理
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; -- 刪除所有二進(jìn)制日志，index文件重新記數(shù) MariaDB [(none)]> FLUSH LOGS; -- 手動(dòng)觸發(fā)日志滾動(dòng)
七、中繼日志
relay log ：主從復(fù)制架構(gòu)中，從服務(wù)器用于保存從主服務(wù)器的二進(jìn)制日志中讀取到的事件
總結(jié)
本篇文章就到這里了，希望可以給你帶來(lái)一些幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容！
相關(guān)文章
Mysql Error Code : 1436 Thread stack overrun
I meet with the error while calling stored procedures from the MySql in my Mac system server. It similar as the description below2011-07-07
本文介紹了基于binlog和GTID的MySQL主從復(fù)制搭建步驟,包括準(zhǔn)備環(huán)境、創(chuàng)建授權(quán)賬戶、配置主從庫(kù)等,通過(guò)主從復(fù)制,可以實(shí)現(xiàn)數(shù)據(jù)的高可用、負(fù)載均衡和災(zāi)難恢復(fù),首先,確保環(huán)境一致性,然后在數(shù)據(jù)庫(kù)中創(chuàng)建具有復(fù)制權(quán)限的賬戶2024-10-10
mysql分區(qū)表的增刪改查的實(shí)現(xiàn)示例
增刪查改在數(shù)據(jù)庫(kù)中是很常見(jiàn)的操作,本文主要介紹了mysql分區(qū)表的增刪改查的實(shí)現(xiàn)示例,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-01-01
本文主要介紹了淺談MySQL中不等號(hào)索引問(wèn)題，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
這篇文章主要介紹了MySQL驗(yàn)證用戶權(quán)限的方法，需要的朋友可以參考下2015-11-11
這篇文章主要為大家詳細(xì)介紹了CentOS 6.4下編譯安裝MySQL 5.6.14教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
MYSQL數(shù)據(jù)庫(kù)主從同步設(shè)置的實(shí)現(xiàn)步驟
本文主要介紹了MYSQL數(shù)據(jù)庫(kù)主從同步設(shè)置的實(shí)現(xiàn)步驟，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-03-03
mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)
MySQL 中的 FIND_IN_SET 函數(shù)用于在逗號(hào)分隔的字符串列表中查找指定字符串的位置,本文就來(lái)介紹一下mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)2024-08-08
MySQL ERROR 1045 (28000) 錯(cuò)誤的解決辦法
這篇文章主要介紹了MySQL ERROR 1045 (28000) 錯(cuò)誤的解決辦法，很實(shí)用的解決方法，感興趣的小伙伴們可以參考一下2016-06-06
這篇文章主要介紹了MySQL 索引的一些細(xì)節(jié)分享，幫助大家更好的理解和使用MySQL中的索引，感興趣的朋友可以了解下2020-12-12