mysql img 恢复_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]# 解决数据超时问题(ERROR2006 (HY000) at line 2202822: MySQL server has gone away)

wait_timeout=2880000interactive_timeout= 2880000max_allowed_packet=200M

# 解决备份恢复时，主从数据库写入方式不同问题(ERROR1666 (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

# 跳过其他报错

# ERROR1062 (23000) at line 30: Duplicate entry '2965060' for key 'PRIMARY'# ERROR1032 (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

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