主講教師：魏巍巍北京信息職業技術學院數據庫技術與應用二進制日志創建采集任務01二進制日志01目錄CONTENTS二進制日志01Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.二進制日志是MySQL重要的日志之一，用于記錄修改數據或有可能引起數據改變的MySQL語句，像select、show這種不會修改數據的語句，二進制日志是不會記錄的。二進制日志的主要作用：可以完成主從復制功能。主服務器把所有修改數據的操作記錄到二進制日志中，從服務器請求主服務器的二進制日志中的事件，然后從服務器再在本地將事件進行回放，從而達到主從復制的目的。進行恢復操作。數據可以通過二進制日志，使用mysqlbinlog命令，實現基于時間點的恢復。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.在my.ini中使用配置參數log_bin=/PATH/LOG_FILE可以啟用二進制日志如果沒有設置log_bin，則為OFFSHOWGLOBALVARIABLESLIKE'%log%';二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.mysql-bin是二進制日志文件的文件名前綴，后面的數字是二進制日志的序列號，號碼是按順序來排列的，每次啟動MySQL、重啟MySQL、刷新日志（flushlogs）或是到達了max_binlog_size值的大小，就會滾動binlog，生成一個新的binlog文件。mysql-bin.index文件是binlog的索引文件，用來記錄產生的二進制日志的序列號。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：max_binlog_size：該參數制定了binlog的最大值，如果超過該值會自動生成新的binlog文件（重啟MySQL實例也會滾動binlog）。從MySQL5.0開始，該值的默認值為1G。注意：一般情況下，生產線上我們binlog的生成時間的最小間隔保持在2-5分鐘，所以該參數不要太大，可以調整為256MB。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：binlog_cache_size：所有未提交的事務會記錄到一個緩存中，等待事務提交時，直接將緩存中的二進制日志寫入到二進制日志文件。該緩存大小由binlog_cache_size的值來決定，它的默認大小是32KB，并且binlog_cache_size是基于會話的，也就說當一個線程要開始一個事務時，MySQL會自動分配一個該值大小的緩存。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：binlog_formatbinlog_format：代表二進制日志的記錄格式，binlog的格式有statement、row、mixed三種。生產線上為保險起見，一般給row。

statement：每一條會修改數據的SQL語句會記錄到binlog中，不建議生產線上使用。優點：并不需要記錄每一行的數據變化，減少了binlog的日志量，節約I/O，提高性能。缺點：在某些情況下會導致master-slave中數據不一致。

二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：row：不記錄每條SQL語句的上下文信息，僅僅記錄哪條數據被修改了，然后修改成什么樣了。5.6以后新增了一個參數binlog_rows_query_log_events，設置該參數，也可以在row模式下，看見用戶的完整SQL語句。優點：任何情況下都可以被復制，可以保證數據的精確性，這對主從復制來說是最安全可靠的。缺點：會產生大量的日志。mixed：混合使用row和statement格式。對于statement無法復制的操作使用row模式保存binlog，mysql會依據SQL語句來自行判斷所保存的日志格式。不推薦使用。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：sync_binlog：當innodb_flush_log_at_trx_commit=1時，表示事務提交時，事務日志立馬從內存刷到磁盤中的事務日志文件中，而sync_binlog對于二進制日志的作用，就像innodb_flush_log_at_trx_commit對于事務日志的作用，由于二進制日志一開始存在于內存（binlog_cache）中，如果將sync_binlog=1，則表示每一次事務提交之后，都會將內存中的二進制日志立即同步到磁盤中的二進制日志文件中。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：如果將sync_binlog=0，則表示事務提交之后，MySQL不會記錄將內存中的binlog刷寫到磁盤中的binlog日志文件中，而是由文件系統決定什么時候刷寫，這可能取決于文件系統的緩存機制。為了保證安全性，我們可以將sync_binlog=1。為了獲得最佳性能，而不考慮安全性，可以將該值設為0。將sync_binlog=1和innodb_flush_log_at_trx_commit=1，這就是MySQL的雙一模式，可以保證數據庫的安全性。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.相關的變量：expire_logs_days：二進制日志過期時間，單位為天，默認為0。一般情況下，我們可以設置的時間長一點。binlog_do_db和binlog_ignore_db：表示需要寫入或忽略哪些庫的二進制日志，默認為空，表示將所有庫的操作都記錄到二進制日志。log_slave_updates：級聯復制。二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.查看所有二進制日志showbinarylogs;二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.查看指定的二進制日志showbinlogeventsin'SC-201902120851-bin.000001'\G二進制日志Copyright?2017,Oracleand/oritsaffiliates.Allrightsreserved.二進制日志會記錄大量的信息，如果很長時間不清理二進制日志，將會浪費很多的磁盤空間。但是刪除之后可能導致數據庫崩潰時無法進行恢復，所以要刪除二進制日志首先將其和數據庫備份一份，其中