mysql 数据恢复实例
基于二进制日志数据恢复
mysql数据恢复演练实例
如何按需截取日志
基于position好的截取
--start-position=
--stop-position=
mysqlbinlog --start-position=219 --stop-position=1272 /data/binlog/mysql-bin.000002 >/tmp/back.sql
恢复
删除库 mysql> drop database oldboy1;
mysql> show databases;
恢复时不产生二进制日志
mysql> set sql_log_bin=0; //临时关闭二进制日志
mysql> source /tmp/bin.sql //恢复
基于时间点的截取
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
案例: 使用binlog日志进行数据恢复
模拟:
1.
[(none)]>create database binlog charset utf8mb4;
2.
[(none)]>use binlog;
[binlog]>create table t1(id int);
3.
[binlog]>insert into t1 values(1);
[binlog]>commit;
[binlog]>insert into t1 values(2);
[binlog]>commit;
[binlog]>insert into t1 values(3);
[binlog]>commit;
4.
[binlog]>drop database binlog;
恢复
1、找到起点和终点
mysql> show master status;
+------------------+----------+--------------+------------------+-----------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
+------------------+----------+--------------+------------------+-----------------
| mysql-bin.000002 | 1435 | | |
+------------------+----------+--------------+------------------+-----------------
mysql> show binlog events in 'mysql-bin.000002';
命令行:mysqlbinlog --start-position=219 --stop-position=1272 /data/binlog/mysql-bin.000002 >/tmp/back.sql
恢复
mysql> set sql_log_bin=0; //临时关闭二进制日志
mysql> source /tmp/bin.sql //恢复
# 5. 验证数据
mysql> use binlog
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
菜鸟9528号,请求开炮。