mysql 数据导入与导出

用户1560186

发布于 2019-12-18 16:53:32

4.9K0

发布于 2019-12-18 16:53:32

文章被收录于专栏：运维录运维录

开始之前

在日常的运维工作中除了频繁操作基本的SQL语句外，还有另外一种场景，就是数据的导出与导入操作。

举个栗子: 例如逻辑备份需要使用mysqldump导出数据进行逻辑备份数据，或者需要将测试环境的某个表导出、处理后在生产环境使用mysql命令导入数据等。

环境描述

1. MySql 版本 5.7.20

2. 数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

有3个库，其中 test库是我们自己创建的。其余的两个库是 mysql服务器自有的系统库。

3. 数据表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| table1         |
| table2         |
+----------------+

我们在 test库，创建了2个table。

导出数据

1. 导出表结构和数据

# 含有`create`和`lock`、`insert`语句，注意选项 `--opt`
# mysqldump --opt 数据库名 -u用户 -p > my-db.sql
mysqldump --opt test -uroot -p > test-full.sql

2. 导出表结构不导出数据

# 含有 `create table` 语句，注意选项 `-d`
# mysqldump -d 数据库名 -u用户 -p > my-db.sql
mysqldump -d test -u root -p > test-table.sql

3. 导出数据不导出表结构

# 含有 `lock` 和 `insert`语句，注意选项 `-t`
# mysqldump -t 数据库名 -u用户 -p > my-db.sql
mysqldump -t test -u root -p > test-data.sql

4. 导出单个表(结构和数据)

# 与上述示例不同，仅导出数据库中的某个表，而不是库中所有表
# mysqldump --opt 数据库名 表名 -u用户 -p > table.sql
mysqldump --opt test table1 -uroot -p > table-single.sql

5. 导出所有库

# 导出全部的数据，包括mysql自身的系统库
# mysqldump --opt --all-databases -u用户 -p > all.sql
mysqldump --opt --all-databases -uroot -p > all.sql

6. 导出并压缩数据

# 使用管道组合 mysqldump 与 gzip 命令
mysqldump --opt test -uroot -pPasswd | gzip > table-full.gz

# 文件类型
file table-full.gz
table-full.gz: gzip compressed data, # 省略...

导入数据

1. 导入SQL数据

# mysql -h Server_IP -u用户 -p 数据库名 < my-db.sql
mysql -h 8.8.8.8 -uroot -p test < table-full.sql

注意: mysqldump 命令使用--opt选项导出的数据默认包含DROP TABLE语句，例如 "DROP TABLE IF EXISTS table1;" 。

这种情况下请在导入数据前做好数据备份工作，或者根据实际需求删除DROP TABLE语句。

2. 导入压缩数据

# 没有 -h 选项，表示服务器为 localhost
gzip -dc table-full.gz | mysql -uroot -p test

命令帮助

mysqldump --help
mysqldump  Ver 10.13 Distrib 5.7.27, for Linux (x86_64)

# 省略...
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

# 省略...
-h, --host=name
    Connect to host.

-u, --user=name    
    User for login if not current user.

-p, --password[=name]

-d, --no-data
    No row information.

-t, --no-create-info 
    Don't write table creation info.

-A, --all-databases 
    Dump all the databases.
    This will be same as --databases with all databases selected.

--opt Same as --add-drop-table, --add-locks, --create-options,
    --quick, --extended-insert, --lock-tables, --set-charset,
    and --disable-keys. Enabled by default, disable with -skip-opt.
    
-R, --routines
Dump stored routines (functions and procedures).