文章目錄
-
- 日志分類
- 一、mysql日志分類
- 二、錯誤日志
-
- 1、查詢錯誤日志存放路徑
- 2、配置錯誤日志（預設是啟用的）
- 3、錯誤日志參數控制
- 4、錯誤日志記錄的資訊
- 三、mysql事務的日志
-
- 【redo】
- 【undo】
- 四、一般查詢日志
- 五、慢查詢日志
-
- 【慢日志的概述】
- 1、慢查詢日志的作用
- 2、慢日志的的查詢
- 3、慢查詢日志的配置
- 4、測試慢日志
- 六、二進制日志（bin log）
-
- 1、二進制日志概述（binlog）
- 2、二進制日志的作用 （binlog）
- 【二進制日志的工作模式】
-
- 1、語句模式：statement
- 2、行級模式：ROW
- 3、混合模式：mixed
- 【binlog的相關概念】
- 【binlog相關參數】
-
- 1、server_id
- 2、 log-bin
- 3、 log_bin_index
- 4、 sql_log_bin
- 5、binlog_format
- 6、max_binlog_size
- 7、sync_binlog
- 8、其他參數
- 【檢視binlog配置項目】
- 【開啟binlog】
- 【檢視binlog日志】
- 【使用binlog恢複資料】
- 【重新整理與清除binlog日志】
-
- 1、重新整理binlog
- 2、清除binlog
- 【日志總述】
-
- 【二進制日志】
日志分類
一、mysql日志分類
日志是MySQL資料庫的重要組成部分。日志檔案中記錄着MySQL資料庫運作期間發生的變化；也就是說用來記錄MySQL資料庫的用戶端連接配接狀況、SQL語句的執行情況和錯誤資訊等。當資料庫遭到意外的損壞時，可以通過日志檢視檔案出錯的原因，并且可以通過日志檔案進行資料恢複
[email protected]日志管理（錯誤日志、一般查詢日志、慢查詢日志、binlog日志）
二、錯誤日志
預設情況下，錯誤日志是開啟的，且無法被禁止。預設情況下，錯誤日志是存儲在資料庫的資料檔案目錄中，名稱為hostname.err，其中，hostname為伺服器主機名
1、查詢錯誤日志存放路徑
#方式一：
[[email protected] ~]# mysqladmin -uroot -p"[email protected]" variables | grep -w log_error
| log_error | /var/log/mysqld.log
#方式二：
mysql> show variables like "%log_error%";
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
+---------------------+---------------------+
3 rows in set (0.00 sec)
2、配置錯誤日志（預設是啟用的）
#自定義錯誤日志位置
[[email protected] ~]# vim /etc/my.cnf
[mysqld]
#絕對路徑
log_error=/var/log/mysql.errlog
#相對路徑
#log_error=mysql.errlog
#建立錯誤日志檔案
[[email protected] ~]# touch /var/log/mysql.errlog
[[email protected] ~]# chmod 640 /var/log/mysql.errlog
[[email protected] ~]# chown mysql.mysql /var/log/mysql.errlog
#重新開機資料庫
[[email protected] ~]# systemctl restart mysqld
3、錯誤日志參數控制
MySQL中，其中log_error定義是否啟用錯誤日志的功能和錯誤日志的存儲位置
此外還可以用參數控制,是否将告警資訊（warning messages）也寫入錯誤日志。
在不同的版本中改控制參數有所不同
#MySQL 5.6中用log_warnings參數控制
log_warnings=0， #表示不記錄告警資訊。
log_warnings=1 #(預設值)表示告警資訊寫入錯誤日志。
log_warnings # 大于1，表示各類告警資訊，例如有關網絡故障的資訊和重新連接配接資訊寫入錯誤日志
#MySQL5.7新增的log_error_verbosity參數
#它有三個可選值， 分别對應：
log_error_verbosity=1 #錯誤資訊；
log_error_verbosity=2 #錯誤資訊和告警資訊；(推薦)
log_error_verbosity=3 #（預設值就是3）錯誤資訊、告警資訊和通知資訊
4、錯誤日志記錄的資訊
1)#伺服器啟動和關閉過程中的資訊
未必是錯誤資訊，比如mysql是如何去初始化存儲引擎的過程記錄在錯誤日志裡等等
2)#伺服器運作過程中的錯誤資訊
比如sock檔案找不到，無法加載mysql資料庫的資料檔案，如果忘記初始化mysql或data dir路徑找不到，或權限不正确等 都會記錄在此
3)#事件排程器運作一個事件時産生的資訊
一旦mysql排程啟動一個計劃任務的時候，它也會将相關資訊記錄在錯誤日志中
4)#在從伺服器上啟動從伺服器程序時産生的資訊
在複制環境下，從伺服器程序的資訊也會被記錄進錯誤日志
三、mysql事務的日志
【mysql事務日志詳解】
【redo】
記錄的是尚未完成的操作，斷電則用其重做
#redo即redo日志:
是用于記錄資料庫中資料變化的日志，隻要你修改了資料塊那麼就會記錄redo資訊，當然nologging除外了。
你的每次操作都會先記錄到redo日志中，當出現執行個體故障（像斷電），導緻資料未能更新到資料檔案，則資料庫重新開機時須redo，重新把資料更新到資料檔案
【undo】
記錄的改動之前的舊資料，一旦改錯，可以復原
#undo即undo段：
是指資料庫為了保持讀一緻性，存儲曆史資料在一個位置。
用于記錄更改前的一份copy，用于復原、撤銷還原
四、一般查詢日志
1)#預設是關閉的
一般不會開啟，因為哪怕你開啟事務一頓操作，最後不送出也會記錄，生産上程式跑sql很多，會非常非常占地方，從來都不啟動，要看操作去binlog
2)#配置檔案開啟
#方式一：
[[email protected] ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log
#方式二：
#指令行設定：可以使用set global general_log=on;設定
#檔案建立并授權
[[email protected] ~]# touch /var/log/select.log
[[email protected] ~]# chmod 640 /var/log/select.log
[[email protected] ~]# chown mysql.mysql /var/log/select.log
[[email protected] ~]# systemctl restart mysqld
3)#檢視一般查詢日志
#方式一：
[[email protected] ~]# mysqladmin -uroot [email protected] variables|grep general_log
#方式二：
[[email protected] ~]# mysql -uroot [email protected]
mysql> show variables like '%gen%';
+----------------------------------------+--------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------+
| auto_generate_certs | ON |
| general_log | OFF |
| general_log_file | /var/lib/mysql/web01.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+--------------------------+
4 rows in set (0.00 sec)
五、慢查詢日志
【慢日志的概述】
1> 慢查詢會導緻CPU，IOPS，記憶體消耗過高。當資料庫遇到性能瓶頸時，大部分時間都是由于慢查詢導緻的。 開啟慢查詢日志，可以讓MySQL記錄下查詢超過指定時間的語句，之後運維人員通過定位分析，能夠很好的優化資料庫性能
2> 慢查詢日志記錄的慢查詢不僅僅是執行比較慢的SELECT語句，還有INSERT，DELETE，UPDATE，CALL等DML操作，隻要超過了指定時間，都可以稱為"慢查詢"，被記錄到慢查詢日志中
3> 預設情況下，慢查詢日志是不開啟的，隻有手動開啟了，慢查詢才會被記錄到慢查詢日志中
1、慢查詢日志的作用
1）#将mysql伺服器中影響資料庫性能的相關SQL語句(所有語句，增删改查)記錄到日志檔案
2）#通過對這些特殊的SQL語句分析并改進，提高資料庫性能
2、慢日志的的查詢
mysql> show variables like "%slow%";
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/slow.log |
+---------------------------+-------------------+
5 rows in set (0.00 sec)
3、慢查詢日志的配置
#配置檔案慢日志預設是不開啟的
[[email protected] ~]# vim /etc/my.cnf
[mysqld]
#指定是否開啟慢查詢日志
slow_query_log = 1
#指定慢日志檔案存放位置（預設在data）
slow_query_log_file=/var/log/slow.log
#設定慢查詢的閥值(預設10s)
long_query_time=0.05
#不使用索引的慢查詢日志是否記錄到日志
log_queries_not_using_indexes=ON
#查詢檢查傳回少于該參數指定行的SQL不被記錄到慢查詢日志，少于100行的sql語句查詢慢的話不記錄，一般不使用
#min_examined_row_limit=100
#日志檔案建立并授權
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld
4、測試慢日志
######################## 測試慢日志 #########################
#方式一：
測試:BENCHMARK(count,expr)
mysql> SELECT BENCHMARK(50000000,2*3);
+-------------------------+
| BENCHMARK(50000000,2*3) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.51 sec)
#執行卡死，檢視執行的sql執行時間，如果停不下來 可以 kill id
show processlist;
kill 3;
#方式二：
#循環插入資料
insert city_new select * from city;
insert city_new select * from city_new;
insert city_new select * from city_new;
insert city_new select * from city_new;
######################## 查詢日志 #########################
#方式一：
#檢視慢日志可以使用
cat /var/log/slow.log
#實時檢視狀态
tail -f /var/log/slow.log
#方式二：
#查詢得到按照時間排序的前10條裡面含有左連接配接的查詢語句
mysqldumpslow -s r -t 10 /var/log/slow.log
#參數說明:
-s:指定排序方式
c、t、l、r分别是按照記錄次數、時間、查詢時間、
傳回的記錄數來排序，ac、at、al、ar，表示相應的倒叙
-t:是top n的意思，即為傳回前面多少條的資料；
-g:後邊可以寫一個正則比對模式，大小寫不敏感的；
#方式三：
#使用軟體查詢
yum provides pt-query-digest #pt-query-digest 是分析MySQL查詢日志最有力的工具，該工具功能強大，它可以分析binlog，Generallog，slowlog，也可以通過show processlist或者通過 tcpdump 抓取的MySQL協定資料來進行分析，比 mysqldumpslow 更具體，更完善
#直接分析慢查詢檔案
pt-query-digest slow.log > slow_report.log #該工具可以将查詢的剖析報告列印出來，可以分析結果輸出到檔案中，分析過程是先對查詢語句的條件進行參數化，然後對參數化以後的查詢進行分組統計，統計出各查詢的執行時間，次數，占比等，可以借助分析結果找出問題進行優化
六、二進制日志（bin log）
二進制日志即binlog日志，記錄了mysql資料庫所有的dml,ddl語句事件（不包含select）。記錄增删改，也可以記錄SQL語句及行記錄變化，還可以記錄這些操作事件；總之會記錄所有修改操作的SQL語句
1、二進制日志概述（binlog）
1> MySQL的二進制日志（bin log）是一個二進制檔案，主要記錄所有資料庫表結構變更（例如CREATE、ALTER TABLE…）以及表資料修改（INSERT、UPDATE、DELETE…）的所有操作。
2> 二進制日志（bin log）中記錄了對MySQL資料庫執行更改的所有操作，并且記錄了語句發生時間、執行時長、操作資料等其它額外資訊，但是它不記錄SELECT、SHOW等那些不修改資料的SQL語句
#不要混淆以下三種日志：
（1）general log：一般日志，記錄資料庫裡的所有SQL操作記錄
（2）redo log：隻記錄innodb存儲引擎的修改日志
（3）binlog：隻記錄server層面内部的修改情況。--select /show 不記錄
2、二進制日志的作用 （binlog）
1）恢複（recovery）：某些資料的恢複需要二進制日志。例如，在一個資料庫全備檔案恢複後，使用者可以通過二進制日志進行point-in-time的恢複
2）複制（replication）：其原理與恢複類似，通過複制和執行二進制日志使一台遠端的MySQL資料庫（一般稱為slave或者standby）與一台MySQL資料庫（一般稱為master或者primary）進行實時同步
3）審計（audit）：使用者可以通過二進制日志中的資訊來進行審計，判斷是否有對資料庫進行注入攻擊
#開啟binlog的好處
（1）#資料恢複：可以基于時間點恢複，以及根據其進行增量與差異備份
（2）#mysql主從複制，通過binlog實作資料複制
【二進制日志的工作模式】
binlog格式分為: STATEMENT、ROW和MIXED三種：
1、語句模式：statement
binlog_format=statement（Mysql5.7.6之前的預設級别）
記錄對資料庫做出修改的sql語句,不記錄該sql的上下文資訊
#語句模式介紹:
STATEMENT格式的binlog記錄的是資料庫上執行的原生SQL語句,這種方式有好處也有壞處
好處就是相當簡單，簡單地記錄和執行這些語句，能夠讓主備保持同步，在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。另一個好處是二進制日志裡的時間更加緊湊，是以相對而言，基于語句的複制模式不會使用太多帶寬，同時也節約磁盤空間
壞處就是同一條SQL在主庫和從庫上執行的時間可能稍微或很大不相同，是以在傳輸的二進制日志中，除了查詢語句，還包括了一些中繼資料資訊，如目前的時間戳。即便如此，還存在着一些無法被正确複制的SQ
#例如:
1)#開啟binlog日志後，我們在某個庫下自定義函數，若想定義成功需要先設定配置項
set global log_bin_trust_function_creators=TRUE;
2)#然後自定義函數
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
3)#最後執行一條插入語句
insert into t1(name) values(concat("hzl",f1(1,2)));
如果使用statement模式，那麼這條insert語句将會被記錄到二進制日志中,而sql語句中依賴的f1
的定義是不會記錄下來的，f1隻存在于目前庫
############################### 缺點 ############################
不需要記錄細到每一行資料的更改變化，是以，binlog日志量小，IO壓力小，性能較高
#例如:
一條語句修改了100萬行，該模式下隻需要記錄下該條語句即可
############################### 優點 ############################
日志中記錄的sql語句可能有上下文依賴，此時脫離了目前資料庫環就無法運作了，是以該模式下容易
出現主從不一緻的問題。
#例如：
主庫記錄的某條sql語句引用了主庫中的函數、觸發器、存儲過程等特殊功能，在從庫上接收了該sql之後，可能就無法正确運作，進而主從庫資料不一緻的問題。
ps ：row模式是基于每一行來記錄變化的，是以不會出現類似的問題。
#應用場景
sql語句對mysql内置功能依賴比較少：不使用存儲過程/觸發器/函數，可以使用該模式，否則還是推薦行級模式
2、行級模式：ROW
binlog_format=row（mysql5.7.6之後+8.0的預設級别）
ROW格式的二進制日志基本是标配了，主要是因為它的優勢遠遠大于缺點。并且由于ROW格式記錄行資料，是以可以基于這種模式做一些DBA工具，比如資料恢複，不同資料庫之間資料同步等
#行級模式介紹
記錄每一行資料修改的細節，即哪一條記錄被修改了，修改成什麼樣了
#例如：執行語句（f1參照上例，此處略）
insert into t1(name) values(concat("hzl",f1(1,2)));
如果使用row模式，那麼日志中會記錄插入了一條新記錄，記錄中的name字段值為’hzl'
############################### 優點 ############################
相當于把上下文依賴都記錄了下來，可以更友善檢視每一條資料修改的細節，并且不會出現某些特定情況下的存儲過程或function以及trigger的調用和觸發無法被正确複制的問題，即該模式下主從複制強一緻，資料最安全
############################### 缺點 ############################
日志量大
#例如
一條語句修改了100萬行，語句模式下隻需要記錄一條語句即可
而行級模式卻修改記錄下100萬行的修改記錄，binlog日志的量可能會大得驚人。
#應用
sql語句對mysql内置功能依賴比較多，希望資料最安全，複制強一緻的場景推薦行級模式
3、混合模式：mixed
binlog_format=mixed，一般不用
MIXED也是MySQL預設使用的二進制日志記錄方式，但MIXED格式預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。比如用到UUID()、USER()、CURRENT_USER()、ROW_COUNT()等無法确定的函數
#混合模式介紹
結合 row level 與statement level的優點
混合（mixed-based）模式預設采用語句模式記錄日志，在一些特定的情況下會将記錄模式切換為行級模式記錄，這些特殊情況包含但不限于以下情況
•當函數中包含UUID()時。
•當表中有自增列（AUTO_INCREMENT）被更新時。
•當執行觸發器（trigger）或者存儲過程（stored function）等特殊功能時。
•當FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等執行時。
#應用場景
看上去這種方式似乎比較美好，但是在生産環境中，為了保險起見，一般會使用row模式。
【binlog的相關概念】
二進制日志檔案，顧名思義，它是二進制的，是以我們不能直接使用cat指令進行檢視，而是需要通過一些别的指令檢視其内容，而且，二進制日志檔案，有”事件”和”位置”的概念
(1)# 事件（events）
通俗的講，我們可以把binlog中的每一條記錄當做一個”事件”
因為binlog記錄了所有對資料庫進行的修改，是以，我們可以認為，資料庫的修改被記錄到二進制日志中，這些記錄每一條都可以了解為一個”事件”。
(2)# 位置（position）
由于二進制日志檔案是二進制的，是以，我們可以把整個二進制檔案想象成一個位元組序列。
假設，二進制日志檔案剛開始是空的，從第1個位元組開始記錄，假設記錄第一個”事件”（第一條記錄），需要15個位元組，那麼第一個事件的開始”位置”就是1，結束”位置”就是15，由于前15個位元組已經被第一個事件占用，那麼當我們想要通過二進制日志記錄第二個事件時，則需要從第15個位元組向後開始記錄，假設記錄第二個”事件”需要20個位元組，那麼第二個事件在binlog中的起始”位置”就是15，結束”位置”就是35，以此類推
【binlog相關參數】
1、server_id
服務ID,主從庫必須不一樣，建議數字為:ip+端口，5.7.3以後版本,必須指定
2、 log-bin
此變量用于控制是否開啟二進制日志，而且這是一個隻讀變量，預設值為OFF
當我們啟動資料庫以後，在目前資料庫連接配接中檢視此變量的值，此變量值可能為OFF，表示不記錄二進制日志，如果想要記錄二進制日志，隻需将此值設定為二進制日志的檔案名即可
但是需要注意的是，我們無法在目前會話中使用set指令設定log_bin的值，因為它是一個隻讀變量，我們隻能通過修改my.cnf的方式，設定log_bin的值
假設，我們編輯my.cnf檔案，設定log_bin的值為mybinlog，那麼，在mysql的資料目錄中，将會自動生成一個以mybinlog為檔案名字首的二進制日志檔案
如果想要再次禁用binlog，隻需要将log_bin這一行配置從my.cnf檔案中注釋即可，或者将其删除，重新開機mysql服務後，再次檢視log_bin的值，其值為OFF，注意，不要直接在my.cnf檔案中将log_bin的值，設定為ON或者OFF，如果這樣做，你将會看到以ON或者OFF為檔案名字首的二進制日志檔案。換句話說，如果my.cnf配置檔案中沒有log_bin的配置，則表示未開啟二進制日志，如果my.cnf中存在log_bin的配置，那麼則表示開啟了二進制日志，同時二進制日志檔案的名稱将會以log_bin對應的值為檔案名字首，同時，二進制日志檔案的字尾名會進行自動編号，每次日志滾動後，字尾名編号自動加1。
#示例：
log-bin=/var/lib/mysql/mybinlog #絕對路徑
#log-bin=mybinlog #也可以用相對路徑，會在$datadir下産生mysqlbinlog-00000N
3、 log_bin_index
不設定的話，會根據log_bin值名稱自動生成mybinlog.index
log_bin_index=var/lib/mysql/mybinlog.index
4、 sql_log_bin
預設為ON
此變量用于辨別目前會話中的操作是否會被記錄于二進制日志
此變量值設定為ON，則表示在目前資料庫連接配接中，對資料庫進行修改的語句将會被記錄到binlog中，此變量值設定為OFF，則表示在目前資料庫連接配接中，對資料庫進行的修改的語句将不會被記錄到binlog中
在主從複制結構中，這些語句由于沒有被binlog記錄，是以也不會同步到從節點中。換句話說，即使在my.cnf配置檔案中設定了log_bin的值，目前會話中，如果sql_log_bin的值設定為OFF，目前會話的操作也不會記錄在二進制日志中。
而且需要注意的是，sql_log_bin是一個會話界别的變量，隻能在目前會話中使用set sql_log_bin指令進行設定，不能使用set global sql_log_bin指令進行設定，因為它是會話級别的變量，而且，sql_log_bin也不能配置在my.cnf檔案中，否則可能會無法啟動mysql。
5、binlog_format
此變量值得含義上文已經解釋過，此變量的值決定了二進制日志的記錄方式，此變量的值可以設定為statement、row、mixed，分别表示以語句的形式記錄二進制日志，以資料修改的形式記錄二進制日志，以混合的方式記錄二進制日志，安全保險起見，推薦使用row的方式記錄
6、max_binlog_size
設定單個二進制日志檔案的最大大小，以位元組為機關，超過此值大小，則二進制日志檔案會自動滾動
比如設定為500M為524288000
7、sync_binlog
innodb_flush_log_at_trx_commit控制redo日志的刷盤政策，建議值為1
sync_binlog控制binlog日志的刷盤政策，建議值為1
當我們把innodb_flush_log_at_trx_commit設定為1的時候，表示事務送出時，事務日志立馬從記憶體刷寫到磁盤中的事務日志檔案中，而sync_binlog對于二進制日志的作用，就像innodb_flush_log_at_trx_commit對于事務日志的作用，由于二進制日志一開始存在于記憶體（binlog_cache）中，如果将sync_binlog設定為1，則表示每1次事務送出之後，都會立即将記憶體中的二進制日志立即同步到磁盤中的二進制日志檔案中
如果将sync_binlog設定為0，則表示當事務送出之後，mysql不會立即将記憶體中的binlog刷寫到磁盤中的binlog日志檔案中，而是由檔案系統決定什麼時候刷寫，這可能取決于檔案系統的緩存機制，當此值設定為0時，一旦作業系統當機，那麼将丢失未從記憶體中同步到磁盤中的binlog
是以，當此值設定為0時，安全性最差，但是性能最高，當此值設定為1時，安全性最高，性能最差，除了将此值設定為0或1，還能設定為N，假設将此值設定為3，則表示每3次事務送出後，将binlog從記憶體刷寫到磁盤一次，值設定的越大，有可能丢失的日志資料将會越多
當然，性能會越好，在追求安全的情況下，推薦設定為1，但是聽說，此值設定為0和設定為1時在性能上的差距還是比較明顯的，如果設定為0或N，最好為作業系統準備帶有備用電源的緩存
8、其他參數
#打開才能檢視詳細記錄，預設為off
binlog_rows_query_log_events=on
#表示自動删除10天以前的日志
expire_logs_days=10
#full,minimal,noblob分别表示binlog中内容全記錄，隻記錄被操作的，和不記錄二進制
binlog_row_image=full #（full,minimal,noblob）
【檢視binlog配置項目】
#方式一：
[[email protected] ~]# mysqladmin -uroot [email protected] variables |grep -w log_bin #不進入資料庫進行檢視日志
#方式二：
#在資料指令行進行檢視
show variables like '%log_bin%';
show variables like '%binlog%';
show variables like '%binlog_format%';
show variables like '%server%';
show variables like 'expire_logs_days'; -- #過期日志天數
【開啟binlog】
1> 預設是關閉的
2> 配置開啟binlog
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog
binlog_format='row' #(row,statement,mixed)，不建議随意去修改binlog工作模式
binlog_rows_query_log_events=on
max_binlog_size=100M
#日志檔案建立并授權 (檔案不存在需要手動建立)
touch /var/lib/mysql/mybinlog
chmod 640 /var/lib/mysql/mybinlog
chown mysql.mysql /var/lib/mysql/mybinlog
systemctl restart mysqld
【檢視binlog日志】
檢視日志日志名、狀态、事件
show binary logs;
show master logs;
show master status;
show binlog events in 'mybinlog.000002';
show binlog events in 'mybinlog.000002' limit 3;
檢視日志内容
1）#執行下述sql
create database if not exists test;
use test;
create table user(name varchar(10));
begin; #開啟一個事務
insert into user values("hzl1"),("hzl2"),("hzl3");
update user set name="HZL" where name="hzl1";
commit; #送出事務
2）#檢視事務狀态
show master status; -- binlog的目前position
檢視binlog，看看有沒有記錄，二進制檔案我們看不了，有專門的指令
檢視全部：
#mysqlbinlog mybinlog.000002
#按時間：
mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56"
mysqlbinlog mybinlog.000002 --stop-datetime="2022-11-05 11:02:54"
mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56" --stop-datetime="2022-11-05 11:02:54"
#按位元組數：
mysqlbinlog mybinlog.000002 --start-position=337
mysqlbinlog mybinlog.000002 --stop-position=662
mysqlbinlog mybinlog.000002 --start-position=337 --stop-position=662
#如果是行級模式，想要看懂詳細内容則需要加上額外參數，但是僅用于看懂内容，如果要用于還原資料，還是應該去掉額外的參數并将内容定位到檔案中
mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002 # 僅用于檢視，不
#能用于日後的資料恢複
mysqlbinlog mybinlog.000002 --start-position=100 > /tmp/1.sql # /tmp/1.sql可用于日後的資料恢複
【使用binlog恢複資料】
#修改資料
begin; #開啟事務
update user set name="XXX" where name="hzl2";
commit;
#發現自己修改錯了
select * from user;
#復原，復原不了，已經送出了
rollback; #復原事務
select * from user;
#一怒之下删表
drop table user;
#恢複資料：檢視binlog資料的起始點與要恢複到的位置點，導出成SQL
mysqlbinlog mybinlog.000002 --stop-position=772 > /tmp/binlog.sql
mysql -uroot [email protected] < /tmp/binlog.sql
【重新整理與清除binlog日志】
1、重新整理binlog
#重新整理binlog：關閉目前的二進制日志檔案并建立一個新檔案
1）#手動執行指令重新整理
flush logs;
或者
mysqladmin -uroot -p flush-logs; #不登入資料庫進行重新整理
或者
mysql -uroot [email protected] -e 'flush logs' #使用參數指定sql語句
2）#重新開機資料庫時會重新整理
3）#二進制日志上限（max_binlog_size）;當binlog達到1G,自動重新整理
2、清除binlog
#清除二進制日志原則
#在存儲能力範圍内，能多保留則多保留，基于上一次全備前的可以選擇删除
1) 删除所有binlog，相當于重置
reset master;
2) 删除指定binlog名之前的所有binlog(保留指定的binlog)
purge binary logs to 'mybinlog.00003'; -- mybinlog.00003之前的都删除掉
3）删除日期之前的日志：手動執行
PURGE {MASTER | BINARY} LOGS BEFORE 'date' --用于删除日期之前的日志，BEFORE變量的
date自變量可以為'YYYY-MM-DD hh:mm:ss'格式
#如：（MASTER 和BINARY 在這裡都是等效的）
PURGE MASTER LOGS TO 'mybinlog.00003';
purge binary logs before '2021-07-13 19:11:00';
還可以做減法：如隻保留3天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
4）删除日期之前的日志：修改配置參數，讓mysql自動執行删除7天前的binlog
#臨時生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[[email protected] data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
【日志總述】
【二進制日志】
以二進制檔案的形式記錄了資料庫的操作，但是不記錄查詢語句，也叫變更日志
################################ 啟動與設定二進制日志 #############################
#在mysql配置檔案中添加
[[email protected] ]# vim /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/mybinlog #預設路徑filename自定義
#log-bin = ./my-binlog #不設定絕對路徑，在工作目錄下
#檢視binlog生成檔案
[[email protected] /]# ll /var/lib/mysql/ |grep binlog
-rw-r----- 1 mysql mysql 0 7月 17 01:10 mybinlog
-rw-r----- 1 mysql mysql 177 7月 17 01:10 mybinlog.000001 #每啟動一次重新開機時生成一個的檔案
-rw-r----- 1 mysql mysql 154 7月 17 01:10 mybinlog.000002
-rw-r----- 1 mysql mysql 62 7月 17 01:10 mybinlog.index
#叙述：
1）啟動與設定二進制日志:在Mysql的配置檔案中添加此參數：log-bin=DIR/filename #DIR是存放二進制日志的目錄，一般自定義在mysql目錄下；
2）每啟動一次Mysql，該目錄下就會生成一個filename.00000x的檔案；
3）目錄下還有一個filename.index的檔案，用于存儲所有二進制檔案清單；
4）如果我們沒有設定DIR和filename，則預設在資料目錄下以hostname-bin.00000x命名
################################# 檢視binlog ##############################
#直接檢視binlog日志檔案
[[email protected] mysql]# mysqlbinlog mybinlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210717 1:03:52 server id 1 end_log_pos 123 CRC32 0x74bd29b5 Start: binlog v 4, server v 5.7.34-log created 210717 1:03:52 at startup
ROLLBACK/*!*/;
........
.....
#mysql指令行檢視binlog
mysql> show master logs\G #檢視所有binlog
*************************** 1. row ***************************
Log_name: mybinlog.000001
File_size: 177
*************************** 2. row ***************************
Log_name: mybinlog.000002
File_size: 154
2 rows in set (0.00 sec)
#################################### 删除binlog ################################
#删除二進制日志binlog
mysql> reset master; #表示删除所有binlog
Query OK, 0 rows affected (0.02 sec)
#指定删除00004之前的所有binlog
mysql> purge master logs to 'mybinlog.000004'; #删除某一個之前的binlog
Query OK, 0 rows affected (0.00 sec)
#指定删除日期之前所有
mysql> purge master logs to '2021-07-19 1:00:00';
############################## 使用binlog還原資料庫 #############################
mysqlbinlog mysql-bin.000001 | mysql -u root -p
mysqlbinlog mysql-bin.000002 | mysql -u root -p
#這條指令可以了解為:使用mysqlbinlog讀取二進制日志檔案然後使用mysql指令還原到資料庫中
ps :注意還原時必須是編号小的先還原
mysql> create table t2(id int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert t2 values("1"),("2"),("3");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
#制定你想恢複的時間點進行恢複binlog
mysqlbinlog --start-position="213" sstop-position="456" mysql-bin.000002 | mysql -u root -p
! ! ! 還沒寫完，續集
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)