MySQL备份与恢复的三种方法总结

一.测试环境准备

1.1 mysql的安装就不说了，见 http://www.linuxidc.com/Linux/2012-04/58990.htm

1.2 编缉/etc/my.cnf把二进制日志存放目录改到其它非数据目录,innodb每表一文件

建立一目录用于存放二进制日志

1. mkdir /mybinlog
2. chown mysql:mysql /mybinlog

修改my.cnf

1. vim /etc/my.cnf
2. log-bin=/mybinlog/mysql-bin ##二进制日志目录及文件名前缀
3. innodb_file_per_table = 1 ##启用InnoDB表每表一文件，默认所有库使用一个表空间

启动mysqld

1. service mysqld start

1.3 创建一个测试库与测试表

1. mysql> create database laoguang;
2. mysql> use laoguang;
3. mysql> create table linux (id tinyint auto_increment primary key,name char(10));
4. mysql> insert into linux (name) values (‘apache’),(‘nginx’),(‘php’);

1.4 创建用于存放备份的目录

1. mkdir /myback
2. chown -R mysql:mysql /myback

二,用mysqldump实现备份

2.1 mysqldump用来温备，所以我们得为所有库加读锁,并且滚动一下二进制日志，并记录当前二进制文件位置

1. mysqldump –all-databases –lock-all-tables –routines –triggers –master-data=2 \
2. –flush-logs > /myback/2012-12-3.19-23.full.sql
3. –all-tables 备份所有库
4. –lock-all-tables 为所有表加读锁
5. –routinge 存储过程与函数
6. –triggers 触发器
7. –master-data=2 在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
8. –flush-logs 日志滚动一次

查看有没有备份成功，有没有启用新二进制的日志，查看备份的文件中有没有记录完整备份后二进制的位置

备份二进制日志

1. cp /mybinlog/mysql-bin.000001 /myback/2012-12-3.19-23.full.00001

2.2 模拟数据库意外损坏，测试完整恢复

1. rm -rf /data/mydata/*
2. rm -rf
3. /mybinlog/*

初始化mysql并启动mysql

1. cd /usr/local/mysql
2. ./scripts/mysql_install_db –user=mysql–datadir=/data/mydata
3. rm -rf /mybinlog/* ##因为我们不是全新初始化的，可能会有报错的二进制日志，我们不需要
4. service mysqld start ##启动时会重新生成新的二进制日志的

恢复到备份状态,备份前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的

1. mysql> set global sql_log_bin=0;
2. mysql < /myback/2012-12-3.19-23.full.sql ##如果有账号密码记的-u -h哦
3. 打开记录并查看恢复状况
4. mysql> set global sql_log_bin=1;
5. mysql> show databases;

打开二进制记录并查看恢复状况

1. mysql> set global sql_log_bin=1;
2. mysql> show databases;

2.3 模拟一种场景，我往linux表中新添加了数据，然后不小心将这个表删了，我们要恢复到删除之前的状态，并且新加的数据还存在。

2.3.1 新增数据

1. mysql> use laoguang;
2. mysql> insert into linux (name) values (‘haddop’), (‘mysql’);
3. mysql> drop table linux;
4. mysql> show master status; ##查看当前所在二进制日志中的位置
5. +——————+———-+————–+——————+
6. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
7. +——————+———-+————–+——————+
8. | mysql-bin.000001 | 9005 | | |
9. +——————+———-+————–+——————+

我们先恢复完整数据，再恢复完整备份后到删除之前的数据，对应二进制日志就是完整备份后的二进制日志位置到删除表之前的位置

2.3.2 先恢复完整备份，同样恢复过程不要记录日志

1. mysql > set global sql_log_bin=0;
2. mysql < /myback/2012-12-3.19-23.full.sql

2.3.3 查看删除表时的记录位置

1. mysqlbinlog /mybinlog/mysql-bin.000001
2. # at 8893
3. #121202 14:14:07 server id 1 end_log_pos 9005 Query thread_id=5exec_time=0error_code=0
4. SET TIMESTAMP=1354428847/*!*/;
5. DROP TABLE `linux` /* generated by server */
6. /*!*/;
7. DELIMITER ;
8. # End of log file

2.3.4 由上图可知删除是在8893时做的，将二进制文件中完整备份到删除表之前的记录导出

1. mysqlbinlog –stop-position=8893 /mybinlog/mysql-bin.000001 > /tmp/change.sql
2. –start-position 指定从哪开始导出二进制日志
3. –stop-position 指定到哪结束
4. –start-datetime 从哪个时间开始格式如”2005-12-25 11:25:56″
5. –stop-datetime 到哪个时间结束

由于这个二进制日志是我们完整恢复后才启用的，所以我们直接从头开始即可，如果你的二进制日志很多，请查看完整备份中记录的备份时的位置，从那开始到删除之前即可

将这段二进制记录应用到mysql的库中

1. mysql < /tmp/change.sql

进入数据库查看数据有没有恢复

1. mysql> select * from linux;

基于mysqldump通常我们就是完整备份+二进制日志来进行恢复的。