5.3 二进制日志
二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能:通过“重放”日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于“语句”记录:statement,记录语句,默认模式),日志量较少
基于“行”记录:row,记录数据,日志量较大,更加安全,建议使用的格式 (mysqlbinlog --base64-output=decode-rows -v log_bin.000003)
混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式
格式配置
show variables like 'binlog_format';
二进制日志文件的构成
有两类文件 1.日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: mysql-bin.000001 2.索引文件:mysql|mariadb-bin.index,文本格式
二进制日志相关的服务器变量
sql_log_bin=ON|OFF:#是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项 log-bin=log-bin:#指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
server-id=1(5.7.3之后要配合此选项使用) binlog_format=STATEMENT|ROW|MIXED:#二进制日志记录的格式,默认STATEMENT max_binlog_size=1073741824:#单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G #说明:文件达到上限时的大小未必为指定的精确值 binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接) max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。 sync_binlog=1|0:#设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘 expire_logs_days=N:#二进制日志可以自动删除的天数。 默认为0,即不自动删除
sync_binlog: 1.影响binlog对MySQL的性能损耗 2.影响MySQL中数据的完整性 设置如下: 1.0 MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中信息到磁盘,让fs来决定什么时候同步,或者cache满了同步 2.n n次提交事务之后,MySQL将进行一次fsync 刷写cache到磁盘 分析: 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失 而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。
二进制日志相关配置
查看mysql自行管理使用中的二进制日志文件列表,及大小 SHOW {BINARY | MASTER} LOGS 查看使用中的二进制日志文件 SHOW MASTER STATUS
二进制日志事件的格式:
# at 328 #151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1446712300/*!*/; CREATE TABLE tb1 (id int, name char(30)) /*!*/; 事件发生的日期和时间:151105 16:31:40 事件发生的服务器标识:server id 1 事件的结束位置:end_log_pos 431 事件的类型:Query 事件发生时所在服务器执行此事件的线程的ID:thread_id=1 语句的时间戳与将其写入二进制文件中的时间差:exec_time=0 错误代码:error_code=0 事件内容: GTID:Global Transaction ID,mysql5.6以mariadb10以上版本专属属性:GTID
清除二进制日志:
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } PURGE BINARY LOGS TO 'mysql-bin.000003'; #删除mysql-bin.000003之前的日志 PURGE BINARY LOGS BEFORE '2017-01-23'; PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
查看二进制日志事件
mysql> show binlog events in 'mysql_bin.000002';