/ 2019-07-16
创建了一个库 db, 导入了表t1 ,t1表中录入了很多数据
一个开发人员,drop database db;
没有备份,日志都在.怎么恢复?
思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)
故障案例模拟:
(0) drop database if exists db ;
(1) create database db charset utf8;
(2) use db;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database db;
========================
drop database if exists db ;
create database db charset utf8;
use db;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db;
=======
运行以上语句,模拟故障场景
需求:将数据库恢复到以下状态(提示第9步和第13步是误操作,其他都是正常操作)
查看当前使用的 binlog文件
oldguo [db]>show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 1873 | | | |
+------------------+----------+--------------+------------------+-------------------+
2.查看事件:
第一段:
| mysql-bin.000006 | 813 | Query | 1 | 907 | use `db`; create table t1 (id int) |
| mysql-bin.000006 | 907 | Query | 1 | 977 | BEGIN |
| mysql-bin.000006 | 977 | Table_map | 1 | 1020 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1020 | Write_rows | 1 | 1070 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1070 | Table_map | 1 | 1113 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1113 | Write_rows | 1 | 1163 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1163 | Xid | 1 | 1194 | COMMIT /* xid=74 */ |
| mysql-bin.000006 | 1194 | Query | 1 | 1264 | BEGIN |
| mysql-bin.000006 | 1264 | Table_map | 1 | 1307 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1307 | Update_rows | 1 | 1353 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1353 | Xid | 1 | 1384 | COMMIT /* xid=77 */
mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql
第二段:
| mysql-bin.000006 | 1568 | Query | 1 | 1638 | BEGIN |
| mysql-bin.000006 | 1638 | Table_map | 1 | 1681 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1681 | Write_rows | 1 | 1731 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1731 | Xid | 1 | 1762 | COMMIT /* xid=81 */
mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql
3.恢复
set sql_log_bin=0;
source /tmp/bin1.sql
source /tmp/bin2.sql
set sql_log_bin=1;
oldguo [db]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 30 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
(1)截取
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql
(2)恢复
set sql_log_bin=0;
source /tmp/bin.sql
show variables like '%expire%';
expire_logs_days 0
自动清理时间,是要按照全备周期+1
set global expire_logs_days=8;
永久生效:
my.cnf
expire_logs_days=15;
企业建议,至少保留两个全备周期+1的binlog
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';
注意:不要手工 rm binlog文件
1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
*reset master; 主从关系中,主库执行此操作,主从环境必崩
(2)