17.1数据备份

17.1　数据备份

数据备份是数据库管理员非常重要的工作。系统意外崩溃或者硬件的损坏都可能导致数据的丢失，因此MySQL管理员应该定期地备份数据库，使得在意外情况发生时，尽可能地减少损失。本节将介绍数据备份的三种方法。

17.1.1　使用mysqldump命令备份

mysqldump是MySQL提供的一个非常有用的数据库备份工具。执行mysqldump命令时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

mysqldump备份数据库语句的基本语法格式如下：

　　mysqldump –u user –h host –ppassword dbname[tbname, [tbname...]]> filename.sql

user表示用户名称；host表示登录用户的主机名称；password为登录密码；dbname为需要备份的数据库名称；tbname为dbname数据库中需要备份的数据表，可以指定多个需要备份的表；右箭头符号‘>’告诉mysqldump将备份数据表的定义和数据写入到备份文件；filename.sql为备份文件的名称。

1．使用mysqldump备份单个数据库中的所有表

【例17.1】使用mysqldump命令备份数据库中的所有表，执行过程如下。

为了更好地理解mysqldump工具如何工作，本章给出一个完整的数据库例子。首先登录MySQL，按下面的数据库结构创建booksDB数据库和各个表，并插入数据记录。数据库和表定义如下：

