mariadb常用备份与还原工具介绍

一、备份的意义

从数据安全的角度来说，数据库服务器磁盘都会做RAID，Mariadb本身也有主从等容灾机制，但它们都无法完全取代备份。容灾和高可用能帮我们有效的应对物理的、硬件的、机械的故障，但是由我们手工操作导致的逻辑错误、系统本身内生性BUG、恶意攻击等方式破坏了你的数据却无能为力。每一种逻辑错误发生的概率都极低，但是当多种可能性叠加的时候，小概率事件就放大成很大的安全隐患，这时候备份的必要性就凸显了。

二、备份类型

1、根据备份时，数据库服务器是否在线：

冷备：cold backup

温备：warm backup

热备：hot backup

2、根据备份的数据集：

完全备份：full backup

部分备份: partial backup

3、根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）：

物理备份：直接复制(归档)数据文件的备份方式；physical backup

逻辑备份：把数据从库中提出出来保存为文本文件；logical backup

4、根据备份时是备份整个数据还是仅备份变化的数据：

增量备份：incremental backup

差异备份：differential backup

5、备份策略：

选择备份方式

选择备份时间

考虑到恢复成本

恢复时长

备份成本

锁时间

备份时长

备份负载

6、备份对象

数据

配置文件

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

OS相关的配置文件，如crontab配置计划及相关的脚本，跟复制相关的配,二进制日志文件

三、常见的备份工具

mariadb本身为我们提供了mysqldump、mysqlbinlog备份工具，percona也为我们提供了强大的Xtrabackup，加上开源的mydumper，还有基于主从同步的延迟备份、从库冷备等方式，以及基于文件系统快照的备份，其实将这些方式合理搭配已经能够满足我们的需要了。而备份本身是为了恢复，所以能够让我们在出现故障后迅速、准确恢复的备份方式，就是最适合我们的，当然，同时能够省钱、省事，那就非常完美。下面就几种备份工具进行一些比较，探讨下它们各自的适用场景，及简单的使用做一下说明

1、mysqldump

⑴、mysqldump优缺点

mysqldump是最简单的逻辑备份方式（工作方式单线程）。在备份myisam表的时候，如果要得到一致的数据，就需要锁表，简单而粗暴。而在备份innodb表的时候，加上–master-data=2 –single-transaction 选项，在事务开始时刻，记录下binlog-pos点，然后利用mvcc（多版本并发控制）来获取一致的数据，由于是一个大事务，在写入和更新量很大的数据库上，将产生非常多的undo，显著影响性能，所以要慎用。

优点：简单，可针对单表备份，在完全导出表结构的时候尤其有用。可以做到对不同的存储引擎进行备份（InnoDB热备、MyISAM温备、Aria温备）

缺点：简单粗暴，单线程，备份慢而且恢复，不支持差异或增量备份，如果要进行差异或增量备份要结合binlog日志文件

mydumper是mysqldump的加强版。相比mysqldump：

内置支持压缩，可以节省2-4倍的存储空间。

支持并行备份和恢复，因此速度比mysqldump快很多，但是由于是逻辑备份，仍不是很快，如果要进行差异或增量备份要结合binlog日志文件

部分备份工具

SELECT clause INTO OUTFILE '/path/to/somefile'

LOAD DATA INFILE '/path/from/somefile'

不会备份关系定义，仅备份表中的数据；

逻辑备份工具，快于mysqldump。

⑵、mysqldump命令介绍及简单使用

①命令介绍

mysqldump [options] [db_name [tbl_name ...]]

备份单个库：

mysqldump [options] db_name

恢复时：如果目标库不存在，需要事先手动创建

options说明

--all-databases:　备份所有库

--databases db1 db2 ...: 备份指定的多个库

注意：备份前要加锁

--lock-all-tables：请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备

--single-transaction: 能够对InnoDB存储引擎实现热备；

备份代码：

--events: 备份事件调度器代码

--routines: 备份存储过程和存储函数

--triggers：备份触发器

备份时滚动日志：

--flush-logs: 备份前、请求到锁之后滚动日志；

复制时的同步位置标记：

--master-data=[0|1|2]

0: 不记录

1：记录为CHANGE MASTER语句

2：记录为注释的CHANGE MASTER语句

注意

