@Zjmainstay
2023-02-02T09:47:10.000000Z
字数 2788
阅读 1794
MySQL
binlog
数据恢复
@author Zjmainstay
@website http://www.zjmainstay.cn
在my.cnf [mysqld]节点添加:
log-bin=mysql-bin
binlog_format=mixed
show variables like '%log_bin%';
Variable_name | Value |
---|---|
log_bin | ON |
show variables like '%datadir%';
Variable_name | Value |
---|---|
datadir | /var/lib/mysql/ |
show binlog events in 'mysql_bin.000001'\G
mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001|more
可以指定参数:--start-position=0 --stop-position=xxx
请留意日志中额外添加的注释,如(作者注:开始的位置)
#161118 21:58:58 server id 1 end_log_pos 1974 CRC32 0x733660ed Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1479477538/*!*/;
insert into `tb_abc` (`id`, `name`) values ('1', 'abc')
/*!*/;
# at 1974(作者注:开始的位置)
# (作者注:需要恢复的内容Begin)
#161118 21:58:06(开始的时间) server id 1 end_log_pos 2161 CRC32 0xcd3cd3d2 Query thread_id=7 exec_time=784 error_code=0
SET TIMESTAMP=1479477486/*!*/;
CREATE TABLE `binlog-find` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) DEFAULT CHARACTER SET `utf8`
/*!*/;
# at 2161
#161118 21:58:18 server id 1 end_log_pos 2307 CRC32 0x1e28b93d Query thread_id=7 exec_time=772 error_code=0
SET TIMESTAMP=1479477498/*!*/;
ALTER TABLE `binlog-find` ADD `name` INT
NULL
DEFAULT NULL
AFTER `id`
/*!*/;
# at 2307
#161118 21:58:23 server id 1 end_log_pos 2386 CRC32 0x34669ad7 Query thread_id=7 exec_time=767 error_code=0
SET TIMESTAMP=1479477503/*!*/;
BEGIN
/*!*/;
# at 2386
#161118 21:58:23 server id 1 end_log_pos 2518 CRC32 0xf4f4adb4 Query thread_id=7 exec_time=767 error_code=0
SET TIMESTAMP=1479477503/*!*/;
INSERT INTO `binlog-find` (`id`, `name`) VALUES ('1', '2')
/*!*/;
# at 2518
#161118 21:58:23 server id 1 end_log_pos 2549 CRC32 0x54679b22 Xid = 2426
COMMIT/*!*/;
# at 2549(作者注:结束的位置)
# (作者注:需要恢复的内容End)
#161118 21:58:27(作者注:结束的时间) server id 1 end_log_pos 2673 CRC32 0x35b852fa Query thread_id=7 exec_time=860 error_code=0
SET TIMESTAMP=1479477507/*!*/;
DROP TABLE `binlog-find` /* generated by server */
/*!*/;
# at 2673
##导入sql结果到sql文件
sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 >> res.sql
##通过管道方式直接运行(基于位置)
sudo mysqlbinlog --no-defaults --start-position="1974" --stop-position="2549" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
--start-position 为需要恢复开始语句上一条语句运行结束位置
--stop-position 为需要恢复结束语句运行结束位置(删表上一条语句)
或
##通过管道方式直接运行(基于时间)
sudo mysqlbinlog --no-defaults --start-datetime="2016-11-18 21:58:06" --stop-datetime="2016-11-18 21:58:27" /var/lib/mysql/mysql-bin.000001 |mysql -uzjmainstay --binary-mode
--start-datetime 为需要恢复开始语句运行时间
--stop-datetime 为需要恢复结束语句下一条语句运行时间(删表语句)
如果没有上一次备份,使用类似mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=0 --stop-position=9999999999999 | mysql -uroot -pxxxx
从第一个bin-log日志文件,逐步恢复到删表操作前的位置
--stop-position=9999999999999设置成接近无限大是为了无视文件的结束位置,方便操作