数据备份与还原-16(共22讲)

目录：

一、数据备份

二、数据还原

三、数据库迁移

四、导出和导入文本文件

一、数据备份：

1,mysqldump 

工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个create语句，然后将表中的所有记录转换成一条insert语句。生成

的.sql文件其实是包含了create语句来恢复表结构、insert语句恢复表数据

使用方法：可以实现备份一个库、多个库、所有库------

mysqldump -u root -p‘123’ dbname1 table1  table2 ... > /home/wuxy/table1.sql

mysqldump -u root -p‘123’ --databases dbname1 dbname2  > /home/wuxy/dbbacpup.sql

mysqldump -u root -p'123' --all-databases > /home/wuxy/all.sql

2,直接复制mysql的数据文件（需停止mysql服务，否则造成数据不一致，但实际情况一般是不允许停止mysql服务的），对Innodb存储引擎

的表不适用，对MyISAM存储引擎的表，这样的备份和还原还是很方便的。但是还原时最好还是相同版本的Mysql数据库，否则可能会存在文

件类型不同的情况。

3.mysqlhotcopy

如果备份时不能停止mysql服务器，可以采用mysqlhotcopy工具，这种备份方式比mysqldump命令快。

工作原理：mysqlhotcopy是一个perl脚本，主要在linux系统下使用，mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES 和cp来进行快速

备份。其工作原理是：先将需要备份的数据库加上一个读操作锁，然后用FLUSH TABLES将内存中的数据库写回到硬盘中的数据库中，最后把

需要备份的数据库文件复制到目标目录

使用方法：

mysqlhotcopy [options] dbname1 dbname2  backDir/

可以同时备份多个数据库

help 查看参数

二、数据还原：

1、

如果使用mysqldump命令将数据库中的数据备份成一个文本文件，通常这个文件的后缀是sql，需要还原的时候，可以使用mysql命令来还原

备份的数据。

工作原理：备份文件中通常包含create语句和insert语句。mysql命令可以执行备份文件中的create语句和insert语句。通过create语句来

创建数据库和表。通过insert语句来插入备份的数据

使用方法：mysql -u root -p'123' [daname] < backup.sql

2、如果使用的是直接复制数据的备份方法，那么可以直接将备份数据复制到mysql的数据目录下。通过这种方式还原时，必须保证两个

Mysql数据库的主版本号相同，因为只有Mysql数据库的主版本号相同时，才能保证这两个Mysql数据库的文件类型是相同的。而且这种方式

对MyISAM类型的表比较有效，对应InnoDB类型的表则不可用，因为InnoDB表的表空间不能直接复制。

linux操作系统下，数据库目录通常在/var/lib/mysql、/usr/local/mysql/data或者/usr/local/mysql/var/这3个目录下。上述位置只是数

据库目录最常用的位置，具体位置根据安装时设定的位置而定。

三、数据库迁移：

数据库迁移就是指将数据库从一个系统移动到另一个系统上。

大致可以分为3类：

1、相同版本的Mysql数据库之间迁移

2、迁移到其他版本的Mysql数据库中

3、迁移到其他类型的数据库中

详细而言：

1、相同版本的Mysql数据库之间的迁移就是在主版本相同的Mysql数据库之间进行数据库移动。这种迁移的方式最容易实现。

迁移的原因比如有：换了新的服务器、或者是装了新的操作系统。

因为迁移后的Mysql数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移（需停止Mysql服务，否则会造成数据不一致）

，但是只有数据库表都是MyISAM类型的才能使用这种方式。也可以通过mysqldump备份，然后通过mysql命令还原。

2，不同版本的Mysql数据库之间进行数据迁移通常是因为Mysql升级的原因，例如：5.0版本推出以后，改进了4.0版本的很多缺陷，因此需

要升级。高版本的mysql数据库通常会兼容低版本，因此可以从低版本的mysql数据库迁移到高版本的mysql数据库。但是高版本的mysql数据

