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 |
+——–+——+——–+