命令行中导入导出网站MySQL备份

网站数据库的备份和迁移是运维中很常见的工作。大多数虚拟主机、VPS管理面板自带了 PhpMyAdmin，用这个 Web 客户端备份 MySQL 的数据简单又方便。或者也可以用诸如 Navicat 这类图形化客户端远程操作。

但如今的建站环境多是在 VPS 下用 LNMP 之类的环境直接搭建的，出于安全等原因很多时候会禁用 PhpMyAdmin，而且也没法用客户端连接服务器的 MySQL，这时候就只能在 Shell 命令行下操作了。

这里对用到的命令做了相对完整的整理，并补充了迁移数据中可能会遇到的一些相关问题（所有命令在 Windows 和 Linux 下通用）。

查看数据库表

在执行导入导出前，需要知道数据库的名字。方法有很多，例如 PHP、Python 程序一般都会保存数据库信息在配置文件里，打开就能看到。

如果实在不清楚话，也可以用 MySQL 命令行登录后查看所有的数据库。

连接数据库命令：

mysql -h 连接地址 -P 端口号 -u 用户名 -p密码

-h 后指定要连接的IP或URL

-P 指定连接的端口号，MySQL 默认的端口号是3306（注意大写P和小写p是两个不同参数）

-u 指定登录数据库的用户名（-u 和用户名之间加不加空格都行）；

-p 表示使用密码登录，直接跟随密码（不加空格），如果 -p 后没有密码，回车后会提示输入；

如果是连接到本地的默认端口 MySQL，也可以简写成如下命令

mysql -u 用户名 -p密码

输入命令回车后，就进入到 MySQL 命令行控制台界面了，行开头会变成“mysql>”。

使用以下命令查看数据库和表名（-- 开头的表示注释）

-- 列出所有数据库;
show databases;
-- 根据列出的结果，选择一个数据库;
use 数据库名;
-- 显示选择的数据库中所有的表名;
show tables;
-- 查看指定表的结构;
desc 表名; -- 或 
show columns from 表名;
-- 退出 MySQL 命令行（返回到操作系统的终端）;
quit; -- 或 exit;

这样就能知道要导出的数据库名以及表名了。

导出备份

导出备份可以用 mysqldump 命令，基础用法如下：

mysqldump -u用户名 -p 数据库名 [表名] > backup.sql

直接在命令行中执行，不需要登录到MySQL控制台中。

回车后会提示输入密码，再次按回车确定。这会导出表的结构和内容（表中的数据），保存到 backup.sql 中。

其中的参数解释：

-u 指定登录数据库的用户名（-u 和用户名之间加不加空格都行）；

-p 表示使用密码登录，回车后会提示输入密码，或者也可以在 -p 后直接跟随密码（不能加空格）；

> 这是Shell（控制台）的重定向符，表示把内容写入到后面的文件（backup.sql）中，一般用 .sql 结尾（表示是数据库的文件），如果写的是相对路径，则文件会保存在当前命令行的工作路径下；

命令中的“表名”为可选字段，多个用空格隔开，如果不加则备份整个数据库。

不过请注意，用这个方式导出的文件中不会包含建立数据库的语句。也就是说，再导入数据时，先要手动在 MySQL 中创建好数据库，然后再导入数据。

对于 WordPress 网站，习惯上一个网站使用一个单独的数据库。导入导出的数据库名可能也不一定一致，所以一般直接导出一个数据库即可。

命令还有以下常见用法（这些方式导出的文件中会包含创建数据库的语句）：

备份多个数据库

mysqldump -u用户名 -p --databases 数据库1 数据库2 > backup.sql

或者导出全部的数据库

mysqldump -u用户名 -p --all-databases > backup.sql

导入备份

命令

导入备份时，在命令行中使用以下命令

mysql -u用户名 -p密码 < backup.sql

这会把前面导出的 backup.sql 数据重新导回数据库中。

如果要指定导入的数据库名，且 SQL 文件中不包含创建数据库的语句（若数据库不存在需要先手动创建）。

mysql -u用户名 -p密码 数据库名< backup.sql

新建库

如果数据库不存在，可以用以下命令在 Shell 中创建数据库：

# 连接登录MySQL命令行
mysql -u 用户名 -p密码
-- 在MySQL命令行里运行
CREATE DATABASE `数据库名`;
-- 返回Shell
quit

数据覆盖

注意，默认情况下，如果当前数据库中已有同名的表，则这张表会被文件中的内容覆盖。

打开导出的文件中可以看到以下 SQL 语句：

DROP TABLE IF EXISTS `test_table`;

也就是说，如果现在数据库中已经有了一张叫 test_table 的表，MySQL会先删除此表，然后再按照备份的文件创建表结构并导入内容。

如果要在导入时保留已有的数据，可以在导出备份文件时加上参数 --skip-add-drop-table。

例如：

mysqldump -u用户名 -p 数据库名 --skip-add-drop-table > backup.sql

这样导出的备份文件中就不会包含删表语句了，导入时正常导入即可。

不同版本之间备份文件的兼容性

到这里已经解决了如何导入和导出的问题。但是还剩下一个应该被了解的事情——跨版本兼容性（加这句话是因为多数人会习惯性忽略）。

有时候会遇到在不同版本的 MySQL 之间迁移数据的情况，例如导出的是 MySQL 5.7，要导入的是 MySQL 5.5。

实际上 MySQL 也考虑到了版本兼容问题。先说结论：即使跨版本迁移数据，大部分情况下也可以什么都不做。

打开导出的备份文件，能找到类似于以下的内容：

-- 省略前面
DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_table` (
-- 省略后续

在SQL中，-- 和 \* 都是注释的开头，而类似 /*!40101 这样形式的开头的注释还有特殊含义。

/*! 后的 40101 代表了 MySQL 版本号 4.1.1，这个开头表示注释中的内容从 MySQL 4.1.1 版本开始提供支持。

在导入数据时，如果执行命令的 MySQL 高于 4.1.1，则 /*!40101 和 */ 之间的内容被当作 SQL 语句来执行。如果版本低于 4.1.1 则被当作注释忽略。

所以这样不会导致低版本 MySQL 导入高版本备份的文件时报错，除了高版本的一些特性不支持外，数据还是会被导入的。反之，低版本导出高版本导入也没问题。