库很难迁移到低版本的mysql数据库，因为高版本的mysql数据库可能有一些新的特性，这些新的特性是低版本mysql数据库所不具有的。

MyISAM类型的表可以直接复制，也可以使用mysqlhotcopy工具

但是InnoDB类型的表不可以使用这两种方法。最常用的办法是使用mysqldump命令进行备份，然后通过mysql命令将备份文件还原到目标

MySQL数据库。

数据库迁移时要特别小心，最好是使用mysqldump命令来进行备份，避免迁移时造成数据丢失。

3，不同数据库之间迁移是指从其他类型的数据库迁移到Mysql数据库，或者从mysql数据库迁移到其他类型的数据库。例如：某个网站原来

使用的是oracle数据库，因为运营成本太高等原因，希望该用户mysql数据库。或者某个管理系统原来使用的是mysql数据库，希望改用

oracle数据库，这样的不同数据库之间的迁移也经常会发生，但是这种迁移没有普通使用的解决方法。

   mysql以外的数据库也有类似于mysqldump这样的备份工具，可以将mysql数据库中的文件备份成sql文件或者文本。但是，因为不同数据

库厂商没有完全按照SQL标准设计数据库，这就造成了不同数据库使用的sql语句的差异。例如：微软的sql server软件使用的是T-SQL语言

，T-SQL中包含了非标准的SQL语句，这就造成了SQL server和mysql的sql语句不能兼容。

注意：在不同数据库之间的迁移可能会使用一些其他的一些工具，具体的可以百度。

四、表的导出和导入：

在数据库的日常维护中，经常需要进行表的导出和导入操作。mysql数据库中的表可以导出成文本文件、xml文件、或者html文件。相应的文

本文件也可以导入mysql数据库中。

1，用select ... into outfile 导出文本文件

使用方法：select [列名] from table [where语句] into outfile '目标文件' [option];

option常见如下：

fields terminated by '字符串'   ##设置每个字段之间用什么隔开，默认是制表符

fields enclosed by '字符串'     ##用什么字符来括上字段的值，默认是不使用任何字符的

fields optionally enclosed by '字符串'  ##字符型数据（char、varchar、txt类型的字段）用什么引起来，默认是不使用任何字符的

fields escaped by '字符串'      ##设置转移字符，默认的是斜杠

lines starting by '字符串'  ##设置每一行的起始字符，默认是没有任何字符的

lines terminated by '字符串' ##设置每一行的结尾字符，比如：'\r\n'（换行回车），默认的情况下是'\n'(回车)

示例：

select * from test.student into outfile '/home/data/student.txt' fields terminated by '\.' fields optionally enclosed by 

'\"' lines starting by '\>' lines terminated by '\r\n';

2，用mysqldump命令导出文本文件

mysqldump命令可以备份数据库中的数据，但是备份时是在备份文件中保存了create语句和insert语句。

*2.1不仅如此，mysqldump命令还可以导出文本文件。

使用方法如下：

mysqldump -u root -p'123' -T 目标目录 dbname table [option];

option

--fields-terminated-by=字符串   ##设置字符串作为字段的分隔符，默认是制表符（\t）

--fileds-enclosed-by=字符       ##设置用什么字符来括上字段的值

--fields-optionally-enclosed-by=字符  ##字符型数据（char、varchar、txt类型的字段）用什么引起来，默认是不使用任何字符的

--fields-escaped-by=字符        ##设置转移字符，默认的是斜杠

--lines=terminated-by=字符串    ##设置每一行的结尾字符，比如：'\r\n'（换行回车），默认的情况下是'\n'(回车)

mysqldump -u root -p'123' -T /home/data/ test student "--fields-terminater-by=","--fields-optionally-enclosed-by='"'

*2.2 mysqldump命令导出xml文件

mysqldump -u root -p'123' --xml test student > /home/data/student.xml

##注意这个语句不能以分号结束

3 用mysql命令导出文本文件

mysql命令可以用来登录mysql服务器，也可以用来还原备份文件，同时，mysql命令也可以导出文本文件。

