MySQL/MariaDB数据库备份与恢复

前言

数据库一般存放着企业最为重要的数据,它关系到企业业务能否正常运转,数据库服务器总会遇到一 些不可抗拒因素,导致数据丢失或损坏,而数据库备份可以帮助我们避免由于各种原因造成的数据丢失或着数据库的其他问题。本文将讲解 MySQL/MariaDB数据库的几种备份方法。

基础知识

备份类型

完全备份:备份整个数据库

部分备份:仅备份其中的一张表或多张表

增量备份:仅备份从上次完全备份或增量备份之后变化的数据部分

差异备份:备份上次备份后变化的数据部分,和增量备份区别在于差异备份只可以相对完全备份做备份

热备份、温备份和冷备份:

热备份:在线备份,读写操作不受影响

温备份:在线备份,读操作可继续进行,但写操作不允许

冷备份:离线备份,数据库服务器离线,备份期间不能为业务提供读写服务

物理备份和逻辑备份:

物理备份:直接复制数据文件进行的备份

优点:无需额外工具,直接copy即可,恢复直接复制备份文件即可

缺点:与存储引擎有关,跨平台能力较弱

逻辑备份:从数据库中“导出”数据另存而进行的备份

优点: 能使用编辑器处理,恢复简单,能基于网络恢复,有助于避免数据损坏

缺点: 备份文件较大,备份较慢,无法保证浮点数的精度,使用逻辑备份数据恢复后,还需手动重建索引,十分消耗CPU资源

备份对象

数据文件

代码:存储过程,存储函数,触发器等

OS相关的配置文件,如crontab配置计划及相关脚本

跟复制相关的配置信息

二进制日志文件

备份工具

mysqldump: 逻辑备份工具,适用于所有存储引擎,温备、完全备份、部分备份;对InnoDB存储引擎支持热备

cp, tar等文件系统工具:物理备份工具,适用于所有存储引擎,冷备、完全备份、部分备份

lvm2的快照:几乎热备,借助于文件系统工具实现物理备份

mysqlhotcopy: 几乎冷备,仅适用于MyISAM存储引擎

数据库备份

备份方案

①mysqldump+binlog:

完全备份,通过备份二进制日志实现增量备份

②lvm2快照+binlog:

几乎热备,物理备份

③xtrabackup:

对InnoDB:热备,支持完全备份和增量备份

对MyISAM:温备,只支持完全备份

备份须知

备份某一个数据库和备份所有库是有区别的,要备份某一个库要确保所有的InnoDB存储引擎的表都是存放在单个表空间中,否则必须执行全库备份

MariaDB [none]> show global variables like 'innodb_file_p%'; #查看是否开启单独表空间MariaDB [none]> set global innodb_file_per_table=1; #开启单独表空间,也可在配置文件设置

mysqldump+binlog

命令的语法格式

mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库mysqldump [OPTIONS] --all-databases [OPTIONS]:备份所有库

其他选项

-x, --lock-all-tables:锁定所有表-l, --lock-tables:锁定备份的表--single-transaction:启动一个大的单一事务实现备份-C, --compress:压缩传输-E, --events:备份指定库的事件调度器-R, --routines:备份存储过程和存储函数--triggers:备份触发器--master-data={0|1|2} 0:不记录 1:记录CHANGE MASTER TO语句;此语句未被注释 2:记录为注释语句-F,--flush-logs:锁定表之后执行flush logs命令

注意:二进制日志文件与数据文件不应该放置于同一磁盘,这里是实验便不可以修改

备份过程

准备备份目录

准备备份数据库及表

进行完整备份

向表中插入数据

进行增量备份,备份二进制日志

继续插入数据,在没备份的情况下删除数据库,模拟误操作

数据恢复

#建议关闭二进制日志,关闭其它用户连接MariaDB [(none)]> set session sql_log_bin=0;

由于最后我们没有备份就删除了数据库,所以我们首先需要保护最后的二进制日志,查看删除操作之前的position值

