mysql二进制日志导入_mysql利用二进制日志来进行恢复数据的实例操作

mysql 利用二进制日志来进行恢复数据的实例操作

备份开始前的工作环境准备：

1、创建用于保存二进制日志文件的目录

# mkdir /mybinlog

# chown mysql.mysql /mybinlog

2、修改配置文件

# vim /etc/my.cnf

log-bin=/mybinlog/mysql-bin 二进制日志目录及文件前缀

innodb_file_per_table = 1 启用innoDB表

datadir = /mydata/data 指定数据库的目录

3、为备份数据库创建存放点

# mkdir /mybackup

# chown -R mysql.mysql /mybackup

4、启动mysql服务器

# service mysqld start

5、插入需要备份的数据库

# mysql < jiaowu.sql

使用mysqldump工具完成完全备份

mysqldump用来温备份，首先需要为所有库加读锁,并且滚动一下二进制日志，记录当前二进制文件位置

# mysqldump –all-databases –lock-all-tables –routines –triggers –master-data=2 –flush-logs > /mybackup/alldatabase.sql

解释各个选项的意义：

–all-databases 备份所有数据库

–lock-all-tables 为所有表加锁

–routines 存储过程与存储函数

–triggers 触发器

–master-data=2 以change master to的方式记录位置，但默认为被注释

–flush-logs 执行日志滚动

备份二进制日志

# cp /mybinlog/mysql-bin.000001 /mybackup/alldatabase.000001

模拟数据库意外损坏，利用完全备份实现数据库的恢复

# rm -rf /mydata/data/*

# rm -rf /mybinlog/*

初始化mysql并启动服务器

# cd /usr/local/mysql/

# ./scripts/mysql_install_db –user=mysql –datadir=/mydata/data

删除二进制日志，启动服务

# rm -rf /mybinlog/*

# netstat -tnlp 查看启动的mysql进程号

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2523/mysql

# kill 2523 (注意，如果在备份之前mysql服务是开启的，重启是不成功的，需要把这个进程先kill掉)

# service mysqld restart

恢复到备份状态,导入备份的数据库文件：

# mysql < /mybackup/alldatabase.sql

模拟往students表中添加数据，添加完成后不小心将表删除了，我们要恢复到删除之前的状态，并且新加的数据还要存在

往students表中添加数据

mysql> use jiaowu;

mysql> insert into students (Name,Age,Gender) values (‘hadoop’,22,’M');

模拟一下，不小心将表删除了

mysql> drop tables students;

查看一下二进制日志文件的位置

mysql> show master status;

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000002 | 520351 | | |

+——————+———-+————–+——————+

先恢复完整数据(恢复过程不要记录在日志中)

mysql> set global sql_log_bin=0;

# mysql < /mybackup/alldatabase.sql

查看删除表时的记录位置

#mysqlbinlog /mybinlog/mysql-bin.000002 内容如下：

# at 520084

#130501 16:44:08 server id 1 end_log_pos 520212 Query thread_id=2 exec_time=0 error_code=0

use jiaowu/*!*/;

SET TIMESTAMP=1367397848/*!*/;

insert into students (Name,Age,Gender) values (‘hadoop’,22,’M')

/*!*/;

# at 520212

#130501 16:44:08 server id 1 end_log_pos 520239 Xid = 308

COMMIT/*!*/;

# at 520239 删除命令在这个时刻开始执行的

#130501 16:45:37 server id 1 end_log_pos 520351 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1367397937/*!*/;

DROP TABLE `students` /* generated by server */

/*!*/;

DELIMITER ;

# End of log file

将二进制文件中完整备份到删除表之前的记录导出

#mysqlbinlog –stop-position=520239 /mybinlog/mysql-bin.000002 > /root/change.sql

解释：

–start-position 指定从哪开始导出二进制日志

–stop-position 指定到哪结束

–start-datetime 从哪个时间开始

–stop-datetime 到哪个时间结束

将改变的数据库日志导入到mysql库中

# mysql < /root/change.sql

见证奇迹的时刻数据库恢复成功，并且插入的数据也还原回来了

mysql> select Name,Age,Gender from students where Name=’hadoop’;

+——–+——+——–+

| Name | Age | Gender |

+——–+——+——–+

| hadoop | 22 | M |

+——–+——+——–+