Mysql数据备份之逻辑备份-tutorial mysql-php.cn

什么样的备份是数据库逻辑备份呢？

大家都知道，数据库在返回数据给我们使用的时候都是按照我们最初所设计期望的具有一定逻辑关联格式的形式一条一条数据来展现的，具有一定的商业逻辑属性，而在物理存储的层面上数据库软件却是按照数据库软件所设计的某种特定格式经过一定的处理后存放。

数据库逻辑备份就是备份软件按照我们最初所设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以达到备份的目的。

常用的逻辑备份

逻辑备份可以说是最简单，也是目前中小型系统最常使用的备份方式。在 MySQL中我们常用的逻辑备份主要就是两种，一种是将数据生成可以完全重现当前数据库中数据的INSERT 语句，另外一种就是将数据通过逻辑备份软件，将我们数据库表数据以特定分隔符进行分隔后记录在文本文件中。

1、生成 INSERT 语句备份

两种逻辑备份各有优劣，所针对的使用场景也会稍有差别，我们先来看一下生成 INSERT语句的逻辑备份。

在 MySQL 数据库中，我们一般都是通过 MySQL 数据库软件自带工具程序中的 mysqldump来实现声称 INSERT 语句的逻辑备份文件。其使用方法基本如下：

Dumping definition and data mysql database or table

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

由于 mysqldump 的使用方法比较简单，大部分需要的信息都可以通过运行 “mysqldump --help”而获得。这里我只想结合 MySQL 数据库的一些概念原理和大家探讨一下当我们使用mysqldump 来做数据库逻辑备份的时候有些什么技巧以及需要注意一些什么内容。

我们都知道，对于大多数使用数据库的软件或者网站来说，都希望自己数据库能够提供尽可能高的可用性，而不是时不时的就需要停机停止提供服务。因为一旦数据库无法提供服务，系统就无法再通过存取数据来提供一些动态功能。所以对于大多数系统来说如果要让每次备份都停机来做可能都是不可接受的，可是 mysqldump 程序的实现原理是通过我们给的参数信息加上数据库中的系统表信息来一个表一个表获取数据然后生成 INSERT 语句再写入备份文件中的。这样就出现了一个问题，在系统正常运行过程中，很可能会不断有数据变更的请求正在执行，这样就可能造成在 mysqldump 备份出来的数据不一致。也就是说备份数据很可能不是同一个时间点的数据，而且甚至可能都没办法满足完整性约束。这样的备份集对于有些系统来说可能并没有太大问题，但是对于有些对数据的一致性和完整性要求比较严格系统来说问题就大了，就是一个完全无效的备份集。

对于如此场景，我们该如何做？我们知道，想数据库中的数据一致，那么只有两种情况下可以做到。

第一、同一时刻取出所有数据；

第二、数据库中的数据处于静止状态。

对于第一种情况，大家肯定会想，这可能吗？不管如何，只要有两个以上的表，就算我们如何写程序，都不可能昨晚完全一致的取数时间点啊。是的，我们确实无法通过常规方法让取数的时间点完全一致，但是大家不要忘记，在同一个事务中，数据库是可以做到所读取的数据是处于同一个时间点的。所以，对于事务支持的存储引擎，如 Innodb 或者 BDB 等，我们就可以通过控制将整个备份过程控制在同一个事务中，来达到备份数据的一致性和完整性，而且 mysqldump 程序也给我们提供了相关的参数选项来支持该功能，就是通过 “--single-transaction”选项，可以不影响数据库的任何正常服务。

对于第二种情况我想大家首先想到的肯定是将需要备份的表锁定，只允许读取而不允许写入。是的，我们确实只能这么做。我们只能通过一个折衷的处理方式，让数据库在备份过程中仅提供数据的查询服务，锁定写入的服务，来使数据暂时处于一个一致的不会被修改的状态，等 mysqldump 完成备份后再取消写入锁定，重新开始提供完整的服务。mysqldump 程序自己也提供了相关选项如“--lock-tables”和“--lock-all-tables”，在执行之前会锁定表，执行结束后自动释放锁定。这里有一点需要注意的就是， “--lock-tables”并不是一次性将需要 dump 的所有表锁定，而是每次仅仅锁定一个数据库的表，如果你需要 dump 的表分别在多个不同的数据库中，一定要使用“--lock-all-tables”才能确保数据的一致完整性。

