MySQL5.7的备份与恢复简介和使用mysqldump备份MySQL数据库

2021年2月16日发(作者：无可挑剔)

MySQL5.7

的备份与恢复简介和使用

my sqldump

备份

MySQL

数据库

一、备份的类型和其他说明：

1.1

按备份时服务器是否继续提供服务区分：

热备份：备份时读写都不受影响

温备份：备份时仅可进行读操作

冷备份：也叫离线备份，读写操作均中止

不同的存储引擎对备份的支持也是不一样的，

MyISAM

存储

引擎可以使用

LVM

快照功能配合实现热备份，如果没有

LVM

< p>
，

则只能实现温备份。

而

InnoDB

则可以完全支持热备，

可使用的热备工具有：

xtrabackup, mysqldump

等

从数据的安全 角度来说，离线备份（冷备份）是最安全的，

且是最快速的。

但 离线备份需要停止服务，

对业务带来影响。

如果

MySQL

服务器存在主从服务器，则可以使用将从服务

器停机进行冷备份，即保证服务，又保障数据数据安全。

1.2

按备份操作方式区分：

物理备份：复制数据文件，特点：速度快

逻辑备份：将数据导出至文本文件中，特点：速度慢、丢失< /p>

浮点数精度；方便使用文本处理工具直接对其处理、可移植

能力强

1.3

按是否备份全部数据还是只备份部分数据区别：

完全备份：备份全部需要备份的数据

增量备份：仅备份上次完全备份或增量备份以后变化的数据

差异备份：仅备份上次完全备份依赖变化的数据

一般情况下，根据备份策略组合使 用：完全

+

增量；完全

+

差

异

1.4

其他说明：

备份的目的是在灾难发生 时用来还原，因此为了保证可以还

原，因此需要定期进行恢复测试，另外还需要根据实际 情况

制定最优的备份和恢复策略。

那 么

MySQL

备份需要备份那些数据呢？

主要包括：数据、配置文件、二进制日志、事务日志

二、备份工具介绍：

2.1 MySQL

备份工具：

mysqldump:

逻辑备份工 具、

MyISAM(

温

)

、

InnoDB(

热备份

)

mysqlhotcopy

：物理备份工具、温备份

2.2

文件系统工具：

cp

：冷备份

lv

：逻辑卷的快照功能，可以实现几乎热备的功能，备份过

程如下：

mysql> FLUSH TABLES;

mysql> LOCK TABLES

然后创建快照：释放锁，而后复制数据

2.3

第三方工具：

ibbackup:

商业工具，备 份速度非常快，支持热备份，但同

时也非常贵

xtrabackup:

开源工具

三、使用

mysqldump

对

MySQL

进行备份：

3.1

基本语法：

备份单个数据或单个数据中的指定表：

mysqldump [OPTIONS] database [tb1] [tb2]…

备份多个数据库：

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2

DB3...]

备份所有数据库：

mysqldump [OPTIONS] --all-databases [OPTIONS]

3.2

选项

[OPTIONS]

说明：

--all-databases

, -A

导出全部数据库。

mysqldump

-uroot -p --all-databases

--all-tablespaces

, -Y

导出全部表空间。

mysqldump

-uroot -p --all-databases --all- tablespaces

--no- tablespaces

, -y

不导出任何表空间信息。

mysqldump

-uroot -p --all-databases --no-tablespaces

--add-drop-database

每个数据库创建之 前添加

drop

数据库语句。

mysqldump

-uroot -p --all-databases

--add-drop-database

--add-drop-table

每个数据表创建之前添加

drop

数据表语句。

(

默认为打开状

态，使用

--skip-add- drop-table

取消选项

)

mysqldump

-uroot -p --all-databases

(

默认添加

drop

语

句

)

mysqldump

-uroot -p --all-databases

–

skip-add-drop-table

(

取消

dr op

语句

)

--add-locks

在每个表导出之前增加

LOCK TABLES

并且之后

UNLOCK

TABLE

。

(

默认为打开状态，使用

--skip-add- locks

取消选项

)

mysqldump

-uroot -p --all-databases

(

默认添加

LOCK

语句

)

mysqldump

-uroot -p --all-databases

–

skip- add-locks

(

取消

LOCK

语句

)

--allow-keywords

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

mysqldump

-uroot -p --all-databases --allow- keywords

--apply-slave- statements

在

'CHANGE MASTER'

前添加

'STOP SLAVE'

，并且在导出

的最后添加

'START SLAVE'

。

