MySQL 学习<十四> 数据备份与恢复

数据备份

使用MySQLdump

MySQLdump是MySQL提供的一个非常有用的数据库备份工具。命令执行时，可以将数据库备份成一个文本文件；该文件中包含了多个CREATE和INSERT语句，其基本语法如下：

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

user表示用户名称；

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

1.使用MySQLdump备份单个数据库中的所有表

输入命令：

mysqldump -u root -p booksdb > C:/booksdb.sql；

如上，会提示错误，unknown option

查找了下解决方法，说查看 my.ini 发现[clien]下有 no-beep 参数，mysql客户端将会读取此参数(该参数作用暂时不知)。
这里具体解决办法有两种：

1.删除my.ini [client]下的 no-beep 参数;

2.在 mysqldump 后加--no-defaults参数,即:mysqldump  --no-defaults -u user  -h host  -ppassword dbname[tbname,[tbname...]]>filename.sql;
经测试，这两种方法有效。

查看备份文件，其内容大致如下：

可以看到，备份文件包含了一些信息，文件开头首先写明了mysqldump工具的版本号；

然后是主机信息，以及备份的数据库名称，最后是mysql服务器的版本号5.6.34

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

备份文件中的“--”字符开头的行为注释语句；以“/*!”开头、以“*/”结尾的语句为可执行的mysql注释，这些语句可以被mysql执行
但在其他数据库管理系统将被作为注释忽略，这可以提高数据库的可移植性。

另外注意到，备份文件开始的一些语句以数字开头，这些数字代表了mysql版本号，该数字告诉我们这些语句只有在指定的mysql版本或者比该版本高的情况下才能执行。

例如：40101，表明这些语句只有在mysql版本为4.01.01或者更高版本的条件下才可以执行。

2.使用MySQLdump备份数据库中的某个表

例如，备份booksDB下的books表，其命令如下：

3.使用MySQLdump备份多个数据库

需要用到--databases参数，其基本语法格式如下：

mysqldump  --no-defaults -u user  -h host  -ppassword --databases  dbname[tbname,[tbname...]]>filename.sql;

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

4.4.使用--all-databases参数备份系统中所有的数据库

直接复制整个数据库目录

因为MySQL表保存为文件方式，所以可以直接复制MySQL数据库的存储目录及文件进行备份windows平台一般是在安装目录下面的 \MySQL\MySQL Server 5.6\data中，大家应在自己使用的平台中查找相应目录；

这是一种简单的备份方式；但是要想保持备份的一致性，备份前需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES操作；

另外，这种方法对InnoDB存储引擎的表不适用，使用这种方法备份的数据最好恢复到相同版本的服务器中，不同的版本可能不兼容。

使用MySQLhotcopy

其基本语法如下：

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

db_name_1...n代表要备份的数据库的名称；
path/to/new_directory指定备份文件目录。

需要注意的是：

1.要执行MySQLhotcopy，必须可以访问备份的表文件，具有那些表的SELECT权限、RELOAD权限（以便执行FLUSH TABLES）和LOCK TABLES权限；

2.mysqlhotcopy只是将表所在目录复制到另一个位置，只能用于备份MyISAM和ARCHIVE表。备份InnoDB表会出现错误信息，由于他复制本地格式的文件，故也不能移植到其他硬件或操作系统下。

数据恢复

使用MySQL命令恢复

对于已经备份的包含CREATE、INSERT语句的文本文件，可以使用myslq命令导入数据库中。

备份的sql文件中包含CREATE、INSERT语句（有时也会有DROP语句）。mysql命令可以直接执行文件中的这些语句

其语法如下：

mysql -u user -h localhost -p [dbname]<filename.sql
user是执行backup.sql中语句的用户名；-p表示输入用户密码；dbname是数据库名

如果filename.sql文件为mysqldump工具创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名

用mysql命令将booksdb.sql文件中的备份导入到数据库中

执行语句之前我们必须建好booksDB2数据库，如果不存在恢复过程将会出错。

如果已经登录mysql，那么可以使用source命令导入备份文件，其基本命令如下：

source filename；

直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份文件到MySQL数据目录下实现还原。通过这种方式还原时，必须保证备份数据的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只对MyISAM引擎有效，对于InnoDB引擎的表不可用

windows平台在执行还原以前关闭MySQL服务，将备份的文件或目录覆盖MySQL的data目录，再启动MySQL服务。
对于Linux操作系统来说，复制完文件需要将文件的用户和组更改为MySQL运行的用户和组，通常用户是MySQL，组也是MySQL

使用MySQLhotcopy

mysqlhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件复制到MySQL存放数据的位置（MySQL的data文件夹），重新启动MySQL服务即可。

Linux下如果根用户执行该操作，必须指定数据库文件的所有者，输入语句如下：

chown -R mysql.mysql /var/lib/mysql/dbname

从mysqlhotcopy复制的备份恢复数据库

cp -R /usr/backup/test usr/local/mysql/data
执行完该语句，重启服务器，MySQL将恢复到备份状态

需要注意的是：