使用mysqldump备份

请求锁：--lock-all-tables或使用--singe-transaction进行innodb热备；

滚动日志：--flush-logs

选定要备份的库：--databases

记录二进制日志文件及位置：--master-data=

恢复

建议：关闭二进制日志，关闭其它用户连接；

建议备份策略：基于mysqldump

备份部分

mysqldump+二进制日志文件；

完全备份+各二进制日志文件中至此刻的事件，对MySQL配置文件，以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份；

②、实例

要求如下：

备份所有数据库，要每周日凌晨自动执行；

③解决方案（此方法不唯一）

备份阶段

第一步，先远程登录到数据库上，事先看一下现有的数据库。

由上图可见，除了hellodb数据库，其它的数据库都是系统自带的，看一下hellodb中有那些表，及当前binlog日志的记录的位置。

由于当前数据库只有一个用户数据库，所以我们在乎其表的存储引擎来判断使用什么方式来备份。（是申请锁或是单事务）

查看表状态发现hellodb中的表全部都是MyISAM的存储引擎，那么就可以使用申请全局锁来备份了。

<code># mysqldump -uroot -p </code><code>--all-databases --lock-all-tables --flush-logs --master-data=2 >/tmp/all.sql</code>

这时数据库全部备份完毕（此方法只适用于数据量不是很大，挑选一个相对并发的写请求不是特别多的时间或午夜备份。如果数据量特别大，此方法将不做参考范围）。

假如在这之后我们又在原来的库中建立新的表或插入数据，而在某一次我误操作删除了此数据库。将如何恢复？

由上图可见，原来的hellodb数据库中多了一张表，为不演示恢复效果我将hellodb删除，看怎么样恢复

现在hellodb己经删除，看如何恢复。

恢复部分

第一步，将服务器离线，导出现在正在使用的binlog日志，还原之前的完全备份

查看完全备份中binlog日志的起始位置。

<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysql-bin.000002'</code><code>, MASTER_LOG_POS=365;</code>

将此日志中到删除数据库drop database hellodb之前的数据导出到存放起来，以供时间点还原之用

<code># mysqlbinlog --start-position=365 --stop-position=863 mysql-bin.000002 >/tmp/binlogbakup.sql</code>

登录数据库

<code>[root@localhost bin]# mysql</code>

<code>Welcome </code><code>to</code> <code>the MariaDB monitor. Commands </code><code>end</code> <code>with</code> <code>; </code><code>or</code> <code>\g.</code>

<code>Your MariaDB </code><code>connection</code> <code>id </code><code>is</code> <code>4</code>

<code>Server version: 10.0.10-MariaDB-log Source distribution</code>

<code>Type </code><code>'help;'</code> <code>or</code> <code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to</code> <code>clear the </code><code>current</code> <code>input statement.</code>

<code>MariaDB [(none)]></code>

注：此时要临时关闭二进制日志，关闭其它用户连接；

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

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec)</code>

导入之前的备份　

<code>MariaDB [(none)]> source /tmp/</code><code>all</code><code>.sql</code>

查看数据库中的表，里面没有我们备份之后建立的表，这时就用到了之前备份的binlog日志了

<code>MariaDB [test]> show databases;</code>

<code>| </code><code>Database</code> <code>|</code>

<code>| hellodb |</code>

<code>| information_schema |</code>

<code>| mysql |</code>

<code>| performance_schema |</code>

<code>MariaDB [test]> use hellodb</code>

<code>Database</code> <code>changed</code>

<code>MariaDB [hellodb]> show tables;</code>

<code>| Tables_in_hellodb |</code>

<code>| classes |</code>

<code>| courses |</code>

<code>| scores |</code>

<code>| students |</code>

<code>| teachers |</code>

导入binlog日志，完全恢复数据。

<code>MariaDB [hellodb]> source /tmp/binlogbakup.sql</code>

<code>| addressbook |</code>

<code>MariaDB [hellodb]> </code><code>select</code> <code>* </code><code>from</code> <code>addressbook;</code>

这回数据就完整了。

附上一个基于mysqldump简单复制单库的myisam的脚本

<code>#此脚本用来单库完全份</code>

<code>#每个星期日做一次完全备份</code>

<code>#设置用户名和密码</code>

