mysql之 binlog维护详细解析(开启、binlog相关参数作用、mysqlbinlog解读、binlog删除)-阿里云开发者社区

开发者社区> 技术小美> 正文

mysql之 binlog维护详细解析(开启、binlog相关参数作用、mysqlbinlog解读、binlog删除)

简介:
+关注继续查看

binary log 作用:主要实现三个重要的功能:用于复制,用于恢复,用于审计。
binary log 相关参数:
log_bin
设置此参数表示启用binlog功能,并指定路径名称
log_bin_index
设置此参数是指定二进制索引文件的路径与名称
binlog_format
此参数控制二进制日志三种格式:STATEMENT,ROW,MIXED
① STATEMENT模式(SBR)
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况(如非确定函数)下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)
② ROW模式(RBR)
不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。
③ MIXED模式(MBR)
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
binlog_row_image
此参数控制二进制日志记录内容,有三种选择full、minimal、noblob,默认值是full。
full:在“before”和“after”影像中,记录所有的列值;
minimal:在“before”和“after”影像中,仅仅记录被更改的以及能够唯一识别数据行的列值;
noblob:在“before”和“after”影像中,记录所有的列值,但是BLOB 与 TEXT列除外(如未更改)。
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_size
此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
binlog_cache_use:使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束
sync_binlog
这个参数直接影响mysql的性能和完整性
sync_binlog=0:
当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
sync_binlog=n,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷Crash,在文件系统缓存中的所有Binlog信息都会丢失

1.开启二进制日志 
mysql>show variables like '%log_bin%'; 
+---------------------------------+-------+ 
| Variable_name | Value | 
+---------------------------------+-------+ 
| log_bin | OFF | --该参数用于设定是否启用二进制日志 
| log_bin_trust_function_creators | OFF | 
| sql_log_bin | ON | 
+---------------------------------+-------+ 
[root@mysql ~]# service mysql stop
Shutting down MySQL.... [ OK ]
[root@mysql ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql ~]# vi /etc/my.cnf
说明: 在/etc/my.cnf 文件中添加 log_bin=/var/lib/mysql/binarylog/binlog 
[root@mysql ~]# mkdir -p /var/lib/mysql/binarylog
[root@mysql ~]# chown -R mysql:mysql /var/lib/mysql/binarylog 
[root@mysql mysql]# service mysql start
Starting MySQL. [ OK ]
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binarylog/binlog |
| log_bin_index | /var/lib/mysql/binarylog/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)
[root@mysql mysql]# ll /var/lib/mysql/binarylog/
total 8
-rw-rw---- 1 mysql mysql 120 May 30 16:57 binlog.000001
-rw-rw---- 1 mysql mysql 39 May 30 16:57 binlog.index
2. 切换二进制日志
mysql> flush logs; 
Query OK, 0 rows affected (0.05 sec)
[root@mysql mysql]# ll /var/lib/mysql/binarylog/
total 12
-rw-rw---- 1 mysql mysql 164 May 30 17:09 binlog.000001
-rw-rw---- 1 mysql mysql 120 May 30 17:09 binlog.000002
-rw-rw---- 1 mysql mysql 78 May 30 17:09 binlog.index
3.查看 binary log 个数
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 164 |
| binlog.000002 | 164 |
4. 查看正在使用的 binary log
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000010 | 120 | | | |
+---------------+----------+--------------+------------------+-------------------+
5.查看二进制日志事件
5.1 
mysql> show binlog events in 'binlog.000010';
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000010 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |
| binlog.000010 | 120 | Query | 1 | 219 | use `test`; create table andy(id int) |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec) 
5.2
mysql> show binlog events in 'binlog.000010' from 120 limit 2; 
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
| binlog.000010 | 120 | Query | 1 | 219 | use `test`; create table andy(id int) |
| binlog.000010 | 219 | Query | 1 | 298 | BEGIN |
+---------------+-----+------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
6. 用 mysqlbinlog 工具查看 二进制日志
6.1
[root@mysql ~]# mysqlbinlog /var/lib/mysql/binarylog/binlog.000016
# at 199
#170530 19:42:52 server id 1 end_log_pos 306 CRC32 0x64d982e4 
Query thread_id= exec_time=0
error_code=0
use `test`/*!*/;
SET TIMESTAMP=1496144572/*!*/;
insert into name values('陶叶')
/*!*/;
# at 306
#170530 19:42:52 server id 1 end_log_pos 337 CRC32 0xf75d46d1 
Xid = 36
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
内容解析:
位置> 位于文件中的位置,“at 199”说明“事件”的起点,是以第199字节开始;“end_log_pos 306”说明以第306字节结束,下一个事件将以上一个事件结束位置为起点,周而复始。
时间戳> 事件发生的时间戳:“170530 19:42:52”
事件执行时间> 事件执行花费的时间:"exec_time=0"
错误码> 错误码为:“error_code=0”
服务器的标识> 服务器的标识id:“server id 1”
6.2 用 mysqlbinlog 工具查看 指定时间戳 binlog
[root@mysql ~]# mysqlbinlog --start-datetime="2017-05-30 19:42:52" /var/lib/mysql/binarylog/binlog.000016
6.3 用 mysqlbinlog 工具查看 指定position 的binlog
[root@mysql ~]# mysqlbinlog --start-position=199 --stop-position=306 /var/lib/mysql/binarylog/binlog.000016
7. 删除 binary log
7.1 自动删除 , my.cnf中 添加 expire_logs_days
expire_logs_days = X # X为指定天数
7.2 手动删除( 自动在操作系统层面把 os file 删除了)
mysql> reset master; //删除master的binlog
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before '2017-05-30 18:27:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000011'; //删除binlog.000011之前的,不包含binlog.000011