1.如果需要恢复的数据库已经存在，则在使用DROP语句删除已经存在的数据库之后，恢复才能成功。
2.另外MySQL不同版本之间必须兼容，恢复之后的数据才可以使用。

数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上。迁移的一般原因：

1.需要安装新的数据库服务器

2.mysql版本更新

3.数据库管理系统变更（从SQLSERVER迁移到MySQL）

相同版本的MySQL数据库之间迁移

相同版本MySQL数据库间的迁移就是主版本号相同的MySQL数据库直接进行数据库移动。

前面讲解备份和还原的时候，知道最简单的方法就是复制数据库文件目录，但是这种方法只适合于MyISAM表,对于InnoDB表，不能直接复制文件来备份数据库；

最常用的方法是使用mysqldump导出数据，然后在目标数据库服务器使用MySQL命令导入

例如，将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上。

在www.abc.com主机上执行以下命令：

mysqldump -h www.abc.com -u root -p dbname |
mysql -h www.bcd.com -u root -p

mysqldump导入的数据直接通过管道符“|”，传给MySQL命令导入到主机www.bcd.com数据库中，dbname为需要迁移的数据库名称，如果要迁移全部数据库，可以使用--all -databases参数

不同版本的MySQL数据库之间的迁移

因为数据库升级，需要将旧版本MySQL数据库中的数据迁移到新版本数据库中。

MySQL服务器升级，需要先停止服务，然后卸载旧版本，并安装新版本的MySQL，这种更新方法很简单。如果想保留旧版本中的用户访问控制信息，则需要备份MySQL的MySQL库，在新版本MySQL安装完成后，重新读入MySQL备份文件中的信息

旧版本和新版本的MySQL可能使用不同的默认字符集，例如MySQL.4.x中大多数使用latin1作为默认字符集，而MySQL5.x的默认字符集为utf8。如果数据库中有中文数据，迁移过程中需要对默认字符集进行修改，不然可能无法正常显示结果。

新版本对旧版本有一定兼容性。从旧版本的MySQL向新版本MySQL迁移时，对于MyISAM引擎的表，可以直接复制数据库文件，也可以用mysqlhotcopy工具、mysqldump工具。对于InnoDB引擎的表一般只能使用mysqldump将数据导出。然后使用MySQL命令导入目标服务器。从新版本向旧版本MySQL迁移数据时要小心，最好使用mysqldump命令导出，然后导入目标数据库中。

不同数据库之间的迁移

不同类型的数据库之间的迁移，是指把MySQL数据库迁移到其他的数据库，例如从MySQL迁移到oracle，从ORACLE迁移到MySQL，从MySQL迁移到SQLSERVER等。

迁移之前，需要了解不同数据库的结构，比较他们的差异。不同数据库定义相同类型的数据的关键字可能不同。
例如：MySQL中日期字段分为DATE 和TIME两种，而ORACLE的日期字段只有DATE。

数据库迁移可以使用一些工具，例如，在Windows系统下，可以使用MyODBC实现MySQL和SQLSERVER之间的迁移（使用SQLSERVER导入导出向导），MySQL官方提供的工具：MYSQL Migration Toolkit也可以在不同数据库间进行数据迁移。

表的导入导出

MySQL数据库可以将数据导出成sql文本文件、xml文件、html文件。同样这些导出文件也可以导入到MySQL数据库中。

用SELECT...INTO OUTFILE导出文本文件

MySQL导出数据时，允许使用包含表定义的SELECT语句进行数据的导出操作，该文件被创建在服务器主机上，因此必须有文件写入权限(FILE权限)，才能使用此语法，基本‘SELECT INTO…OUTFILE’语法如下：

SELECT columnlist FROM Table WHERE condition INTO OUTFILE  'filename' [OPTIONS]

--OPTION选项

FIELDS TERMINATED BY  'VALUE'

FIELDS  [OPTIONALLY] ENCLOSED BY 'VALUE'

FIELDS  ESCAPED BY 'VALUE'

LINES STARTING BY 'VALUE'

LINES TERMINATED BY  'VALUE'

INTO OUTFILE语句的作用就是把前面SELECT语句查询出来的结果导出到名称为“filename”的外部文件中

[OPTIONS]部分为可选参数，[OPTIONS]部分的语法包括FILED和LINES子句，其可能取值为：
1. FIELDS子句：在FIELDS子句中有三个子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。
如果指定了FIELDS子句，则这三个子句中至少要指定一个。
（1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志，默认为“\t”制表符。
（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间，
若加上关键字OPTIONALLY表示所有的值都放在双引号之间，则只有CHAR和VARCHAR等字符数据字段被包括。
（3）ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。

2.LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志，默认值为“\n”。

FIELDS子句和LINES子句都是自选的，但是如果两个都被指定了，FIELDS子句必须位于LINES子句的前面

SELECT INTO…OUTFILE只能在本机执行，如果要在其他服务器上导出数据，则需要使用下面命令来生成文件

MySQL -e "SELECT ...">filename

