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
后面还有其他问题再更新。。。。
好记性不如烂笔头!