mysql数据恢复，使用binlog配置恢复未备份数据

使用mysqlbinlog配置，恢复数据库

什么是mysqlbinlog

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

使用mysqlbinlog功能必须先在mysql配置binlog，开启binlog服务

配置binlog

一． 5.7之前，my.cnf/my.ini都在mysql得根目录下，只需要在mysqld下面添加配置

log-bin=mysql-bin

binlog-format=Row

server-id=1

解释：

1.开启binlog。=mysql-bin这部分参数可以改成一个路径存放binlog文件，默认是这个配置，文件存放到mysql得data文件夹内初始化生成mysql-bin.00000x/mysql-bin.index文件

2. binlog-format有3个参数

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

在MySQL 5.7.7之前，默认格式是 STATEMENT。在MySQL 5.7.7及更高版本中，默认值是ROW。 例外：在NDB群集中，默认值是MIXED; NDB群集不支持基于语句的复制。

用户必须有权SUPER 设置全局binlog_format值或会话值。

3. server-id，开启binlog必须设置此参数，否则报错，不能启动服务，值是一个随机数，在主从mysql配置中要设定不能重复得值

二．5.7之后

5.7之后my.cnf/my.ini被挪到了mysql根目录下得bin文件夹，我在此配置中开启binlog无效，在mysql根目录下配置my.cnf/my.ini，内容如下：

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

log-bin=mysql-bin

binlog-format=Row

server-id=1

即可正常使用，

备注：开启binlog配置后，个人使用得windows10系统，在用一些集成工具比如：phpstudy，wamp启动mysql得时候不能启动，权限不足，需要在cmd窗口下，使用管理员权限启动mysql服务。

三．配置好binlog之后检查配置是否开启

mysql> show variables like 'log_bin%'; 

Log_bin  NO开启binlog服务

四．删库之后使用备份与binlog文件恢复数据

新建数据库并建表填入数据如下

备份数据库，数据id=7，备份后添加几条数据，最终表数据如下

 

删除数据库，

恢复数，此时执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，理论说 已有得binlogn 文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；

      mysql> mysql> flush logs;

      mysql> show master status;

导入备份数据库文件，恢复如下

此时可以看到，id=8，9，10内容我们并没有备份，使用mysql命令查看最新得binlog日志

mysql> show binlog events in 'mysql-bin.000001';

在11694进行了删库操作，恢复到删库操作前一个节点，11594

进入数据库数据文件目录，本人是windwos10系统，进入mysql下得data文件夹，

从binlog日志恢复数据

     

      恢复语法格式：

      # mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

 

        常用选项：

          --start-position=953                   起始pos点

          --stop-position=1437                   结束pos点

          --start-datetime="2013-11-29 13:18:54" 起始时间点

          --stop-datetime="2013-11-29 13:21:53"  结束时间点

          --database=zyyshop                     指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

           

        不常用选项：   

          -u --user=name              Connect to the remote server as username.连接到远程主机的用户名

          -p --password[=name]        Password to connect to remote server.连接到远程主机的密码

          -h --host=name              Get the binlog from server.从远程主机上获取binlog日志

          --read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

 

实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；

我们需要恢复到11694，命令如下：

报错如下：

一是在MySQL的配置my.cnf/my.ini中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用--no-defaults 命令参数

5.7之后，mysql不允许在命令行直接键入数据库密码，建议在输入密码提示之后在键入密码，命令行得密码可以忽略掉，或者在my.cnf/my.ini中配置

最终命令如下：

D:\phpStudy\PHPTutorial\MySQL\data> mysqlbinlog --no-defaults mysql-bin.000001 --stop-position 11694 | mysql -uroot

去看下数据库吧

id8，9，10在没有备份文件得情况下也恢复成功，还是要备份好数据库文件，不能所有数据完全依赖binlog日志恢复。binlog开启后，会占用不小得空间，以及一部分mysql性能。