为什么要记录binlog(binlog 有什么用)
都记录了些什么
格式
设置相关
和redo的关系
为什么要记录binlog(binlog 有什么用)
1.复制:对于主库,主库上的binlog发送到从服务器,为其提供数据的变化,从库执行这些事件使主从数据一致。For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
2.备份恢复:一些数据恢复需要用到binlog,使用备份恢复数据后,重新实行binlog里面的事件,这些事件使得数据库有更新的数据。Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
参考资料:mysql5.7手册
都记录了些什么
对数据库更改的语句,ddl操作,dml操作,时间信息。
(如果是主从环境并且主库记录了binlog,没有过滤情况下,对表的操作ddl语句和对于数据的增删改的操作一定会传到从库。)The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data
格式
row
查看mysqlbinlog --base64-output=decode-rows -v /opt/mysql/log/mysql-bin.000017
优点:安全,系统的特殊函数也可以复制,更少的锁
缺点:binlog比较大,单语句更新可能产生太多的binlog
statemnet
优点:日志量小,保留原始sql
缺点:可能导致主从复制不一致 uuid,loadfile, user(),found rows(),…无法复制
mixed格式
如果使用innodb 并且隔离级别rc or ru 那么还是row格式 。
If you use READ COMMITTED, you must use row-based binary logging.
和redo的关系
xa 事物redo parpared 状态
binlog sync
binlog filename position xid 写到redo 里面
redo commit 状态
参考
MySQL的Crash Safe和Binlog的关系
设置
5.7手册
本文由【waitig】发表在等英博客 本文固定链接:mysql binlog 简述 欢迎关注本站官方公众号,每日都有干货分享!
点赞 (0)赏分享 (0)