mysql基于时间恢复的一个过程

(一)创建表

[root@SR3 ~]# /opt/mysql/bin/mysql  -h10.86.87.155 -uroot  -pinfohow
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.16-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use clas
Database changed
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:14:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> use clas;
Database changed
mysql> create table upl (id int , user varchar(16)); 
Query OK, 0 rows affected (0.16 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:18:42 |
+---------------------+
1 row in set (0.00 sec)

（二）插入表数据

mysql> insert into upl
    -> values (1,'tom'),(2,'mary'),(3,'bean');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:19:55 |
+---------------------+
1 row in set (0.00 sec)

（3）删除id=2的一条数据

mysql> delete from upl where id=2;
Query OK, 1 row affected (0.05 sec)

mysql>  select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:20:29 |
+---------------------+
1 row in set (0.00 sec)

（4）插入表二条数据

mysql> insert into upl values (4,'moto'),(5,'nokia');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:21:06 |
+---------------------+
1 row in set (0.00 sec)

（5）刷新日志，查看日志

mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status\G;
*************************** 1. row ***************************
            File: mysqlSR3-bin.000006
        Position: 107
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:22:29 |
+---------------------+
1 row in set (0.00 sec)

（6）删除表

mysql> drop table up1;
ERROR 1051 (42S02): Unknown table 'up1'
mysql> drop table upl;
Query OK, 0 rows affected (0.07 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-11-26 08:22:57 |
+---------------------+
1 row in set (0.00 sec)

mysql>

 

 

（7）实现恢复

 

可以用以下语句将数据恢复到故障前

mysqlbinlog --stop-date='2013-11-26 08:14:11' /mysqldata/mysqlSR3-bin.000006 | mysql -h10.86.87.155 -u root -pinfohow

跳过故障的时间点，继续执行后面的binlog，完成恢复

mysqlbinlog --start-date='2013-11-26 08:18:42' /mysqldata/mysqlSR3-bin.000006 | mysql  -h10.86.87.155 -u root -pinfohow

 

 

[mysql@SR3 mysqldata]$ mysqlbinlog --stop-date='2013-11-26 08:14:11' /mysqldata/mysqlSR3-bin.000006 | mysql -h10.86.87.155 -u root -pinfohow
[mysql@SR3 mysqldata]$ mysqlbinlog --start-date='2013-11-26 08:18:42' /mysqldata/mysqlSR3-bin.000006 | mysql  -h10.86.87.155 -u root -pinfohow
ERROR 1051 (42S02) at line 23: Unknown table 'upl'
[mysql@SR3 mysqldata]$

或加-S  /var/lib/myql/mysql.sock

 

解决方案：