mysql truncate恢复_求一个恢复truncate或drop table的完整案例

这两天自己找资料实现了上面的流程,详细步骤如下:

1. 锁定当前误操作表->改名->解锁

2. 创建新实例,恢复操作在新实例上进行

3. 先还原备份-->恢复备份到误操作之前的业务-->恢复误操作之后的业务

4. 确认恢复之后的表

5. 导入到生产库

1. 确认log_bin是否打开

mysql> show global variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

root@localhost [(none)]> use test1;

2. 建立测试表

create table t1 (

id int unsigned not null auto_increment,

name char(20) not null,

sex enum('f','m') not null default 'm',

address varchar(30) not null,

primary key(id)

);

insert into t1 (name,sex,address)values('daiiy','m','guangzhou');

insert into t1 (name,sex,address)values('tom','f','shanghai');

insert into t1 (name,sex,address)values('liany','m','beijing');

insert into t1 (name,sex,address)values('lilu','m','zhuhai');

root@localhost [test1]> select * from t1;

+----+-------+-----+---------+

| id | name  | sex | address |

+----+-------+-----+---------+

|  1 | daiiy | m   | zhuhai  |

|  2 | tom   | f   | zhuhai  |

|  3 | liany | m   | zhuhai  |

|  4 | lilu  | m   | zhuhai  |

+----+-------+-----+---------+

3. 清空binglog

reset master;

4. 备份test1数据库

select now();   #2017-06-22 08:42:26

mysqldump -uroot -p3308 --protocol tcp --host 192.168.220.128 --port 3308 --flush-logs --force --databases test1 >test1.dmp #备份指定数据库

mysql -S /home/data/3308/mysql.sock -uroot -p3308 -e "reset master"

5. 模拟备份后的业务操作

5.1 向t1表中添加记录

select now();  #2017-06-22 13:07:29

insert into t1 (name,sex,address)values('111','m','guangzhou');

insert into t1 (name,sex,address)values('222','f','shanghai');

5.2 创建表t2

select now();  #2017-06-22 13:07:56

create table t2 (a int);

insert into t2 values(10),(11),(12),(13),(14),(15);

select * from t2;

5.3 模拟误操作,删除表t1内容

select now();  #2017-06-22 13:08:35

truncate table t1;

5.4 向t2追加记录

select now();  #2017-06-22 13:09:02

insert into t2 values(16),(17),(18),(19),(20);

select * from t2;

5.5 继续向t1表追加记录

select now(); #2017-06-22 13:09:31

insert into t1 (name,sex,address)values('333','m','beijing');

insert into t1 (name,sex,address)values('444','m','zhuhai');

########## 要求恢复表t1以及t1后面新插入的记录

1. 首先LOCK 生产库被truncate的表,禁止所有的操作.

use test1;

lock tables t1 write;

alter table t1 rename to t1_old;

unlock table;

select now();  # 2017-06-22 13:17:26,恢复到这个时间点

2. 创建一个新实例如3308_recover

mkdir /home/data/3309

chown -R mysql:mysql /home/data/3309

cp /home/data/3308/my.cnf /home/data/3309

vi /home/data/3309/my.cnf

:1,$s/3308/3309/g   #把所有3308替换成3309

#log-bin = /home/data/3