mysql备份还原库命令有哪些

共计 6271 个字符，预计需要花费 16 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 mysql 备份还原库命令有哪些的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。

关于 mysql 数据库备份与还原的方法，这里首先讲到备份的工具：mysqlhotcopy，使用 mysqlhotcopy 工具可进行快速备份，然后数据还原，使用 MySQL 命令还原；最后需要导出数据库表。详细的介绍还得阅读本文。

1. 数据备份

定期的备份数据库，使得在意外情况发生的时候，尽量的减少损失。

1. 使用 mysqldump 命令备份

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

【使用 mysqldump 备份单个数据库中】

mysqldump -u user -h host -p password dbname filename.sql

【使用 mysqldump 备份数据库中的指定表】

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

【使用 mysqldump 备份多个数据库】

mysqldump -u user -h host -p password --databases[dbname,[dbname…]] filename.sql

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

【备份系统中所有的数据库】

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

提示：如果在服务器上进行备份，并且表均为 MyISAM，应考虑使用 mysqlhotcopy，因为可以更快的进行备份和恢复；

2. 直接复制整个数据库目录

因为 MySQL 表保存为文件方式，所以可以直接复制 MySQL 数据库的存储目录以及文件进行备份。

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

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

3. 使用 mysqlhotcopy 工具快速备份

mysqlhotcopy 是一个 Perl 脚本。

只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 和 ARCHIVE 类型的表；

2. 数据还原 1. 使用 MySQL 命令还原

mysql -u username -p [dbname] filename.sql

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

如果已经登录到 MySQL 服务器，还可以使用 source 命令导入 SQL 文件。

source filename

提示：执行 source 命令之前，必须使用 use 语句选择数据库。不然，恢复过程中会出现错误；

2. 直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份的文件到 MySQL 数据目录下实现还原。

通过该方式还原的时候，必须保持备份数据库和待还原的数据库服务器的主版本号相同。而且这种方式只是对 MyISAM 引擎的表有效，对于 InnoDB 引擎的表不可用；

执行还原以前关闭 MySQL 服务，将备份的文件或者目录覆盖 MySQL 的 data 目录，启动 MySQL 服务。

对于 Linux/Unix 操作系统来讲，复制完文件需要将文件的用户或者用户组更改为 mysql 运行的用户和组，通常用户是 mysql，组也是 mysql；

3.mysqlhotcopy 快速恢复

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

如果以根用户执行该操作，必须指定数据库文件的所有者

chown -R mysql.mysql /var/lib/mysql/dbname
cp -R /usr/backup/test usr/local/mysql/data

执行完该语句，重启服务器，MySQL 将恢复到备份状态

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

3. 数据库迁移

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

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

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

举例：

将 www.abc.com 主机上的 MySQL 数据库全部迁移到 www.bcd.com 主机上：

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

说明：

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

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

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

旧版本与新版本的字符集不同时，迁移过程需要对默认字符集进行修改，不然可能无法正常显示结果；

对于 InnoDB 引擎的表，一般只能使用 mysqldump 工具将数据导出，然后使用 mysql 命令导入到目标服务器上。

从新版本向旧版本迁移数据的时候，需要特别的小心，最好使用 mysqldump 命令导出，然后导入目标数据库中；

3. 不同数据库之间的迁移

数据库迁移可以使用一些工具，例如在 Windows 系统下，可以使用 MyODBC 实现 MySQL 和 SQL Server 之间的迁移。

MySQL 官方提供的工具 MySQL Migration Toolkit 也可以实现在不同数据库间进行数据迁移；

4. 表的导出和导入

MySQL 数据库中的数据可以导出成 SQL 文本文件、xml 文件或者 HTML 文件。

1. 使用 SELECT…INTO OUTFILE 导出文本文件

MySQL 数据库导出数据的时候，允许使用包含导出定义的 SELECT 语句进行数据导出操作。该文件被创建到服务器主机上，因此必须拥有文件写入权限（FILE 权限），才能使用此语法。

语法格式：

SELECT columnlist FORM table WHERE condition INTO OUTFILE‘filename’[OPTIONS]

[OPTIONS] 选项：

● FIELDS TERMINATED BY‘value’

● FIELDS [OPTIONALLY] ENCLOSED BY‘value’

● FIELDS ESCAPED BY‘value’

● LINES STARTING BY‘value’

● LINES TERMINATED BY‘value’

说明：filename 不能是一个已经存在的文件；

OPTIONS 部分语法包括 FIELDS 部分的语法和 LINES 子句，其可能的取值有：

FIELDS TERMINATED BY‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY‘value’：

设置字段的包围字符，只能为单个字符，如果使用了 OPTIONALLY，则只有 CHAR 和 VERCHAR 等字符数据字段被包括；

FIELDS ESCAPED BY‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

注意：FIELDS 和 LINES 两个子句是可选的，如果同时指定，FIELDS 必须位于 LINES 的前面；

2. 使用 mysqldump 命令导出文本文件