　　CREATE DATABASE booksDB;
　　user booksDB;
　　CREATE TABLE books
　　(
　　    bk_id INT NOT NULL PRIMARY KEY,
　　    bk_title VARCHAR(50) NOT NULL,
　　    copyright YEAR NOT NULL
　　);
　　INSERT INTO books
　　VALUES (11078, 'Learning MySQL', 2010),
　　        (11033, 'Study Html', 2011),
　　        (11035, 'How to use php', 2003),
　　        (11072, 'Teach youself javascript', 2005),
　　        (11028, 'Learing C++', 2005),
　　        (11069, 'MySQL professional', 2009),
　　        (11026, 'Guide to MySQL 5.5', 2008),
　　        (11041, 'Inside VC++', 2011);
　　CREATE TABLE authors
　　(
　　    auth_id INT NOT NULL PRIMARY KEY,
　　    auth_name VARCHAR(20),
　　    auth_gender CHAR(1)
　　);
　　INSERT INTO authors
　　VALUES (1001, 'WriterX' ,'f'),
　　        (1002, 'WriterA' ,'f'),
　　        (1003, 'WriterB' ,'m'),
　　        (1004, 'WriterC' ,'f'),
　　        (1011, 'WriterD' ,'f'),
　　        (1012, 'WriterE' ,'m'),
　　        (1013, 'WriterF' ,'m'),
　　        (1014, 'WriterG' ,'f'),
　　        (1015, 'WriterH' ,'f');
　　CREATE TABLE authorbook
　　(
　　    auth_id INT NOT NULL,
　　    bk_id INT NOT NULL,
　　    PRIMARY KEY (auth_id, bk_id),
　　    FOREIGN KEY (auth_id) REFERENCES authors (auth_id),
　　    FOREIGN KEY (bk_id) REFERENCES books (bk_id)
　　);
　　INSERT INTO authorbook
　　VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
　　        (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

完成数据插入后打开操作系统命令行输入窗口，输入备份命令如下：

　　C:\ >mysqldump -u root -p booksdb > C:/backup/booksdb_20110101.sql
　　Enter password: **

输入密码之后，MySQL便对数据库进行了备份。注意，这里我们将备份后的文件存储在C盘backup文件夹中，在执行mysqldump备份语句之前，要确保C:\backup文件夹已经创建存在，否则，执行上面的备份语句会出错。备份完成之后，在C:\backup文件夹下面查看刚才备份过的文件，使用文本查看器打开文件可以看到其部分文件内容大致如下：

　　-- MySQL dump 10.13  Distrib 5.5.13, for Win32 (x86)
　　--
　　-- Host: localhost    Database: booksdb
　　-- ------------------------------------------------------
　　-- Server version	5.5.13
　　/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
　　/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
　　/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
　　/*!40101 SET NAMES utf8 */;
　　/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
　　/*!40103 SET TIME_ZONE='+00:00' */;
　　/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
　　/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KE
　　Y_CHECKS=0 */;
　　/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=
　　'NO_AUTO_VALUE_ON_ZERO' */;
　　/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
　　--
　　-- Table structure for table 'authorbook'
　　--
　　DROP TABLE IF EXISTS 'authorbook';
　　/*!40101 SET @saved_cs_client     = @@character_set_client */;
　　/*!40101 SET character_set_client = utf8 */;
　　CREATE TABLE 'authorbook' (
　　  'auth_id' int(11) NOT NULL,
　　  'bk_id' int(11) NOT NULL,
　　  PRIMARY KEY ('auth_id','bk_id'),
　　  KEY 'bk_id' ('bk_id'),
　　  CONSTRAINT 'authorbook_ibfk_1' FOREIGN KEY ('auth_id')
　　  REFERENCES 'authors' ('auth_id'),
　　  CONSTRAINT 'authorbook_ibfk_2' FOREIGN KEY ('bk_id')
　　REFERENCES 'books' ('bk_id')
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　/*!40101 SET character_set_client = @saved_cs_client */;
　　--
　　-- Dumping data for table 'authorbook'
　　--
　　LOCK TABLES 'authorbook' WRITE;
　　/*!40000 ALTER TABLE 'authorbook' DISABLE KEYS */;
　　INSERT INTO 'authorbook' VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012, 11041),(1014,11069),(1003,11072),(1011,11078);
　　/*!40000 ALTER TABLE 'authorbook' ENABLE KEYS */;
　　UNLOCK TABLES;
　　…
　　…省略部分内容
　　…
　　/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
　　/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
　　/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
　　/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
　　/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
　　/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
　　/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
　　/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
　　-- Dump completed on 2011-08-18 10:44:08

可以看到，备份文件包含了一些信息，文件开头首先表明了备份文件使用的mysqldump工具的版本号，然后是备份账户和主机信息，以及备份的数据库的名称，最后是MySQL服务器的版本号，在这里为5.5.13。

备份文件接下来的部分是一些SET语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如：

　　/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

该SET语句将当前系统变量CHARACTER_SET_CLIENT的值赋给用户定义变量@OLD_CHARACTER_SET_CLIENT。其他变量与此类似。

备份文件的最后几行MySQL使用SET语句恢复服务器系统变量原来的值，例如：

　　/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

该语句将用户定义的变量@OLD_CHARACTER_SET_CLIENT中保存的值赋给实际的系统变量CHARACTER_SET_CLIENT。

在备份文件中，以“--”字符开头的都是SQL语言的注释。以“/*!40101”开头，“*/”结尾的语句都是与MySQL有关的注释。其中40101代表MySQL数据库版本号，表示为MySQL 4.1.1。在还原数据时，如果MySQL版本号比4.1.1高，则/*!40101”和“*/”之间的语句将被当作SQL命令执行。如果比4.1.1低，则/*!40101”和“*/”之间的语句被当作注释。

2．使用mysqldump备份数据库中的某个表

在前面mysqldump语法中介绍，mysqldump还可以备份数据库中的某个表，其语法格式为：

　　mysqldump -u user -h host -p dbname [tbname, [tbname...]] > filename.sql

tbname表示数据库中的表名，多个表名之间用空格隔开。

备份表与备份数据库中所有表的语句不同的地方在于要在数据库名称dbname之后要指定需要备份的表名称。

【例17.2】备份booksDB数据库中的books表，输入语句如下：

　　mysqldump -u root -p booksDB books > C:/backup/books_20110101.sql

该语句创建名称为books_20110101.sql的备份文件，文件中包含了前面介绍的SET语句等内容，不同的是，该文件只包含books表的CREATE和INSERT语句。

3．使用mysqldump备份多个数据库

如果要使用mysqldump备份多个数据库，需要使用--databases参数。备份多个数据库的语句格式如下：

　　mysqldump -u user -h host -p --databases [dbname [dbname...]] > filename.sql

使用--databases参数之后，必须指定至少一个数据库的名称，多个数据库名称之间用空格隔开。

【例17.3】使用mysqldump备份booksDB和test数据库，输入语句如下：

　　mysqldump -u root -p --databases booksDB test> C:\backup\books_testDB_20110101.sql

该语句创建名称为books_testDB_20110101.sql的备份文件，文件中包含了创建两个数据库booksDB和test所必需的所有语句。

另外，使用--all-databases参数可以备份系统中所有的数据库，语句如下：

　　mysqldump -u user -h host -p --all-databases > filename.sql

使用--all-databases参数时，不需要指定数据库名称。

【例17.4】使用mysqldump备份服务器中的所有数据库，输入语句如下：

　　mysqldump -u root -p --all-databases > C:/backup/alldbinMySQL.sql

该语句创建名称为alldbinMySQL.sql的备份文件，文件中包含了对系统中所有数据库的备份信息。

提　示

mysqldump还有一些其他选项可以用来控制备份过程，例如--opt选项，该选项将打开--quick 、--add-locks、--extended-insert等多个选项。使用--opt选项可以提供最快速的数据库转储。

mysqldump其他常用选项如下：

•--add-drop—database ：在每个CREATE DATABASE语句前添加DROP DATABASE语句。

•--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。

•--add-locking ：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

•--all--database，-A：转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。

•---comments[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip—comments与---comments=0的结果相同。默认值为1，即包括额外信息。

•--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。

•--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。

•--complete-insert，-c：使用包括列名的完整的INSERT语句。

•---debug[=debug_options]，-# [debug_options]：写调试日志。

•--delete，-D：导入文本文件前清空表。

•--default-character-set=charset：使用charsetas默认字符集。如果没有指定，mysqldump使用utf8。

•--delete-master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。

•--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，重载文件时可以加速插入。

•--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。

•--force，-f：在表转储过程中，即使出现SQL错误也继续。

•--lock-all-tables，-x：所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭--single-transaction和--lock-tables。

•--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表，例如InnoDB和BDB，--single-transaction是一个更好的选项，因为它不根本需要锁定表。

•--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name语句，如果给出---database或--all--database选项，则包含到输出中。

•--no-create-info，-t：不写重新创建每个转储表的CREATE TABLE语句。

•--no-data，-d：不写表的任何行信息，只转储表的结构。

•--opt：该选项是速记，等同于指定--add-drop-tables--add-locking，--create-option，--disable-keys--extended-insert，--lock-tables –quick和--set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。要想只禁用确信用-opt启用的选项，使用--skip形式，例如--skip-add-drop-tables或--skip-quick。

•--password[=password]，-p[password]：连接服务器时使用的密码。如果使用短选项形式(-p)，选项和密码之间不能有空格。如果在命令行中--password或-p选项后面没有密码值，则提示输入一个密码。

•--port=port_num，-P port_num：用于连接的TCP/IP端口号。

•--protocol={TCP | SOCKET | PIPE | MEMORY}：使用的连接协议。

•--replace，-r --replace和--ignore选项：控制复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行。如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

•--silent，-s：沉默模式。只有出现错误时才输出。

•--socket=path，-S path：当连接localhost时使用的套接字文件(为默认主机)。

•--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。

•--verbose，-v：冗长模式。打印出程序操作的详细信息。

•--version，-V：显示版本信息并退出。

•--xml，-X：产生XML输出。

mysqldump提供许多的选项，包括用于调试和压缩的。在这里只是列举最有用的。运行帮助命令mysqldump --help，可以获得特定版本的完整选项列表。

提　示

17.1.2　直接复制整个数据库目录

因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份。MySQL的数据库目录位置不一定相同，在Windows平台下，MySQL 5.5存放数据库的目录通常默认为“C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data”或者其他用户自定义目录；在Linux平台下，数据库目录位置通常为/var/lib/mysql/，不同Linux版本下目录会有所不同，读者应在自己使用的平台下查找该目录。

这是一种简单、快速、有效的备份方式。要想保持备份的一致性，备份前需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。这样当复制数据库目录中的文件时，允许其他客户继续查询表。开始备份前需要FLUSH TABLES语句来确保将所有激活的索引页写入硬盘。当然，也可以停止MySQL服务再进行备份操作。

这种方法虽然简单，但并不是最好的方法。因为这种方法对InnoDB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中，不同的版本可能不兼容。

提　示

17.1.3　使用mysqlhotcopy工具快速备份

mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上，并且只可以备份MyISAM类型的表。mysqlhotcopy在UNIX系统中运行。

mysqlhotcopy命令语法格式如下：

　　mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory

da_name1,…,da_name_n分别为需要备份的数据库的名称；/path/to/new_directory指定备份文件目录。

【例17.5】使用mysqlhotcopy备份test数据库到/usr/backup目录下，输入语句如下：

　　mysqlhotcopy -u root –p test /usr/backup

要想执行mysqlhotcopy，必须可以访问备份的表文件，具有那些表的SELECT权限、RELOAD权限（以便能够执行FLUSH TABLES）和LOCK TABLES权限。

提　示