<code>v_password=</code><code>"mysql"</code>

<code>#mysql安装可执行程序所在位置</code>

<code>MysqlDir=</code><code>/usr/local/mari/bin</code>

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

<code>database=</code><code>"hellodb"</code>

<code>#设置备份路径，创建备份文件夹</code>

<code>BackupDir=</code><code>/maria_bak</code>

<code>Full_Backup=$BackupDir</code><code>/Full_backup</code>

<code>mkdir</code> <code>-p $Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code>

<code>#开始备份,记录备份开始时间</code>

<code>echo</code> <code>'###'</code><code>$(</code><code>date</code> <code>+</code><code>"%Y-%m-%d %H:%M:%S"</code><code>)</code><code>'###'</code><code>"备份开始"</code><code>>>$Full_Backup</code><code>/full_buckup</code><code>.log</code>

<code>$MysqlDir</code><code>/mysqldump</code> <code>-u$v_user -p$v_password --lock-all-tables --flush-logs --master-data=2 --databases $database>$Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code><code>/full_backup</code><code>.sql</code>

<code>#压缩备份文件</code>

<code>gzip</code> <code>$Full_Backup/$(</code><code>date</code> <code>+%Y%m%d)</code><code>/full_backup</code><code>.sql</code>

<code>echo</code> <code>'###'</code><code>$(</code><code>date</code> <code>+</code><code>"%Y-%m-%d %H:%M:%S"</code><code>)</code><code>'###'</code><code>"备份完成"</code><code>>>$Full_Backup</code><code>/full_buckup</code><code>.log</code>

2. 基于文件系统的快照

基于文件系统的快照，是物理备份的一种。在备份前需要进行一些复杂的设置，在备份开始时刻获得快照并记录下binlog pos点，然后采用类似copy-on-write的方式，把快照进行转储。转储快照本身会消耗一定的IO资源，而且在写入压力较大的实例上，保存被更改数据块的前印象也会消耗IO，最终表现为整体性能的下降。而且服务器还要为copy-on-write快照预留较多的磁盘空间，这本身对资源也是一种浪费。因此这种备份方式在生产环境使用的不多。

注：

当刚为数数据文件所在的卷（源卷）创建快照时，此时的快照卷是空的。

当源卷中的数据发生变化后，则将发生变化的那部分数据复制到快照卷上，此时通过快照访问则是未发生变化的数据来自于源卷，变化后的数据来自于快照卷。

快照不是备份，它是仅是提供访问文件的一条通路，当源卷变化的量超出快照卷的大小时，快照卷将崩溃失效。

实例

前提：

首先，mariadb的数据文件在逻辑卷上，如果使用的是支持事务的存储引擎的话，那么要将事务日志与数据文件放在同一个卷上，这样才能保证数据的完整性。

看一下数据文件的属性

步骤：

第一步，如果对源卷创建快照，那么首先要对数据库申请全局锁，滚动二进制日志，记录二进制日志位置标记

⑴、申请全局锁

<code>MariaDB [(none)]> flush tables </code><code>with</code> <code>read</code> <code>lock;</code>

<code>MariaDB [(none)]> flush logs;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.02 sec)</code>

<code>MariaDB [(none)]> show master status;</code>

将此二进制日志信息记录下来

<code># mysql -e 'show master status;'>/maribak/binlog_pos/binlog_$(date +%F_%T).txt</code>

<code># cat /maribak/binlog_pos/binlog_2014-04-14_04\:17\:13.txt</code>

<code>File Position Binlog_Do_DB Binlog_Ignore_DB</code>

<code>mysql-bin.000012 365</code>

此时可以创建快照了

<code># lvcreate -L 200M -s -n databak_snap -p r /dev/mydata/lv1</code>

<code> </code><code>Logical volume </code><code>"databak_snap"</code> <code>created</code>

第二步，创建完快照后解锁

当快照卷创建完成后，可以对数据库解锁（创建锁与解锁必须是同一会话，也就是说，当创建完锁之后，这个会话不可退出，之后还要在这个会话中对数据库解锁，一但退出，此会话的全局锁将失效）

<code>MariaDB [(none)]> unlock tables;</code>

<code>Query OK, 0 rowsaffected (0.00 sec)</code>

这时数据库又可以正常进行写入操作了

