使用命令行工具进行MySQL逻辑备份及还原

一、前言

对 MySQL 数据库及其表数据进行备份、还原，可以采用很多 MySQL 的数据库管理工具来完成，比如 Navicat、SQLyog，等等。但是个人觉得，作为优秀的程序员，很有必要掌握命令行完成相关的备份及还原。

二、备份

mysqldump 命令

1. mysqldump 命令语法

# 以下为3种用法，更详细的信息参考 mysqldump 的帮助手册
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

2. mysqldump 命令常用选项

1. --databases, -B: 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump 把每个名字都当作为数据库名。
2. --all-databases, -A：备份所有数据库
3. -h, --host: 主机名（IP地址或域名）
4. -P, --port: 端口号，不写默认3306
5. -u, --user: 用户名
6. -p, --password: 密码
7. -d, --no-data: 备份数据库的表时，只备份表结构，不包含表数据
8. -t, --no-create-info : 备份数据库的表时，不备份表结构（建表语句），只包含表数据
9. -X, --xml ：备份的数据格式为xml文件

3. mysqldump 命令使用示例

1. 备份单个数据库的多个表结构和数据

# 假设数据库服务器地址为 192.168.1.10，端口为 3306，数据库用户/密码为 root/123456。现在需要备份数据库 db1 中的表table1、table2，并且将相关内容备份到文件 test.sql 中
mysqldump -h192.168.1.10 -P3306 -uroot -p123456 \
  --result-file=/home/user/test.sql \
  db1 table1 table2

2. 备份多个数据库

# 默认 MySQL 服务器地址为 127.0.0.1，端口为 3306
mysqldump -uroot -p --databases db1 db2 > backup.sql

# 加上 -R 参数以便导出存储过程
mysqldump -uroot -p --databases db1 db2 -R > backup.sql

3. 备份所有数据库

# 默认 MySQL 服务器地址为 127.0.0.1，端口为 3306
mysqldump -uroot -p123456 --all-databases > all_db.sql

注意点：导出时，使用重定向 > 或 --result-file 在多数场景下效果是一样的，但后者可能在跨操作系统兼容性方面更优。

三、还原

mysql 命令、source 命令。还原单个数据库的多个表或者单个数据库时需要指定数据库，而还原多个数据库时不用指定数据库。

1. mysql 命令使用示例

# 还原时，由于数据库大小不同，恢复还原处理时间也不同，请耐心等待处理
mysql -h192.168.1.10 -P3306 -uroot -p123456 db1 < test.sql

# 注1：如果提示还没创建相应数据库（），先执行脚本创建
create database if not exists db1 default character set utf8;

# 注2：如果相应文件 test.sql 为 mysqldump 工具创建的含创建并使用数据库语句的文件，执行时无需指定数据库名
mysql -h192.168.1.10 -P3306 -uroot -p123456 < test.sql

2. source 命令使用示例

# 注意先登录 MySQL 服务器进入命令行界面，才能执行
mysql > create database db1; # 创建数据库[简单]
mysql > create database if not exists db1 default character set utf8; # 或创建数据库[推荐]
mysql > use db1; # 使用已创建的数据库
mysql > set names utf8; # 设置编码(必选，在中文语境下，很多时候编码默认为GBK，应根据实际情况设好编码，否则出现中文乱码问题)
mysql > source test.sql;