MySQL 备份与数据导入导出

库级操作

CREATE DATABASE <数据库名>;
DROP DATABASE <数据库名>;
USE <数据库名>;

导出数据

mysqldump 是 MySQL 自带的转存储工具，产生的 SQL 脚本包含重建数据库所需的 CREATE TABLE、INSERT 等命令。

基本语法：

mysqldump -u username -p password -h hostname database_name > output_file.sql

常见导出场景

# 1、导出整个数据库
mysqldump -u root -p mydatabase > mydatabase_backup.sql

# 2、导出指定表
mysqldump -u root -p mydatabase mytable > mytable_backup.sql

# 3、只导出结构（不含数据）
mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

# 4、导出并压缩
mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

# 5、备份所有数据库
mysqldump -u root -p --all-databases > database_dump.txt

# 6、导出为文本格式（--tab 指定目录，该目录必须可写）
mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl

--all-databases 选项自 MySQL 3.23.12 起可用，适合做整库备份策略。

复制到另一台主机

# 源主机导出
mysqldump -u root -p database_name table_name > dump.txt

# 目标主机导入（需先创建好数据库）
mysql -u root -p database_name < dump.txt

# 两台机器互通时可直接管道导入
mysqldump -u root -p database_name | mysql -h other-host.com database_name

导入数据

方式一：mysql 命令

mysql -u your_username -p -h your_host -P your_port -D your_database

# 实例
mysql -uroot -p**** < runoob.sql

导入前请确认：SQL 文件若含建库语句，数据库需已存在；若含建表语句，表应不存在或为空，避免冲突。

方式二：source 命令

先登录到数据库终端，再执行：

mysql> create database abc;          -- 创建数据库
mysql> use abc;                      -- 使用已创建的数据库
mysql> set names utf8;               -- 设置编码
mysql> source /home/abc/abc.sql      -- 导入备份

好处是不必退出 MySQL 命令行即可导入。

方式三：LOAD DATA

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• 指定 LOCAL 表示从客户端主机按路径读取；不指定则从服务器端按路径读取；
• 默认分隔符是定位符（Tab），行尾标记是换行符；

自定义分隔符与列顺序：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
    -> FIELDS TERMINATED BY ':'
    -> LINES TERMINATED BY '\r\n';

-- 数据文件列顺序为 a,b,c，但表列顺序为 b,c,a
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
    -> INTO TABLE mytbl (b, c, a);

FIELDS 子句必须出现在 LINES 子句之前；FIELDS 的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY、ESCAPED BY）至少一个要指定。

方式四：mysqlimport

mysqlimport 是 LOAD DATA INFILE 的命令行接口，大多数选项与其子句一一对应。

mysqlimport -u root -p --local mytbl dump.txt

# 指定格式
mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt

# 指定列顺序
mysqlimport -u root -p --local --columns=b,c,a mytbl dump.txt

mysqlimport 的常用选项：