【MySQL】误删/误改数据库数据后恢复（基于mysqlbinlog文件）

误删数据库数据后恢复数据库

1. 首先确保mysql数据库开启了binlog功能

• 在数据库执行show variables like 'log_%';，查看是否开启了binlog，并查看binlog文件的位置

• 注：在docker容器中的mysql，查出来的位置是在docker容器中的位置
  

• 找到最新的binlog文件，我这里时binlog.000021
  

2. 查看binlog日志

• 在SQL中执行show binlog events in 'binlog文件名'，查看mysql历史日志
  

3.找到误删/误改的位置

• 从表格info栏可以看到，每个操作从BEGIN开始，COMMIT结束
• Event_type可以看到操作类型
• 找到错误操作的BEGIN前一个的End_log_pos作为结束pos
• 一般开始pos是第一条数据的pos字段，一般数值比较小
  

4.删除目标数据库所有表和数据（做好备份）

• 因为执行恢复数据时会有建表操作，如果不删表存在的话就会报错

5.恢复数据

• 在shell窗口中执行语句mysqlbinlog --no-defaults --database=数据库名 /var/lib/mysql/binlog文件名 --start-position=开始pos --stop-position=结束pos -v | mysql -uroot -p，即可恢复
• 注：在docker容器中的mysql，使用命令docker exec -it 容器名/容器id bash进入容器内部再运行该语句
• 涉及参数
  • --start-position指定开始位置上述show binlog events in 'binlog文件名'语句查出来的表中的Pos字段
  • --stop-position指定开始位置上述show binlog events in 'binlog文件名'语句查出来的表中的End_log_pos字段
  • --database指定数据库执行，即只执行该数据库相关的操作
• mysqlbinglog参数表

-?, --help                      # 显示帮助信息并退出
--base64-output=name            # binlog输出语句的base64解码 分为三类：默认是值auto ,仅打印base64编码的需要的信息，如row-based 事件和事件的描述
                                  信息。never 仅适用于不是row-based的事件 decode-rows   配合--verbose选项一起使用解码行事件到带注释的伪SQL语句
--bind-address=name             # 绑定的IP地址
--character-sets-dir=name       # 字符集文件的目录
-d, --database=name             # 仅列出此数据库的条目（仅限本地日志）
--rewrite-db=name               # 将行事件重写为指向，以便将其应用于新数据库
-#, --debug[=#]                 # 输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
--debug-check                   # 当程序退出时打印一些调试信息
--debug-info                    # 当程序退出时打印调试信息和内存和CPU使用统计信息
--default-auth=name             # 要使用的默认身份验证客户端插件
-D, --disable-log-bin           # 禁用binlog日志，若开启--to-last-log并发送输出文件到相同的mysql server。这种方式避免无限循环。在规避数据库崩
                                  溃恢复数据的时候有用。注意：需要super权限来使用此选项
-F, --force-if-open             # 若binlog非正常关闭，强制开启binlog，默认是on可使用--skip-force-if-open关闭
-f, --force-read                # 强制读取未知的binlog事件
-H, --hexdump                   # 使用十六进制和ASCII码导出输出的信息
-h, --host=name                 # 获取binlog的服务名
-i, --idempotent                # 通知服务器使用幂等模式应用行事件
-l, --local-load=name           # 准备LOAD DATA INFILE的本地临时文件指定目录
-o, --offset=#                  # 跳过前n个条目
-p, --password[=name]           # 连接到服务器的密码
--plugin-dir=name               # 客户端插件的目录
-P, --port=#                    # 用于连接的端口，0表示默认值。端口使用的优先级：my.cnf，$ MYSQL_TCP_PORT,/etc/services，内置默认值(3306)
--protocol=name                 # 用于连接的协议(tcp, socket, pipe, memory)
-R, --read-from-remote-server   # 从MySQL服务器读取二进制日志，是read-from-remote-master = BINLOG-DUMP-NON-GTIDS的别名。
--read-from-remote-master=name  
--raw                           # 配合参数-R一起使用,输出原始的binlog数据而不是SQL语句
-r, --result-file=name          # 输出指定的文件，和--row一起使用，此时是数据文件的前缀
--secure-auth                   # 如果客户端使用旧的（4.1.1之前的）协议，则拒绝连接到服务器
--server-id=#                   # 提取给定id的服务器创建的binlog条目                
--server-id-bits=#              # 设置server-id中的有效位数
--set-charset=name              # 添加'SET NAMES character_set' 到输出
-s, --short-form                # 仅适用于常规查询，没有额外的信息和row-based事件信息。仅用于测试，不使用于生产环境。如果你想抑制
                                  base64-output，考虑使用--base64-output = never代替
-S, --socket=name               # 连接时使用的socket文件
--ssl-mode=name                 # SSL连接模式
--ssl-ca=name                   # PEM格式的CA文件
--ssl-capath=name               # CA目录
--ssl-cert=name                 # PEM格式的X509证书
--ssl-cipher=name               # 要使用的SSL密码
--ssl-key=name                  # PEM格式的X509密钥
--ssl-crl=name                  # 证书吊销列表
--ssl-crlpath=name              # 证书吊销列表路径
--tls-version=name              # 要使用的TLS版本，允许值为：tlsv1、tlsv1.1
--start-datetime=name           # binlog文件读取的起始时间点，可接受datetime和timestamp类型，格式2004-12-25 11:25:56
-j, --start-position=#          # 从N位置开始读取binlog。适用于命令行上传递的第一个binlog
--stop-datetime=name            # binlog文件读取的结束时间点
--stop-never                    # 等待来自服务器的更多数据，而不是在最后一个日志结束时停止。隐式地设置--to-last-log ，但不是在最后一个日志结
                                  束时停止而是继续等待直到服务器断开连接
--stop-never-slave-server-id=#  # 从服务器server_id使用--read-from-remote-server --stop-never。该选项不能和--connection-server-id一起使用
--connection-server-id=#        # 从服务器server_id使用--read-from-remote-server,该选项不能和--stop-never-slave-server-id一起使用
--stop-position=#               # binlog文件结束的时间点
-t, --to-last-log               # 和-r一起使用，不会在请求的binlog结尾处停止，而是继续打印，直到mysql服务器的最后一个binlog结束。如果将输出发
                                  送到同一个MySQL服务器，可能会导致无休止的循环
-u, --user=name                 # 连接到服务器用户名
-v, --verbose                   # 重新构建伪SQL语句的行信息输出，-v -v会增加列类型的注释信息
-V, --version                   # 打印版本信息
--open-files-limit=#            # 打开文件的限制，用于保留文件描述符以供此程序使用
-c, --verify-binlog-checksum    # 验证binlog的事件信息
--binlog-row-event-max-size=#   # 指定基于行的binlog的大小，改值必须是256的倍数
--skip-gtids                    # 不要保留全局事务标识符，而是让服务器像执行新事务一样执行这些事务。
--include-gtids=name            # 打印提供了全局事务标识符的事件
--exclude-gtids=name            # 打印所有事件，但提供全局事务标识符的事件除外