created by Dejavu
mysql版本: mysql 5.7.26
操作系統(tǒng): ubuntu 18.04
cd /var/log/mysql
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
binlog簡介
? binlog是一個二進制格式的文件九榔，用于記錄用戶對數據庫增量操作的SQL語句信息苫幢，例如更改數據庫表和更改內容的SQL語句都會記錄到binlog里，但是對庫表等內容的查詢不會記錄。
? 默認情況下扒吁，binlog日志是二進制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看险掀，而使用mysqlbinlog解析查看
binlog是 記錄數據庫增刪改,不記錄查詢的二進制日志.
用于數據恢復，主從復制及數據的增量恢復
binlog的配置
配置文件的各個option作用
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
binlog_format = "MIXED"
max_binlog_size = 100M
binlog_do_db = include_database_name
binlog_ignore_db = include_database_name
server_id
mysql> mysqlvariables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 1 |
| server_id_bits | 32 |
+----------------+-------+
server_id 自然數湾宙，標識數據庫id
log_bin
日志路徑樟氢，填入后重啟數據庫，binlog啟動并開始記錄
expire_logs_days
日志過期時間侠鳄，設置為0則永不過期
-- 修改過期時間
mysql> set global expire_logs_days=7;
-- 設置之后不會立即清除埠啃，觸發(fā)條件是：
-- binlog大小超過max_binlog_size
-- 或者執(zhí)行 flush logs
mysql> flush logs;
-- 清除000055和以前的binlog
mysql>purge binary logs to 'bin.000055';
binlog_format
① STATEMENT模式（SBR）
每一條會修改數據的sql語句會記錄到binlog中。優(yōu)點是并不需要記錄每一條sql語句和每一行的數據變化畦攘，減少了binlog日志量，節(jié)約IO十电，提高性能知押。缺點是在某些情況下會導致master-slave中的數據不一致(如sleep()函數叹螟， last_insert_id()，以及user-defined functions(udf)等會出現問題)
② ROW模式（RBR）
不記錄每條sql語句的上下文信息台盯，僅需記錄哪條數據被修改了罢绽，修改成什么樣了。而且不會出現某些特定情況下的存儲過程静盅、或function良价、或trigger的調用和觸發(fā)無法被正確復制的問題。缺點是會產生大量的日志蒿叠，尤其是alter table的時候會讓日志暴漲明垢。
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的復制使用STATEMENT模式保存binlog市咽，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog痊银，MySQL會根據執(zhí)行的SQL語句選擇日志保存方式。
以下是操作實例
-
statement
執(zhí)行插入操作
insert into role (name, value) values ('name1', 1), ('name2', 2);命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... SET TIMESTAMP=1560862594/*!*/; insert into role (name, value) values ('name1', 1), ('name2', 2) ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;更新操作
update role set value=3;命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... SET TIMESTAMP=1560862624/*!*/; update role set value=3 ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; -
row
執(zhí)行插入操作
insert into role (name, value) values ('name1', 1), ('name2', 2);命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... ### INSERT INTO `aprint`.`role` ### SET ### @1=1 ### @2='name1' ### @3=1 ### @4=0 ### INSERT INTO `aprint`.`role` ### SET ### @1=2 ### @2='name2' ### @3=2 ### @4=0 ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;更新操作
update role set value=3;命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... ### UPDATE `aprint`.`role` ### WHERE ### @1=1 ### @2='name1' ### @3=1 ### @4=0 ### SET ### @1=1 ### @2='name1' ### @3=3 ### @4=0 ### UPDATE `aprint`.`role` ### WHERE ### @1=2 ### @2='name2' ### @3=2 ### @4=0 ### SET ### @1=2 ### @2='name2' ### @3=3 ### @4=0 ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; -
mixed
執(zhí)行插入操作
insert into role (name, value) values ('name1', 1), ('name2', 2);命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... SET TIMESTAMP=1560862362/*!*/; insert into role (name, value) values ('name1', 1), ('name2', 2) ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;更新操作
update role set value=3;命令行查看結果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... SET TIMESTAMP=1560862439/*!*/; update role set value=3 ...... # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
max_binlog_size
MySQL實例空間內生成binlog日志的規(guī)則如下：
- 通常情況下施绎，當前binlog大小超過max_binlog_size或超過6小時會切換到下一序號文件繼續(xù)寫入溯革，即寫滿max_binlog_size或超過6小時就會生成新的binlog日志文件。新的binlog文件繼續(xù)寫入谷醉，老的binlog文件并不會立刻上傳致稀，會異步上傳。
- 有些情況下俱尼，binlog日志不滿max_binlog_size就不再寫入抖单，比如由于命令的執(zhí)行、系統(tǒng)重啟等原因号显。
- 有些情況下臭猜，會出現binlog文件尺寸超過max_binlog_size的情況，比如當時在執(zhí)行大事務押蚤，不斷寫入binlog導致當前binlog文件尺寸超過max_binlog_size蔑歌。
binlog_do_db
后更要記錄的數據庫名database1,database2以這種格式
