MySQL怎样从binlog文件指定时间去恢复数据，或者从指定位置恢复数据

灰太狼 2024-01-10 00:43 57阅读 0赞

binlog默认保留0天（永不过期）

binlog哪三种模式，优缺点

Mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。

对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

1、STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last\_insert\_id()，以及user-defined functions(udf)等会出现问题)

2、ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是altertable的时候会让日志暴涨。

3、MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

1.binlog内容查看：

使用工具查看：

mysqlbinlog binlog.000005 |more  
mysqlbinlog binlog.000005 >>/tmp/binlog.000005.txt

因为模式为binlog-format=ROW，  
所以使用参数，解析SQL语句：

mysqlbinlog --base64-output=decode-rows -v  
binlog.000008 >/tmp/test  
mysqlbinlog --base64-output=decode-rows -v \\  
\--start-datetime='2016-01-04 15:10:00' \\  
\--stop-datetime='2016-01-04 15:11:00' binlog.000008 >/  
tmp/test  
mysqlbinlog --base64-output=decode-rows -v \\  
\--start-position='398' \\  
\--stop-position='617' binlog.000008 >/tmp/test

SQL查询：

show binlog events in 'binlog.000006'\\G;  
show binlog events in 'binlog.000006' limit 10;

2.binlog恢复操作：  
时间和位置界定参数：

mysqlbinlog --start-datetime='2016-01-04 15:11:00'  
binlog.000006  
mysqlbinlog --stop-datetime='2016-01-04 15:11:00'  
binlog.000006  
mysqlbinlog --start-position=249 binlog.000006  
mysqlbinlog --stop-position=249 binlog.000006

使用--one-database参数，从binlog日志中恢复单个库：

mysqlbinlog --stop-datetime='2016-01-04 15:11:00' /  
usr/local/mariadb/log/binlog.000006 |mysql -uroot -  
pvincent --one-database test  
\#使用mysqlbinlog命令，指定结束时间，指定binlong日志文件抽取  
SQL，  
\#管道给mysql，使用--one-database指定库名，  
\#从所有binlog日志中抽取对应库的SQL行为进行恢复。

使用mysqlbinlog将binlog日志导出成文本文件，使用source执行该文件：

mysqlbinlog /usr/local/mariadb/log/binlog.000006>/tmp/  
redo.sql  
mysql -uroot -pvincent  
\\. /tmp/redo.sql  
\#这种方法也是mysql中调用sql脚本的方法。