MySQL数据备份

为了防止无良网站的爬虫抓取文章，特此标识，转载请注明文章出处。LaplaceDemon/SJQ。

http://www.cnblogs.com/shijiaqi1066/p/3830007.html

 

 

 

1 mysqldump命令备份

mysqldump命令备份将数据库中的数据备份成一个文本文件。mysqldump命令备份的文件后缀名一般为".sql"，其他后缀名也可以。

mysqldump命令通过表结构生成create table语句；将表中数据生成insert语句。

注：mysqldump命令实在shell环境下使用的。非数据库环境下的命令。

 

1.1 备份数据表

备份表生成的备份脚本是不包含create table语句的。

基本语法：

mysqldump -u 用户名 -p [密码] 数据库 表1 表2 ... > 脚本名称

例：root用户备份test数据库下的student表。

mysqldump -u root -p test student > D:\student.sql

 

 

1.2 备份数据库

使用--databases选项用于备份数据库。

基本语法：

mysqldump -u用户名 -p[密码] --databases 库1 库2 ... > 脚本名称

例：使用root用户备份test数据库和mysql数据库。

mysqldump -u root -p --databases test mysql  >D:\backup.sql

 

 

1.3 备份所有数据库

使用--all-databases选项用于备份所有数据库。

基本语法：

mysqldump -u 用户名 -p [密码] --all-databases ... > 脚本名称

例：使用root用户备份当前连接的所有数据库。

mysqldump -u root -p --all-databases  > D:\all.sql

 

 

1.4 数据还原

使用mysql命令来还原备份的数据。

基本语法：

mysql -u 用户名 -p 密码 [数据库] < 备份脚本

 

 

1.5 通过MySQL存储文件备份

对于MyISAM引擎的数据库，可以通过复制MySQL的存储文件来实现备份数据库。使用这种方式备份数据库请保证数据库主版本号相同。

在Linux使用这种方式备份数据需要注意文件权限。

 

 

1.6 读锁

在备份期间，不允许写数据库操作。此时应当将数据库读锁，禁止写入操作。

只需要为mysqldump命令添加 -l 选项，即可。

 

1.7 刷新日志 

若使用了日志，特别是二进制日志。在备份之后，最好重新生成最新的二进制数据文件。使用-F可以使命令自动刷新日志。

例：备份test数据库

mysqldump -uroot -p test -l -F > temp.sql

 

 

 

 

2 数据库迁移

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

数据库迁移大致分为三类：

• MySQL相同版本的数据库之间迁移。
• 迁移到MySQL其他版本的数据库中。
• 迁移到其他类型的数据库中。

 

对于相同版本的MySQL数据库，使用MySQL备份和还原技术即可完成数据库迁移。

例：使用mysqldump命令备份并使用mysql命令在另一个数据库中还原。

mysqldump -h host1 -u user1 -p password1 -all-database |
mysql -h host2 -u user2 -p password2

 

 

对于不同的数据库，由于SQL标准定义的区别，使用如上类似方法可能会导致错误。可以选择使用工具完成这些工作。

 

 

 

3 表的导入和导出

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

语法：

SELECT [列名] FROM 表 [WHERE 语句] INTO OUTFILE '目标文件' [选项];

说明：

SELECT子句用于查询数据。

INTO OUTFILE用于导出数据。

目标文件为导出数据的备份文件。

选项：

字段选项：

fields terminated by ’字符串’ ：设置字段分隔符，默认为’\t’。

fields enclosed by ’字符’ ：设置括起字段的字符。默认不使用任何字符。

fields optionally enclosed by ’字符’ ：设置字符串括上CHAR，VARCHAR，TEXT等字符型字段。默认不使用任何字符。

fields escaped by ’字符’ ：设置转义字符，默认值’/’。

行选项：

lines starting by ‘字符串’ ：设置每行开头的字符，默认不使用任何字符。

lines terminated by ‘字符串’  ：设置每行结束的字符，默认不使用任何字符。

 

例：导出test库下student表的记录。其中，字段之间用“,”隔开，字符型数据用双引号括起来。每条记录以“>”开头。

SELECT * FROM .student INTO OUTFILE 'D:\student1.txt'
FIELDS TERMINATED BY '\,'
       OPTIONALLY ENCLOSED BY '\'
LINES STARTING BY '\>'
       TERMINATED BY '\r\n';

 

 

3.2 用mysqldump命令导出文本文件

基本语法：

mysqld -u 用户名 -p 密码 -T 目标目录 数据库 表 [选项]

说明：

-T选项的参数是导出文件的路径。

选项：

--fields-terminated-by=字符串 ：设置字段分隔符。

--fields-enclosed-by=字符 ：设置字符串括上字段的值。

--fields-optionally-enclosed-by=字符 ：设置字符串括上字段的值。只能用于CHAR，VERCHAR和TEXT类型的字段。

--fields-escaped-by=字符 ：设置转义字符。

--lines-terminated-by=字符串 ：行记录分隔符。

 

例：导出test库下student表的记录。其中字段之间用“,”隔开，字符型数据用双引号括起来。

mysqldump -u root -p111 -T d:\test student
        --fields-terminated-by =,
        --fields-optionally-enclosed-by =\"

 

 

用mysqldump命令导出XML格式的文件

基本语法：

mysqldump -u 用户名 -p 密码 --xml  数据库 表 > 目标文件

说明：--xml表示导出为xml格式的文件，也可以使用-X代替--xml。

例：导出test库下student表的记录为xml文件。

mysqldump -u root -p111 --xml test student > d:\name.xml;

 

 

3.3 用mysql命令导出文本文件

mysql命令可以用于登录MySQL服务器，备份数据，也可用于导出文本文件。

基本语法：

mysql -u 用户 -p 密码 -e "select语句" 数据库 > 目标文件

说明：-e选项的参数为select语句，用于查找数据。

例：导出test数据库下的student表

mysql -u root -p111 -e "SELECT * FROM student" test > D:/student2.txt

 

 

用mysql命令导出XML与HTML格式的文件

导出xml基本语法：

mysql -u 用户名 -p 密码 --xml -e "select语句" 数据库 表 > 目标文件

说明：--xml表示导出为xml格式的文件，也可以使用-X代替--xml。

 

导出html基本语法：

mysql -u 用户名 -p 密码 --html -e "select语句" 数据库 表 > 目标文件

说明：--html表示导出为html格式的文件，也可以使用-H代替--html。

 

 

 

 

 

更多mysqldump的选项请参考官方文档，或使用搜索引擎查询。

 

 

 

 

为了防止无良网站的爬虫抓取文章，特此标识，转载请注明文章出处。LaplaceDemon/SJQ。

http://www.cnblogs.com/shijiaqi1066/p/3830007.html