MySQL备份与恢复

作者：roverliang

原文链接：MySQL备份与恢复

MySQL备份与恢复

上篇博客讲了MySQL的日志系统，这篇博客将系统的讲解MySQL备份与恢复。按照常理说这些内容都是属于DBA的职责，那么作为后端开发人员为什么要学习这些内容呢？是否与专业深度的思想相违背呢？其实作为WEB开发人员，一定要系统的了解从服务发起到服务终结之间所有的内容，这样才算是专精，提升自己的职业深度。MySQL就如同技术人员的粮仓，怎么储存粮食，失火的时候怎么确保粮食颗粒归仓，这是一个必须引起充分重视的问题。下面我们来系统的了解下MySQL的备份与恢复。

MySQL服务实例运行期间，意外的停电，硬盘损坏，还有一些误操作、服务器宕机等情况。这个时候如何确保数据库能够最大程度地恢复到‘正确‘的状态呢？

对于数据库管理人员来说，防止数据丢失最简单的方法就是：对原始数据定期进行备份，创建数据副本。但数据与预期发生不一致情况，然后使用备份的数据恢复数据。对于MySQL来说，创建数据副本的常用方法有三种

创建数据副本的方法

1、数据备份：

由MySQL日志系统前一篇博客我们知道数据库的全部数据都以文件的形式，存储在硬盘上，那我们就可以直接备份MySQL的所有数据目录下的文件就能够达到目的。因此在开启MySQL服务的时候要设计好各种数据文件以及日志文件的存放位置,以方便能够快速备份。另外备份的时候最好能够备份到其他的机器上。

2、使用二进制日志：

上面的【数据备份方法】属于物理备份，粒度较粗，不能实现更细粒度的数据恢复，特别是对于更新较为频繁的系统。二进制日志记录了数据库所有的更新操作，数据丢失时，可以通过完全备份进行二进制日志的重做，可以完成基于时间点或者操作点的恢复，继而实现数据库更细粒度的恢复。

3、数据库复制：

数据库的复制实际上是通过二进制日志预防数据丢失的，数据复制可以实现数据库的异地备份和恢复。

逻辑备份与物理备份

按照备份后产生的副本文件是否可以编辑，可以将MySQL的备份方法分为逻辑备份和物理备份

1、逻辑备份：

使用逻辑备份是，数据库管理员通常可以直接查看和编辑副本文件中的内容。逻辑备份中产生的副本通常哟两种情况。情形一：副本是SQL文件，该SQL文件中有crete table 和大量的inert 语句。情形二、副本是指定分隔符的文件，导入数据库的时候再以指定分隔符切割数据导入即可。

2、物理备份：

物理备份产生的数据副本都是二进制文件，常常不可编辑，例如数据库的二进制日志。

冷备份、温备份、热备份

数据备份期间，按照是否需要停止MySQL服务实例，可以将MySQL的数据恢复分为：冷备份、温备份、热备份。

1、冷备份：

冷备份是指停止MySQL服务的运行后在进行数据备份，这种备份方法非常简单，但是在服务繁忙的系统中，并不允许这样做。

2、温备份： 温备份介于热备份与冷备份之间，温备份允许MySQL服务实例继续运行，备份数据期间，温备份借助读锁机制保证备份期间，没有新的数据写入。例如常常会执行如下命令来进行温备份：

flush tables with read lock;

3、热备份：

热备份是指不需要停止MySQL服务实例运行，备份数据的方法。如果数据库的更新操作较为频繁，在数据备份期间，备份过的数据可能早已发生变化，因此热备份的实现方式较为复杂。通常热备份需要借助第三方工具实现，例如:

MySQL提供了自带工具mysqlhotcopy 实现了MyISAM引擎的热备份。
percona公司的Xtrabackup工具实现了对InnoDB表的热备份。

完全备份、增量备份

按照副本文件的缺失程度可以将数据备份分为完全备份以及增量备份。

1、完全备份：

完全备份是一个完整的数据备份，仅仅依靠该副本文件就可以将数据库恢复到某个正确的状态。如果不借助热备份工具，完全备份可能需要停止MySQL服务。此时MySQL将无法提供服务，在真实的业务场景中，很少真正使用完全备份。

2、增量备份：

增量备份是指在完全备份的基础上，对更新的数据进行备份，恢复时需要借助完全备份产生的副本文件，目前，MySQL还没有提供真正的增量备份工具。数据库管理人员可以使用热备份工具模拟实现增量备份，也可以通过重新执行二进制日志中的更新语句模拟实现增量备份。

一、逻辑备份与逻辑恢复

逻辑备份与逻辑恢复的最大优点在于，对于不同存储引擎的表，都可以采用同样的逻辑备份方法产生副本文件。采用同样的恢复方法将数据恢复到某个正确的状态中。如果数据库中有多个存储引擎，选用逻辑备份恢复数据会简单一点。逻辑备份常用的有select ... into outfile ，以及mysqldump工具。前者产生的副本文件为指定格式的文本文件，后者产生的副本文件既可以是指定格式的文本文件也可以是SQL脚本文件。逻辑恢复常用的工具有load data in file 以及 mysqlimport。

