MySQL/MariaDB数据备份与数据恢复完整

数据对我们来说再重要不过了，那我们如何做到对数据尽可能的安全呢，当我们的数据丢失了那又该怎么做呢，所以说数据备份对我们的数据安全性来说太重要了。

数据对我们来说再熟悉不过了，也最平常不过了，我们每天都在接触各色各样的数据，数据记录了我们平常相关的业务信息，所以数据对于我们来说是很重要的， 这么重要的数据如果我们的数据丢失了那我们是不是相关的业务都没法进行了呢，这应该是个很麻烦的问题，那我们怎么保护我们的数据的安全呢，这就要用到我们 的数据备份了。

如何执行备份恢复：备份与恢复在工作环境中是重中之重，为什么需要用到备份和恢复呢：

1、一般是做灾难恢复的，比如说自然灾害等。

2、可以做审计的，比如说某一数据在过去是什么样的。

3、做测试的，比如说一个新的业务架构数据存储方式是否扛得着业务的访问

备份的目的是用于恢复的，如果备份的数据用到时恢复不了数据怎么办，所以对备份数据做恢复测试是很有必要的。而且还要定期性的去做测试。

备份类型：有很多种

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

冷备份：cold backup，服务器要离线，意味着我们的读写操作都不可以进行了，这是最安全的备份方式，也是最不靠谱的方式。

温备份：warm backup，全局施加共享锁，只可读，不可写的备份叫温备份

热备份：hot backup，数据库不离线，读写操作都可以进行

InnoDB记录数据时都会给数据一个序列号，所以在备份时基于MVCC(多版本并发控制)的机制自动加快照，每启动一个事务都会创建当前集的一个快 照，而后基于MVCC的机制把每一个序列号都给它记录一份下来，备份时只备份序列号或序列号之前的数据，往后发生的将不做备份，如果事务的隔离级别不是特 别高的话，它并不会影响事务的读写操作，而这样备份出来的数据一定是时间点一致的数据，所以要完成热备份，通常是基于事务的存储引擎才能够完成的。

根据备份时的数据集进行分类：

完全备份：full backup：指备份整个库，当下数据集的整个库的数据

部分备份：partial backup：只备份某张表或某张表的一部份数据，有时备份单张表是有必要的。

根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据)

物理备份：直接复制(归档)数据文件的备份方式;跨平台能力没有逻辑备份好，physucal backup。大数据集用这个比较好。

逻辑备份：把数据库中提出来保存为文本文件;通常使用的工具是mysqldump，logical backup，对于大容量数据不适用。恢复速度很慢，占据空间很大

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

完全备份：full backup，跟备份数据集中的完全备份概念是相同的，也是备份整个库

增量备份：incremental backup，上一次完全备份之后所改变的数据做备份的为增量备份，比如说周一做一次备份，周二做一次备份，周三又做一次，这样一天天累加上去的叫增量备份。比较节约空间。

差异备份：differential backup，比如说周一做一次备份，到周二了就把周一和周二这两天的做一次备份，到周三就把周一周二周三的做一次备份，这就叫差异备份。比较容易恢复。

备份策略：需要考虑到的问题

1、选择备份方式，选择哪种方式根据我们的生产环境所需要来定;

2、执行备份时间，选项一个访问最少的时间做备份是比较合理的;

3、考虑到恢复成本：恢复时长;

4、备份成本：考虑到锁时间、备份时长、备份负载;

备份对象：我们备份需要备份什么呢

1、备份数据库中的数据是最重要的;

2、MySQL的配置文件，这个也是我们备份的对象

3、MySQL的代码也是需要备份的：存储过程，存储函数，触发器

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

5、如果是在主从复制的场景中，跟复制相关的信息也要备份

6、为了保证数据足够可靠，二进制日志文件也需要备份

常用的备份工具：

mysqldump：逻辑备份工具，是单线程备份工具，所以在某个服务器上做备份时它只能启动一个CPU启动一个线程进行备份，性能比较差。

对InnoDB热备、对MyISAM只能做到温备、对Aria温备，备份和恢复过程较慢;

mysqldumper：多线程的mysqldump，对多个库或多张表可以同时进行，提高性能;

mysqldump、mysqldumper，这两个都是逻辑备份工具，通常情况也很难实现差异或增量备份，只能做完全备份，但可以做部分备份，比如只备份一张表是可以实现的;

基于冷备份时：cp， 要基于lvm-snapshot逻辑卷快照进行备份的，接近于热备工具，因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁;而后使用 cp、tar等工具进行物理备份(因为复制的源数据文件)，所以备份和恢复数据速度较快，缺点很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙 的服务器上尤其如此。