第三步，备份快照

挂载备份之后的快照

<code>[root@bogon mnt]</code><code># mount /dev/mydata/databak_snap -o ro /mnt</code>

<code>[root@bogon mnt]</code><code># cd /mnt</code>

<code>[root@bogon mnt]</code><code># ll</code>

<code>total 176172</code>

<code>-rw-rw---- 1 mysql mysql 16384 Apr 14 04:11 aria_log.00000001</code>

<code>-rw-rw---- 1 mysql mysql 52 Apr 14 04:11 aria_log_control</code>

<code>-rw-r----- 1 mysql root 1099 Apr 14 04:11 bogon.err</code>

<code>-rw-rw---- 1 mysql mysql 5 Apr 14 04:11 bogon.pid</code>

<code>drwx------ 2 mysql mysql 4096 Apr 14 04:12 employees</code>

<code>-rw-rw---- 1 mysql mysql 79691776 Apr 14 04:14 ibdata1</code>

<code>-rw-rw---- 1 mysql mysql 50331648 Apr 14 04:14 ib_logfile0</code>

<code>-rw-rw---- 1 mysql mysql 50331648 Apr 14 04:14 ib_logfile1</code>

<code>-rw-rw---- 1 mysql mysql 0 Apr 14 04:11 multi-master.info</code>

<code>drwx------ 2 mysql root 4096 Apr 14 04:11 mysql</code>

<code>drwx------ 2 mysql mysql 4096 Apr 14 04:11 performance_schema</code>

<code>drwx------ 2 mysql root 4096 Apr 14 04:11 </code><code>test</code>

复制快照中的内容

<code>[root@bogon mnt]</code><code># cp -a * /maribak/databak/</code>

<code>[root@bogon mnt]</code><code># cd /maribak/databak/</code>

<code>[root@bogon databak]</code><code># ll</code>

删除快照卷

<code>#lvremove /dev/mydata/databak_snap</code>

里源卷的数据库employees中添入一些数据。

