小谈 MySQL 第五话·备份与恢复之 mysqldump

目录

一、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、常用选项说明

参数名 缩写 含义
--host -h 服务器 IP 地址
--port -p 服务器端口号
--user -u MySQL 用户名
--password -P MySQL 密码
--databases -B 指定要备份的数据库
--all-databases -A 备份 MySQL 服务器上的所有数据库
--no-data -d 不到出任何数据,指导出数据库表结构
--compact   压缩模式,产生更少的输出,去掉备份文件中的注释,适合调试,生产场景不用
--comments  -i 添加注释信息
--complete-insert -c 输出完成的插入语句
--lock-tables -l 备份前锁定所有数据表
--lock-all-tables -x 提交请求锁定所有数据库中的所有表,以保证数据的一致性,这是一个全局锁
--force -f 当出现错误时仍然继续备份操作
--default-character-set   指定默认字符集
--skip-tz-utc   防止导出部分数据是 timestamp 类型会根据时区进行转换

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 的关键参数

参数 说明
-B 指定多个库,在备份文件中增加建库语句和 use 语句
--compact 去掉备份文件中的注释,适合调试,生产环境不用
-A 备份所有库
-F 刷新 binlog 日志
--master-data 在备份文件中增加 binlog 日志文件名及对应的位置点
-x 锁表
-l 只读锁表
-d 只备份结构
-t 只备份数据
--single-transaction

适合 innodb 事务数据库的备份。

InnoDB 表在备份时,通常启用选项 --single-transaction 来保证备份的一致性,原理是设定本次会话的隔离级别为 Repeatable read,来保证本次会话(也就是 dump)时,不会看到其它会话已经提交了的数据。
--skip-tz-utc 防止导出部分数据是 timestamp 类型会根据时区进行转换
--default-character-set 指定默认字符集

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

 

展开阅读全文

Windows版YOLOv4目标检测实战:训练自己的数据集

04-26
©️2020 CSDN 皮肤主题: Age of Ai 设计师: meimeiellie 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值