binlog基于时间点的恢复

1：还原、恢复测试
创建数据库，并用drop语句模拟数据库宕机，通过mysqldump和musqlbinlog来还原和恢复

1.1：在test下创建table  chanelfollowing
mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bittest        |
| char_test      |
| enum_test      |
| people         |
| test           |
| test1          |
| test2          |
| test3          |
+----------------+

mysql> create table chanel_following (id int primary key ,uid int not null);
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| bittest          |
| chanel_following |
| char_test        |
| enum_test        |
| people           |
| test             |
| test1            |
| test2            |
| test3            |
+------------------+

mysql> show columns from chanel_following;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| uid   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1.2：通过mysqldump备份
（为还原实验做准备）：为输入方便，另开一窗口。
# mysqldump -uroot -p123456 -B test --table chanel_following > chanelfollowing.sql
Enter password:

备份成功。

注意：
mysqldump、mysqladmin、mysqlbinlog 等工具是在linux命令行下输入。
如果没有特别写明备份路径，则默认在当前路径下，而不是mysql数据目录下。

查看备份内容：
vi chanelfollowing.sql 
-- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.6.16-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


--
-- Table structure for table `chanel_following`
--


DROP TABLE IF EXISTS `chanel_following`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `chanel_following` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `chanel_following`
--


LOCK TABLES `chanel_following` WRITE;
/*!40000 ALTER TABLE `chanel_following` DISABLE KEYS */;
/*!40000 ALTER TABLE `chanel_following` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


-- Dump completed on 2016-08-24  13:05:19
通过备份文档只针对表chanel_following，说明备份正确。


1.3：在chanel_following 表下插入数据
mysql> insert into chanel_following values(102,100000),(3,69686869),(2132723743,2132723743);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0


查看插入是否正确：
mysql> select * from chanel_following;
+------------+------------+
| id         | uid        |
+------------+------------+
|          3 |   69686869 |
|        102 |     100000 |
| 2132723743 | 2132723743 |
+------------+------------+
3 rows in set (0.00 sec)

此处顺便做了个关于int的实验。如果细心，就会发现，我插入的第三个数据与显示的不一样。这是因为int最大能显示为2147483647，
存的超出了它的最大值，就截断了，也并不是少一位为2132723743。因为uid属性我并没设置为非负unsigned，所以不是4294967295。

1.4：drop 表，模拟宕机
mysql> drop table chanel_following;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from chanel_following;
ERROR 1146 (42S02): Table 'test.chanel_following' doesn't exist

1.5：通过备份文档还原数据库
在1.2步，我们把表备份在了chanelfollowing.sql。这里要注意路径。
[root@mysql data]# mysql -uroot -p test < chanelfollowing.sql
Enter password:

查看恢复结果：
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| bittest          |
| chanel_following |
| char_test        |
| enum_test        |
| people           |
| test             |
| test1            |
| test2            |
| test3            |
+------------------+
成功。


也可以在mysql下用source命令：
mysql> source /opt/chanelfollowing.sql
Query OK, 0 rows affected (0.00sec)
……
Query OK, 0 rows affected (0.00sec)

mysql> show columns from chanel_following;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| uid   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
还原成功。


注意，mysqldump是用来做备份，不能够恢复。恢复用的是mysql命令。 

1.6：mysqlbinlog恢复
用mysqldump还原到表chanel_following建立，还有数据还没恢复，用mysqlbinlog恢复。
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       402 |
| mysql-bin.000002 |      1726 |
+------------------+-----------+

mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                       |
+------------------+------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         1 |         120 | Server ver: 5.6.16-log, Binlog ver: 4                                                                                                                      |
| mysql-bin.000002 |  120 | Query       |         1 |         199 | BEGIN                                                                                                                                                      |
| mysql-bin.000002 |  199 | Table_map   |         1 |         259 | table_id: 71 (backup_test.test)                                                                                                                            |
| mysql-bin.000002 |  259 | Delete_rows |         1 |         309 | table_id: 71 flags: STMT_END_F                                                                                                                             |
| mysql-bin.000002 |  309 | Xid         |         1 |         340 | COMMIT /* xid=21 */                                                                                                                                        |
| mysql-bin.000002 |  340 | Query       |         1 |         419 | BEGIN                                                                                                                                                      |
| mysql-bin.000002 |  419 | Table_map   |         1 |         479 | table_id: 71 (backup_test.test)                                                                                                                            |
| mysql-bin.000002 |  479 | Delete_rows |         1 |         544 | table_id: 71 flags: STMT_END_F                                                                                                                             |
| mysql-bin.000002 |  544 | Xid         |         1 |         575 | COMMIT /* xid=23 */                                                                                                                                        |
| mysql-bin.000002 |  575 | Query       |         1 |         717 | use `test`; create table chanel_following (id int primary key ,uid int not null)                                                                           |
| mysql-bin.000002 |  717 | Query       |         1 |         796 | BEGIN                                                                                                                                                      |
| mysql-bin.000002 |  796 | Query       |         1 |         954 | use `test`; insert into chanel_following values(102,100000),(3,69686869),(2132723743,2132723743)                                                           |
| mysql-bin.000002 |  954 | Xid         |         1 |         985 | COMMIT /* xid=66 */                                                                                                                                        |
| mysql-bin.000002 |  985 | Query       |         1 |        1114 | use `test`; DROP TABLE `chanel_following` /* generated by server */                                                                                        |
| mysql-bin.000002 | 1114 | Query       |         1 |        1253 | use `test`; DROP TABLE IF EXISTS `chanel_following` /* generated by server */                                                                              |
| mysql-bin.000002 | 1253 | Query       |         1 |        1469 | use `test`; CREATE TABLE `chanel_following` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| mysql-bin.000002 | 1469 | Query       |         1 |        1598 | use `test`; /*!40000 ALTER TABLE `chanel_following` DISABLE KEYS */                                                                                        |
| mysql-bin.000002 | 1598 | Query       |         1 |        1726 | use `test`; /*!40000 ALTER TABLE `chanel_following` ENABLE KEYS */                                                                                         |
+------------------+------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

可以看出，整个数据插入在796到985之间。下面做恢复：

进入data目录：
[root@mysql data]# mysqlbinlog mysql-bin.000002 --start-position=796 --stop-position=985 | mysql -uroot -p
Enter password:

恢复好了。终于没报错，检查一下：
如果遇到下面错误：
mysqlbinlog: unknown variable 'default-character-set=utf8'
vi /etc/my.cnf
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
#default-character-set=utf8                                                   注释，在恢复完以后在打开

mysql> select * from chanel_following;
+------------+------------+
| id         | uid        |
+------------+------------+
|          3 |   69686869 |
|        102 |     100000 |
| 2132723743 | 2132723743 |
+------------+------------+

ok，测试完成。

另外注意，如果是盘坏掉，日志文件也会丢失。所以，要想恢复，我们需要MySQL服务器将MySQL二进制日志保存到安全的位置(RAID disks, SAN, ...)，
应为与数据文件的保存位置不同的地方，保证这些日志不在毁坏的硬盘上。(也就是，我们可以用--log-bin选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。