当通过 mysqldump 生成 INSERT 语句的逻辑备份文件的时候，有一个非常有用的选项可以供我们使用，那就是 “--master-data[=value]”。当添加了 “--master-data=1”的时候，mysqldump 会将当前 MySQL 使用到 binlog 日志的名称和位置记录到 dump 文件中，并且是被以 CHANGE_MASTER 语句的形式记录，如果仅仅只是使用“--master-data”或者“--masterdata＝2”，则 CHANGE_MASTER 语句会以注释的形式存在。这个选项在实施 slave 的在线搭建的时候是非常有用的，即使不是进行在线搭建 slave，也可以在某些情况下做恢复的过程中通过备份的 binlog 做进一步恢复操作。

在某些场景下，我们可能只是为了将某些特殊的数据导出到其他数据库中，而又不希望通过先建临时表的方式来实现，我们还可以在通过 mysqldump 程序的“—where='wherecondition'”来实现，但只能在仅 dump 一个表的情况下使用。其实除了以上一些使用诀窍之外， mysqldump 还提供了其他很多有用的选项供大家在不同的场景下只用，如通过 “--no-data”仅仅 dump 数据库结构创建脚本，通过 “--no-createinfo”去掉 dump 文件中创建表结构的命令等等，感兴趣的读者朋友可以详细阅读 mysqldump程序的使用介绍再自行测试。

2、生成特定格式的纯文本备份数据文件备份

除了通过生成 INSERT 命令来做逻辑备份之外，我们还可以通过另外一种方式将数据库中的数据以特定分隔字符将数据分隔记录在文本文件中，以达到逻辑备份的效果。这样的备份数据与 INSERT 命令文件相比，所需要使用的存储空间更小，数据格式更加清晰明确，编辑方便。但是缺点是在同一个备份文件中不能存在多个表的备份数据，没有数据库结构的重建命令。对于备份集需要多个文件，对我们产生的影响无非就是文件多了维护和恢复成本增加，但这些基本上都可以通过编写一些简单的脚本来实现

那我们一般可以使用什么方法来生成这样的备份集文件呢，其实 MySQL 也已经给我们实现的相应的功能。

在 MySQL 中一般都使用以下两种方法来获得可以自定义分隔符的纯文本备份文件。

1、通过执行 SELECT ... TO OUTFILE FROM ...命令来实现

在 MySQL 中提供了一种 SELECT 语法，专供用户通过 SQL 语句将某些特定数据以指定格式输出到文本文件中，同时也提供了实用工具和相关的命令可以方便的将导出文件原样再导入到数据库中。正不正是我们做备份所需要的么？

该命令有几个需要注意的参数如下：

实现字符转义功能的“FIELDS ESCAPED BY ['name']” 将 SQL 语句中需要转义的字符进行转义；

可以将字段的内容“包装”起来的“FIELDS [OPTIONALLY] ENCLOSED BY 'name'”，如果不使用 “OPTIONALLY”则包括数字类型的所有类型数据都会被 “包装”，使用 “OPTIONALLY”之后，则数字类型的数据不会被指定字符“包装”。

通过"FIELDS TERMINATED BY"可以设定每两个字段之间的分隔符；

而通过“LINES TERMINATED BY”则会告诉 MySQL 输出文件在每条记录结束的时候需要
添加什么字符。

如以下示例：

root@localhost : test 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'

-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

-> LINES TERMINATED BY '\n'

-> FROM test_outfile limit 100;

Query OK, 100 rows affected (0.00 sec)

root@localhost : test 10:02:11> exit

Bye

root@sky:/tmp# cat dump.text

350021,21,"A","abcd"

350022,22,"B","abcd"

350023,23,"C","abcd"

350024,24,"D","abcd"

350025,25,"A","abcd"

... ...

2、通过 mysqldump 导出

可能我们都知道 mysqldump 可以将数据库中的数据以 INSERT 语句的形式生成相关备份文件，其实除了生成 INSERT 语句之外，mysqldump 还同样能实现上面“SELECT ... TOOUTFILE FROM ...”所实现的功能，而且同时还会生成一个相关数据库结构对应的创建脚本。

如以下示例：

root@sky:~# ls -l /tmp/mysqldump

total 0

root@sky:~# mysqldump -uroot -T/tmp/mysqldump test test_outfile --fieldsenclosed-by=\" --fields-terminated-by=,

root@sky:~# ls -l /tmp/mysqldump

total 8

-rw-r--r-- 1 root root 1346 2008-10-14 22:18 test_outfile.sql

-rw-rw-rw- 1 mysql mysql 2521 2008-10-14 22:18 test_outfile.txt

root@sky:~# cat /tmp/mysqldump/test_outfile.txt

350021,21,"A","abcd"

350022,22,"B","abcd"

350023,23,"C","abcd"