首先,Binlog恢复数据,最实用的场景是:先用备份恢复了之前一个时间点的数据,再用Binlog恢复该时间点后的增量数据。
当然,如果数据库从建库开始就一直开启了binlog,用于恢复所有的库表数据也是可行的。
查看数据库是否开启了binlog
show variables like 'log_bin';
如果查询结果是ON,则表示你已经开启了binlog,如果是false,后续操作没有参考价值。
恢复binlog可以分为以下几步
- 查询数据库已存在的Binlog文件
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 7107185 |
+------------------+-----------+
- 方法1:根据数据需要恢复的时间段,二进制的binlog文件转为sql
mysqlbinlog --no-defaults --start-datetime="2020-12-09 10:53:50" --stop-datetime="2020-12-09 11:53:50" --database=dolphin_oa /usr/local/mysql/data/mysql-bin.000002 >/opt/dolphin/all.sql
说明
1.--no-defaults: 防止语句执行出现mysqlbinlog: unknown variable 'default-character-set=utf8mb4' 报错
2.database=xxxx:指定数据库
这里额外说明一下如何找到binlog文件位置。
#查看mysql配置文件
vim /etc/my.cnf
#找到下面这个配置
...
log-bin=mysql-bin
...
默认情况下值为mysql-bin,是存放在mysql安装目录下的data文件夹下。例如:
/usr/local/mysql/data
也可能是自定义的一个路径
- 方法2:根据pos点来选择恢复的数据段
-- 查看指定的binlog文件
mysql> show binlog events in 'mysql-bin.000002';
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
mysql-bin.000002 5817543 Query 24 5817630 BEGIN
mysql-bin.000002 5817630 Table_map 24 5817696 table_id: 5340 (bi_meta_3.0.session)
mysql-bin.000002 5817696 Update_rows 24 5817828 table_id: 5340 flags: STMT_END_F
mysql-bin.000002 5817828 Xid 24 5817859 COMMIT /* xid=25163209 */
例如我需要恢复的是Pos在5817543和5817828之间的数据,那么我需要执行的语句为:
mysqlbinlog --no-defaults --start-position=5817543 --stop-position=5817828 --database=dolphin_oa /usr/local/mysql/data/mysql-bin.000002 >/opt/dolphin/all.sql
再将该转储的sql在mysql下执行即可恢复数据。
另:如果你需要恢复所有的数据,即你没有做任何备份,却删了库,那在数据库一开始就打开BinLog的情况下,你需要将所有的blinlog文件全部转为sql文件并按照顺序执行。