[root@MariaDB ~]# mysqlbinlog /mydata/data/mysql-bin.000015

将最后操作的二进制日志备份

导入之前的所有备份

查看数据库及数据

OK,至此数据成功恢复

注意:此方法不适用于大型数据库,备份速度太慢

lvm2快照+binlog

备份过程

#请求锁定所有表MariaDB [test]> flush tables with read lock;#滚动日志MariaDB [test]> flush logs;#记录二进制日志位置MariaDB [test]> show master status;#创建快照卷[root@MariaDB ~]# lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r #释放全局锁MariaDB [test]> unlock tables;#创建快照挂载点[root@MariaDB ~]# mkdir /snap#挂载快照卷[root@MariaDB ~]# mount /dev/myvg/mydata-snap /snap#备份数据库[root@MariaDB ~]# cp -a /snap /backup/#增量备份,查看完整备份之前的二进制日志位置和最后出错操作前一位置[root@MariaDB ~]# mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mysql-bin.000016 > /backup/binlog/binlog-`date +%F_%T`.sql

数据恢复

[root@MariaDB ~]# service mysqld stop[root@MariaDB ~]# rm -rf /mydata/data/*[root@MariaDB ~]# cp -a /backup/snap/* /mydata/data[root@MariaDB ~]# service mysqld start[root@MariaDB ~]# mysql < /backup/binlog/binlog-2015-05-21_20\:23\:41.sql

基于物理备份,数据恢复完成

xtrabackup(推荐)

Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。

特点:

(1)备份过程快速、可靠

(2)备份过程不会打断正在执行的事务

(3)能够基于压缩等功能节约磁盘空间和流量

(4)自动实现备份检验

(5)还原速度快

安装

[root@MariaDB ~]# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm -y

创建最小权限备份用户

备份过程

完全备份

[root@MariaDB ~]# innobackupex --user=bakupuser --password=bakuppass /backup/innobackupex: Backup created in directory '/backup/2015-05-21_21-55-08'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 245150521 21:55:16 innobackupex: Connection to database server closed150521 21:55:16 innobackupex: completed OK!

如果出现如下错误,请在my.cnf文件[mysqld] 中添加innodb_log_file_size = 5M 并重启服务

InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytesInnoDB: than specified in the .cnf file 50331648 bytes!innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.

增量备份

每个InnoDB的页面都会包含一个LSN信息,每当相关的数据发生改变,相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础,即innobackupex通过备份上次完全备份之后发生改变的页面来实现

innobackupex命令会在备份目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外,在执行过增量备份之后再一次进行增量备份时,其–incremental-basedir应该指向上一次的增量备份所在的目录

注:增量备份仅能应用于InnoDB或XtraDB表,对于MyISAM表而言,执行增量备份时其实进行的是完全备份

添加数据

做增量备份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2015-05-21_21-55-08/innobackupex: Backup created in directory '/backup/2015-05-21_22-26-42'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 788150521 22:26:57 innobackupex: Connection to database server closed150521 22:26:57 innobackupex: completed OK!

再次添加数据

再次做增量备份