<code>MariaDB [employees]> </code><code>insert</code> <code>employees </code><code>values</code><code>(99,</code><code>'1998-09-21'</code><code>,</code><code>'JEYY'</code><code>,</code><code>'jose'</code><code>,</code><code>'M'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

<code>MariaDB [employees]> </code><code>insert</code> <code>employees </code><code>values</code><code>(999999,</code><code>'1995-09-21'</code><code>,</code><code>'king'</code><code>,</code><code>'tom'</code><code>,</code><code>'M'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

<code>MariaDB [employees]></code><code>insert</code> <code>employees </code><code>values</code><code>(5211345,</code><code>'1995-09-21'</code><code>,</code><code>'king'</code><code>,</code><code>'tom'</code><code>,</code><code>'F'</code><code>,</code><code>'2012-12-24'</code><code>);</code>

当备份过完成后，过了几个小时，数据库误删除了一些数据，这时就可用到此次完全备份与即时点还原日志。

<code>MariaDB [employees]> </code><code>delete</code> <code>from</code> <code>employees </code><code>where</code> <code>gender=</code><code>'M'</code>

<code>Query OK, 179975 </code><code>rows</code> <code>affected (3 </code><code>min</code> <code>52.82 sec)</code>

还原部分

第一步，停止数据库服务，将备份后的数据中的某一张表或整个库替换此数据库中误删除的表或整个库。

<code>[root@bogon employees]</code><code># service mari stop</code>

<code>Shutting down MySQL............... [ OK ]</code>

替换　

<code>[root@bogon employees]</code><code># cp -a /maribak/databak/employees/employees.* .</code>

<code>cp</code><code>: overwrite `.</code><code>/employees</code><code>.frm'? y</code>

<code>cp</code><code>: overwrite `.</code><code>/employees</code><code>.ibd'? y</code>

<code>[root@bogon employees]</code><code># ll</code>

<code>total 237840</code>

<code>-rw-rw---- 1 mysql mysql 61 Apr 14 04:12 db.opt</code>

<code>-rw-rw---- 1 mysql mysql 1571 Apr 14 04:12 departments.frm</code>

<code>-rw-rw---- 1 mysql mysql 114688 Apr 14 04:12 departments.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1999 Apr 14 04:12 dept_emp.frm</code>

<code>-rw-rw---- 1 mysql mysql 31457280 Apr 14 05:03 dept_emp.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1999 Apr 14 04:12 dept_manager.frm</code>

<code>-rw-rw---- 1 mysql mysql 131072 Apr 14 04:55 dept_manager.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1164 Apr 14 04:12 employees.frm</code>

<code>-rw-rw---- 1 mysql mysql 23068672 Apr 14 04:12 employees.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1501 Apr 14 04:12 salaries.frm</code>

<code>-rw-rw---- 1 mysql mysql 146800640 Apr 14 05:03 salaries.ibd</code>

<code>-rw-rw---- 1 mysql mysql 1647 Apr 14 04:12 titles.frm</code>

<code>-rw-rw---- 1 mysql mysql 41943040 Apr 14 05:03 titles.ibd</code>

注：此时文件的属性要与原文件的一致

第二步，启动服务

<code>[root@bogon employees]</code><code># service mari start</code>

<code>Starting MySQL.. [ OK ]</code>

查看完全备份信息

<code>MariaDB [employees]> </code><code>select</code> <code>* </code><code>from</code> <code>employees </code><code>where</code> <code>hire_date ></code><code>'2000-01-01'</code><code>;</code>

<code>| 47291 | 1960-09-09 | Ulf | Flexer | M | 2000-01-12 |</code>

<code>| 60134 | 1964-04-21 | Seshu | Rathonyi | F | 2000-01-02 |</code>

<code>| 72329 | 1953-02-09 | Randi | Luit | F | 2000-01-02 |</code>

<code>| 205048 | 1960-09-12 | Ennio | Alblas | F | 2000-01-06 |</code>

<code>| 222965 | 1959-08-07 | Volkmar | Perko | F | 2000-01-13 |</code>

<code>| 226633 | 1958-06-10 | Xuejun | Benzmuller | F | 2000-01-04 |</code>

<code>| 227544 | 1954-11-17 | Shahab | Demeyer | M | 2000-01-08 |</code>

<code>| 422990 | 1953-04-09 | Jaana | Verspoor | F | 2000-01-11 |</code>

<code>| 424445 | 1953-04-27 | Jeong | Boreale | M | 2000-01-03 |</code>

<code>| 428377 | 1957-05-09 | Yucai | Gerlach | M | 2000-01-23 |</code>

<code>| 463807 | 1964-06-12 | Bikash | Covnot | M | 2000-01-28 |</code>

<code>| 499553 | 1954-05-06 | Hideyuki | Delgrande | F | 2000-01-22 |</code>

并没有备份后插入的数据，所以在用到binlog日志了,查看当前的binlog的位置

<code>MariaDB [employees]> show master status;</code>

跨文件了~~~，没关系统，先还原同一个日志文件中的数据，再恢复这个日志中的数据，在mysql-bin.000012中找到delect之前的位置，并使用mysqlbinlog将其导出，临时性关闭binlog日志功能，恢复此间数据。

<code># mysqlbinlog /mysql/binlog/mysql-bin.000012</code>

<code>#140414 4:53:25 server id 1 end_log_pos 1146 GTID 0-1-7138</code>

<code>/*!100001 SET @@session.gtid_seq_no=7138*</code><code>//</code><code>*!*/;</code>

<code>BEGIN</code>

<code>#140414 4:53:25 server id 1 end_log_pos 1254 Query thread_id=6 exec_time=233 error_code=0</code>

<code>SET TIMESTAMP=1397422405/*!*/;</code>

<code>delete from employees where gender=</code><code>'M'</code>

从日志中看标识到1108即可恢复到删除之前，将此区间导出

<code>[root@bogon binlog_pos]</code><code># mysqlbinlog --start-position=365 --stop-position=1108 /mysql/binlog/mysql-bin.000012 >zengliang.sql</code>

<code>[root@bogon binlog_pos]</code><code># ls</code>

<code>binlog_2014-04-14_04:17:13.txt zengliang.sql</code>

在数据中临时关闭binlog日志功能。

<code>MariaDB [employees]> </code><code>set</code> <code>session sql_log_bin=0;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>MariaDB [employees]> source /maribak/binlog_pos/zengliang.sql;</code>

查看一下原来的数据