SELECT INTO…OUTFILE是LOAD DATA INFILE的补语。用于语句的OPTIONS部分的语法包括部分FIELDS子句和LINES子句
这些子句与LOAD DATA INFILE语句同时使用.。

举个栗子：

打开文件，其内容大致如下：

再，将person表中的记录导出到文本文件，使用FIELDS和LINES选项，要求字段之间使用逗号‘，’间隔，所有字段值用双引号括起来，定义转移字符为单引号‘\'‘’:

内容大致如下：

使用MySQLdump导出文本文件

除了使用SELECT...INTO OUTFILE导出文本文件之外，也可以使用mysqldump；mysqldump不仅可以将数据导出包含CREATE、INSERT的sql文件，也可以导出为纯文本文件。

mysqldump创建一个包含创建表的CREATE TABLE语句的tablename.sql文件，和一个包含其数据
的tablename.txt文件。mysqldump导出文本文件的基本语法如下：

mysqldump -T path -u root -p dbname [tables][OPTIONS]

--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

需要注意的是：

1.只有指定了-T参数才可以导出纯文本文件；path表示导出数据的目录，tables为指定要导出的表名称，如果不指定，将导出dbname的所有表；
2.基本上每个选项跟SELECT ..INTO OUTFILE语句中的OPTIONS各个参数设置相同，不同的是，等号后面的value值不要用引号括起来。

使用MySQL命令导出文本文件

MySQL是一个功能丰富的工具命令，使用MySQL还可以在命令行模式下执行SQL指令，将查询结果导入到文本文件中。
相比mysqldump，MySQL工具导出的结果可读性更强;
如果MySQL服务器是单独的机器，用户是在一个client上进行操作，用户要把数据结果导入到client机器上，可以使用mysql -e语句
基本格式如下：

mysql -u root -p --execute="SELECT 语句" dbname >filename.txt

该命令使用--execute 选项，表示执行该选项后面的语句并退出，后面的语句必须用双引号括起来
dbname为要导出的数据库名称，导出的文件中不同列之间使用制表符分隔，第一行包含了字段名称

举个栗子~

使用MySQL命令导出fruits表记录到html文件，语法如下：

mysql -u root -p --html --execute="SELECT * FROM fruits;" part_7 >C:\person4.html

如果要导出为xml文件，那么使用--xml选项，导出到xml文件，语法如下：

mysql -u root -p --xml --execute="SELECT * FROM fruits;" part_7 >C:\person5.xml

使用LOAD DATA INFILE 方式导入文本文件

MySQL允许将数据导出到外部文件，也可以从外部文件导入数据。MySQL提供了一些导入数据的工具，这些工具有：LOAD DATA语句、source命令、mysql命令；LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。其基本语法如下：

LOAD DATA  INFILE 'file_name.txt'  INTO TABLE tbl_name [OPTIONS]  [IGNORE number LINES]

   --OPTION选项

FIELDS TERMINATED BY 'value' 
FIELDS [[OPTIONALLY] ENCLOSED BY 'value' 
FIELDS ESCAPED BY 'value' 
LINES STARTING BY 'value' 
LINES TERMINATED BY 'value'

FIELDS TERMINATED BY ','表示字段之间用逗号分隔
ENCLOSED BY '\''表示每个字段用双引号括起来
ESCAPED BY '\''表示将系统默认的转移字符替换为单引号

LINES STARTING BY ''表示每行数据开头的字符，可以为单个或多个，默认不是有任何字符
LINES TERMINATED BY '\r\n'表示每行以回车换行符结尾，保证每一条记录占一行

[IGNORE number LINES] 选项表示忽略文件开始处的行数，number表示忽略的行数。
基本上格式上的参数跟SELECT...INTO OUTFILE是一样的。

使用MySQLimport命令导入文本文件

MsSQLimport是一个单独的exe，他提供了许多与LOAD DATA INFILE语句相同的功能
大多数选项直接对应LOAD DATA INFILE子句，其基本语法如下：

mysqlimport -u root -p dbname filename.txt [OPTIONS]

--[OPTIONS] 选项

--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value

--ignore-lines=n

MySQLimport支持许多选项，常见选项如下：

· --columns=column_list, -c column_list
该选项采用用逗号分隔的列名作为其值。列名的顺序指示如何匹配数据文件列和表列。

· --compress，-C
压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。

· ---debug[=debug_options]，-# [debug_options]
写调试日志。debug_options字符串通常是'd:t:o,file_name'。

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

· --fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--lines-terminated-by=...
这些选项与LOAD DATA INFILE相应子句的含义相同。

· --force，-f
忽视错误。例如，如果某个文本文件的表不存在，继续处理其它文件。不使用--force，如果表不存在则mysqlimport退出。

· --host=host_name，-h host_name
将数据导入给定主机上的MySQL服务器。默认主机是localhost。

· --ignore，-i
参见--replace选项的描述。

· --ignore-lines=n
忽视数据文件的前n行。

· --local，-L
从本地客户端读入输入文件。

· --lock-tables，-l
处理文本文件前锁定所有表以便写入。这样可以确保所有表在服务器上保持同步。

· --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
显示版本信息并退出。