文章可以转载,必须以链接形式标明出处。

本文转自 张冲andy 博客园博客,原文链接:  http://www.cnblogs.com/andy6/p/6921308.html ,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C# 遍历List 并删除某个元素
1、我们选择用for循环, for(int i=0;i {      if(list[i])     {         list.
1136 0
两行代码修复了解析MySQL8.x binlog错位的问题!!
MySQL是互联网行业使用的最多的关系型数据库之一,而且MySQL又是开源的,对于MySQL的深入研究,能够加深我们对于数据库原理的理解。自从开源了mykit-data之后,不少小伙伴试用后,反馈mykit-data无法正确的解析MySQL8的binlog。于是我测试了下,mykit-data在解析MySQL5.x的binlog时,没有啥问题,能够正确的解析出结果数据。然而,在解析MySQL8.x的binlog时,总是与binlog日志位数相差12位而导致解析失败。
10 0
DataStreams+logstash+ILM进行日志定时删除,节省硬盘资源
目前所有的K8S上的容器日志都被收集到了我们的ELK上,随着时间的推移,ELK上的日志所占的存储空间越来越多,我们需要一个定时清理的策略,以节约硬盘资源。 我们主要配置以下ELK里的这几个地方 1.通过kibana新增一个lifecycle policies 2.通过kibana新增一个index template,注意配置DataStreams 3.logstash 的logstashPipeline 4.filebeat的filebeat.yml文件
30 0
SQL SERVER数据库删除LOG文件和清空日志的方案
原文:SQL SERVER数据库删除LOG文件和清空日志的方案 数据库在使用过程中会使日志文件不断增加,使得数据库的性能下降,并且占用大量的磁盘空间。SQL Server数据库都有log文件,log文件记录用户对数据库修改的操作。
4176 0
命令行下开启与关闭windows防火墙关端口(转)
sc config sharedaccess start= auto //设置防火墙服务为自动 net start sharedaccess //开启防火墙服务   关闭端口 netsh firewall add portopening protocol = TCP port = 139 n...
782 0
使用application log 分析navigation target解析错误
Created by Jerry Wang, last modified on Sep 20, 2015
23 0
vnc操作使用指南----开启多个VNCserver
应用场景 在上面博客中介绍了一台Linux服务器上开启一个VNCServer,然后通过Windows端的VNCviewer去连接该Linux,最后可以在Windiwos系统中,远程看到Linux的系统界面。
2096 0
+关注
4813
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载