文章目录

开启二进制日志功能关闭/打开二进制日志记录刷新二进制日志文件查看二进制日志文件的存储位置利用二进制日志文件恢复数据的本质二进制日志提取/导出到脚本文件中查看当前二进制日志的最后一个位置查看二进制日志文件的内容执行 SQL 脚本文件以恢复数据二进制日志分析指定恢复时间指定恢复位置数据恢复演示案例备份数据库操作数据恢复数据

开启二进制日志功能

在配置文件/etc/f中设置二进制日志文件的保存目录及文件名前缀：

log-bin=/var/lib/mysql/mybinlog/mysql-binserver-id=1

启用独占表空间:

innodb_file_per_table=1

说明：

1.如上的配置，数据库服务重启后，就自动生成一个二进制日志文件保存在 /var/lib/mysql/mybinlog 目录下，而且二进制日志文件的名称会以 mysql-bin 为前缀，在后面从序号 000001 开始命名。

第一次生成日志文件的名称为：mysql-bin.000001；如果你刷新了日志，则会生成一个新的日志文件，名称为：mysql-bin.000002；继续书刷新日志，则会生成名为：mysql-bin.000003 的日志文件，以此类推。

2.在配置变量 log-bin 的同时必须要配置变量 server-id，否则数据库服务根本无法重启。

3.启用独占表空间，则每个表都有自己独立的表空间文件；默认是共享表空间，即所有数据库使用一个表空间。至于这项功能是否必须开启，暂时不清楚

关闭/打开二进制日志记录

mysql> set global sql_log_bin=0;mysql> set global sql_log_bin=1;

刷新二进制日志文件

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

注：

1.会生成新的二进制日志文件

2.旧日志文件中的数据库操作日志并不会复制到新日志文件中

查看二进制日志文件的存储位置

mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value|+---------------------------------+-----------------------------------------+| log_bin| ON || log_bin_basename| /var/lib/mysql/mybinlog/mysql-bin || log_bin_index | /var/lib/mysql/mybinlog/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-----------------------------------------+6 rows in set (0.01 sec)

看变量 log_bin_basename 的值，得知二进制日志文件存放在目录 /var/lib/mysql/mybinlog/ 下

利用二进制日志文件恢复数据的本质

二进制日志机制其实就是把一些 DDL、DML 操作语句记录到二进制日志文件中，而你想利用该文件恢复数据，就必须找到相关数据的命令语句，然后去执行这些命令语句。所以你需要看得懂日志文件的内容，能够把相关命令语句提取出来。关于提取你想要的命令语句的命令请看下面。

二进制日志提取/导出到脚本文件中

--start-position：指定从哪个位置开始导出日志

--stop-position：指定导出日志的结束位置

--start-datetime：指定从哪个时间开始导出日志，时间格式为：-12-25 11:25:56

--stop-datetime：指定导出日志的结束时间

[root@htlwk0001host ~]# mysqlbinlog --no-defaults --start-position=1125 --stop-position=1344 /var/lib/mysql/mybinlog/mysql-bin.000004 > /root/test/001.sql

查看当前二进制日志的最后一个位置

mysql> show master status;

查看日志文件的最后一个位置其实没有什么意义。

查看二进制日志文件的内容

[root@htlwk0001host ~]# mysqlbinlog --no-defaults /var/lib/mysql/mybinlog/mysql-bin.000004

执行 SQL 脚本文件以恢复数据

[root@htlwk0001host ~]# mysql -uroot -p123456 -D test < /root/test/004.sql;

二进制日志分析

指定恢复时间

对于 MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定恢复数据的起止时间。

举例说明，假设在今天上午10:00（今天是4月20日），不小心删除一个表。要想恢复表和数据，你可以先恢复前晚上的备份（假设你有定时每天凌晨对数据库进行备份，备份后自动生成一个新的二进制日志文件 mysql-bin.00004），然后执行下面的语句：

[root@htlwk0001host ~]# mysqlbinlog --stop-date="-04-20 9:59:59" /var/log/mysql/mysql-bin.000004 | mysql -uroot -p123456

这条命令可能有问题，因为-04-20 9:59:59之前有很多数据库操作语句，这些操作语句不仅仅是前晚备份时间开始到-04-20 9:59:59之间的操作语句，前晚备份时间之前也有无数的操作语句，这些操作语句也包括在内吧，难道都执行吗？都执行肯定会报错，所以感觉有问题。

接着你可能要恢复-04-20 10:01:00之后产生的数据，你可以用起使日期和时间再次运行mysqlbinlog：

[root@htlwk0001host ~]# mysqlbinlog --start-date="-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -uroot -p123456

指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日期选项相同，不同的是给出日志内容中的位置号。使用日志位置是更准确的恢复方法，特别是执行破坏性SQL语句的同时发生许多事务的时候。要想确定位置号，你必须查看日志文件的内容以寻找执行了不期望的事务的时间范围，建议你将这个时间范围的日志数据导出到文本文件中以便进行检查，你可以执行下面的语句：

[root@htlwk0001host ~]# mysqlbinlog --start-date="-04-20 9:55:00" --stop-date="-04-20 10:05:00" /var/log/mysql/mysql-bin.000004 > /tmp/mysql_restore.sql

执行到停止位置为止的所有事务，即将数据恢复到指定的停止位置：

[root@htlwk0001host ~]# mysqlbinlog --stop-position="368312" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd

将恢复从给定的起始位置直到二进制日志结束的所有事务：

[root@htlwk0001host ~]# mysqlbinlog --start-position="368315" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd

数据恢复演示案例

备份数据库

先对某个数据库进行完整的备份：

[root@htlwk0001host ~]# mysqldump -h10.6.208.183 -uroot -p123456 -P3306 --single-transaction --master-data=2 test > test.sql

执行上述的命令语句后，会在当前工作目录下产生一个备份脚本文件 test.sql。

在 test.sql 文件中我们会看到：

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;

指备份后所有的更改将会保存到bin-log.000002二进制文件中，备份数据在日志文件中对应的位置是107。

操作数据

接着往test数据库下的student表中插入两条记录，然后执行flush logs命令产生一个新的二进制日志文件bin-log.000003。在flush logs之前，数据库的更改操作日志是保存在文件bin-log.000002中，既往表student增加记录的操作日志是保存在文件bin-log.00002中。

接着再往test数据库下teacher表中增加两条记录，然后删除student表和teacher表。执行命令flush logs之后，增加记录和删除表的操作日志都记录在新的二进制日志文件bin-log.000003中。

恢复数据

接着我们来恢复数据，首先导入全备数据：

[root@htlwk0001host ~]# mysql -h10.6.208.183 -uroot -p123456 -P3306 < test.sql

恢复 bin-log.000002：

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/bin-log.000002 | mysql -h10.6.208.183 -uroot -p123456 -P3306

恢复部分 bin-log.000003：

需要查看文件 bin-log.000003 中的内容，找到开始删除数据的位置，然后将这个位置之前的日志数据导出到 SQL 脚本文件中，然后执行该脚本文件以恢复被删除的数据；当然你找到恢复数据的位置点之后，也可以直接指定这个位置点，通过下面的语句来恢复数据：

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000003 --stop-position=208 | mysql -h10.6.208.183 -uroot -p123456 -P3306