mysql 二进制日志类型_mysql日志种类、二进制日志模式、mysqlbinlog解析二进制日志...

mysql日志的种类

二进制日志(binary log)：记录数据更新的操作，mysqlbinlog 可查看二进制日志文件

错误日志(error log)：记录mysql服务进程mysqld的启动、关闭和运行时的错误信息

慢查询日志(slow query log)：记录执行时间超过指定时间(long_query_time)的查询操作

通用查询日志(general query log)：记录客户端连接和执行的sql查询操作(一般不开启)

日志一般存放在安装目录下的data目录下

刷新binlog操作：[root@oldboy data]# mysqladmin -uroot -poldboy flush-logs

binlog日志的三种模式statement、row、mixed

mysql> show variables like '%binlog_for%';+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1.statement level模式

每一条修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行

优点：解决了row level下的缺点，不需要记录每一条数据的变化，减少bin-log的日志量，节约IO，提高性能

缺点：必须记录每条语句在执行时的一些上下文信息，以保证所有语句在slave端执行时能得到和在master端执行时相同的结果；涉及到复杂内容的时候，可能会出现bug，造成复制出现问题，主要是修改数据的时候使用某些特定函数或者功能，例如sleep( )函数、last_insert_id( )函数

2.row level模式

日志记录成每一行数据被修改的形式，然后在slave端在对相同的数据进行修改

优点：不需记录执行sql语句的上下文信息，只需记录哪一条数据被修改了；不会出现某些特定情况下的存储过程、函数、触发器的调用和触发无法被正确复制的问题

缺点：binlog日志量很大(例如update test set name=‘Jane’，test表有几行就会记录几行)

3.mixed模式

statement模式和row模式的混合，mysql会根据执行的每一条具体的sql语句来确定记录的形式，即在statemente和row之间选择一种。新版本对row level模式做了优化，不是所有的修改都以row level来记录，如表结构的变更会以statement模式记录，如果是update或者delete还是会记录所有行的变更

mysql5.5默认的binlog是statement模式，临时修改为行模式mysql>set global  binlog_format ='ROW';(永久生效需改配置文件)

执行update oldboy.test set name=’Jane’ where id>3

查看行模式下的binlog  [root@oldboy data]# mysqlbinlog --base64-output='decode-rows' -v mysql-bin.000053

BEGIN

/*!*/;

# at 725

# at 774

#180717 20:20:55 server id 1  end_log_pos 774   Table_map: `oldboy`.`test2` mapped to number 38

#180717 20:20:55 server id 1  end_log_pos 884   Update_rows: table id 38 flags: STMT_END_F

### UPDATE `oldboy`.`test2`

### WHERE

###   @1=4

###   @2='Jane'

### SET

###   @1=4

###   @2='Jack'

### UPDATE `oldboy`.`test2`

### WHERE

###   @1=5

###   @2='Jane'

### SET

###   @1=5

###   @2='Jack'

### UPDATE `oldboy`.`test2`

### WHERE

###   @1=6

###   @2='Jane'

### SET

###   @1=6

###   @2='Jack'

### UPDATE `oldboy`.`test2`

### WHERE

###   @1=7

###   @2='Jane'

### SET

###   @1=7

###   @2='Jack'

# at 884

mysqlbinlog用来解析mysql的binlog日志，在mysql/data目录下，如mysql-bin.000006(6位)

binlog用来记录mysql内部所有增、删、改等对数据库内容有更新的记录，包含所有数据库及所有表，无法分开记

1.指定库导出，加-d 库名，可将指定库的更新语句截取出来

mysqlbinlog -d oldboy mysql-bin.000011  >  all.sql

日志内容部分如下

#181201 21:43:41 server id 1 end_log_pos 177 Query thread_id=26 exec_time=0 error_code=0

SET TIMESTAMP=1543671821/*!*/;SET @@session.pseudo_thread_id=26/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;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/*!*/;BEGIN

/*!*/;

# at177#181201 21:43:41 server id 1 end_log_pos 287 Query thread_id=26 exec_time=0 error_code=0

use `oldboy`/*!*/;SET TIMESTAMP=1543671821/*!*/;insert into test values(8,'Alice'),(9,'Alan')/*!*/;

# at287#181201 21:43:41 server id 1 end_log_pos 314 Xid = 531

COMMIT/*!*/;

DELIMITER ;

#End of log file

ROLLBACK /*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2.指定位置恢复，--start-position和--stop-position

Insert into test values(4,’钱六’);查看前后两个at的位置分别为570、670，指定更新语句所在的位置

mysqlbinlog mysql-bin.000011 --start-position=570 --stop-position=670 -r /opt/position.sql将结果重定向到/opt/ position.sql文件中(-r相当于重定向>)

3.指定时间恢复, --start-datetime和--stop-datetime(指定时间恢复可能不准确，mysql不会恢复截止时间恰好等于指定时间的sql语句，但是会恢复开始时间恰好等于指定时间的sql语句)

Insert into test values(4,’钱六’);前后时间都为22:41:20，指定时间时可稍微将时间向两个边界扩展一点

mysqlbinlog mysql-bin.000011 --start-datetime='2018-06-22 22:41:10' --stop-datetime='2018-06-22 22:41:21'  -r /opt/datetime.sql

以上指定位置和指定时间，如果不指定结束点，表示从指定点到文件结尾，如果不指定开始点，表示从文件开头到指定点