语法形式如下：

mysql -u root -p'123' -e "select 语句" dbname > /home/data/name.txt

mysql -u root -p'123' -e "select * from student" tets > /home/data/student2.txt ###与select 查询的数据一模一样

同样用mysql命令也可以导出xml文件和html文件

mysql -u root -p'123' --html -e "select * from department" test > /home/data/department.html

4 用load data infile 方式导入文本文件（这个命令是在数据库中执行的）

mysql中，可以使用load data infile 命令将文本文件导入到mysql数据库中。

基本语法形式如下：

load data [local] infile filename into table tablename [option]

load data infile '/home/data/student2.txt' into table student fields terminated by ',' optionally enclosed by '"';

注意：

如果文本文件中字段之间是用逗号分隔的，那么导入数据的时候也要指定文本文件的分隔符

同理，如果字符型数据（cahr、varchar、txt）用双引号括起来了，那么导入的时候也要指定

5 用mysqlimport命令导入文本文件

mysql中可以使用mysqlimport命令将文本文件导入到mysql数据库中。

mysqlimport -u root -p'123' [--local] dbname file [option]

option选项和mysqldump的选项一样

mysqlimport -u root -p'123' test '/home/data/student.txt' "--fields-terminated-by-," "00fields-optionally-enclosed-by-''"

常见问题及解答：

1.如何选择备份数据库的方法？

答：根据数据库的存储引擎的类型不同备份表的方法也不一样。对应MyISAM类型的表，可以直接复制Mysql数据文件夹或者使用mysqlhotcopy命令进行备份。复制mysql数据文件夹需要将mysql服务停止，否则可能会出现异常。而mysqlhotcopy命令则不需要停止mysql服务。mysqldump命令是最安全的备份方法，它既适合于MyISAM类型的表，也适用于InnoDB类型的表

2.如何升级mysql数据库？

答：

（1）先使用mysqldump命令备份mysql数据库中的数据，这样做的目的是为了避免误操作引起mysql数据库中的数据丢失。

（2）停止mysql服务，可以直接终止mysql服务的进程，但是最好还是使用安全的方法停止mysql服务，这样可以避免缓冲中的数据丢失。

（3）卸载旧版本的mysql数据库，通常情况下，卸载mysql数据库软件时，系统会继续保留mysql数据库中的数据文件

（4）安装新版本的mysql数据库，并进行相应的配置。

（5）启动mysql服务，登录mysql数据库查询数据是否完整，如果数据不完整，使用之前备份的数据进行恢复。

小结：本节介绍了备份数据库、还原数据库、数据库迁移、导出表和导入表的内容。备份数据库和还原数据库是本节的重点内容。在实际应用中，通常使用mysqldump命令备份数据库，使用mysql命令还原数据库。数据库迁移、导出表和导入表是本节的难点，数据库迁移需要考虑数据库的兼容性问题，最好是在相同版本的mysql数据库之间迁移。导出表和导入表的方法比较多，希望熟悉且多加练习。

追加内容：

一、MyISAM引擎备份和InnoDB引擎备份

由于MyISAM引擎为表级锁，因此在备份时需要防止在备份期间数据写入而导致不一致，所以，在备份时使用--lock-all-tables加上读锁。

mysqldump -A -F -B --lock-all-tables　| gzip /data/backiup/$(date +%F).tar.gz

由于InnoDB引擎为行级锁，因此备份时可以不对数据库加锁的操作，可以加选项--single-transaction进行备份（Option automatically turns off --lock-tables，所以不会锁表），此参数仅对innodb有效，可以获得一致性备份。

mysqldump -A -F -B --single-transaction | gzip /data/backup/$(date +%F).tar.gz

备注：

1，--single-transaction这个参数仅适用于InnoDB引擎 --master-data=2

2，--single-transaction and --lock-all-tables是互斥的，不可以同时使用

      本文转自Tenderrain 51CTO博客，原文链接：http://blog.51cto.com/tenderrain/1602091，如需转载请自行联系原作者