mysql恢复binlog

命令：mysqlbinlog --database=yun --start-datetime="2019-11-25 02:55:01" --stop-datetime="2019-11-26 00:00:00" /data/pyshell/data_sync/files/masteryun.000207 |  mysql -uroot -padmin

--database  表示指定数据库

--start-datetime  表示数据恢复的开始时间（就是数据库里面数据操作的真实时间）

--stop-datetime  表示数据恢复的结束时间

 

/data/pyshell/data_sync/files/masteryun.000207  表示binlog文件的绝对路径 

mysql -uroot -padmin 数据库登录的账号密码

 

常见坑：在数据恢复的时候，会有一些报错，将导致恢复直接失败，一般来说都可以通过修改my.cnf来避免出错

[mysqld]

# 解决数据超时问题（ERROR 2006 (HY000) at line 2202822: MySQL server has gone away）

wait_timeout=2880000
interactive_timeout = 2880000
max_allowed_packet = 200M

# 解决备份恢复时，主从数据库写入方式不同问题（ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.）

# ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

binlog_format=mixed 

# 跳过其他报错 
# ERROR 1062 (23000) at line 30: Duplicate entry '2965060' for key 'PRIMARY' 
# ERROR 1032 (HY000) at line 55: Can't find record in 'card_broker_house' 
# ERROR 1452 (23000) at line 659: Cannot add or update a child row: a foreign key constraint fails (`yun`.`fang_houseimg`, CONSTRAINT `fang_houseimg_house_id_755dc6c0_fk_fang_house_id` FOREIGN KEY (`house_id`) REFERENCES `fang_house` (`id`))
# ERROR 1146 (42S02) at line 523518: Table 'yun.statistics_xf' doesn't exist
# ERROR 1050 (42S01) at line 4870964: Table 'epay_payextendinfo' already exists
slave_skip_errors=1062,1032,1452,1146,1050  

后面还有其他问题再更新。。。。