文档章节

MySQL binlog

秋风醉了
 秋风醉了
发布于 2016/09/25 16:35
字数 1494
阅读 45
收藏 1

表结构

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(10) unsigned DEFAULT NULL,
  `col2` varchar(45) DEFAULT NULL,
  `col3` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql 版本

➜  ~ mysql -V
mysql  Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using  EditLine wrapper

mysql的安装请看https://my.oschina.net/xinxingegeya/blog/751154

启动mysql的binlog,在配置文件中添加,

[mysqld]

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin = mysql-bin

# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server_id = 001

重新启动MySQL。

二进制日志(bingary log)记录了对MySQL数据执行更改的所有操作,但是不包括select 和 show 这类操作,因为这类操作对数据本身并没有修改。然而,若操作本身没有导致数据库发生变化,那么该操作可能也会写入二进制日志。可以通过以下命令来查看二进制日志,

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 27954
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show binlog events in 'mysql-bin.000002' \G ;

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.

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.

binlog的部分参数

max_binlog_cache_size
binlog_cache_size

当使用InnoDB时,所有未提交的二进制日志会被记录到一个缓存中去,等该事务提交时直接将缓存中的二进制日志写入二进制日志文件,而该缓存的大小由binlog_cache_size决定,默认大小为32K。此外binlog_cache_size 是基于会话的,也就是说当一个线程开始一个事务时,mysql会自动分配一个大小为binlog_cache_size 的缓存,因此该值的设置需要当心,不能设置过大。当一个事务的记录大于设定的binlog_cache_size 时,mysql会把缓存中的日志写入一个临时文件中,因此该值又不能设置的太小。 可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

  • binlog_cache_use:使用二进制日志缓存的事务数量
    
  • binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
    
max_binlog_size

指定了单个二进制日志文件的最大值,如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到.index 文件。该设置并不能严格控制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信息都会丢失

binlog的格式

STATEMENT causes logging to be statement based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.

The server uses several logging formats to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. 
**_This is called statement-based logging._** 
You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. 
It is important therefore that tables always use a primary key to ensure rows can be efficiently identified. 
You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
A third option is also available: mixed logging. 
With mixed logging, statement-based logging is used by default, 
but the logging mode switches automatically to row-based in certain cases as described below. 
You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.

通过mysqlbinlog查看binlog

要查看二进制日志文件的内容,必须通过mysql提供的工具mysqlbinlog。

当binlog_format设置为STATEMENT时,

binlog_format = STATEMENT

如下方式使用mysqlbinlog查看二进制日志,

mysql> update t set col1 = col1+1 where id = 50;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 443
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show binlog events in 'mysql-bin.000005' \G ;
.....
.....
*************************** 5. row ***************************
   Log_name: mysql-bin.000005
        Pos: 298
 Event_type: Query
  Server_id: 1
End_log_pos: 412
       Info: use `test`; update t set col1 = col1+1 where id = 50

第五行是一个statement,pos为298,根据这些信息可以使用mysqlbinlog来查看具体的信息,

➜  mysql bin/mysqlbinlog --no-defaults --start-position=298 --stop-position=412 ./data/mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160925 16:56:47 server id 1  end_log_pos 123 CRC32 0x0856521a 	Start: binlog v 4, server v 5.7.15-log created 160925 16:56:47 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
T5HnVw8BAAAAdwAAAHsAAAABAAQANS43LjE1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABPkedXEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARpSVgg=
'/*!*/;
# at 298
#160925 16:57:46 server id 1  end_log_pos 412 CRC32 0x8752a208 	Query	thread_id=2	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1474793866/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
update t set col1 = col1+1 where id = 50
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

引用和参考:

http://dev.mysql.com/doc/refman/5.7/en/binary-log.html

http://dev.mysql.com/doc/internals/en/binary-log.html

=======END=======

© 著作权归作者所有

共有 人打赏支持
秋风醉了
粉丝 246
博文 543
码字总数 412294
作品 0
朝阳
程序员
私信 提问
MySQL(Replication-MS)

mysql cluster master: .binmysqld --defaults-file=.my.ini create directory: D:/Server/mysql/mysql-master/tmp and D:/Server/mysql/mysql-master/data master-client: .binmysql -h loc......

赵-猛
2016/07/03
11
0
mysql binlog 恢复

开始先执行每天自动备份的数据库 再使用binlog恢复 binlog直接恢复数据库,不建议直接使用 优先使用下面的,把binlog导出sql文件,看下没有问题再重新导入 恢复的数据不包含视图,视图需要另外使...

donald121
2018/08/11
0
0
MySQL 5.7.25 binary log 配置及使用简介

Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录(对数据库的改动),对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及...

songbill
02/04
0
0
一次数据库误update之后的数据恢复

工作需要,要对数据库(mysql)数据进行对比,写完测试脚本。想在表中修改一条数据,然后运行程序看看效果。正确的sql。可是实际情况是没有写where条件,就按下了回车,shell很快返回“**条数...

youthflies
2014/08/23
0
1
MySQL数据库开启mysql-binlog日志

  前言   mysql-binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容。      MySQL binlog格...

linux运维菜
2018/10/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Python中判断字符串是否包含中文

Python import reRE = re.compile(u'[\u4e00-\u9fa5]', re.UNICODE)match = re.search(RE, str)if match is None:# 没有包含中文字符 参考 Python regular expressions return true/f......

亚林瓜子
28分钟前
0
0
《CNCF × Alibaba 云原生技术公开课》正式首播

《CNCF × Alibaba 云原生技术公开课》立即观看:点击立即观看 云原生最初来描述云上应用的典型架构与特性,随着容器、Kubernetes、Serverless、FaaS技术的演进,CNCF(云原生计算基金会)把...

mcy0425
29分钟前
2
0
网站漏洞如何修复网站程序问题

jeecms 最近被爆出高危网站漏洞,可以导致网站被上传webshell木马文件,受影响的版本是jeecms V6.0版本到jeecmsV7.0版本。该网站系统采用的是JAVA语言开发,数据库使用的是oracle,mysql,sql数...

网站安全
32分钟前
2
0
git 中“warning: CRLF will be replaced by LF in”解决方案

https://stackoverflow.com/questions/5834014/lf-will-be-replaced-by-crlf-in-git-what-is-that-and-is-it-important...

qwfys
37分钟前
1
0
Spring Boot配置ssl证书启用HTTPS协议

一 、点睛 SSL是为网络通信提供安全及数据完整性的一种安全协议,SSL在网络传输层对网络连接进行加密。SSL协议位于TCP/IP协议和各种应用层协议之间,为数据通信提供安全支持。 SSL协议分为两...

故久呵呵
38分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部