SELECT clause INTO OUTFILE ‘/path/to/somefile’;把挑选出来的子句保存到某一个文件中，是个部分备份工具，不会备份关系定义，仅备份表中的数据，但这也是个逻辑 备份工具，在速度上也快于mysqldump，也没法实现增量备份。

LOAD ADTA INFILE ‘/path/from/somefile’;表示从哪里读数据来恢复的;

Innobase：提供了商业备份工具为Innobackup，可以实现InnoDB的热备支持增量备份;但是对于MyISAM不支持增量备份，只能实现完全备份，属于物理备份，速度比较快。

Xtrabackup：由Percona组织提供的开源备份工具，物理备份，速度快;

Mysqlhostcopy：几乎冷备，吹牛工具，不适用;

mysqldump：常用的备份工具，也是个逻辑备份工具，用于小数据备份，一般都是在5G以下的小数据进行备份;可以使用文本进行二次处理;相当于MySQL的客户端工具

使用格式：mysqldump [options] [db_name [tbl_name ...]]

备份单个库时用这个工具：mysqldump[option] db_name

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

--all-databases：备份所有的数据库--databases db1 db2：备份指定的多个数据库，用空格隔开--lock-all-tables：请求锁定所有表之后再备份，一般只对MyISAM做温备，不过也可以对InnoDB和Aria做温备。--events：备份事件调度器代码--routines：备份存储过程和存储函数--triggers：备份触发器--flush-logs：备份前、请求到锁以后滚动日志，备份时滚动日志，手动滚动就要手动施加锁--master-data=[0|1|2]复制时的同步位置标记,0表示不记录，1记录carnge master语句，2记录为注释为change master语句--single-transaction：能够对InnoDB存储引擎实现热备份，启动一个单一的大事物，基于MVCC(多版本并发控制)实现对InnoDB存储引擎的热备，它会自动加锁的，不要跟--lock-all-tables同时使用;

# mysqldump --databases hellodb --lock-all-tables > /tmp/hellodb.sql ：备份数据库，并且请求表锁，这里备份的所有写操作都会被阻塞，这种方式也不太理想。

# mysqldump --databases hellodb --lock-all-tables --flush-logs > /tmp/hellodb.sql

# mysqldump --databases hellodb --single-transaction --flush-logs > /tmp/hellodb.sql：保证这个库下所有的表的存储引擎都是InnoDB的前提下使用--single-transaction进行热备。

分时间段查看二进制日志后重定向到某个文件中去：

# mysqlbinlog --start-porition=367 --stop-position=669 master-bin.000005 > /tmp/hellodb.inc.sql

定义一个开始点和一个结束点就可以把二进制文件重定向到某个文件中再进行恢复了;

使用mysqldump备份时：

请求锁：--lock-all-tables使用--single-transaction进行innodb热备;

滚动日志：--flush-logs

选定要备份的库：--databases

指定二进制日志文件及位置：--master-data=2

注意：备份前需要加锁，恢复时，建议关闭二进制日志，关闭其它用户连接

备份策略：mysqldump+二进制日志文件：

恢复：完全备份+各二进制日志文件中至此刻的事件，恢复过程发生的事件没必要也写到二进制日志文件中去;所以在恢复时要临时性的关闭二进制日志文件：

MariaDB [(hellodb)] > set session sql_log_bin=0：临时关闭二进制日志文件

MariaDB [(hellodb)] > source /tmp/hellodb.sql;在数据库命令行直接读取备份文件

对MySQL配置文件，以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份

lvm-snapshot：基于LVM快照的备份

1、基于快照做备份有个提前，事物日志跟数据文件必须在同一个卷上;

2、创建快照卷之前，要请求MySQL的全局锁，在快照创建完成之后释放锁;

3、请求全局锁完成这后做一次日志滚动;做标记，时间记录，做二进制日志文件及位置标记(需要手动进行);

注意：

1、将数据和备份放在不同的磁盘设备上，异机或异地的备份存储为理想;

2、备份的数据应该周期性的进行还原测试;

3、每次灾难恢复后都应该立即做一次完全备份

4、针对不同规模或级别的数据量，要定制好备份策略;

5、二进制日志应该跟数据文件在不同的磁盘上，并周期性的备份好二进制日志文件;

从备份中恢复应该遵循的步骤：

1、停止MySQL服务器;

2、记录服务器的配置和文件权限;

3、将数据从备份移到MySQL数据目录，其执行方式依赖于工具;

4、改变配置和文件权限;

5、以限制访问模块重启服务器，mysqld的--skip-network选项可跳过网络功能;

方法：编辑my.cnf配置文件，添加如下项

skip-networking

socket=/tmp/mysql-recovery.sock

6、载入逻辑备份(如果有)，而后检查和重放二进制日志

7、检查已经还原的数据;

8、重新以完全访问模式重启服务器;

