MySQL笔记-数据备份和恢复

物理备份与逻辑备份简介

物理备份与逻辑备份	简介
物理备份	直接复制数据库文件，备份数据文件，转储数据库物理文件到某一目录
逻辑备份	备份的是建表、建库、插入等操作所执行SQL语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现

物理备份与逻辑备份对比

对比	物理备份	逻辑备份
速度	备份恢复速度比较快，但占用空间比较大	备份恢复速度慢，但占用空间小，更灵活
工具	xtrabackup工具	mysqldump工具
适用	大型数据库环境，但不能恢复到异构系统中，如Windows	中小型数据库，效率相对较低

逻辑备份

备份一个数据库

mysqldump -u用户名 -h 主机名 -p密码 待备份的数据库名称[表名, [表名...]]> 备份文件名称.sql

案例：使用root用户备份test数据库

# 备份在当前目录下
mysqldump -uroot -p test > test.sql
# 备份在其他目录
mysqldump -uroot -p test > /opt/test.sql

备份多个数据库（-B）

使用 –databases 或 -B 参数，参数后面跟数据库名称，多个数据库间用空格隔开

# 方式一
mysqldump -u用户名  -h 主机名 -p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
# 方式二
mysqldump -u用户名  -h 主机名 -p -B [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

备份全部数据库（-A）

使用 –all-databases 或 -A 参数

# 方式一
mysqldump -uroot -p密码 --all-databases > all_database.sql
# 方式二
mysqldump -uroot -p密码 -A > all_database.sql

备份部分表

mysqldump -u用户名  -h 主机名 -p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

案例：备份test数据库下的user表

mysqldump -uroot -p test user> book.sql   

备份多张表

mysqldump -u用户名  -h 主机名 -p 数据库的名称 表名1 表名2... > 备份文件名称.sql

备份单表的部分数据（–where）

使用 –where 选项，后面附带需要满足的条件

mysqldump -u用户名 -p 数据库的名称 表名 --where="条件" > 备份文件名称.sql

案例：备份student表中id小于10的数据

mysqldump -uroot -p test student --where="id < 10 " > student_id10.sql

排除某些表的备份（–ignore-table）

使用–ignore-table参数

mysqldump -u用户名 -p 数据库的名称 --ignore-table=数据库的名称.表名 > 备份文件名称.sql

通过如下指定判定文件中没有student表结构

grep "表名" 备份文件名称.sql

只备份结构（-d）

只备份结构的话可以使用 –no-data 简写为 -d 选项

mysqldump -u用户名 -p 数据库的名称 --no-data > 备份文件名称.sql

只备份数据（-t）

只备份数据可以使用 –no-create-info 简写为 -t 选项

mysqldump -u用户名 -p 数据库的名称 --no-create-info > 备份文件名称.sql

备份中包含存储过程、函数、事件（-R、-E）

mysqldump备份默认是不包含存储过程，自定义函数及事件

使用 –routines 或 -R 选项来备份存储过程及函数

使用 –events 或 -E 参数来备份事件

mysqldump -u用户名 -p -R -E --databases 数据库的名称 > 备份文件名称.sql

mysqldump常用选项

运行帮助命令，获得特定版本的完整选项列表

mysqldump --help

参数	简介
–verbose，-v	冗长模式，打印出程序操作的详细信息
–silent，-s	沉默模式。只有出现错误时才输出
–xml，-X	产生XML输出
–all-database, -A	转储所有数据库中的所有表。与使用–database选项相同，在命令行中命名所有数据库。
–default-character-set=charset	使用charsets默认字符集。如果没有指定，就使用utf8
–delete，-D	导入文本文件前清空表
–no-create-info，-t	只导出数据，而不添加CREATE TABLE语句。
–no-data，-d	不写表的任何行信息，只转储表的结构

恢复数据

mysql -u用户名 -p密码 [数据库名称] < 备份文件名称.sql

转储文件（dump文件）里面一般指定了set names utf8，所以我们在导入的时候不在需要指定特殊的字符集。例外的情况是，有一些特殊的场合，SQL文件是以其他字符集导出的，这个时候导入要注意保持文件的字符集、客户端字符集和连接的字符集的一致性，例如

# --default-character-set的意思是，客户端和连接都默认使用charset_name字符集
mysql --default-character-set=gbk < 备份文件名称.sql

物理备份

数据库文件目录

物理备份是直接将MySQL中的数据库文件复制出来

• Windows平台：MySQL 8.0存放数据库的目录默认为 C:\ProgramData\MySQL\MySQL Server 8.0\Data 或其他用户自定义目录；
• Linux平台：数据库目录位置通常为/var/lib/mysql/；
• MAC OSX平台：数据库目录位置通常为“/usr/local/mysql/data”

保证数据备份的一致性

物理备份为了保证备份的一致性，需要做到如下

方式1：备份前，将服务器停止

方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作

这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时确保开始备份前将所有激活的索引页写入硬盘。

物理恢复

物理恢复步骤

（1）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器

（2）查询相关表的数据是否恢复

物理恢复要求

（1）必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。

因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的，在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。

（2）物理恢复对 MyISAM 类型的表比较有效 ，对于 InnoDB 类型的表则不可用，因为 InnoDB 表的表空间不能直接复制

（3）在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下

表的导出

准备工作

部分版本的mysql对通过文件导入导出作了限制，默认不允许

（1）查询secure_file_priv值（如果value值为null，则为禁止）

show global variables like '%secure%';

（2）打开mysql配置文件添加如下(等号一定要有，否则mysql无法启动)

secure_file_priv=

（3）重启mysql服务，再次查询

使用select…into outfile导出文本文件

# target_file 为准备工作中指定的路径，若未指定，则可以为任意路径
select * from tablename into outfile 'target_file' [option];

option常用的参数有fields 和 lines，不能同时使用（char 表示此符号只能是单个字符，string表示可以是字符串）

# fields 和 lines 不能同时使用
[fields terminated by 'string'
	[optionally] enclosed by 'char' 
	escaped by 'char']
[lines starting by 'string'
	terminated by 'string']

各选项介绍

参数	简介
fields terminated by ‘string’	字段分隔符，默认为制表符\t
fields [optionally] enclosed by ‘char’	字段引用符，定义字符串使用什么符号括起来，默认不使用引用符 如果加 optionally 选项则只用在 char、varchar 和 text 等字符型字段上
fields escaped by ‘char’	转义字符、默认为\
lines starting by ‘string’	每行前都加此字符串，默认无字符
lines terminated by ‘string’	每行结束符，默认为\n

案例：使用select…into outfile将test数据库中users表中的记录导出到文本文件

select * from users into outfile "E:/users.txt";

案例：使用select…into outfile将test数据库中account表中的记录导出到文本文件，使用fields选项或lines选项，要求字段之间使用逗号，间隔，所有字段值用双引号括起来

select * from test.account into outfile "E:/users.txt"
fields terminated by ',' 
enclosed by '\"';

使用mysqldump命令导出文本文件

# target_file 为准备工作中指定的路径，若未指定，则可以为任意路径
mysqldump -uroot -p密码 [参数选项] "target_file" 数据库名 表名

案例：使用mysqldump命令将将test数据库中users表中的记录导出到文本文件

mysqldump -uroot -p123456 -T "E:/" test users

mysqldump命令执行完毕后，在指定的目录 E:/ 下生成了users.sql和users.txt文件。

打开users.sql文件，其内容包含创建account表的CREATE语句

打开users.txt文件，其内容只包含account表中的数据

使用mysql命令导出文本文件

# target_file 为准备工作中指定的路径，若未指定，则可以为任意路径
mysql -uroot -p密码 [参数选项] -e "select 语句" 数据库名 > 'target_file'

常用参数

参数	简介
–execute	执行命令并退出
-N, –skip-column-names	不输出字段名
-H, –html	输出数据html格式
-X, –xml	输出数据为xml格式

案例：使用mysql语句导出test数据中users表中的记录到文本文件（-e）

mysql -uroot -p123456 --execute="select * from users;" test> "E:/users.txt"

案例：使用mysql语句导出test数据中users表中的记录到xml文件（-X –execute）

mysql -uroot -p123456 --xml --execute="select * from users;" test > "E:/users.xml"

案例：使用mysql语句导出test数据中users表中的记录到xml文件（-H –execute）

mysql -uroot -p123456 --html --execute="select * from users;" test > "E:/users.html"

表的导入

使用load data infile方式导入文本文件

案例：恢复users表的数据

# 步骤一：使用select...into outfile将test数据库中users表的记录导出到文本文件
select * from users into outfile "E:/users.txt";
# 步骤二：删除users表中的数据
delete from test.users;
# 步骤三：从文本文件users.txt中恢复数据
load data infile 'E:/users.txt' into table test.users;

案例：使用load data infile恢复字段不同的数据

# 步骤一：使用select...into outfile将test数据库中account表中的记录导出到文本文件，
# 使用fields选项或lines选项，要求字段之间使用逗号，间隔，所有字段值用双引号括起来
select * from test.account into outfile "E:/users.txt"
fields terminated by ',' 
enclosed by '\"';
# 步骤二：删除users表中的数据
delete from test.users;
# 步骤三：使用load data infile命令从文本文件users.txt中恢复字段不同的数据
load data infile 'E:/users.txt' into table test.users
fields terminated by ',' 
enclosed by '\"';

使用mysqlimport方式导入文本文件

案例：使用mysqlimport恢复字段不同的数据

# 步骤一：使用select...into outfile将test数据库中account表中的记录导出到文本文件，
# 使用fields选项或lines选项，要求字段之间使用逗号，间隔，所有字段值用双引号括起来
select * from test.users into outfile "E:/users.txt"
fields terminated by ',' 
enclosed by '\"';
# 步骤二：删除users表中的数据
delete from test.users;
# 使用mysqlimport命令从文本文件users.txt文件中恢复字段不同的数据
mysqlimport -uroot -p123456 test "E:/users.txt"
--fields-terminatedby=','
--fields-optionally-enclosed-by='\"'