总结：mysql三种灾备与恢复使用解析。

精选原创

qw87112 2014-06-30 17:33:55 博主文章分类：MySQL ©著作权

文章标签 恢复 mysql 灾备热备 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者qw87112的原创作品，如需转载，请与作者联系，否则将追究法律责任

目标：

1.使用mysqldump实现从逻辑角度完全备份mysql，配合二进制日志备份实现增量备份

2.使用lvm照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份

3.使用percona公司的xrabackup实现完全热备份与增量热备份

环境准备：

建立目录用于存放二进制文件位置

mkdir /home/mybinlog
chown mysql:mysql /home/mybinlog

修改my.cnf

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

导入hellodb.sql，在库内创建study表，插入一些数据。

mysql <~/Desktop/hellodb.sql
use hellodb
create table study (id tinyint auto_increment primary key,name char(10));
insert into study (name) values ('redhat'),('php'),('asp'),('CCNA'),('MSIC'),('CCNP'),('HCSE');

创建用于存放备份的目录

mkdir /home/mysqlbackup \\用于存放备份文件
mkdir /home/mysqlbackup/mylogbin \\用于存放备份的二进制日志
mkdir /home/mysqlbackup/mylogstatus \\用于存放二进制日志起始位置
chown -R mysql:mysql /home/mysqlbackup

一、使用mysqldump实现完全热备+增量备份。

我们要使用mysqldump工具对innodb存储引擎的数据库做完全热备，并且滚动二进制日志，为了下次恢复或者增量方便，还要记录一下当前二进制日志文件位置。

mysqldump -uroot -predhat --single-transaction --master-data=2 --all-databases --routines --events > /home/mysqlbackup/mydb_all_`date +%F`.sql
flush logs
mysql -uroot -predhat -e 'show master status' > /home/mysqlbackup/mylogstatus/`date +%F-%H`.txt

选项注解：

--databases \\指定数据库名
--lock-all-tables \\为所有表加读锁，如果使用的是非事务型表，推荐使用这个选项备份。
--master-data=2 \\在备份文件中记录当前二进制日志的位置，并且为注释的，1是不注释掉在主从复制中才有意义
--single-transaction \\（使得转储的数据为事务开始前的数据）启动一个大事物，并为每张表创建快照，基于此项能实现热备InnoDB表，由此，不需要同时使用--lock-all-tables
--routines \\同时，备份存储过程和存储函数
--events \\同时，备份事件调度器代码
--all-databases \\备份服务器上的所有库

这时我们又给数据库添加了数据

create table system(id tinyint auto_increment primary key,Name char(10));
insert into system (Name) values ('windows'),('Centos'),('fedora');

做一次增量备份

查看刚刚做完全备份，滚动日志之后记录二进制日志文件与起始位置。

cat /home/mysqlbackup/mylogstatus/2014-06-30-15.txt

查看当前位置

show master status;

# mysqlbinlog --start-position=107 --stop-position=504 /home/mybinlog/mysql-bin.000007 >/home/mysqlbackup/incremental-`date +%F-%H`.sql

记录当前show master status 状态

mysql -uroot -predhat -e 'show master status' > /home/mysqlbackup/mylogstatus/`date +%F-%H`.txt

这时数据库又加入了一些数据

insert into system (Name) values ('rlel'),('suselinux'),('VMX');
create table firm(id tinyint auto_increment primary key ,name char(10))
insert into firm (name) values ('baidu'),('huabang'),('sohu');

模拟这时数据库突然崩了