使用mysqldump实现备份,用二进制日志恢复数据，这里我们以当前系统上的数据库hellodb为例：

第一步：先把hellodb这个数据库做一次完全备份，当然，如果数据库的数据很大，比如说大于10G的话不建议使用mysqldump这个工具做备份，这里我们只为了说明问题：

# -u指定用户，-p指用户密码，--databases指定备份哪个数据库，--lock-all-tables指备份时请求表锁，--flush-logs滚动日志

[root@node0 ~]# mysqldump -uroot -plinux --databases hellodb --lock-all-tables --flush-logs --master-data=2 > /root/hellodb.sql

[root@node0 ~]# ll -h hellodb.sql-rw-r--r-- 1 root root 7.8K May 2 14:26 hellodb.sql

[root@node0 ~]#

第二步：在数据库hellodb中修改或创建一些表或数据，使得之前备份的数据跟现有的数据库中的数据存在差别，以完后后面的通过二进制日志进行数据恢复：

MariaDB [hellodb]> CREATE TABLE newtable;

ERROR 1113 (42000): A table must have at least 1 column

MariaDB [hellodb]> CREATE TABLE newtable(Name CHAR(20));

Query OK, 0 rows affected (1.11 sec)

MariaDB [hellodb]> INSERT INTO newtable values ('Tom'),('Jerry'),('Lucy');

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

第三步：把这个hellodb这个数据库给删除了去：

MariaDB [hellodb]> CREATE TABLE newtable;

ERROR 1113 (42000): A table must have at least 1 column

MariaDB [hellodb]> CREATE TABLE newtable(Name CHAR(20));

Query OK, 0 rows affected (1.11 sec)

MariaDB [hellodb]> INSERT INTO newtable values ('Tom'),('Jerry'),('Lucy');

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

第四步：查看我们的二进制日志文件，把后来改修改和创建的记录二进制日志的信息保存出来，以便我们恢复新增的数据内容，我们备份时做了日志滚动，所以查看最后一个日志信息就可以了;

[root@node0 ~]# cd /mydata/data/[root@node0 data]# mysqlbinlog --start-position=403 --stop-position=663 mysql-bin.000004 > /tmp/hellodb.binlog.sql

第五步：恢复数据，进入到mysql的命令行模式下，把二进制日志关掉，恢复数据时不需要把恢复信息记录到二进制日志中去;

# 关闭当前全话的二进制日志文件

MariaDB [(none)]> SET session sql_log_bin=0;

MariaDB [(none)]> source /root/hellodb.sql

MariaDB [hellodb]> SHOW MASTER STATUS; 查看两次二进制文件有没有改动;+------------------+----------+--------------+------------------+

File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

mysql-bin.000004 | 815 | | |

+------------------+----------+--------------+------------------+MariaDB [hellodb]> SHOW TABLES;

MariaDB [hellodb]> source /tmp/hellodb.binlog.sql

MariaDB [hellodb]> SELECT * FROM newtable;+-------+

Name |

+-------+

Tom |

Jerry |

Lucy |

+-------+3 rows in set (0.00 sec)

MariaDB [hellodb]> SET session sql_log_bin=1; 让二进制上线

OK，这种就是基于mysqldump+二进制日志完成数据完全恢复过来了，通过二进制日志可以很好的帮助我们恢复那些可能没有来得急备份的数据，对我们的数据安全有很重要。

lvm-snapshot：基于逻辑卷快照做备份，这里要注意的是，快照并不是备份，最多也只是说借助于快照来做数据备份，快照本身不是备份;

做了快照之后，一旦源卷的数据需要修改，就要把源卷上的数据复制一份到快照卷上，以后再通过快照卷访问时，那些修改了的数据都通过快照存储来访问，没修改的还通过源卷访问，快照只存储变化的数据;

1、它仅仅是作为源卷的一个访问路径;

2、刚创建好的快照卷时，快照卷中是没有任何数据的，它仅是所有数据指向了源卷的数据，所以访问的数据都是来自源卷;

3、一旦源卷中的数据需要修改了，某一数据在改之前需要将数据复制一份到快照卷中，所以以后再通过快照卷访问的数据都是一部分来自快照卷，一部份来自源 卷;那些修改的数据来自快照卷，没修改的数据来自源卷;快照卷仅是能够给我们提供了一个时间一致性文件的访问通路;

做快照卷要注意的几点：

1、基于快照备份时，事务日志必须跟数据文件在同一个卷上;

2、创建快照卷之前，要请求mysql的全局锁，在快照创建完成之后释放锁;

3、请求全局锁完成之后做一次日志滚动，以便记录时间点的;做二进制日志文件及位置标记，就是master-data;

第一步：首先先看一下你的数据目录的是不是逻辑卷，mount看一下，如果不是逻辑卷的话就别这样玩;

