前言
在最近的工作中, 由于自己粗 (zuo) 心(si)误 update 操作导致几百行的数据出现错误, 在心急如焚的同时 (那时候我竟然不知道除了备份之后还有 binlog 日志恢复) 立马查资料学习 binlog 的恢复, 随后立马进行了恢复. 虽然可以假装自己没出错(emmmmm...... 最后还是得承认的!), 但下班之后心情不能久久平复, 立马打开电脑进行一次实验记录才能对得起自己犯的错误.
注: 此次实验是在 Wnidows 下进行的(网上 Linux 挺多, 但是 Windows 的甚少, 加上本身我的电脑也是 Win7 就简单做一次实验吧!
1, 初识 binlog
(1)MySQL 的 binlog 就是大家经常所说的 Binary Log, 即 bin-log, 是 MySQL 存储对数据库改动的二进制文件, 也就是记录了所有 DDL 与 DML(select 除外)语句, 利用它主要可以做两件事:
数据恢复: 通过 mysqlbinog 工具进行恢复;
数据复制: MySQL Replication 在 Master 端开启 binlog,Mster 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的.
(2)如何查看 MySQL 的日志情况(是否开启等), 当 MySQL 还没开启时候, 通过命令: show variables like 'log_bin%'查看
- mysql> show variables like 'log_bin%';
- +---------------------------------+-------+
- | Variable_name | Value |
- +---------------------------------+-------+
- | log_bin | OFF |
- | log_bin_basename | |
- | log_bin_index | |
- | log_bin_trust_function_creators | OFF |
- | log_bin_use_v1_row_events | OFF |
- +---------------------------------+-------+
- 5 rows in set
(3)如何在 Wnidows 在修改 log_bin 状态为 ON 开启呢?
找到 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini 文件(注意: 是 C 盘下隐藏文件夹 ProgramData, 而不是 Program Files 下)
增加 / 修改常用的配置属性
(4)常用简单属性说明: 除了以上 log_bin,binlog_format 两个简单的配置外, 还可以有其他的属性配置
- log-bin = /xxx/xxx/mysql_bin #binlog 日志文件, 以 mysql_bin 开头, 六个数字结尾的文件: mysql_bin.000001, 并且会将文件存储在相应的 xxx/xxx 路径下, 如果只配置 mysql_bin 的话默认在 C:\ProgramData\MySQL\MySQL Server 5.7\Data 下;
- binlog_format = ROW #binlog 日志格式, 默认为 STATEMENT: 每一条 SQL 语句都会被记录; ROW: 仅记录哪条数据被修改并且修改成什么样子, 是 binlog 开启并且能恢复数据的关键;
- expire_logs_days= 7 #binlog 过期清理时间;
- max_binlog_size = 100m #binlog 每个日志文件大小;
- binlog_cache_size = 4m #binlog 缓存大小;
- max_binlog_cache_size = 512m #最大 binlog 缓存大小.
2, 恢复数据测试
(1)准备表 user
- mysql> select * from user;
- +----+----------+----------------------------------+
- | id | name | password |
- +----+----------+----------------------------------+
- | 1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
- | 2 | Lisi | 2d73641c8e5111e8af74201a060059ce |
- | 3 | Wangwu | 2d73670c8e5111e8af74201a060059ce |
- +----+----------+----------------------------------+
- 3 rows in set
(2)误 update 恢复, 比如我在 update user set name = 'Lijian' where id = 1; 的时候忘写 where id = 1 重要条件, 结果导致全部数据被更新
- mysql> update user set name ='Lijian';
- Query OK, 3 rows affected
- Rows matched: 3 Changed: 3 Warnings: 0
- mysql> select*from user;
- +----+--------+----------------------------------+
- | id | name | password |
- +----+--------+----------------------------------+
- | 1 | Lijian | 2d7284808e5111e8af74201a060059ce |
- | 2 | Lijian | 2d73641c8e5111e8af74201a060059ce |
- | 3 | Lijian | 2d73670c8e5111e8af74201a060059ce |
- +----+--------+----------------------------------+
- 3 rows in set
这个时候你肯定很慌, 但是先不要慌 (实际上慌也没用), 先看没有备份, 如果没有再看是否开启 binlog(show variables like 'log_bin%'), 如果两者都没有(我相信大家都会定时备份 + binlog) 从数据库这个层面是无法恢复的了, 如果 binlog 开启的话, 一切都好说. 就开始执行下面几步恢复吧!
第一步: 找到当前 mysql 记录的 binlog 文件, 执行 show master status;
第二步: 查看 binlog, 定位误操作的 pos 或者时间段. 执行 show binlog events in 'mysql_bin.000001';
- mysql> show binlog events in 'mysql_bin.000001';
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- | mysql_bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.12-log, Binlog ver: 4 |
- | mysql_bin.000001 | 123 | Previous_gtids | 1 | 154 | |
- | mysql_bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql_bin.000001 | 219 | Query | 1 | 291 | BEGIN |
- | mysql_bin.000001 | 291 | Table_map | 1 | 344 | table_id: 108 (test.user) |
- | mysql_bin.000001 | 344 | Update_rows | 1 | 650 | table_id: 108 flags: STMT_END_F |
- | mysql_bin.000001 | 650 | Xid | 1 | 681 | COMMIT /* xid=22 */ |
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- 7 rows in set
第三步: 进入
C:\ProgramData\MySQL\MySQL Server 5.7\Data 执行 mysqlbinlog --start-position=219 --stop-position=681 mysql-bin.000001> e:\\update.sql 将 update 部分单独备份出来到 E 盘下为 update.sql
第四步: 登录 mysql(mysql -uroot -p123);
第五步: 执行 source e:update.sql 恢复数据, 部分截图如下:
第六步: 查看结果
- mysql> select * from user;
- +----+----------+----------------------------------+
- | id | name | password |
- +----+----------+----------------------------------+
- | 1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
- | 2 | Lisi | 2d73641c8e5111e8af74201a060059ce |
- | 3 | Wangwu | 2d73670c8e5111e8af74201a060059ce |
- +----+----------+----------------------------------+
- 3 rows in set
3, 总结
(1)使用 binlog 只能针对针对数据量不是很多的情况, 真正的生产环境每个几个 G 的日志文件, 不止是光靠 binlog 恢复的, 还有更多的办法, 在此只是做一个简单的学习记录!
(2)判断时间 binlog 日志的时间阶段与 pos 位置很重要, 但是需要知道关于 binlog 的很多参数!
(3)之前天真的以为开发人员就不需要太了解数据库相关的运维, 但是今天经历过才知道数据库的相关知识也是开发人员必须了解的!
来源: https://www.cnblogs.com/jian0110/p/9404773.html