Mysql的binlog日志,使用binlog日志恢复数据

binlog日志：

１）binlog日志也叫做二进制日志，虽然它保存了数据却是以二进制的形式保存的

２）binlog日志是Mysql服务日志中的一种

３）binlog日志记录除查询语句外的语句，insert,update,delete,create 等，除select

４）binlog日志可以用于恢复数据

５）binlog日志是做数据库服务器主从同步的必要条件

启动binlog日志：

修改配置文件,在my.cnf中加入两项

]#vim /etc/my.cnf

server_id=1

log_bin=db 　　　　　　//你也可以只写log_bin 系统也会以默认日志名为你创建,默认bin.xxx

]#systemctl restart mysqld

binlog日志的文件序号

你每一次刷新日志，都会将序号加１，在mysql下刷新日志命令：flush logs;

如果想查看当前使用哪个binlog日志，在mysql下执行：show master status;

bin.000001

bin.000002

bin.000xxx

删除binlog日志

删除某序号之前的binlog日志：purge master log to "binlog文件名"

删除全部日志：reset master;

binlog日志模式

binlog有三种日志模式，分别是row,mixd,statement

1)row行模式

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改
优点：bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题。
缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如我们需要修改某一条记录，并且当天做了100次修改，但是我们做数据备份并不需要知道它前９９次修改的过程，我们只需要第一百条修改记录就可以了（即最新修改内容），row模式并不会这样做，它是这样的恢复数据的：记载并执行１００次修改记录．

2)statement报表模式

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
优点：statement level下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

3)mixd混合模式

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

补充：

如果没有理解row和statement行，看一下这个例子．

假设我想修改我的个人信息，有一个name名字字段，我当天修改了１００次

那在row模式下，我需要记录一百条 

update myinfo set name ='aaa';

update myinfo set name ='bbb';＊100s

在statement模式下，只需要一条，是这样记录的,

context（上下文信息）

update myinfo set name ='最后一个结果';

statement会通过上下文找到这条语句并且与新的语句作对比，然后替换修改结果

binlog日志分析

1)查看当前日志模式

mysql->show variables like "binlog_format";

2)修改日志模式

]# vim /etc/my.cnf

[mysqld]

 binlog_format=row/statement/mixd;   //三选一

3)查看binlog日志

如果你想查看当前使用的是哪一个binlog日志或者binlog日志的偏移量，在mysql下输出命令

mysql-> show master status;　　　　　　//文件名host51-bin.000003，偏移量：154

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host51-bin.000003 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
 

知道当前使用的binlog日志后，你就可以查看一下binlog日志的信息了，因为binlog是用二进制写的，你也别指望去看这个文件，得用命令来查看

mysqlbinlog [选项]　binlog日志文件名

例：查看当天的binlog日志

]#mysqlbinlog --start-date='2019-09-11 00:00' --stop-datetime='2019-09-12 00:00'  /var/lib/mysql/host51-bin.000003

使用binlog来实恢复数据

步骤：

１）使用mysqlbinlog提取sql语句

２）将结果管道给mysql执行

格式：

mysqlbinlog [选项] binlog文件日志 | mysql -u root -p密码

例：

mysqlbinlog /var/lib/mysql/host51-bin.000003 | mysql -u root -p12346