MySQL/MariaDB数据库备份与恢复

前言

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

基础知识

备份类型

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

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

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

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

热备份、温备份和冷备份：

热备份：在线备份，读写操作不受影响

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

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

物理备份和逻辑备份：

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

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

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

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

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

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

备份对象

数据文件

代码：存储过程，存储函数，触发器等

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

跟复制相关的配置信息

二进制日志文件

备份工具

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

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

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

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

数据库备份

备份方案

①mysqldump+binlog: 

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

②lvm2快照+binlog：

几乎热备，物理备份

③xtrabackup: 

对InnoDB：热备，支持完全备份和增量备份

对MyISAM：温备，只支持完全备份

备份须知

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

1

2

<code>MariaDB [none]&gt; show global variables like </code><code>'innodb_file_p%'</code><code>; </code><code>#查看是否开启单独表空间</code>

<code>MariaDB [none]&gt; </code><code>set</code> <code>global innodb_file_per_table=1; </code><code>#开启单独表空间，也可在配置文件设置</code>

mysqldump+binlog

命令的语法格式

3

<code>mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表</code>

<code>mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库</code>

<code>mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库</code>

其他选项

4

5

6

7

8

9

10

11

12

<code>-x, --lock-all-tables：锁定所有表</code>

<code>-l, --lock-tables：锁定备份的表</code>

<code>--single-transaction：启动一个大的单一事务实现备份</code>

<code>-C, --compress：压缩传输</code>

<code>-E, --events：备份指定库的事件调度器</code>

<code>-R, --routines：备份存储过程和存储函数</code>

<code>--triggers：备份触发器</code>

<code>--master-data={0|1|2}</code>

<code>    </code><code>0：不记录</code>

<code>    </code><code>1：记录CHANGE MASTER TO语句；此语句未被注释</code>

<code>    </code><code>2：记录为注释语句</code>

<code>-F，--flush-logs：锁定表之后执行flush logs命令</code>

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

备份过程

准备备份目录

<a href="http://s3.51cto.com/wyfs02/M01/6D/2A/wKiom1Vdju7Ss1idAAB_kLgyrAo223.jpg" target="_blank"></a>

准备备份数据库及表

进行完整备份

<a href="http://s3.51cto.com/wyfs02/M01/6D/2A/wKiom1VdkXWgGX_WAACTf9D9SIs505.jpg" target="_blank"></a>

向表中插入数据

<a href="http://s3.51cto.com/wyfs02/M02/6D/2A/wKiom1VdkqvSMntSAAGEtI8YJrY763.jpg" target="_blank"></a>

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

<a href="http://s3.51cto.com/wyfs02/M02/6D/2A/wKiom1VdlSni34YJAACoVEP8f7Y819.jpg" target="_blank"></a>

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

<a href="http://s3.51cto.com/wyfs02/M01/6D/26/wKioL1Vdl5Oyu7KyAAFbiUpxwuA845.jpg" target="_blank"></a>

数据恢复

<code>#建议关闭二进制日志，关闭其它用户连接</code>

<code>MariaDB [(none)]&gt; </code><code>set</code> <code>session sql_log_bin=0;</code>

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

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

<a href="http://s3.51cto.com/wyfs02/M00/6D/2B/wKiom1VdmJiCjTSRAAE-IM_0uts532.jpg" target="_blank"></a>

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

导入之前的所有备份

<a href="http://s3.51cto.com/wyfs02/M01/6D/26/wKioL1VdnMnjlyQ7AACpzl8js74689.jpg" target="_blank"></a>

查看数据库及数据

<a href="http://s3.51cto.com/wyfs02/M02/6D/26/wKioL1VdnROgS2hJAACLbJn68Fs831.jpg" target="_blank"></a>

OK，至此数据成功恢复

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

lvm2快照+binlog

13

14

15

16

17

18

19

<code>#请求锁定所有表</code>

<code>MariaDB [</code><code>test</code><code>]&gt; flush tables with </code><code>read</code> <code>lock;</code>

<code>#滚动日志</code>

<code>MariaDB [</code><code>test</code><code>]&gt; flush logs;</code>

<code>#记录二进制日志位置</code>

<code>MariaDB [</code><code>test</code><code>]&gt; show master status;</code>

<code>#创建快照卷</code>

<code>[root@MariaDB ~]</code><code># lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r </code>

<code>#释放全局锁</code>

<code>MariaDB [</code><code>test</code><code>]&gt; unlock tables;</code>

<code>#创建快照挂载点</code>

<code>[root@MariaDB ~]</code><code># mkdir /snap</code>

<code>#挂载快照卷</code>

<code>[root@MariaDB ~]</code><code># mount /dev/myvg/mydata-snap /snap</code>

<code>#备份数据库</code>

<code>[root@MariaDB ~]</code><code># cp -a /snap /backup/</code>