[root@node0 ~]# mount/dev/mapper/vg0-root on / type ext4 (rw)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

tmpfs on /dev/shm type tmpfs (rw)/dev/sda1 on /boot type ext4 (rw)/dev/mapper/vg0-usr on /usr type ext4 (rw)/dev/mapper/vg0-var on /var type ext4 (rw)/dev/mapper/mydata-mysqldata on /mydata/data type ext4 (rw,noatime)

none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

[root@node0 ~]# lvs

LV VG Attr LSize Pool Origin Data% Move Log Cpy%Sync Convert

mysqldata mydata -wi-ao---- 8.00g

root vg0 -wi-ao---- 20.00g

swap vg0 -wi-ao---- 2.00g

usr vg0 -wi-ao---- 10.00g

var vg0 -wi-ao---- 20.00g

[root@node0 ~]# vgs

VG #PV #LV #SN Attr VSize VFree

mydata 1 1 0 wz--n- 10.00g 2.00g

vg0 1 4 0 wz--n- 59.99g 7.99g

[root@node0 ~]#

第二步：请求锁，这个如果是在生产环境中我们可以用脚本来完成，因为这样请求到释放锁的时间就可以比较快;

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; # 请求锁

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH LOGS; # 滚动一下日志

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> SHOW MASTER STATUS; # 查看一下日志position信息+------------------+----------+--------------+------------------+

File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

mysql-bin.000002 | 365 | | |

+------------------+----------+--------------+------------------+1 row in set (0.04 sec)

MariaDB [(none)]>

第三步：创建快照卷，要在你的逻辑卷的有效空间大小范围内创建，不能超过逻辑卷的大小：

[root@node0 ~]# mkdir backup

[root@node0 ~]# mysql -e 'show master status'+------------------+----------+--------------+------------------+

File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

mysql-bin.000002 | 365 | | |

+------------------+----------+--------------+------------------+[root@node0 ~]# mysql -e 'show master status' > /root/backup/binlog.pos

[root@node0 ~]# lvcreate -L 100M -s -n mydata-snap -p r /dev/mydata/mysqldata

Logical volume "mydata-snap" created

[root@node0 ~]# lvs

LV VG Attr LSize Pool Origin Data% Move Log Cpy%Sync Convert

mydata-snap mydata sri-a-s--- 100.00m mysqldata 0.01

mysqldata mydata owi-aos--- 8.00g

root vg0 -wi-ao---- 20.00g

swap vg0 -wi-ao---- 2.00g

usr vg0 -wi-ao---- 10.00g

var vg0 -wi-ao---- 20.00g

[root@node0 ~]#

第四步：然后再到mysql命令行中释放锁：

MariaDB [(none)]> UNLOCK TABLES;

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>

第五步：挂载快照卷，然后需要备份什么只需要复制一份数据目录就可以了，其实的按需要做备份就可以了，如果不确定也可以全都复制好了，安全起见：

[root@node0 ~]# mount /dev/mydata/mydata-snap /data -o ro

[root@node0 ~]# cd /data/[root@node0 data]# ls aria_log.00000001 ibdata1 multi-master.info mysql-bin.000002 node0.tanxw.com.pid

aria_log_control ib_logfile0 mysql mysql-bin.index test

hellodb ib_logfile1 mysql-bin.000001 node0.tanxw.com.err

[root@node0 data]#

第六步：这里才是做真正的备份，只要直接复制数据就可以了，备份完之后就可以卸载快照卷了，如果不需要就可以把快照卷删除就可以了：

[root@node0 data]# cp -a /data/ /root/backup/data-2014-05-02 # -a归档复制数据

[root@node0 data]# cd /root/backup/data-2014-05-02[root@node0 data-2014-05-02]# ls aria_log.00000001 ibdata1 multi-master.info mysql-bin.000002 node1.tanxw.com.pid

aria_log_control ib_logfile0 mysql mysql-bin.index test

hellodb ib_logfile1 mysql-bin.000001 node1.tanxw.com.err

[root@node0 data-2014-05-02]# pwd/root/backup/data-2014-05-02[root@node0 data-2014-05-02]# umount /data/[root@node0 ~]# lvremove /dev/mydata/mydata-snap # 删除快照

Do you really want to remove active logical volume mydata-snap? [y/n]: y

Logical volume "mydata-snap" successfully removed

[root@node0 ~]#

在这里需要注意的是：如果备份这一刻有数据修改了，那我们备份下来的数据是不会保存快照后修改的数据的;

结束：

数据备份与恢复的重要性大家都知道，当然备份也是其中一个而且是归重要的一个保护数据安全性的重要手段之一，所以能熟练掌握数据的备份与恢复对我们工作来说也是至关重要的，有什么不对希望大神多多指点。