mysql数据恢复

mysql在默认安装的情况下是不打开mysqlbinlog的，

登录mysql执行以下命令结果如下所示：

mysql> show binlog events;
Empty set (0.00 sec)

mysql> 

返回结果为空

所以要修改配置文件，打开这个选项

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysqlbin-log

很简单，log-bin=mysqlbin-log ,名字自定义，至于这里有啥坑，目前还不知道，我一直用这个名字

然后执行show binlog events

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

mysql> show binlog events;
+---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+
| Log_name            | Pos  | Event_type  | Server_id | End_log_pos | Info                                                             |
+---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+
| mysqlbin-log.000001 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.33-log, Binlog ver: 4                            |
| mysqlbin-log.000001 |  107 | Query       |         1 |         174 | BEGIN                                                            |
| mysqlbin-log.000001 |  174 | Intvar      |         1 |         202 | INSERT_ID=3                                                      |
| mysqlbin-log.000001 |  202 | Query       |         1 |         317 | use `url`; insert into urlinfo(url,info) values("sogou","hello") |
| mysqlbin-log.000001 |  317 | Query       |         1 |         385 | COMMIT                                                           |
| mysqlbin-log.000001 |  385 | Query       |         1 |         452 | BEGIN                                                            |
| mysqlbin-log.000001 |  452 | Query       |         1 |         544 | use `url`; delete from urlinfo where id=3                        |
| mysqlbin-log.000001 |  544 | Query       |         1 |         612 | COMMIT                                                           |
| mysqlbin-log.000001 |  612 | Query       |         1 |         679 | BEGIN                                                            |
| mysqlbin-log.000001 |  679 | Intvar      |         1 |         707 | INSERT_ID=4                                                      |
| mysqlbin-log.000001 |  707 | Query       |         1 |         822 | use `url`; insert into urlinfo(url,info) values("sogou","hello") |
| mysqlbin-log.000001 |  822 | Query       |         1 |         890 | COMMIT                                                           |
| mysqlbin-log.000001 |  890 | Query       |         1 |         957 | BEGIN                                                            |

后面还有很多，省略了，可以看到精确的记录了我的操作记录

同时查看mysql数据所在的目录会增加了mysqlbin-log

[root@Git mysql]# ll
total 28700
-rw-rw----. 1 mysql mysql 18874368 Dec 16 20:16 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Dec 16 20:16 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 Aug 13 04:21 ib_logfile1
drwx------. 2 mysql mysql     4096 Aug 13 04:21 mysql
-rw-rw----  1 mysql mysql     2448 Dec 16 20:14 mysqlbin-log.000001
-rw-rw----  1 mysql mysql      107 Dec 16 20:16 mysqlbin-log.000002
-rw-rw----  1 mysql mysql       44 Dec 16 20:16 mysqlbin-log.index
srwxrwxrwx  1 mysql mysql        0 Dec 16 20:16 mysql.sock
drwx------  2 mysql mysql     4096 Dec  6 06:09 mywebsite
drwx------. 2 mysql mysql     4096 Aug 13 04:21 test
drwx------. 2 mysql mysql     4096 Aug 13 04:35 url

这里对比测试做的不是很好，其实mysqlbin-log.000001和mysqlbin-log.000002是新增加的，记录了操作日志，同时这里需要注意的是，每重启一次mysqld，mysqlbing-log会在原来的基础上+1，命名。

同时你还会发现，为什么 show binlog events 怎么没有mysqlbin-log.000002。OK执行下面的这条命令就会有了

摘自官方：

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.

mysql> show binlog events in 'mysqlbin-log.000002';
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name            | Pos | Event_type  | Server_id | End_log_pos | Info                                                              |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysqlbin-log.000002 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.33-log, Binlog ver: 4                             |
| mysqlbin-log.000002 | 107 | Query       |         1 |         174 | BEGIN                                                             |
| mysqlbin-log.000002 | 174 | Intvar      |         1 |         202 | INSERT_ID=5                                                       |
| mysqlbin-log.000002 | 202 | Query       |         1 |         318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") |
| mysqlbin-log.000002 | 318 | Query       |         1 |         386 | COMMIT                                                            |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
5 rows in set (0.00 sec)

下面进入数据恢复

数据库及时间如下所示：

