一、备份概述
备份不是单纯的复制数据，因为这样无法留下历史记录和系统的DNS或Registry等信息。完整的备份应包括自动化的数据管理与系统的全面恢复，即备份=复制+管理。
1. 备份需要考虑的问题
- 可以容忍丢失多长时间的数据恢复数据
- 要在多长时间内完成
- 恢复的时候是否需要持续提供服务
- 恢复的对象是什么
2. 分类
2.1 根据是否需要数据库离线划分
分类 说明 冷备(cold backup) 需要关闭MySQL服务 温备(warm backup) 可不关闭服务，但仅支持读请求，不支持写请求 热备(hot backup) 不需要关闭MySQL服务
2.2 根据要备份的数据集合的范围划分
分类 说明 完全备份(full backup) 备份全部字符集 增量备份(incremental backup) 借助完全备份，备份上次完全备份或增量备份以来改变了的数据 差异备份(differential backup) 备份上次完全备份以来改变了的数据
2.3 根据备份的对象划分
分类 说明 物理备份 直接备份数据文件。可以跨MySQL版本，恢复速度快。 逻辑备份 备份表中的数据和代码。备份与存储引擎无关，备份结构为ASCII文件，但恢复时需要MySQL服务参与，且浮点数可能会丢失。
二、文件方式的优缺点
文件备份是将数据库中的数据备份为一个文本文件，该文本文件可以移植到其他机器上。
在MySQL中，可以直接复制磁盘中对应的数据文件，这种方式需要先执行 flush tables语句将所有数据写入到数据文件，之后 停止MySQL服务器才能复制数据文件。不够这种方式 只适合MyISAM引擎，并且需要保证数据还原时MySQL数据库与备份时的 数据库的主版本号一致。
命令 类型 对象 使用文件 mysqldump命令 导出 结构、数据 .sql
文件 select语句 导出 数据 .txt
文件 mysql命令 导入 结构、数据 .sql
文件 mysqlimport命令 导入 数据 .txt
文件 load语句 导入 数据 .txt
文件 source语句 导入 结构、数据 .sql
文件
三、备份（导出）
1. mysqldump命令导出
mysqldump命令可以导出数据表、数据库的结构和数据。
1.1 语法
-- 备份db_name数据库中的tb_name数据表
mysqldump [options] db_name [tb_name ...] > sql_file
-- 备份多个数据库
mysqldump [options] -B db_name1 [db_name2 ...] > sql_file
-- 备份所有数据库
mysqldump [options] -A > sql_file
- 第一个是备份单个数据库的数据表，当tb_name未指定时备份db_name这个数据库的所有数据表。
- 第二个是备份多个数据库，在这里不能指定某个数据表，其中参数
-B可换为--databases。 - 第三个是备份所有的数据库，其中参数
-A可换为--all-databases。 - sql_file为备份文件的路径。
options参数 说明 -h host_name 指定服务器地址，host_name为localhost时可以省略 -u user_name 指定登录的用户名 -p [pwd] 指定登录密码，密码与-p间没有空格，当然也可以采用不显式输入，见例子 -t, –no-create-info 备份时不备份表结构 -d, –no-data 备份时不备份表数据 -tab=path 备份时把数据和sql语句分开存储（ .txt
文件和 .sql
文件），此选项不能和 -A -B
同时使用，且mysqldump命令必须运行在服务器主机上
1.2 例子
（1）备份一个表
mysqldump -h localhost -u root -p123456 webnews news > E:\news_backup.sql
这里是备份webnews数据库中的一个表news。
注意-p和密码间无空格，若存在空格， 123456会作为一个数据库来看待。这里 -h localhost可省略， -p可使用安全模式输入，如下方Warning所说。
（2）备份多个数据库
mysqldump -u root -p -B webnews book > E:\dbOf2_backup.sql
这里是备份webnews和book两个数据库，其中 -B可以换成 --databases。
（3）备份所有数据库
mysqldump -u root -p -A > E:\allDB_backup.sql
这里 -A为 --all-databases表示所有数据库。
（4）使用-tab分别备份表数据和表结构
注意：使用-tab进行分开存储之前需要先看一下设置，如下：
show variables like '%secure%';
正常情况下， secure_file_priv的默认值为 null，表示限制mysqld不允许导入导出，可通过修改配置文件 安装路径下的my.ini文件，添加如下内容即可：
在安装路径下的my.ini文件内添加
secure_file_priv=''
注意等号左右没有空格
修改完成之后重启mysql服务器，以管理员身份运行cmd，执行如下命令：
net stop mysql
net start mysql
重启之后，可以重新登录mysql服务器，查看 secure_file_priv的值是否我上图所示。
好了，配置到此，正文开始。
mysqldump -u root -p webnews news user roles --tab=E:\testtab
这里是备份webnews数据库中news、user、roles三个表。
2. select语句导出
select语句只能导出数据表的数据，并存为txt文件。
2.1 语法
select [col_name...] from [db_name.]table_name [where语句]
into outfile txt_file [options];
db_name省略时，选择当前数据库的table_name数据表的数据。txt_file需要加上引号，路径分割使用/。- options中，
fields语句需写在lines语句前面。
options参数 说明 fields terminated by ‘str’ 设置 str
为字段间的分隔符，默认为 \t
fields optionally enclosed by ‘str’ 设置 str
来括住char、varchar、text类型字段的数据，默认为空 fields escaped by ‘str’ 设置 str
为转义字符，默认为 \
lines starting by ‘str’ 设置每行数据开头字符 lines terminated by ‘str’ 设置每行数据结尾字符，默认为 \n
2.2 例子
select * from book.t_user
into outfile 'E:/user_backup.txt'
fields
terminated by '\、'
optionally enclosed by '\"'
lines
starting by '\>'
terminated by '\r\n';
四、恢复（导入）
1. mysql命令导入
mysql命令可以导入数据表、数据库的结构和数据。
1.1 语法
mysql [-h host_name] -u user_name -p[pwd] [db_name] < sql_file
- 若sql_file备份的是数据库，那么db_name不用加，它会按照sql_file内的代码创建和使用选定的数据库。
- 若sql_file只备份数据表，那么db_name必须要有，它会把sql_file备份的表还原在db_name数据库中。
1.2 备份数据库例子
（1）备份book数据库
（2）删除book数据库
（3）还原数据库
（4）查看数据库
1.3 备份数据表例子
（1）备份book数据库内的t_user数据表
（2）创建新数据库newbook
（3）还原数据到新数据库
（4）查看新数据内容
2. mysqlimport命令导入数据
mysqlimport命令只能导入存储在txt文件中的数据表数据。
2.1 语法
mysqlimport [-h host_name] -u user_name -p[pwd] [options] db_name txt_file
这里只能恢复db_name数据库中与txt_file同名的表的数据，即txt_file中存储的是db_name数据库中txt_file数据表的数据。
options参数 说明 -d, –delete 删除表中的所有记录 -r, –replace 替换掉存在重复的唯一键的旧行
2.2 例子
t_user表的表结构：
（1）导出book数据库中的t_user表的数据
（2）清空t_user表的数据
（3）导入数据
（4）再导入数据
现在t_user表中不是空的，而且数据表有设置主键和唯一键，如果直接导入会报错，这时候就需要使用options中的参数了。 --replace和 --delete这两个参数在目前情况下都可使用。
3. load语句导入
load语句只能导入存储在txt文件中的数据表数据，事实上，mysqlimport命令使用的就是load语句来实现的。
3.1 语法
load data [low_priority | concurrent] [local] infile txt_file
[replace | ignore] into table [db_name.]tb_name
[fields语句] [lines语句]
[ignore number lines]
[(col_name, col_name...)]
[set col_name=expr, ...]
参数 说明 low_priority 本参数适用于表锁存储引擎，当写入过程中有其他客户端程序读取表内容时，load语句会延后至其他程序写入完成 concurrent 允许写入过程中其他客户端程序读取表内容 local 表示txt_file文件在服务器上读取 txt_file 导入文件的路径，用使用引号 replace 当有重复的唯一键时，替换掉原来的行 ignore 当有重复的唯一键时，跳过原来的行
3.2 例子
load data local infile 'E:/testload/user.txt'
into table book.t_user;
4. source命令导入
4.1 语法
source sql_file
sql_file不用使用引号，路径分隔符使用/- 当
sql_file备份数据库时，使用source语句之前可以不用use选择数据库，若备份的是表，则需要。
4.2 例子
（1）备份数据库
（2）删除数据库、还原数据库
这里相当于是一句句执行sql文件，所以会有很多个返回语句(Query OK...)。
参考资料
Original: https://www.cnblogs.com/bpf-1024/p/14136570.html
Author: 步平凡
Title: NoteOfMySQL-12-备份与还原
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/572714/
转载文章受原作者版权保护。转载请注明原作者出处！