IT虾米网

mySQL Binlog详解

admin 2018年06月04日 数据库 364 0

MySQL的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录(对数据库的改动),对数据库的查询select和show等操作不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。mysql的binlog日志必须打开log-bin功能才能生存binlog日志必须打开log-bin功能才能生成binlog日志。

1、打开mysql的log-bin功能

编辑my.cnf配置文件,首先要找到这个配置文件,不同的操作系统和安装方式最终呈现的目录结构是不一样的,下面以linux操作系统为例进行说明

二进制文件安装:

将下载的mysql tar文件安装包在指定目录进行解压,则会生成下图所示的目录

源码安装:

编译完源码之后,便开始安转。默认情况下,可以将文件安装到/usr/local,即在该目录下生成下面的一系列子目录

可以看到上面两种不同的安装方式,在一个安装目录内,远吗安装的目录布局完全不同于二进制安装:

mysqld服务器被安装在“libexec”目录而不是“bin”目录内。

  •  数据目录是“var”而非“data”
  • mysql_install_db被安装在“bin”目录而非“scripts”内。
  • 头文件和库目录是“include/mysql”“lib/mysql”而非“include”“lib”
  • 执行源码分发根目录下的scripts/make_binary_distribution脚本,你可以用编译过的源码分发版创建你自己的二进制安装。

了解了mysql的目录结构之后,my.cnf配置文件一般都在/opt/mysql/data/database_instance_name/my.cnf

编辑my.cnf配置文件

# grep log-bin my.cnf 
log-bin = /data/3306/mysql-bin 

  添加完成之后查看是否已经启动了日志

mysql>show variables like 'log_bin'; 

 2、Mysqlbinlog解析工具

Mysqlbinlog命令功能是将Mysql的binlog日志转换成Mysq领域具。默认情况下binlog日志是二进制文件,无法直接查看

Mysqlbinlog参数如下所示:

参数 描述
-d 指定库的binlog
-r 相当于重定向到指定文件
--start-position--stop-position 按照指定位置精确解析binlog日志(精确),如不接--stop-positiion则一直到binlog日志结尾
--start-datetime--stop-datetime 按照指定时间解析binlog日志(模糊,不准确),如不接--stop-datetime则一直到binlog日志结尾

 备注:mysqlbinlog分库到处binlog,如使用-d参数,那么在导入更新数据时需要在mysql命令行使用use database命令进入到相应的数据库中。因为bin-log里记录了在改mysql上面存储的所有数据库的增量操作信息,但是实际使用中我们常常需要针对某一个数据库分别进行,所以通常需要使用mysqlbinlog将binlog分库导出。

例如:解析test数据库的binlog日志并将其写入my.sql文件中。

#mysqlbinlog -d test mysql-bin.000001 -r my.sql 

  通过条件查询精确解析日志:

mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql 
 
mysqlbinlog dadong-bin.000005 --start-position=2265 --stop-position=2552 -r pos.sql 
mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql 
mysqlbinlog dadong-bin.000009 --start-position=365 -r pos.sql 
mysqlbinlog dadong-bin.000009 --stop-position=456 -r pos.sql 
截取部分binlog根据pos 
mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql 
mysqlbinlog dadong-bin.000009 --start-position=365 -r pos.sql 
mysqlbinlog dadong-bin.000009 --stop-position=456 -r pos.sql 
 
截取部分binlog根据时间 
mysqlbinlog dadong-bin.000009 --start-datetime='2014-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql 
mysqlbinlog dadong-bin.000009 --start-datetime='2014-10-16 17:14:15'  -r time.sql 
mysqlbinlog dadong-bin.000009  --stop-datetime='2014-10-16 17:15:15' -r time.sql 
 
Row模式下解析binlog日志:#mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000001
 
 
3、MySQL binlog的三种工作模式
  (1)Row level
  日志中会记录 每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
  优点:能清楚的记录每一行数据修改的细节
  缺点:数据量太大
  (2)Statement level(默认)
  每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
  优点:解决了 Row level下的缺点,不需要记录每一行的数据变化,减少bin-log日志量,节约磁盘IO,提高新能
  缺点:容易出现主从复制不一致
  (3)Mixed(混合模式)
  结合了Row level和Statement level的优点

总结:

以insert into xxtable values (x,y,z)为例, 
 
影响: 1行,且为新增1行, 对于其他行没有影响.  
 
这个情况,用row格式,直接复制磁盘上1行的新增变化. 
 
  
 
以update xxtable set age=21 where name=’sss’; 
 
这个情况,一般也只是影响1行. 用row也比较合适. 
 
  
 
以过年发红包,全公司的人,都涨薪100元. 
 
update xxtable set salary=salary+100; 
 
这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化. 
 
此处,适合就statment格式的日志. 
 
  
 
2种日志,各有各的高效的地方,mysql提供了mixed类型. 
 
可以根据语句的不同,而自动选择适合的日志格式. 

  

4、MySQL企业binlog模式的选择

  1. 互联网公司使用MySQL的功能较少(不用存储过程、触发器、函数),选择默认的Statement level
  2. 用到MySQL的特殊功能(存储过程、触发器、函数)则选择Mixed模式
  3. 用到MySQL的特殊功能(存储过程、触发器、函数),又希望数据最大化一直则选择Row模式
 
5、设置MySQL binlog模式
  查看MySQLbinlog模式
1
2
3
4
5
6
7
8
9
10
mysql>show global variables like  "binlog%" ;
+-----------------------------------------+-----------+
| Variable_name                           | Value     |
+-----------------------------------------+-----------+
| binlog_cache_size                       | 1048576   |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |        #系统默认为STATEMENT模式
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+
4 rows  in  set  (0.00 sec) 
MySQL中设置binlog模式
1
mysql> set  global binlog_format= 'ROW' ;  
配置文件中设置binlog模式
1
2
3
4
5
6
#vim my.cnf
[mysqld]
binlog_format= 'ROW'           #放在mysqld模块下面
user    = mysql
port    = 3306
socket  =  /data/3306/mysql .sock
6、配置完成后需要重启mysql服务

service mysqld restart或者./mysqld restart 

当然现在很多公司为了安全起见对mysql的原生命令比如mysqld进行了改造   不能直接通过该命令句进行重启等操作   需要使用自定义的命令进行操作

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Servlet规范详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。