service mysqld stop
rm -rf /mydata/data/*

假如重装了mysql，接下来完成恢复

./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# service mysqld start

导入完全备份与增量备份。

mysql < /home/mysqlbackup/mydb_all_2014-06-30.sql
mysql < /home/mysqlbackup/incremental-2014-06-30-16.sql

分析二进制日志，导出从上一次增量以后，到崩溃时候文件。

查看上一次记录的二进制日志文件和位置。

cat /home/mysqlbackup/mylogstatus/2014-06-30-16.txt

mysqlbinlog --start-position=504 /home/mybinlog/mysql-bin.000007 >/home/mysqlbackup/huifu-2014-06-30-17.sql
mysql -uroot -predhat < /home/mysqlbackup/huifu-2014-06-30-17.sql

为了下次做备份方便，再滚动下日志，记录下show master status; 状态。

这就是一次使用mysqldump完整备份+二进制日志进行恢复的实例。

二、使用LVM的快照实现热备。

前提是mysql的数据目录，要在lvm逻辑卷上。

创建逻辑卷，挂载这些就省略了。

在MySQL中为所有表加读锁，不要关闭终端，否则锁将失效,滚动日志

flush tables with read lock;
flush logs;

记录当前show master status状态，创建快照卷，释放读锁。

mysql -uroot -p -e 'show master status'>/var/mybackup/logstatus/`date +%F-+%H`.txt
lvcreate -L 200M -n mydata-snap -p r -s /dev/mapper/vg0-lv0
unlock tables;

挂载快照，拷备出来，卸载快照，删除快照

mkdir /mysnap
mount /dev/vg0/mydata-snap /mysnap/

cp /mysnap/* /var/mybackup/2014-06-30/ -a
umount /mysnap/
lvremove /dev/vg0/mydata-snap
这样备份就完成了

这时有用户创建了表，插入了数据。

create table uu(id int primary key,yy int);
insert into uu (id,yy) values (123,111),(223,121);

然后数据库崩溃了，现在测试下是否能完全+增量恢复。

/etc/init.d/mysqld stop
rm -rf /mydata/data/*

把备份数据拷贝回来，启动服务，查看记录的show mater status；状态，根据二进制日志导出.sql脚本，再导入执行sql脚本。

cp /var/mybackup/2014-06-30/* /mydata/data/ -a
service mysqld start
cat /var/mybackup/logstatus/2014-06-30-+22.txt

mysqlbinlog --start-position=107 /var/mybinlog/mysql-bin.000002 >/var/mybackup/incremental-2014-06-30-23.sql
mysql -uroot -predhat < /var/mybackup/incremental-2014-06-30-23.sql

好，lvm快照+二进制日志恢复完成。

三、使用xtrabackup实现对mysql进行热备（完全+增量）。

安装程序

yum install percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

完全热备数据库。

innobackupex --user=root --password=redhat /var/mybackup/

这时假设数据库崩溃了

service mysqld stop
rm -rf /mydata/data/*

预处理备份，恢复数据，修改属主属组，启动mysql。

innobackupex --apply-log /var/mybackup/2014-07-01_13-49-38/
innobackupex --copy-back /var/mybackup/2014-07-01_13-49-38/
chown -R mysql.mysql /mydata/
service mysqld start

服务启动了，恢复成功，每一次数据库恢复完成以后，要立即做一次完全备份。

innobackupex --user=root --password=redhat /var/mybackup/

数据库运行了一段时间，加入了一些数据。

create database redhat;
use redhat
create table study(id tinyint auto_increment primary key,students char(10),course char(10));
insert into study (students,course) values ('tuchao','linux'),('fangchao','bashshell'),('tyz','lvm'),('yujiaqing','auto china');
select * from study;

做第一次增量备份。

innobackupex --user=root --password=redhat --incremental /var/mybackup/incrbak/ --incremental-basedir=/var/mybackup/2014-07-01_14-41-41/
--incremental-basedir //这里要指定上一次最近的备份为基准，进行增量备份。

查看检查点文件

cat incrbak/2014-07-01_14-51-28/xtrabackup_checkpoints

$_G2HNDIUI%RL{GCL$W602[R.jpg$

再次给mysql新增一些数据。

insert into study (students,course) values ('wujihe','Java'),('qiulin','Ios kaifa');

做第二次增量备份。

注意：这里的--incremental-basedir=指的是上一次增量备份的位置

innobackupex --user=root --password=redhat --incremental /var/mybackup/incrbak/ --incremental-basedir=/var/mybackup/incrbak/2014-07-01_14-51-28/

查看检查点文件

cat incrbak/2014-07-01_15-11-57/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1667183

to_lsn = 1667534

last_lsn = 1667534

compact = 0

mysql又运行了一段时间，再次加入了一些数据。

create table test(id tinyint auto_increment primary key ,name char(10));
insert into test (name) values ('aaa'),('bbb'),('redhat'),('hello world'),('centos');
select * from test;