小谈 MySQL 第五话·备份与恢复之 mysqldump_mysql 还原时 mysqldump: couldn't find table: "book_re

目录

一、mysqldump 简介

二、mysqldump 备份命令

1、命令格式

2、选项解析

3、常用选项说明

4、备份实例

① 导出数据库

② 导出数据库中的数据表

5、恢复操作

① 系统命令行

② source 方法

三、生产环境不同引擎备份命令参数

1、mysqldump 的关键参数

2、不同引擎备份命令参数用法

① MyISAM 存储引擎

② InnoDB 存储引擎

③ 生产环境命令参考

一、mysqldump 简介

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出来的数据转换成对应的 insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应数据还原。

二、mysqldump 备份命令

1、命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名
或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
或
mysqldump [选项] --all-databases [选项]  > 脚本名

2、选项解析

-A --all-databases：导出全部数据库
-Y --all-tablespaces：导出全部表空间
-y --no-tablespaces：不导出任何表空间信息
--add-drop-database：每个数据库创建之前添加 drop 数据库语句。
--add-drop-table：每个数据表创建之前添加 drop 数据表语句。(默认为打开状态，使用 --skip-add-drop-table 取消选项)
--add-locks：在每个表导出之前增加 LOCK TABLES 并且之后 UNLOCK TABLE。(默认为打开状态，使用 --skip-add-locks 取消选项)
--comments：附加注释信息。默认为打开，可以用 --skip-comments 取消
--compact：导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
-c --complete-insert：使用完整的 insert 语句(包含列名称)。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。
-C --compress：在客户端和服务器之间启用压缩传递所有信息
-B--databases：导出几个数据库。参数后面所有名字参量都被看作数据库名。
--debug：输出 debug 信息，用于调试。默认值为：d:t:o,/tmp/
--debug-info：输出调试信息并退出
--default-character-set：设置默认字符集，默认值为 utf8
--delayed-insert：采用延时插入方式（INSERT DELAYED）导出数据
-E--events：导出事件。
--master-data：在备份文件中写入备份时的 binlog 文件，在恢复进，增量数据从这个文件之后的日志开始恢复。值为 1 时，binlog 文件名和位置没有注释，为 2 时，则在备份文件中将 binlog 的文件名和位置进行注释。
--flush-logs：开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项 --databases 或者 --all-databases)，将会逐个数据库刷新日志。除使用 --lock-all-tables 或者 --master-data 外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用 --lock-all-tables 或者 --master-data 和 --flush-logs。
--flush-privileges：在导出 mysql 数据库之后，发出一条 FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出 mysql 数据库和依赖 mysql 数据库数据的任何时候。
--force在导出过程中忽略出现的SQL错误。
-h --host：需要导出的主机信息
--ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
-x --lock-all-tables：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。
-l --lock-tables：开始导出前，锁定所有表。用 READ LOCAL 锁定表以允许 MyISAM 表并行插入。对于支持事务的表例如 InnoDB 和 BDB，--single-transaction 是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，--lock-tables 分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
--single-transaction：适合 innodb 事务数据库的备份。保证备份的一致性，原理是设定本次会话的隔离级别为 Repeatable read，来保证本次会话（也就是 dump）时，不会看到其它会话已经提交了的数据。
-e, --extended-insert：使用具有多个 VALUES 列的 INSERT 语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用 --skip-extended-insert 取消选项。
--skip-tz-utc：mysqldump 的时候带上 --skip-tz-utc ,防止导出部分数据是 timestamp 类型会根据时区进行转换。
-F：刷新 binlog，如果 binlog 打开了，-F参数会在备份时自动刷新 binlog 进行切换。
-n --no-create-db：只导出数据，而不添加 CREATE DATABASE 语句。
-t --no-create-info：只导出数据，而不添加 CREATE TABLE 语句。
-d --no-data：不导出任何数据，只导出数据库表结构。
-p --password：连接数据库密码。
-P --port：连接数据库端口号。
-u --user：指定连接的用户名。

3、常用选项说明

4、备份实例

① 导出数据库