<code>#增量备份，查看完整备份之前的二进制日志位置和最后出错操作前一位置</code>

<code>[root@MariaDB ~]</code><code># mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mys</code>

<code>ql-bin.000016 &gt; </code><code>/backup/binlog/binlog-</code><code>`</code><code>date</code> <code>+%F_%T`.sql</code>

<code>[root@MariaDB ~]</code><code># service mysqld stop</code>

<code>[root@MariaDB ~]</code><code># rm -rf /mydata/data/*</code>

<code>[root@MariaDB ~]</code><code># cp -a /backup/snap/* /mydata/data</code>

<code>[root@MariaDB ~]</code><code># service mysqld start</code>

<code>[root@MariaDB ~]</code><code># mysql &lt; /backup/binlog/binlog-2015-05-21_20\:23\:41.sql</code>

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

xtrabackup（推荐）

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

特点：

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

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

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

(4)自动实现备份检验

(5)还原速度快

安装

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

创建最小权限备份用户

完全备份

<code>[root@MariaDB ~]</code><code># innobackupex --user=bakupuser --password=bakuppass /backup/</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_21-55-08'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 245</code>

<code>150521 21:55:16  innobackupex: Connection to database server closed</code>

<code>150521 21:55:16  innobackupex: completed OK!</code>

<a href="http://s3.51cto.com/wyfs02/M00/6D/34/wKiom1Vd5XHTrk7uAACqhpePaRQ631.jpg" target="_blank"></a>

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

<code>InnoDB: Error: log </code><code>file</code> <code>.</code><code>/ib_logfile0</code> <code>is of different size 5242880 bytes</code>

<code>InnoDB: than specified </code><code>in</code> <code>the .cnf </code><code>file</code> <code>50331648 bytes!</code>

<code>innobackupex: Error: The xtrabackup child process has died at </code><code>/usr/bin/innobackupex</code> <code>line 2672.</code>

增量备份

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

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

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

添加数据

<a href="http://s3.51cto.com/wyfs02/M02/6D/34/wKiom1Vd6siC_h2JAACh2TBsIaI746.jpg" target="_blank"></a>

做增量备份

<code>[root@MariaDB ~]</code><code># innobackupex --incremental /backup/ --incremental-basedir=/backup/201</code>

<code>5-05-21_21-55-08/</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_22-26-42'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 788</code>

<code>150521 22:26:57  innobackupex: Connection to database server closed</code>

<code>150521 22:26:57  innobackupex: completed OK!</code>

再次添加数据

<a href="http://s3.51cto.com/wyfs02/M01/6D/34/wKiom1Vd7BbzKaWcAAC9iyCq3Qw920.jpg" target="_blank"></a>

再次做增量备份

<code>5-05-21_22-26-42/ </code><code>#在第一次增量备份的基础上做增量备份</code>

<code>innobackupex: Backup created </code><code>in</code> <code>directory </code><code>'/backup/2015-05-21_22-32-01'</code>

<code>innobackupex: MySQL binlog position: filename </code><code>'mysql-bin.000017'</code><code>, position 1056</code>

<code>150521 22:32:10  innobackupex: Connection to database server closed</code>

<code>150521 22:32:10  innobackupex: completed OK!</code>

准备阶段

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

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

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

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

于是，操作就变成了：

<code># innobackupex --apply-log --redo-only BASE-DIR</code>

接着执行：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1</code>

而后是第二个增量：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2</code>

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

<a href="http://s3.51cto.com/wyfs02/M01/6D/35/wKiom1Vd81nAIeoUAABVi6V8loE804.jpg" target="_blank"></a>

完整备份准备

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log /backup/2015-05-21_21-55-08/</code>

<code>InnoDB: FTS optimize thread exiting.</code>

<code>InnoDB: Starting </code><code>shutdown</code><code>...</code>

<code>InnoDB: Shutdown completed; log sequence number 2766618</code>

<code>150521 23:02:43  innobackupex: completed OK!</code>

增量备份准备

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/</code>

<code>[root@MariaDB ~]</code><code># innobackupex --apply-log --redo-only /backup/2015-05-21_21-55-08/ </code>

<code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2015-05-21_22-26-42/</code>

<code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2015-05-21_22-32-01/</code>

恢复阶段

还原备份，即完全备份

<code>[root@MariaDB ~]</code><code># innobackupex --copy-back /backup/2015-05-21_21-55-08/</code>

<code>[root@MariaDB ~]</code><code># chown -R mysql.mysql /mydata/data/</code>

测试数据是否恢复

<a href="http://s3.51cto.com/wyfs02/M00/6D/35/wKiom1Vd-BiCuz8JAACAz3UA59k539.jpg" target="_blank"></a>

OK，数据恢复成功

The end

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

本文转自 北城书生  51CTO博客，原文链接:http://blog.51cto.com/scholar/1653792