mysql二進制日志

​​    mysql二進制日志    ​​        

1.啟動和設定二進制日志

        預設情況下，二進制日志是關閉的，可以通過修改MySQL的配置檔案來啟動和設定二進制日志

log-bin 定義開啟二進制日志；後面參數為path[/filename],如果不指定path ，預設為datadir，如果不指定filename,預設為主機名稱會生成filename.0000001,filename.000002等，還有一個filename.index檔案,檔案内容為所有日志的清單

expire_logs_days 定義了MySQL清除過期日志的時間，即二進制日志自動删除的天數。預設值為0，表示不自動删除，當MySQL啟動或重新整理二進制日志時可能删除該檔案。

max_binlog_size 定義了單個檔案的大小限制，如果二進制日志寫入的内容大小超出給定值，日志就會發生復原（關閉目前檔案，重新打開一個新的日志檔案）。預設1GB, 不能大于1GB或小于4096B

        添加完畢後，重新開機MySQL，即可打開二進制日志

             使用show master status 檢視目前的二進制檔案名稱

            使用show binary logs;檢視二進制日志檔案個數即檔案名

            使用show variables like 'log_bin%';檢視二進制日志配置資訊

       到這裡啟動算是完了，但是我同樣的配置，在linux環境下卻一直啟動不了報錯，

            Starting MySQL...The server quit without updating PID file (/usr/local/lnmp/mysql/data/ljstu.pid).[失敗]

       最後查mysql錯誤日志，發現了這行錯誤

          You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentatio

       在MySQL官方文檔下找到了server-id的說明，MySQL5.7如果開啟了二進制日志，則server-id必須指定，否則會不允許服務啟動，而8.0隻會有個提示。server-id是幹什麼用的以後再說，這裡不做讨論

         5.7：

         8.0：

      本地mysql是8.0，linux伺服器mysql是5.7，是以才出了這個問題。

      解決辦法修改配置檔案 vim /etc/my.cnf  添加server-id=100 （随便一個唯一值）

2.檢視二進制日志  

         使用mysqlbinlog檢視二進制日志（先使用show master status檢視目前正在使用二進制檔案）。

         舉個例子，先進資料庫對任意一張表添加一行資料 ，然後使用上面說到的指令檢視日志

           通過日志可以看到15:53的時候操作了`ljstu`.`user`,但具體幹了啥，還是很難看懂，這時候就要談到二進制的記錄格式了

           MySQL有三種日志記錄格式：

基于語句的日志記錄：MySQL中的複制功能最初基于SQL語句主伺服器到從伺服器的傳播，配置binlog-format=STATEMENT

基于行的日志記錄，主伺服器将事件寫入二進制日志日志，該事件訓示如何影響各個表行，是以，表始終使用主鍵以確定行可以有效的辨別，配置binlog-format=ROW

混合日志記錄：預設情況下使用基于語句的日志記錄，但在某些情況下，日志記錄模式會基于行，配置binlog-format=MIXED

            修改配置檔案 vim /etc/my.cnf ，添加配置 binlog-format=statement ,重新開機mysql。我們再在`ljstu`.`user`裡面插入一行資料，然後檢視目前使用的日志檔案是哪一個，

　　　 最後在使用mysqlbinlog檢視，現在就可以看到執行的sql語句了

        　　

       　　 

    3.删除二進制日志 

    　　MySQL的二進制檔案可以配置自動删除，同時MySQL也提供了安全的手動删除方法

        　例子：先檢視目前的日志（因為linux上都是今天的，是以拿本地的做測試）

        　删除今天之前的日志檔案

　　　　purge master logs before '2019-12-18';

       　 删除指定的日志檔案

　　　　purge master logs to 'mybinlog.000010'

    　　                         

  4.使用二進制日志恢複資料

  　　  如果開啟了二進制日志，在資料庫出現意外丢失資料時，可以使用mysqlbinlog工具從指定的時間點（例如最後一次備份）到現在，或另一個指定的時間點的日志中恢複資料，（使用二進制日志是進行時間點增量恢複）

　　　mysqlbinlog [option] filename | mysql -uuser -ppass

　　　option為可選參數，filename是日志檔案名。常用的option參數有 --start-date(開始時間),--stop-date（結束時間）,--start-position（開始位置）,--stop-position（結束位置）

    　　先在資料庫表中添加3行記錄，更新其中一行

    　　

    　　然後檢視日志檔案，可以看到剛剛的update語句

     　　最後使用mysqlbinlog工具恢複資料，發現報錯了，而且是主鍵沖突，為什麼會出現這個問題？

    　　    

     　　MySQL官方文檔指出，時間點恢複将伺服器從完全備份時逐漸更新到最近的時間，如果沒有指定開始時間的話，mysql會把日志檔案的sql都執行一遍，是以執行到前面的insert的時候就報錯了。這裡，我們需要指定一個開始時間。

  　　

           找到插入資料的時間，然後執行指令，因為我這裡是一次性添加的3行資料，是以我把三行資料都删除了，然後使用mysqlbinlog工具恢複

　　    

5.暫停二進制日志功能

  　　 暫停：set sql_log_bin = 0;

    　　啟動：set sql_log_bin = 1;