MySQL数据库备份与恢复

MySQL数据库备份与恢复
1、备份方式
逻辑备份（文本表示：SQL 语句）
物理备份（数据文件的二进制副本）
基于快照的备份
基于复制的备份
增量备份（刷新二进制日志）
2、备份类型
2.1 热备份
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
2.2冷备份
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
2.3温备份
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
3、物理备份
物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于需要在发生问题时快速恢复的大型重要数据库。
3.1物理备份的特点
备份由数据库目录和文件的精确副本组成。通常这是全部或部分MySQL数据目录的副本。
物理备份方法比逻辑更快，因为它们只涉及文件复制而无需转换。
输出比逻辑备份更紧凑。
由于备份速度和紧凑性对繁忙，重要的数据库非常重要，MySQL企业备份产品可以执行物理备份。份的特点：
备份和恢复从整个数据目录级别到单个文件级别的粒度范围。这可能会也可能不会提供表级粒度，具体取决于存储引擎。例如， InnoDB表可以分别放在一个单独的文件中，或与其他InnoDB表共享文件存储 ; 每个 MyISAM表格唯一对应一组文件。
除数据库外，备份还可以包含任何相关文件，如日志或配置文件。
来自MEMORY表的数据很难以这种方式备份，因为它们的内容不存储在磁盘上。（MySQL企业备份产品具有可以MEMORY在备份过程中从表中检索数据的功能。）
备份只能移植到具有相同或相似硬件特性的其他机器。
备份可以在MySQL服务器不运行时执行。如果服务器正在运行，则需要执行适当的锁定，以便服务器在备份期间不更改数据库内容。MySQL Enterprise Backup会自动为需要它的表执行此锁定。
物理备份工具包括 mysqlbackup MySQL企业备份的 InnoDB或任何其他桌，文件系统级的命令（如CP， SCP，焦油， rsync的），或mysqlhotcopy的 对MyISAM表。
为了恢复：
MySQL企业备份还原InnoDB 和其他备份的表。
ndb_restore恢复 NDB表格。
可以使用文件系统命令 将文件系统级别或使用mysqlhotcopy复制的文件 复制回原来的位置。
4、逻辑备份
逻辑备份保存表示为逻辑数据库结构（CREATE DATABASE， CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）的信息。这种类型的备份适用于可能编辑数据值或表结构的较小数据量，或者在不同的计算机体系结构上重新创建数据。
4.1逻辑备份的特点
备份是通过查询MySQL服务器来获得数据库结构和内容信息。
备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出写入客户端，服务器也必须将其发送到备份程序。
输出大于物理备份，特别是以文本格式保存时。
在服务器级别（所有数据库），数据库级别（特定数据库中的所有表）或表级别都可以使用备份和还原粒度。无论存储引擎如何，情况都是如此。
备份不包括日志或配置文件，或其他不属于数据库的与数据库相关的文件。
以逻辑格式存储的备份与机器无关并且非常便携。
逻辑备份是在MySQL服务器运行的情况下执行的。服务器没有脱机。
逻辑备份工具包括mysqldump 程序和SELECT … INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。
要恢复逻辑备份，可以使用mysql客户端处理SQL格式的转储文件。要加载分隔文本文件，请使用 LOAD DATA INFILE语句或 mysqlimport客户端。
5、备份工具
1.mysqldump

mysql原生自带很好用的逻辑备份工具
2.mysqlbinlog

实现binlog备份的原生态命令
3.xtrabackup

precona公司开发的性能很高的物理备份工具
5.1mysqldump介绍
5.1.1语法
1

2

3

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

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

5.1.2参数
1

2

3

4

5

6

7

8

9

10

11

12

13

-A, --all-databases Dump all the databases. #全库

-B, --databases Dump several databases. #单库

-d #仅表结构

-t #仅数据

–compact #减少无用数据输出(调试)

-R, --routines #备份存储过程和函数数据

–triggers #备份触发器数据

–master-data={1|2} #告诉你备份后时刻的binlog位置，2：注释；1：非注释，要执行（主从复制）

–master-data #自动进行锁表和释放锁

–single-transaction #对innodb引擎进行热备

-x #锁住所有备份表

-l #锁住单表

-F, --flush-logs #刷新binlog日志(回顾binlog)

5.2使用
5.2.1 全库备份
1

2

3

[root@db02 ~]# mkdir /backup

[root@db02 ~]# mysqldump -A >/backup/full.sql

#注意：mysqldump恢复数据必须数据库是开启状态下，假如数据库数据目录被删除，那么此时就需要重新初始化数据库，然后启动数据库，更新密码，然后登陆数据库，进行source 命令进行恢复

5.2.2 备份多个表
1

2

3

4

mysqldump库1 表1 表2 表3 >库1.sql

mysqldump库2 表1 表2 表3 >库2.sql

#单表备份：

mysqldump -uroot -p123 oldboy test>/backup/test.sql

5.2.3 分库备份:for循环
1

2

3

4

5

6

7

<