mysqldump

-uroot -p --all-databases

--apply-slave- statements

--character- sets-dir

字符集文件的目录

mysqldump

-uroot -p --all-databases

--character-sets- dir=/usr/local/mysql/share/mysql/charsets

--comments

附加注释信息。默认为打开，可以用

--skip- comments

取消

mysqldump

-uroot -p --all-databases

(

默认记录注释

)

mysqldump

-uroot -p --all-databases --skip-comments

(

取消注释

)

--compatible

导出的数据将和其它数据库或旧版本的

MySQL

< p>
相兼容。

值

可以为

ans i

、

mysql323

、

mysql40

、

postgresql

< p>
、

oracle

、

mss ql

、

db2

、

maxdb

、

no_key_options

、

no_tables_options

、

< p>
no_field_options

等，

要使用几个值，

用逗号将它们隔开。

它并不保 证能完全兼容，

而是尽量兼容。

mysqldump

-uroot -p --all-databases --compatible=ansi

--compact

导出更少的输出信息

(

用于调试

)

。去掉注释和头尾等 结构。

可以使用选项：

--skip-add-drop- table

--skip-add-locks

--skip-comments --skip-disable-keys

mysqldump

-uroot -p --all-databases --compact

--complete-insert,

-c

使用完整的

< br>insert

语句

(

包含列名称

)

。这么做能提高插入效

率，但是可能 会受到

max_allowed_packet

参数的影响而导

致插入失败。

mysqldump

-uroot -p --all-databases --complete-insert

--compress, -C

在客户端和服务器之间启用压缩传递所有信息

mysqldump

-uroot -p --all-databases --compress

--create-options,

-a

在

CREATE TABLE

语句中包 括所有

MySQL

特性选项。

(

默

认为打开状态

)

mysqldump

-uroot -p --all-databases

--databases,

-B

导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump

-uroot -p --databases test mysql

--debug

输出

debug

信息，用于调试。默认值为：

d:t:o ,/tmp/

mysqldump

-uroot -p --all-databases --debug

mysqldump

-uroot -p --all-databases --

debug=”

d:t:o,/tmp/”

--debug-check

检查内存和打开文件使用说明并退出。

mysqldump

-uroot -p --all-databases --debug-check

--debug-info

输出调试信息并退出

mysqldump

-uroot -p --all-databases --debug-info

--default-character-set

设置默认字符集，默认值为

utf8

mysqldump

-uroot -p --all-databases

--default-character- set=latin1

--delayed-insert

采用延时插入方式（

INSERT DELAYED

）导出数据

mysqldump

-uroot -p --all-databases --delayed-insert

--delete-master-logs

master

备份后删除日志

.

这个参数将自动激活

--master- data

。

mysqldump

-uroot -p --all-databases --delete-master-logs

--disable-keys

对于每个表，

用

/*!40000 ALTER TABLE tbl_name DISABLE

KEYS */;

和

/*!40000 ALTER TABLE tbl_name ENABLE

KEYS */;

语句引用

INSERT

语句。

这样可以更快地导 入

dump

出来的文件，因为它是在插入所有行后创建索引的。 该选项

只适合

MyISAM

表，默认为 打开状态。

mysqldump

-uroot -p --all-databases

--dump-slave

该选项 将导致主的

binlog

位置和文件名追加到导出数据的文

件中。

设置为

1

时 ，

将会以

CHANGE MASTER

命令输出到

数据文件；设置为

2

时，在 命令前增加说明信息。该选项将

会打开

--lock-all- tables

，除非

--single-transactio n

被指定。

该选项会自动关闭

--lo ck-tables

选项。默认值为

0

。

mysqldump

-uroot -p --all-databases --dump-slave=1

mysqldump

-uroot -p --all-databases --dump- slave=2

--events, -E

导出事件。

mysqldump

-uroot -p --all-databases --events

--extended-insert,

-e

使用具有多个

VALUES

列的

INSERT

语法。这样使导出文

件更小，并加速导入时的速度。默认为打开状态，使用

--skip -extended-insert

取消选项。

mysqldump

-uroot -p --all-databases

mysqldump

-uroot -p

--all-databases--skip-extended- insert

(

取消选项

)

--fields-terminated-by

导出文件中忽略给定字段。

与

--tab

选项一起使用，

不能用于

--databas es

和

--all- databases

选项

mysqldump

-uroot -p test test --

tab=”/home/mysql”

--fields- terminated-

by=”#”

--fields-enclosed-by

