由于一时手误输入了 DELETE FROM TABLE WHERE age REGEXP '\d';导致这个表的数据被删除
解决方法:
登录mysql,查看目前的binlog文件
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000016 | 16895 | | | | +---------------+----------+--------------+------------------+-------------------+
将日志复制到.sql文件 添加--base64-output=decode-rows
参数导出的sql文件里不包含数据部分(即insert,delete,update),主要是方便分析,实际导出的 sql是不能加这个参数的
mysqlbinlog --no-defaults --base64-output=decode-rows
binlog.000016 > 20190322.sql
# at 12731 823 #190321 18:54:17 server id 1 end_log_pos 12810 CRC32 0xa49cda19 Anonymous_GTID last_committed=47 sequence_number=48 rb r_only=yes original_committed_timestamp=1553165658210230 immediate_commit_timestamp=1553165658210230 transaction_length=333
将日志复制到.sql文件
mysqlbinlog --no-defaults binlog.000016 > 20190322.sql
从at 12731开始恢复
mysqlbinlog --start-position=219 --stop-position=12731 binlog.000016 > ./recovery-restore.sql
mysql -uroot -p gc < recovery-restore.sql