MySQL为什么要备份
为什么数据库需要备份呢？MySQL事务中讲到ACID四大特性：原子性、一致性、隔离性和持久化。MySQl存储引擎告诉我们还有不支持事务的存储引擎，这样会导致写不到磁盘。即使是支持事务的存储引擎，写到磁盘的数据也有可能文件损坏。
备份就是为了防止原数据丢失，保证数据的安全。当数据库因为某些原因造成部分或者全部数据丢失后，备份文件可以帮我们找回丢失的数据。
常见数据库备份的应用场景如下：
1) 数据丢失应用场景：
人为操作失误造成某些数据被误操作
软件 BUG 造成部分数据或全部数据丢失
硬件故障造成数据库部分数据或全部数据丢失
安全漏洞被入侵数据恶意破坏
2) 非数据丢失应用场景：
特殊应用场景下基于时间点的数据恢复
开发测试环境数据库搭建
相同数据库的新环境搭建
数据库或者数据迁移
ps：说实话备份功能对于所内研发(测试和开发)都有好处，比如测试数据的预置。
备份类型
备份是以防万一的一种必要手段，在出现硬件损坏或非人为的因素而导致数据丢失时，可以使用备份恢复数据，以将损失降低到最小程度，因此备份是必须的。备份可以分为以下几个类型。
根据备份的方法（是否需要数据库离线）可以将备份分为：热备(Hot Backup)、冷备(Cold Backup)和温备(Warm Backup)。
热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup (在线备份)。
冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL官方手册中称为 Offline Backup（离线备份）。
温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。
按照备份后文件的内容，热备份又可以分为：逻辑备份和裸文件备份。
在 MySQL 数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本内容。内容一般是由一条条 SQL 语句，或者是表内实际数据组成。如mysqldump和SELECT * INTO OUTFILE的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复的时间较长。
裸文件备份是指复制数据库的物理文件，既可以在数据库运行中进行复制(如ibbackup、xtrabackup 这类工具)，也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多。
按照备份数据库的内容来分，备份又可以分为：完全备份和部分备份
完全备份是指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间。
部分备份是指备份部分数据库（例如，只备份一个表）。
部分备份又分为：增量备份和差异备份
增量备份需要使用专业的备份工具。指的是在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因此每次备份都比差异备份节约空间，但是恢复数据麻烦。
差异备份指的是自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单。
理解下：增量备份只考虑某个时间段内的前后变化数据的备份(比如中间变化的数据不考虑备份所以相对来说少了一些空间)。差异备份考虑空间上的差异(中间变化的数据也考虑备份)。
MySQL 中进行不同方式的备份还要考虑存储引擎是否支持，如 MyISAM 不支持热备，支持温备和冷备。而 InnoDB 支持热备、温备和冷备。
一般情况下，我们需要备份的数据分为以下几种：表数据、二进制日志、InnoDB 事务日志、代码（存储过程、存储函数、触发器、事件调度器）和服务器配置文件。
几种常用的备份工具：
mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
lvm2 snapshot：借助文件系统管理工具进行备份。
mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。
我理解还有其他工具：SELECT * INTO OUTFILE这个也挺好的，mysqldump备份的数据在pg(postgre)数据库中可以用吗？没求证过，但感觉应该不行，反而前面的SELECT更通用一些。
mysqldump备份数据
MySQL 中提供了两种备份方式，即 mysqldump 命令以及 mysqlhotcopy 脚本。由于 mysqlhotcopy 只能用于 MyISAM 表，所以MySQL 5.7移除了mysqlhotcopy脚本。
mysqldump -u username -p dbname [tbname ...] > filename.sql
对上述语法参数说明如下：
username：表示数据库用户名称；
dbname：表示需要备份的数据库名称；
tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
右箭头“>”：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。
注意：mysqldump命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的，通常建议为sql格式。
备份文件以“--”开头的都是 SQL 语言的注释。以 “/*!40101” 等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，这里就表示 MySQL4.1.1。如果恢复数据时，MySQL 的版本比4.1.1高，“/*!40101” 和“*/”之间的内容被当作 SQL 命令来执行。如果比 4.1.1低，“/*!40101”和“*/”之间的内容被当作注释。“/*!”和“*/”中的内容在其它数据库中将被作为注释忽略，这可以提高数据库的可移植性。
DROP 语句、CREATE 语句和 INSERT 语句都是数据库恢复时使用的；“DROP TABLE IF EXISTS 'student' ”语句用来判断数据库中是否还有名为 student 的表，如果存在，就删除这个表；CREATE 语句用来创建student表；INSERT 语句用来恢复所有数据。文件的最后记录了备份的时间。
备份多个数据库，mysqldump需要使用 --databases 参数，语法格式如下：
mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql
加上“--databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开。
备份所有数据库，mysqldump需要使用 --all-databases 参数，语法格式如下：
mysqldump -u username -P --all-databases >filename.sql
问：备份文件中应该没有数据库的用户及其密码信息。
mysql恢复数据库
mysql命令可以执行备份文件中的CREATE语句和INSERT语句，也就是说，mysql命令可以通过CREATE语句来创建数据库和表，通过INSERT语句来插入备份的数据。
语法格式如下：mysql -u username -P [dbname] < filename.sql
username 表示数据库用户名称；
dbname 表示数据库名称，该参数是可选参数。如果filename.sql文件为mysqldump命令创建的包含创建数据库语句的文件，则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错；
filename.sql 表示备份文件的名称。
问：备份文件中应该没有数据库的用户及其密码信息，所以恢复时应该先创建用户名及其密码。
SELECTI...INTO OUTFILE导出表数据
通过对数据表的导入导出，可以实现MySQL数据库服务器与其它数据库服务器间移动数据。导出是指将MySQL数据表的数据复制到文本文件。数据导出的方式有多种，本节主要介绍使用 SELECTI...INTO OUTFILE 语句导出数据。
SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]
MySQL导入导出文件只能在secure-file-priv变量的指定路径下的文件才可以导入导出。
show variables like '%secure%';语句查看 secure-file-priv 变量配置。如果secure_file_priv值为 NULL，则为禁止导出，可以在MySQL安装路径下的my.ini文件中添加secure_file_priv=设置路径语句，然后重启服务即可。
MySQL数据库恢复（LOAD DATA）
系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。
数据库恢复机制设计的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。
建立冗余数据最常用的技术是数据转储和登录日志文件。通常在一个数据库系统中，这两种方法是一起使用的。
数据转储是 DBA 定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的版本成为后备副本或后援副本。
可使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。
脑洞打开：不知道这个备份的sql文件不能能用到其他数据库上?如果不能，是不是可以通过查到的数据库表结构以及表内容等自行恢复?通select查到的信息，然后add到相应表里。
网友评论