mysqldump 工具不仅可以将数据导出为包含 CREATE、INSERT 的 SQL 文件，也可以导出为纯文本文件；

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

–OPTIONS 选项：

● –fields-terminated-by=value

● –fields-enclosed-by=value

● –fields-optionally-enclosed-by=value

● –fields-escaped-by=value

● –lines-terminated-end-by=value

说明：只有指定了 T 参数才可以导出为纯文本文件；path 表示导出数据的目录；tables 为指定要导出表的名称；如果不指定，将导出数据库 dbname 中的所有的表；

[options] 取值：

● –fields-terminated-by=value：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

● –fields-enclosed-by=value：

设置字段的包围字符；

● –fields-optionally-enclosed-by=value：

设置字段的包围字符，只能为单个字符，如果使用了 OPTIONALLY，则只有 CHAR 和 VERCHAR 等字符数据字段被包括；

● –fields-escaped-by=value：

控制如何写入或者读取特殊字符，只能为单个字符，及设置转义字符，默认为反斜线“\”;

● –lines-terminated-end-by=value：

设置每行数据结尾的字符，可以为单个或者多个字符，默认值为‘\n’

3. 使用 MySQL 命令导出文本文件

mysql 是一个功能丰富的工具命令，使用 MySQL 还可以在命令行模式下执行 SQL 指令将查询结果导入到文本文件中。相比 mysqldump，MySQL 工具导出的结果可读性更强。

如果 MySQL 服务器是一个单独的机器，用户是在一个 client 上进行操作，用户要把数据导入到 client 机器上，可以使用 mysql - e 语句；

使用 MySQL 导出数据文本文件语句的基本格式如下：

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

使用 MySQL 命令还可以指定查询结果的显示格式：

如果某行记录字段很多，可能一行不能完全显示，可以使用 –vartical 参数，将每条记录分为多行显示；

【将查询结果导出到 HTML 文件中】

mysql -u root -p --html --execute=”SELECT 语句”dbname filename.html

【将查询结果导出到 xml 文件中】

mysql -u root -p --xml --execute=”SELECT 语句”dbname filename.xml

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

LOAD DATA INFILE 语句用于高速的从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。

LOAD DATA INFILE‘路径 + 文件名.txt’INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

注意：如果导出的.txt 文件中指定了一些特殊的字符，因此还原语句中也要指定这些字符，以确保还原之后数据的完整性和正确性；

–OPTIONS 选项

● FIELDS TERMINATED BY‘value’

● FIELDS [OPTIONALLY] ENCLOSED BY‘value’

● FIELDS ESCAPED BY‘value’

● LINES STARTING BY‘value’

● LINES TERMINATED BY‘value’

可以看到 LOAD DATA 语句中，关键字 INFILE 后面的 filename 文件为导入数据的来源；

tablename 表示待导入的数据表名称；

OPTIONS 部分语法包括 FIELDS 部分的语法和 LINES 子句，其可能的取值有：

FIELDS TERMINATED BY‘value’：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY‘value’：

设置字段的包围字符，只能为单个字符，如果使用了 OPTIONALLY，则只有 CHAR 和 VERCHAR 等字符数据字段被包括；

FIELDS ESCAPED BY‘value’：

设置如何写入或者读取特殊字符，只能为单个字符，即设置转义字符，默认值为“\”

LINES STARTING BY‘value’：

设置每行数据开始字符，可以为单个或者多个，默认不使用任何字符

LINES TERMINATED BY‘value’：

设置每行数据结尾的字符 可以为单个或者多个字符，默认值为‘\n’;

[IGNORE number LINES]

选项表示忽略文件开始处的行数，number 表示忽略的行数。执行 LOAD DATA 语句需要 FILE 权限；

5. 使用 mysqlimport 命令导入文本文件

使用 mysqlimport 命令可以导入文本文件，并且不需要登录 MySQL 客户端。

使用 mysqlimport 语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport 命令的基本语法如下：

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

[options] 取值：

● –fields-terminated-by=value：

设置字段之间的分隔字符，可以为单个或者多个字符，默认情况下为制表符‘\t’

● –fields-enclosed-by=value：

设置字段的包围字符；

● –fields-optionally-enclosed-by=value：

设置字段的包围字符，只能为单个字符，如果使用了 OPTIONALLY，则只有 CHAR 和 VERCHAR 等字符数据字段被包括；

● –fields-escaped-by=value：

控制如何写入或者读取特殊字符，只能为单个字符，及设置转义字符，默认为反斜线“\”;

● –lines-terminated-end-by=value：

设置每行数据结尾的字符，可以为单个或者多个字符，默认值为‘\n’

● –ignore-lines=n

忽略数据文件的前 n 行；

注意：mysqlimport 命令不能指定导入数据库的表名称，数据表的名称由导入文件名称决定，即文件名作为表名，导入数据之前该表必须存在。

感谢各位的阅读！关于 mysql 备份还原库命令有哪些就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！