跳转至

Mysql 数据导出与导入

2014-01-08 by dongnan

开始之前

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

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

环境描述

版本 5.4.20

数据库

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

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

数据表

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

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

导出数据

导出表结构和数据

含有create和lock、insert语句，注意选项 --opt

# mysqldump --opt 数据库名 -u用户 -p > my-db.sql
mysqldump --opt test -uroot -p > test-full.sql

导出表结构不导出数据

含有 create table 语句，注意选项 -d

# mysqldump -d 数据库名 -u用户 -p > my-db.sql
mysqldump -d test -u root -p > test-table.sql

导出数据不导出表结构

含有 lock 和 insert语句，注意选项 -t

# mysqldump -t 数据库名 -u用户 -p > my-db.sql
mysqldump -t test -u root -p > test-data.sql

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

与上述示例不同，仅导出数据库中的某个表，而不是库中所有表

# mysqldump --opt 数据库名 表名 -u用户 -p > table.sql
mysqldump --opt test table1 -uroot -p > table-single.sql

导出所有库

导出全部的数据，包括mysql自身的系统库，合成一个文件

# mysqldump --opt --all-databases -u用户 -p > all.sql
mysqldump --opt --all-databases -uroot -p > all.sql

导出并压缩数据

使用管道组合 mysqldump 与 gzip 命令

mysqldump --opt test -uroot -pPasswd | gzip > table-compress.gz

导入数据

导入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语句。

压缩的数据

先使用gzip命令解压缩然后使用管道传递给mysql

# mysql -u用户 -p 数据库名 (从管道获得数据) 
gzip -dc table-compress.gz | mysql -uroot -p test

命令帮助

mysqldump --help
# 省略...
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).

小结

最后来总结下文章中的知识点

  • mysqldump 与 mysql 两个命令日常工作使用频繁，建议掌握使用方法。
  • mysqldump 导出数据如果很大，可以使用shll管道与gzip命令压缩。
  • 慎用--all-databases选项备份全部数据库，建议独立备份每个库。
  • 对于含有储过程和函数、触发器，还要加上--routines 选项。
回到页面顶部