专栏首页北京马哥教育MySQL/MariaDB数据库备份与恢复

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),作者:马帮学员-书生

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 学会用各种姿势备份MySQL数据库

    前言 我们试着想一想, 在生产环境中什么最重要?如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢?这可能是最恐怖的事...

    小小科
  • linux下用tar进行数据备份

    豌豆贴心提醒,本文阅读时间7分钟 本机上数据的手工备份 Linux系统上配有功能强大的tar命令,可以灵活地备份数据。 tar最初是为了制作磁带备份而设计的把...

    小小科
  • 只需五步,完美利用命令行工具创建LinuxMac系统下的网站备份

    创建网站备份应该是一个网站管理员最为重要的日常工作之一。但现实情况是,备份这一步往往被很多人忽略,也就是说仍然有很多网管的网站安全意识较低。 所有的Linux/...

    小小科
  • amazing 真的不错,备份苹果手机系统简直不能再厉害!

    前端正义联盟
  • 学会用各种姿势备份MySQL数据库

    前言 我们试着想一想, 在生产环境中什么最重要?如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢?这可能是最恐怖的事...

    小小科
  • 多云虚拟化环境的备份遵从性

    企业IT部门是否可以保护它存储在云中的数据?在一定程度上是这样。云计算提供商在为活动数据构建冗余和可用性方面通常是值得信赖的,但是真正的合规性需要可靠的备份。

    静一
  • mysql备份与恢复

    对于DBA来说,备份和恢复是一项最基本的操作,在服务器宕机、磁盘损坏、RAID卡损坏等意外情况下,要保证数据不丢失或者丢失量在可接受范围内,每个DBA应该时刻...

    AsiaYe
  • Mysql备份与恢复(1)---物理备份

    数据库对企业来说最重要的莫过于其中的数据,所以做好数据库的备份是一个不可或缺的工作。数据库及时备份可以帮助我们在数据库出现异常宕机时及时的使用备份数据进行恢复工...

    逆月翎
  • 图文简述三种备份方式

    我们经常提到备份时,会想到全量备份、增量备份,但实际还有一个差异备份,我们就开始迷糊了。原来我们曾经以为理解的备份也不这么简单,我们来看一下。

    希望的田野
  • 混合备份可以从云备份和本地备份中获益

    组织应该有一个本地备份服务来加速恢复,即使最终的备份位置在云中。这种混合备份方法对于勒索软件的安全防护很有用。

    CloudBest

扫码关注云+社区

领取腾讯云代金券