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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

目标:

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');

wKiom1Ow3EvglsoAAACORfpuzF0937.jpg

创建用于存放备份的目录

  • 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

wKioL1OxGXawDjl5AADnXptXL2o179.jpg

查看当前位置

  • show master status;

wKioL1OxGHuCT8PgAADBxKomiOM625.jpg

  • # 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');

wKioL1OxHvPDNQqeAAEyDuASp-s279.jpg

模拟这时数据库突然崩了

  • 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

wKiom1OxKvuAUogzAAD_goe9gos103.jpg

  • 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

wKiom1OxLZrTZaERAAGRNFqF-Sk032.jpg

为了下次做备份方便,再滚动下日志,记录下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);

wKiom1OxgB7iDMcFAABsZIxKkHI698.jpg

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

  • /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

wKiom1Oxg2DTY-raAADXDEVCwqQ040.jpg

  • 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

wKioL1OxhCqg2qLpAAEv3ofDhIA861.jpg

好,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;

wKiom1OyU1qhE8uGAADEtZh0hPQ938.jpg

做第一次增量备份。

  • 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

wKiom1OyXN3h92mKAAD9WV5AmJw273.jpg

再次给mysql新增一些数据。

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

wKioL1OyXqOTKaKcAAFncAyooeg011.jpg


做第二次增量备份。

注意:这里的--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;

wKioL1OyYhuCTOFvAACodkVZ8U8897.jpg

这时数据库崩溃了。

  • service mysqld stop

  • rm -rf /mydata/data/*

预处理完全备份

  • innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/

  • --redo-only //只做提交处理,不做回滚。

预处理第一个增量到完全备份

  • innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/ --incremental-dir=/var/mybackup/incrbak/2014-07-01_14-51-28/

预处理第二个增量到完全备份

  • innobackupex --apply-log --redo-only /var/mybackup/2014-07-01_14-41-41/ --incremental-dir=/var/mybackup/incrbak/2014-07-01_15-11-57/

切换到完全备份的目录里面

  • cd /var/mybackup/2014-07-01_14-41-41/

查看检查点文件

  • cat xtrabackup_checkpoints

backup_type = full-prepared

from_lsn = 0

to_lsn = 1667534

last_lsn = 1667534

compact = 0

你会发现日志序列号范围已经变成第二次增量备份后的日志序列号了,表示增量同步成功。

恢复完全备份,修改文件权限,启动服务。

  • innobackupex --copy-back /var/mybackup/2014-07-01_14-41-41/

  • chown -R mysql.mysql /mydata/

  • service mysqld start

登陆mysql交互界面查看数据。

wKioL1OyaF-BYzKKAAFxoWwAQTU441.jpg

但是我们在第二次增量备份之后创建test表和插入的数据都没有,没关系我们通过二进制日志来恢复。

查看第二次增量备份后xtrabackup帮我们创建的文件(xtrabackup_binlog_info ),里面记录了二进制日志备份后的状态和位置。

  • cat /var/mybackup/incrbak/2014-07-01_15-11-57/xtrabackup_binlog_info 

mysql-bin.000009941

分析二进制日志,导出sql脚本,并执行。

  • mysqlbinlog --start-position=941 /home/mybinlog/mysql-bin.000009 >/tmp/huifu-15-56.sql

  • source /tmp/huifu-15-56.sql

wKiom1Oya5_QcCIGAAEMoDcUHes997.jpg

mysql数据库的灾备及恢复完成。
小菜掌握的也不太好,有问题欢迎指出与我交流QQ1183710107



本文转自qw87112 51CTO博客,原文链接:http://blog.51cto.com/tchuairen/1432606

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL Kubernetes 关系型数据库
实时计算 Flink版产品使用合集之怎么解析 MySQL DDL 语句
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
20 2
|
6天前
|
负载均衡 关系型数据库 MySQL
MySQL读写分离技术深度解析
在高并发、大数据量的互联网应用环境中,数据库作为数据存储的核心组件,其性能直接影响着整个系统的运行效率。MySQL作为最常用的开源关系型数据库之一,虽然功能强大,但在处理大量并发读写请求时,单点服务器的性能瓶颈逐渐显现。为了解决这一问题,MySQL读写分离技术应运而生,成为提升数据库性能、实现负载均衡的有效手段。
|
6天前
|
关系型数据库 MySQL 数据库
【MySQL】:约束全解析
【MySQL】:约束全解析
26 0
|
6天前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之在DataWorks中,使用JSON解析函数将MySQL表中的字段解析成多个字段将这些字段写入到ODPS(MaxCompute)中如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
30 3
|
6天前
|
SQL 关系型数据库 MySQL
MySQL锁:解析隐式锁与显式锁
【4月更文挑战第20天】
42 0
|
6天前
|
SQL 关系型数据库 MySQL
[AIGC] MySQL连接查询全面解析
[AIGC] MySQL连接查询全面解析
|
6天前
|
存储 SQL 关系型数据库
MySQL数据库:深入解析与应用实例
MySQL数据库:深入解析与应用实例
41 0
|
4天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
79 0
|
6天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
60 0
|
6天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
44 0

推荐镜像

更多