mysql> select * from urlinfo;
+----+----------------+-------+
| id | url            | info  |
+----+----------------+-------+
|  1 | www.baidu.com  | 0     |
|  2 | www.google.com | 0     |
|  4 | sogou          | hello |
|  5 | sogou1         | hello |
+----+----------------+-------+
4 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-12-16 20:30:18 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

插入一条数据

mysql> insert into urlinfo(url,info) values("soso","hello");
Query OK, 1 row affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-12-16 20:31:25 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from urlinfo;
+----+----------------+-------+
| id | url            | info  |
+----+----------------+-------+
|  1 | www.baidu.com  | 0     |
|  2 | www.google.com | 0     |
|  4 | sogou          | hello |
|  5 | sogou1         | hello |
|  6 | soso           | hello |
+----+----------------+-------+
5 rows in set (0.00 sec)

mysql> 

然后把新插入的数据删除

mysql> delete from urlinfo where url='soso';
Query OK, 1 row affected (0.05 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
|2013-12-16 20:35:29 |
+---------------------+
1 row in set (0.01 sec)

mysql> select * from urlinfo;
+----+----------------+-------+
| id | url            | info  |
+----+----------------+-------+
|  1 | www.baidu.com  | 0     |
|  2 | www.google.com | 0     |
|  4 | sogou          | hello |
|  5 | sogou1         | hello |
+----+----------------+-------+
4 rows in set (0.00 sec)

mysql> 

操作记录查看

mysql> show binlog events in 'mysqlbin-log.000002';
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name            | Pos | Event_type  | Server_id | End_log_pos | Info                                                              |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysqlbin-log.000002 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.33-log, Binlog ver: 4                             |
| mysqlbin-log.000002 | 107 | Query       |         1 |         174 | BEGIN                                                             |
| mysqlbin-log.000002 | 174 | Intvar      |         1 |         202 | INSERT_ID=5                                                       |
| mysqlbin-log.000002 | 202 | Query       |         1 |         318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") |
| mysqlbin-log.000002 | 318 | Query       |         1 |         386 | COMMIT                                                            |
| mysqlbin-log.000002 | 386 | Query       |         1 |         453 | BEGIN                                                             |
| mysqlbin-log.000002 | 453 | Intvar      |         1 |         481 | INSERT_ID=6                                                       |
| mysqlbin-log.000002 | 481 | Query       |         1 |         595 | use `url`; insert into urlinfo(url,info) values("soso","hello")   |
| mysqlbin-log.000002 | 595 | Query       |         1 |         663 | COMMIT                                                            |
| mysqlbin-log.000002 | 663 | Query       |         1 |         730 | BEGIN                                                             |
| mysqlbin-log.000002 | 730 | Query       |         1 |         828 | use `url`; delete from urlinfo where url='soso'                   |
| mysqlbin-log.000002 | 828 | Query       |         1 |         896 | COMMIT                                                            |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+
12 rows in set (0.00 sec)

然后把那条数据恢复起来，也就是说数据应该是2013-12-16 20:35:29之前的数据

执行下面这条命令：

[root@Git mysql]# mysqlbinlog --database=url  --start-datetime="2013-12-16 20:29:00"  --stop-datetime="2013-12-16 20:34:00" mysqlbin-log.000002|mysql -h localhost -u root -phello -f 
[root@Git mysql]# 

然后

mysql> select * from urlinfo;
+----+----------------+-------+
| id | url            | info  |
+----+----------------+-------+
|  1 | www.baidu.com  | 0     |
|  2 | www.google.com | 0     |
|  4 | sogou          | hello |
|  5 | sogou1         | hello |
|  6 | soso           | hello |
+----+----------------+-------+
5 rows in set (0.00 sec)

删除的数据就出现了，我这是最最最简答的一个入门

关于mysqlbinlog的使用说明,可用的简单的命令参数：

mysqlbinlog支持下面的选项：

·         ---help，-？

显示帮助消息并退出。

·         ---database=db_name，-d db_name

使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。

·         --host=host_name，-h host_name

获取给定主机上的MySQL服务器的二进制日志。

·         --password[=password]，-p[password]

当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中--password或-p选项后面没有 密码值，则提示输入一个密码。

·         --port=port_num，-P port_num

用于连接远程服务器的TCP/IP端口号。

 --start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：

shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003

该选项可以帮助点对点恢复。

·         --stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复

简单的入门了解，然后自己继续构造数据进行试验