[root@MariaDB ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2015-05-21_22-26-42/ #在第一次增量备份的基础上做增量备份innobackupex: Backup created in directory '/backup/2015-05-21_22-32-01'innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 1056150521 22:32:10 innobackupex: Connection to database server closed150521 22:32:10 innobackupex: completed OK!

数据恢复

准备阶段

一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的 事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至 数据文件也使得数据文件处于一致性状态

“准备”(prepare)增量备份与整理完全备份有着一些不同,尤其要注意的是:

(1)需要在每个备份(包括完全和各个增量备份)上,将已经提交的事务进行“重放”。“重放”之后,所有的备份数据将合并到完全备份上。

(2)基于所有的备份将未提交的事务进行“回滚”。

于是,操作就变成了:

# innobackupex --apply-log --redo-only BASE-DIR

接着执行:

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量:

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

其中BASE-DIR指的是完全备份所在的目录,而INCREMENTAL-DIR-1指的是第一次增量备份的目录,INCREMENTAL-DIR-2指的是第二次增量备份的目录,其它依次类推,即如果有多次增量备份,每一次都要执行如上操作

完整备份准备

[root@MariaDB ~]# innobackupex --apply-log /backup/2015-05-21_21-55-08/InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 2766618150521 23:02:43 innobackupex: completed OK!

增量备份准备

[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ --incremental-dir=/backup/2015-05-21_22-26-42/[root@MariaDB ~]# innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ --incremental-dir=/backup/2015-05-21_22-32-01/

恢复阶段

还原备份,即完全备份

[root@MariaDB ~]# innobackupex --copy-back /backup/2015-05-21_21-55-08/[root@MariaDB ~]# chown -R mysql.mysql /mydata/data/[root@MariaDB ~]# service mysqld start

测试数据是否恢复

OK,数据恢复成功

The end

好了,MySQL/MariaDB数据库备份与恢复就总结到这里了,以上总结的三种方法各有各的特色,读者可根据实际需求进行选择,再啰嗦一句,实际生产环境中数据和二进制日志文件一定要分开存放。以上仅为个人学习整理,如有错漏,大神勿喷~~~

原文发布于微信公众号 - 马哥Linux运维(magedu-Linux)

原文发表时间:2015-05-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

使用innobackup 2.4遇到的问题

一 前言 Percona公司发布 innobackup 2.4 版本已经很久了,增加了新的特性比如支持非Innodb表备份,指定 --safe-slave-...

1112
来自专栏IT笔记

Lepus搭建企业级数据库慢查询分析平台

Lepus的慢查询分析平台是独立于监控系统的模块,该功能需要使用percona-toolkit工具来采集和记录慢查询日志,并且需要部署一个我们提供的shell脚...

1212
来自专栏PHP实战技术

高可用架构-- MySQL主从复制的配置

环境 操作系统:CentOS-6.6-x86_64-bin-DVD1.iso MySQL版本:mysql-5.6.26.tar.gz 主节点IP:192.168...

3367
来自专栏pangguoming

Centos7 Mysql 双机热备实现数据库高可用

mysql双主热备,也称主主互备,目的是mysql数据库高可用,只支持双机,原因是mysql的复制是一主多从,但一个从服务器只能有一个主服务器。

3822
来自专栏L宝宝聊IT

Mysql备份与还原——mysqldump结合binlog

MySQL 备份一般采取全库备份加日志备份的方式,例如每天执行一次全备份,每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份...

1142
来自专栏杨建荣的学习笔记

半自动化运维之快速连接到指定环境(一) (r5笔记第61天)

在大量的分布式环境中,可能存在着大量的主机配置,ip配置,数据库实例配置,甚至操作系统用户,数据库用户密码也不同,这个时候如果记录在10条左右还能应付,但是如果...

3689
来自专栏北京马哥教育

利用硬链接和truncate降低drop table对线上环境的影响

众所周知drop table会严重的消耗服务器IO性能,如果被drop的table容量较大,甚至会影响到线上的正常。 首先,我们看一下为什么drop容量大的ta...

2818
来自专栏自由而无用的灵魂的碎碎念

将MySQL数据迁移到Oracle

因为项目的原因,今晚将mysql数据库的内容尝试迁移到oracle,虽然结果失败,不过学到了不少,下次就不一定了,哈哈

1801
来自专栏pangguoming

mysql主从配置(清晰的思路)

例如:主数据库里的a的数据库里有b,c,d表,那从数据库里的就应该有一个模子刻出来的a的数据库和b,c,d表

1602
来自专栏云计算教程系列

Debian 8如何使用Postgresql和Django应用程序

Django是一个用于快速创建Python应用程序的灵活框架。默认情况下,Django应用程序配置为将数据存储到轻量级SQLite数据库文件中。虽然这在某些负载...

1213

扫码关注云+社区

领取腾讯云代金券