使用mysqlbinlog恢复指定表

从整库备份的sql文件中导出某个表的sql语句时，vim查找到表的第一条INSERT语句后，按上下换行键计数INSERT语句的条数，然后按n yy复制，退出vim后，
再新建一个文件，按p粘贴刚才的n条INSERT语句，再进行source导入。
（2）将mysql的binlog导出为sql语句

mysqlbinlog mysql-bin.000024 -d mytestdb > /home/myhome/mytestdb.sql

4、mysqlbinlog命令参数说明：

-d   -database
-h   -host
-p   -password
-P   -port
--start-datetime
--start-position
--stop-datetime
--stop-position

这篇文章记录了方法使用http://bbs.chinaunix.net/thread-3764294-1-1.html
过滤保留^update" -e "^insert" -e "^delete" -e "^replace"
数据库名替换为空，去掉注释

/www/server/mysql/bin/mysqlbinlog  --database="beiyaozhongtai" --start-datetime="2021-11-24 23:50:00" --stop-datetime="2021-11-25 16:50:00" --base64-output=decode-rows -v /www/server/mysql/bin/mysql-bin.000472  \
|  grep -e "^##* UPDATE" -e "^##* INSERT" -e "^##* DELETE" -e "^##* \
REPLACE" -i -e "^update" -e "^insert" -e "^delete" -e "^replace"|  \
awk -F ' ' ' { print $1,$2,$3,$4}' |  sed 's/^##*//g' | tr '[A-Z]' '[a-z]' \
|  awk -F ' ' ' { if($1=="update") {$3=""} print $1,$2,$3}' \
|  sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/[a-zA-Z0-9\_\-]*\.//;s/ set .*$//;s/ as .*$//" \

---

一、登录数据库刷新binlog

1.1)查看当前的binlog

MySQL> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| test-150-bin.000003 |  2895377 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

1.2)刷新binlog

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

1.3)确认刷新binlog成功

mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| test-150-bin.000004 |      120 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

用show master status 命令查看当前的binlog已经由test-150-bin.000003变为test-150-bin.000004，
证明binlog已经刷新成功。

二、查询二进制日志位置

mysql> show variables like'log_bin%';
+---------------------------------+------------------------------------------+
| Variable_name                   | Value                                    |
+---------------------------------+------------------------------------------+
| log_bin                             | ON                                       |
| log_bin_basename             | /db/mysql5.6/data/test-150-bin       |
| log_bin_index                    | /db/mysql5.6/data/test-150-bin.index |
| log_bin_trust_function_creators  | ON                                       |
| log_bin_use_v1_row_events       | OFF                                      |
+---------------------------------+------------------------------------------+

三、从二进制日志中获取表被删除的时间

$ mysqlbinlog test-150-bin.000003 | grep -i DROP  -A3 -B4
COMMIT/*!*/;
# at 122869
#140126 17:03:35 server id 150  end_log_pos 122989 CRC32 0x8707c4c4     Query   thread_id=12519 exec_time=0     error_code=0
SET TIMESTAMP=1390727015/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;
# at 122989
#140126 17:04:06 server id 150  end_log_pos 123039 CRC32 0x43476aad     Rotate to test-150-bin.000004  pos: 4

drop语句的前两行表名drop语句的执行时间是在 17:03:35

mysql> SELECT from_unixtime('1390727015');
+-----------------------------+
| from_unixtime('1390727015')  |
+-----------------------------+
| 2014-01-26 17:03:35.000000 |
+-----------------------------+
1 row in set

四、从binlog中获取指定数据库的改变数据
用mysqlbinlog 恢复ivr数据库在二进制日志test-150-bin.000003中的数据
假设从上一次mysql备份后，只有一个binlog产生，即登录数据库时查询到binlog test-150-bin.000003

mysqlbinlog -d ivr --stop-datetime='2014-01-26 17:03:35' test-150-bin.000003 > recover_ivr.sql

如果从上次备份刷新binlog，到发现表被删掉的过程中产生了多个binlog。则要按照binlog产生的顺序进行恢复，那
么恢复的次序应该是按照binglog的产生的序号，从小到大依次恢复。
假如从上次备份，到发现表被删除，共有两个binlog文件，分别是test-150-bin.000002，test-150-bin.000003 ，
则按照binlog序号从小到大的排列，恢复的顺序应该是：

mysqlbinlog -d ivr  test-150-bin.000002  > recover_ivr.sql

mysqlbinlog -d ivr --stop-datetime='2014-01-26 17:03:35' test-150-bin.000003  > recover_ivr.sql

由于恢复的文件recover_ivr.sql中包含了整个ivr数据库的所有表，我们只要恢复指定的表mytable，还要对恢复出来的sql进行过滤。

cat recover_ivr.sql | grep  -A1 -B3 -i -E '^insert|^update|^delete|^replace|^alter' | grep -A1 -B3 mytable  > mytable.sql

可以先使用more确认一下
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
--
# at 122721
#140126 17:03:28 server id 150  end_log_pos 122721 CRC32 0xe0f851bb     Intvar
SET INSERT_ID=4/*!*/;
#140126 17:03:28 server id 150  end_log_pos 122838 CRC32 0x9efcc7b2     Query   thread_id=12578 exec_time=0     error_code=0
use `ivr`/*!*/;
SET TIMESTAMP=1390727008/*!*/;
INSERT INTO `testtuo` (`name`) VALUES ('d')
/*!*/;
# at 122838

将过滤后的结果保存为sql脚本，恢复到数据库即可。

恢复之前为了避免产生没有用的二进制日志，可以关闭二进制日志的记录

SET SESSION sql_log_bin=0;

恢复完成，启用记录二进制日志

SET SESSION sql_log_bin=1;