输出文件中的各个字段用给定字符包裹。

与

--ta b

选项一起使

用，不能用于

--dat abases

和

--all- databases

选项

mysqldump

-uroot -p test test --

tab=”/home/mysql”

--fields- enclosed-

by=”#”

--fields-optionally-enclosed-by

输出文件中的各个字段用给定字符选择性包裹。

与

-- tab

选项

一起使用，不能用于

--d atabases

和

--all- databases

选项

mysqldump

-uroot -p test test --

tab=”/home/mysql”

--fields- enclosed-

by=”#”

--fields- optionally-enclosed-by

=”#”

--fields-escaped-by

输出文件中的各个 字段忽略给定字符。

与

--tab

选项 一起使用，

不能用于

--databases

< br>和

--all- databases

选项

mysqldump

-uroot -p mysql user --

tab=”/home/mysql”

--fields- escaped-

by=”#”

--flush-logs

开始导出之前刷新日志。

请注意：假 如一次导出多个数据库

(

使用选项

-- databases

或

者

--all- databases)

，将会逐个数据库刷新日志。除使用

-- lock-all-tables

或者

--master-da ta

外。

在这种情况下，

日志

将会被刷新一次，相应的所以表同时被锁定。因此，如果打

算同时导出和 刷新日志应该使用

--lock-all-tables

或者

--master-data

和

--flush- logs

。

mysqldump

-uroot -p --all-databases --flush-logs

--flush-privileges

在导出

mysql

数据库之后，

发出一条

< br>FLUSH

PRIVILEGES

语句。为了正确恢复，该选项应该用于导出

mysql

数据库和

依赖

mysql

数据库数据的 任何时候。

mysqldump

-uroot -p --all-databases --flush-privileges

--force

在导出过程中忽略出现的

SQL

错误。

mysqldump

-uroot -p --all-databases --force

--help

显示帮助信息并退出。

mysqldump

--help

--hex-blob

使用十六进 制格式导出二进制字符串字段。如果有二进制数

据就必须使用该选项。影响到的字段类型 有

BINARY

、

VARBINARY

、

BLOB

。

mysqldump

-uroot -p --all-databases --hex-blob

--host, -h

需要导出的主机信息

mysqldump

-uroot -p --host=localhost --all-databases

--ignore-table

不导出指定表。指定忽略多个 表时，需要重复多次，每次一

个表。每个表必须同时指定数据库和表名。例如：

--ignore-table=1

--ignore-

table=2 ……

mysqldump

-uroot -p --host=localhost --all-databases

--ignore-table=

--include-master-host-port

在

--dump- slave

产生的

'CHANGE

MASTER TO..'

语句中

增加

'MASTER_HOST=<host>

，

MASTER_PORT=<port>'

mysqldump

-uroot -p --host=localhost --all-databases

--include-master-host-port

--insert-ignore

在插入行时使用

INSERT IGNORE

语句

.

mysqldump

-uroot -p --host=localhost --all-databases

--insert-ignore

--lines-terminated-by

输出文件的每 行用给定字符串划分。与

--tab

选项一起使用，

< p>
不能用于

--databases

和

--all-databases

选项。

mysqldump

-uroot -p --host=localhost test test

--

tab=”/tmp/mysql”

--lines- terminated-

by=”##”

--lock-all-tables,

-x

提交请求锁定所有数据库中的 所有表，以保证数据的一致性。

这是一个全局读锁，并且自动关闭

--single-transaction

和

--lock-tables

选项。

mysqldump

-uroot -p --host=localhost --all-databases

--lock-all-tables

--lock-tables,

-l

开始导出前，锁定所有表。用

READ

LOCAL

锁定表以允

许

MyISAM

表并行插入。

对于支持事务的表例如

InnoDB

和

BDB

，

--single-transaction

是一个更好的选择，因为它根本

不需要锁定表。

请注意当导出多个数据库时，

--lock-tables

分别为每个数据

库锁定表。因此，该选项不能保证导出文件中的 表在数据库

之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

mysqldump

-uroot -p --host=localhost --all- databases

--lock-tables

--log-error

附加警告和错误信息到给定文件

mysqldump

-uroot -p --host=localhost --all-databases

--log-error=/tmp/mysqldump_error_

--master-data

该选 项将

binlog

的位置和文件名追加到输出文件中。

如果为

1

，将会输出

C HANGE MASTER

命令；如果为

2

< br>，输出的

CHANGE

MASTER

命令前添加注释信息。该选项将打开