# 备份单个库
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 「dbname」 > 「dbname」.sql
或
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -B 「dbname」 > 「dbname」.sql
# 说明：加了 -B 参数后，备份文件中多的 Create database 和 use mytest 的命令
  
## 备份多个库
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -B 「dbname01」 「dbname02」 > 「dbnames」.sql
# 说明：通过 -B 参数指定相关数据库，每个数据库名之前用空格分格。当使用 -B 参数后，将所有数据库全部列全，则此时等同于 -A 参数
  
## 分库备份
for dbname in `mysql -u「username」 -p「password」 -h「IP」 -P「port」 -e "show databases;" | grep -Evi "database|schema|mysql|test"`
do
    mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 --events -B ${dbname} | gzip > ${dbname}_bak.sql.gz
done
  
## 导出数据库结构（不含数据）
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -d 「dbname」 > 「dbname」.sql
或
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -d -B 「dbname」 > 「dbname」.sql
# 说明：加上 -B 参数后，导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复。
  
## -A 参数备份全库，并且 -F 刷新和切换 binlog
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -A -B -F > 「dbname」.sql
  
## --master-data 参数在备份文件中写入当前 binlog 文件号，值为 1 时，binlog 文件名和位置没有注释，为 2 时，则在备份文件中将 binlog 的文件名和位置进行注释
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 --master-data=1 > 「dbname」.sql

② 导出数据库中的数据表

## 导出数据库中某张数据表（含数据）
# 注意：不能加 -B 参数
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 「dbname」 「tbname」 > 「tbname」.sql
  
## 导出数据库中某张数据表的结构（不含数据）
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -d 「dbname」 「tbname」 > 「tbname」.sql
  
## 单个 value 列的 insert 语法导出某张数据表
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 --skip-extended-insert --compact 「dbname」 「tbname」 > 「tbname」.sql
  
## 导出数据库中某张数据表（包含数据，且相关时间字段为 timestamp 类型）
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 --compact 「dbname」 「tbname] --skip-tz-utc > 「tbname」.sql
  
## -t参数，只备份数据
mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 --compact -t 「dbname」 「tbname」 > 「tbname」.sql

5、恢复操作

① 系统命令行

mysql -u「username」 -p「password」 -h「IP」 -P「port」 『dbname』 < 『dbname』.sql
# 说明：在导入备份数据库前，dbname 如果没有，是需要创建的，而且与 <dbname>.sql 中数据库名一致才可以导入。
mysql -u「username」 -p「password」 -h「IP」 -P「port」 『dbname』 < 『tbname』.sql
# 说明：指定 <dbname>，相当于use <dbname>

② source 方法

mysql > use <dbname>
mysql > source <dbname>.sql
或
mysql > source <tbname>.sql
# 说明：<dbname>.sql 或 <tbname>.sql 必须是绝对路径

三、生产环境不同引擎备份命令参数

1、mysqldump 的关键参数

2、不同引擎备份命令参数用法

① MyISAM 存储引擎

mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -A -B --master-data=1 -x | gzip > /data/all_$(date +%F).sql.gz

② InnoDB 存储引擎

mysqldump -u「username」 -p「password」 -h「IP」 -P「port」 -A -B  --master-data=1 --single-transaction > /data/all_$(date +%F).sql

③ 生产环境命令参考

## for MyISAM
mysqldump \
--user=「username」 \
--password=「password」 \
--host=「IP」 \
--port「port」 \
--all-databases \
--flush-privileges \
--lock-all-tables \
--master-data=1 \
--flush-logs \
--triggers \
--routines \
--events \
--hex-blob > ${BACKUP_DIR}/full_dump_${BACKUP_TIMESTAMP}.sql
 
## for InnoDB
mysqldump \
--user=「username」 \
--password=「password」 \
--host=「IP」 \
--port「port」 \
--all-databases \
--flush-privileges \
--single-transaction \
--master-data=1 \
--flush-logs \
--triggers \
--routines --events \
--hex-blob > ${BACKUP_DIR}/full_dump_${BACKUP_TIMESTAMP}.sql