使用select ...into outfile备份数据

语法格式如下： select 语句 into outfile 文本文件 文本文件选项和参数如下：

fields terminated by ‘字符串‘ ： 字符串分割符，默认是制表符‘\t‘
fileds escaped by ’字符‘ ： 转义字符，默认是’\‘
fileds [optionally] enclose by 字符’‘： 字段引用符，负责向字段值两端加上字段引用符。如果使用optionally 选项，则表示字符串类型上添加字段分隔符。
lines starting by ‘字符串‘，每条记录前添加该字段。
lines terminated by ’字符串‘，每条记录后添加该字符串，默认是换行符 ‘\n’

例如：

mysql> select * from classes into outfile ‘data_back‘ fields terminated by ‘|‘;

结果会生成在data目录下，对应的数据库目录下面。可以直接使用cat命令查看。

恢复表数据：

1、使用load data infile...快速地从一个指定格式的文本文件中读取数据到一个数据库表中。

更复杂的配置就不写了，没什么必要，还增加大脑的负担。等用到的时候，去网上查找具体的参数即可，我们并不擅长记忆这些并不常用的选项。 一句话概括这种逻辑备份的精要：怎么吃我的，怎么给我吐出来。怎么吐出来来，怎么给我装进去。选用了什么样的分割选项，导入数据的时候就需要使用对应的分割选项去导入数据。 结合我自己平时的工作内容与实践，基本上不使用这种方式导出。反而使用mysqldump 更多一点。

使用mysqldump 备份数据库

mysqldump也是MySQL转储数据库常用的自带工具，mysqldump产生副本文件有两种情形：

1、文件是指定格式的文本文件 2、是可以执行的SQL脚本文件

使用方法有以下3种：

1、备份指定的多个数据库。

mysqldump -u root -p --databases choose test > roverliang.sql

2、备份所有的数据库

mysqldump -u root -p --all-databases choose test > roverliang.sql

3、备份指定数据库中的某些表

mysqldump -u root -p  choose test > roverliang.sql

mysqldump 完整的参数是：

mysqldump -u USER_NAME -p PASSWORD [其他选项] DB [其他数据库库表]

• --default-character-set： 设置字符集
• --single-transaction : 将导出设置成事务
• --no-data : 导出的SQL脚本中，将只包含创建表的create 语句。
• --add-drop-table :导出的脚本中，包含 drop table if exists
• --routines导出存储过程及函数
• --events导出事件
• --triggers导出触发器

导入mysqldump 的数据文件

一句命令就能搞定，在mysql终端下执行：
命令格式：
source FILE_PATH
source ~/demo.sql

二、MySQL物理备份与热备份

这一部分介绍两款热备份工具：mysqlhotcopy 与 Xtrabackup;

其中mysqlhotcopy 是mysql自带的热备份工具，但仅能够备份MyISAM引擎的数据，而Xtrabackup 则可以备份InnoDB引擎和MyISAM引擎的数据，操作复杂。

Xtrabackup 的下载地址

MySQL数据库中，表的存储引擎不同，与之对应的文件类型也不相同。以MyISAM存储引擎为例，每张表有都会有3个文件，分别是表结构定义文件（frm）,表索引文件（MYI）,表数据文件（MYD）。那么备份的时候只需要物理的复制这三个文件就能完成备份。而InnoDB引擎为了维护事务安全，则要复杂的多。如果你看过前文的日志的话，就明白每个InnoDB都会存在frm表定义文件，InnoDB的数据和索引是在一个文件里面，称作表空间文件（共享表空间文件，独享表空间）。重做日志（redo）记录在iblogfile0 和iblogfile1中。回滚日志（undo）则记录在表空间文件中，共享表空间文件（ibdata1）或者独享表空间文件（ibd）中。关于InnoDB事务日志详情可查看MySQL系统日志。因此备份InnoDB表引擎的表时，上面提到的几个文件都要备份到。

有前文提到，冷、热、温备份的本质在于是否能够继续提供服务，根据提供服务的级别，才有了冷、热、温之分。

MyISAM 表物理备份、热备份工具 使用非常简单，但是我还是折腾了挺长的时间。先是mac mysql 5.7 版本去掉了mysqlhotbin脚本。然后登陆自己的阿里云服务器，安装一些mysqlhotcopy依赖的时候，报错无法解决。然后切换到自己的内网虚拟机，才最终解决了这个问题。 如安装遇到问题：可参考mysqlhotbin

mysqlhotcopy -u USER_NAME -p  USER_PASSWORD -addtodest DB1 DB2 DB3.table1 > PATH/TOSAVE.sql
另外可以在在my.cnf中配置密码：
[mysqlhotcopy]
interactive-timeout
user=root
password=123456
port=3306