Slow Log
簡介
用于記錄執(zhí)行時間超過指定值的 SQL 語句的詳細信息，多用于調(diào)試和監(jiān)控皇耗。
配置
因為開啟會略微影響性能南窗，所以默認沒有開啟，所以需要配置廊宪。
查看是否開啟
show variables like '%slow%';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | .../slow.log |
+---------------------+-------------------------------------+
若沒有開啟矾瘾，在命令行執(zhí)行，注意給足文件所在目錄的權(quán)限箭启。
set global slow_query_log = on;
set global slow_launch_time= 1;
set global slow_query_log_file = /var/log/mysql/slow.log;
想要永久生效壕翩，可在/etc/my.cnf中寫入，注意給足文件所在目錄的權(quán)限傅寡。
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
打開日志驗證：
# Time: 2024-02-21T12:18:10.954969Z
# User@Host: root[root] @ localhost [::1] Id: 3
# Query_time: 20.001907 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1708517890;
select sleep(20);
Redo Log
簡介
重做日志放妈，記錄了數(shù)據(jù)庫中發(fā)生的每次修改，如增荐操、刪芜抒、改、對數(shù)據(jù)頁的更改托启。這些修改被記錄在 redo 日志中宅倒，以便在數(shù)據(jù)庫崩潰或意外關(guān)閉時能夠恢復(fù)到最近的一致狀態(tài)。
解決了什么問題
在宕機或者斷電等其它異常的情況下屯耸，確保數(shù)據(jù)的一致性拐迁，持久性，增加高可用性疗绣。
必須提前知道一些背景：讀數(shù)據(jù)頁時线召，需要把磁盤上的頁緩存到內(nèi)存中的buffer pool中，所有的變更多矮，也是先更新緩沖池缓淹，此時并沒有持久化到磁盤，稱之為臟頁，然后臟頁通過checkpoint機制去刷盤讯壶。
如果實時刷盤料仗，代價太大，用戶改了1字節(jié)的數(shù)據(jù)鹏溯，都需要整頁刷盤罢维，如果用戶改了大量的數(shù)據(jù)，就需要立即同步到多個頁上丙挽，隨機io性能低下肺孵。所以采用了非實時刷盤的策略。
假定一秒刷一次盤颜阐，第0.9秒時機器斷電或被強制停止平窘，這0.9秒的進度在內(nèi)存中就丟失了，為了保證高可用凳怨，所以MySQL采用redo log的方式來保證這0.9秒的進度不丟失瑰艘。
InnoDB采用的是WAL技術(shù)，在事務(wù)提交時先寫入日志（Redo Log）記錄事務(wù)所做的修改操作肤舞，再寫磁盤紫新，如果期間斷電，下次啟動時李剖，也可以使用redo log來恢復(fù)數(shù)據(jù)進度芒率。
注意上面的事務(wù)提交，不僅僅是顯式聲明的事務(wù)篙顺，只要auto commit已打開偶芍，每個單獨的DML語句（增、刪德玫、改）通常會被視為一個獨立的事務(wù)匪蟀。
寫redo log的代價比同步臟頁到磁盤的代價要小。
配置
查看內(nèi)存上的重做緩沖大性咨（默認16MB材彪，最大4096MB，最小1MB）：
select @@innodb_log_buffer_size; 或 show variables like 'innodb_log_buffer_size';
設(shè)置內(nèi)存上的重做緩沖大星俣（低版本只讀段化，不支持修改）：
set global innodb_log_buffer_size = 16777216 ;
也可在配置文件中
[mysqld]
innodb_log_buffer_size = 16777216
架構(gòu)
組成：
redo log可以分為兩部分，內(nèi)存上的的重做日志緩沖凤类，和磁盤上的重做日志文件。
磁盤上的重做日志在設(shè)置的datadir目錄下普气，通常叫做ib_logfile0谜疤，ib_logfile1，兩個大小一致的文件（默認48MB）。
執(zhí)行流程夷磕，假設(shè)執(zhí)行update語句：
- 先寫redo log到redo log buffer履肃。
- 更新內(nèi)存中的數(shù)據(jù)頁（改的是SQL，不是redo）坐桩。
- 事務(wù)提交時尺棋，用追加寫的方式，將內(nèi)存中的重做日志緩沖绵跷，寫入重做日志（改的是redo膘螟，不是SQL）。
- 通過一些checkpoint策略碾局，定期將內(nèi)存中修改的update數(shù)據(jù)刷新到磁盤（改的是SQL荆残，不是redo）。
從redo log buffer到 redo log file的刷盤策略
從redo log寫入磁盤净当，MySQL提供了一個線程來處理redo log 的寫入操作内斯，可以減少對主線程的影響。
通過 innodb_flush_log_at_trx_commit配置來變更刷盤策略像啼，默認為1俘闯，安全性最好，性能最差忽冻。
0 ：不管事務(wù)是否提交真朗，每隔1秒將redo log buffer寫入到 redo log file。
1 ：每次事務(wù)提交時甚颂，都將redo log buffer寫入到 redo log file蜜猾。
2 ：每次事務(wù)提交時，只把redo log buffer寫入操作系統(tǒng)的page cache（page cache的控制權(quán)是操作系統(tǒng)振诬，page cache也是內(nèi)存蹭睡，此舉是讓操作系統(tǒng)自主控制從內(nèi)存刷入磁盤的動作），這個階段MySQL的任務(wù)完成了赶么，所以MySQL掛了不會丟數(shù)據(jù)肩豁，服務(wù)器系統(tǒng)掛了會丟失數(shù)據(jù)。
注意辫呻，修改這個值為0清钥，會極大提高MySQL InnoDB引擎的寫入速度，適合對對事務(wù)持久性要求較低的場景放闺。
用插入數(shù)據(jù)實測（不想開虛擬機祟昭，用的windows），改成0的速度比改成2快2倍怖侦，比改成1快57倍篡悟。
Undo Log
簡介
回滾日志谜叹，用于記錄事務(wù)所做的更改，以便在事務(wù)回滾或發(fā)生回滾操作時能夠撤銷事務(wù)中的修改搬葬。
如果使用undo log回滾荷腊，這個回滾的動作，也會產(chǎn)生redo log急凰，用于保證數(shù)據(jù)的高可用女仰。
解決了什么問題
如果一個事務(wù)執(zhí)行了一部分，突然SQL執(zhí)行出錯了抡锈，進程被強制干掉了疾忍，斷電了，或者回滾企孩，這種情況下沒有undo log锭碳，則無法完成原子性回滾操作。
提供MVCC的支持勿璃。
架構(gòu)
組成：
日志通常在ibdata1文件里擒抛。
相關(guān)配置：
innodb_undo_directory: undo日志文件的存儲路徑，默認為 ./补疑，表示與數(shù)據(jù)文件存儲在同一目錄下歧沪。
innodb_undo_tablespaces: undo表空間的數(shù)量，默認為 2莲组。每個undo表空間包含多個undo日志文件诊胞，可以根據(jù)需要增加或減少undo表空間的數(shù)量。
innodb_max_undo_log_size: 單個undo日志文件的最大大小锹杈，默認為 10MB撵孤。可以根據(jù)實際需求調(diào)整此參數(shù)竭望，適當(dāng)增大以減少 UNDO 日志文件的頻繁創(chuàng)建和刪除邪码。
innodb_undo_logs: UNDO 日志的數(shù)量，默認為 128咬清”兆ǎ可以根據(jù)系統(tǒng)負載和事務(wù)頻率適當(dāng)增加undo日志的數(shù)量，以提高并發(fā)處理能力旧烧。
innodb_undo_log_truncate: 控制是否開啟 undo日志的截斷功能影钉，默認為 on。開啟后掘剪，可以定期截斷舊的undo日志平委，釋放空間并提高性能。
執(zhí)行流程夺谁，假設(shè)執(zhí)行update語句：
- 定位要更新數(shù)據(jù)廉赔，將其加入到緩沖池中愚墓。
- 將舊的數(shù)據(jù)寫入undo log，為數(shù)據(jù)回滾做準備昂勉。
- 更新緩沖池中的數(shù)據(jù)（SQL）。
- 隨后記錄redo log扫腺，走redo log那套邏輯岗照。
[圖片上傳失敗...(image-28e275-1709958103424)]
Bin Log
簡介：
二進制日志，記錄所有DDL笆环、DML的日志攒至，用于主從服務(wù)器之間的數(shù)據(jù)同步。
也可用于MySQL意外停止情況下的數(shù)據(jù)恢復(fù)躁劣。
解決了什么問題
沒它做不了主從復(fù)制迫吐。
binlog做數(shù)據(jù)備份與恢復(fù)
配置
查看相關(guān)配置
show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------+
各個字段含義：
log_bin：是否啟用binlog。
log_bin_basename：二進制日志文件目錄账忘。
log_bin_index：二進制日志索引文件的路徑志膀。
log_bin_trust_function_creators：是否信任二進制日志中的函數(shù)創(chuàng)建者，例如在主機上執(zhí)行now();和從機上執(zhí)行now();有一些時間誤差鳖擒。
log_bin_use_v1_row_events：值為0表示正在使用版本2的行事件格式溉浙，值為1表示正在使用版本1的行事件格式。版本2比版本1提供了更好的性能和更好的可讀性蒋荚。
推薦直接改配置文件戳稽，以下簡單的幾行配置就可以，如果需更多的自定義配置期升，可以再添加
vim /etc/my.cnf
[mysqld]
server_id=1 設(shè)置 MySQL 服務(wù)器唯一 ID惊奇，每個服務(wù)器要求唯一的 ID
log_bin=mysql-bin 啟用二進制日志并設(shè)置日志文件名
binlog_format=ROW 設(shè)置二進制日志格式
expire_logs_days=7 設(shè)置二進制日志過期時間，單位為天
保存完后重啟播赁，然后在data目錄就有mysql-bin.000001文件了颂郎。
使用binlog恢復(fù)數(shù)據(jù)
根據(jù)時間恢復(fù)（用當(dāng)前時區(qū)就好）：
mysqlbinlog --start-datetime="開始時間年月日時分秒" --stop-datetime="結(jié)束時間年月日時分秒" binlog文件 | mysql -u用戶名 -p密碼 -v 數(shù)據(jù)庫名
可加--database=數(shù)據(jù)庫名，限制數(shù)據(jù)庫按偏移量恢復(fù)（不建議用行拢，pos偏移量不容易定位）：
查看偏移量：show binlog events in "binlog文件";
mysqlbinlog --start-position=偏移量數(shù)字 --stop-position=偏移量數(shù)字 binlog文件 | mysql -u用戶名 -p密碼 -v 數(shù)據(jù)庫名
可加--database=數(shù)據(jù)庫名祖秒，限制數(shù)據(jù)庫。
日志操作
- 新增：每重啟一次舟奠，就會新增一個binlog文件竭缝。
- 刪除（按文件名）：
purge master logs to 'binlog文件名'刪除的是小于此編號名稱的binlog文件。 - 刪除（按時間）：
purge master logs before 'YYYY-MM-DD HH:II:SS'刪除的是小于此編號名稱的binlog文件沼瘫。 - 修改：直接運行DDL或DML語句吧抬纸。
- 查看：
show binary logs;
兩階段提交
redo log事務(wù)執(zhí)行時就會寫入，binlog事務(wù)提交時才會寫入耿戚，寫入的時機不一樣湿故，小概率事件阿趁，會造成數(shù)據(jù)不一致的問題。假如寫完redo log后bin log寫入異常坛猪，這可能導(dǎo)致主機數(shù)據(jù)正常篙悯，但是從機數(shù)據(jù)不一致虐秋。
可以使用兩階段提交的方式，就是說寫入bin log前后分別進行redo log寫入，但是redo log明明只需寫入一次就夠了澜沟，兩次又怎么處理呢垢箕？
prepare redo log階段會寫入日志控嗜，然后等bin log執(zhí)行完畢后庸推，在commit redo log階段判斷 bin log是否寫入成功，如果沒有寫入成功洋机，回滾redo log坠宴，重新走以上流程。
Relay Log
簡介：
MySQL的主從復(fù)制绷旗，主服務(wù)器會把自己的binlog發(fā)送到從服務(wù)器喜鼓。從服務(wù)器會將接收到的binlog后，會寫入自己的中繼日志中衔肢，然后再應(yīng)用到自己的數(shù)據(jù)庫中颠通。
相當(dāng)于快遞送到了驛站。
中繼日志只有從服務(wù)器有膀懈，默認在data目錄下顿锰。
General log
簡介：
普通日志，記錄日常執(zhí)行過的命令启搂，默認關(guān)閉硼控，不建議開啟，因為每執(zhí)行一個SQL就記錄一條日志胳赌，占空間還降低性能牢撼。
若真想做SQL的調(diào)試，推薦使用編程框架的機制疑苫，自定義封裝一個日志功能熏版，因為MySQL日志屬于運維層，框架日志屬于開發(fā)層捍掺，更容易自定義和接觸的到撼短。
解決了什么問題
配合業(yè)務(wù)做數(shù)據(jù)庫調(diào)試使用。
配置
查看相關(guān)狀態(tài)
show variables like 'general_log%'
+------------------+--------------------------------+
| Variable_name | Value |
+------------------+--------------------------------+
| general_log | OFF |
| general_log_file | .... /你的用戶名.log |
+------------------+--------------------------------+
臨時修改全局配置
set global general_log = on;
set global general_log_file = path;
持久化配置
vim /etc/my.cnf
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql.log
示例日志如下挺勿，格林尼治時間
2024-03-04T18:07:35.824760Z 8 Query select * from cs
2024-03-04T18:08:27.707305Z 8 Query select * from cs
2024-03-04T18:08:28.136048Z 8 Query select * from cs
2024-03-04T18:08:28.477068Z 8 Query select * from cs
Error log
簡介
錯誤日志就是出錯的時候出現(xiàn)的日志曲横，對于異常排查，十分有用。
解決了什么問題
運維禾嫉，服務(wù)器管理人員灾杰，做排查使用。
配置
show variables like 'log_error%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| log_error | ... /你的用戶名.err |
| log_error_verbosity | 3 |
+---------------------+--------------------------------+
log_error_verbosity是錯誤的等級
1：記錄錯誤信息熙参，但不包含錯誤堆棧跟蹤艳吠。
2：記錄錯誤信息，并包括簡短的錯誤堆棧跟蹤孽椰。
3：記錄錯誤信息讲竿，并包括完整的錯誤堆棧跟蹤和源代碼行號。
內(nèi)容如下：
2023-01-02T06:57:05.481440Z 0 [Note] ...mysqld (mysqld 5.7.24) starting as process 11088 ...
2023-02-18T15:51:49.645048Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2023-02-18T15:51:49.645900Z 0 [Note] ...mysqld (mysqld 5.7.24) starting as process 4924 ...
補充：
Redo Log對比Undo Log
作用差不多弄屡，但是側(cè)重點不同。
redo log：偏向物理操作的日志鞋诗，比如針對頁的操作膀捷，保證事務(wù)的持久性。
undo log：偏向邏輯操作的日志削彬，記錄的是每個增全庸、刪、改的逆向操作融痛，以便于事務(wù)回滾壶笼，保證事務(wù)的原子性。
Redo/Undo Log對比Bin log
Redo log對比Bin log雁刷，都有持久化的功能覆劈，但是側(cè)重點不一樣，redo log偏向回滾的備份沛励，bin log偏向主從賦值责语，DML、DDL操作的恢復(fù)目派。
redo log事務(wù)執(zhí)行時就會寫入坤候，binlog事務(wù)提交時才會寫入。
redo和undo企蹭，對比bin白筹，前者是為了保證高可用，防止進度丟失谅摄，后者也是為了數(shù)據(jù)備份徒河，但是也偏向主從復(fù)制。
Redo log送漠，與DML語句結(jié)果刷盤順序虚青？
先寫redo日志到磁盤，在把sql執(zhí)行后要寫入的數(shù)據(jù)寫入到磁盤中螺男。
如果Redo log準備寫入磁盤時斷電棒厘，會導(dǎo)致進度丟失嗎纵穿？
這會導(dǎo)致當(dāng)前執(zhí)行的sql無效， 這個過程SQL 語句的修改結(jié)果奢人，并未寫入到磁盤谓媒。
所以為了保證高可用，推薦UPS不間斷電源何乎，通過硬件層面的加持句惯，達到高可用。
