2023学年完整公开课版二进制日志

主讲教师：魏巍巍北京信息职业技术学院数据库技术与应用二进制日志创建采集任务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.二进制日志会记录大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是删除之后可能导致数据库崩溃时无法进行恢复，所以要删除二进制日志首先将其和数据库备份一份，其中