MYSQL备份与恢复

数据库管理员最为重要的任务就是防止数据丢失，或者发生数据丢失后将损失降到最低。通过数据库备份可以实现该任务目标，数据库备份就成了数据库管理员的日常工作。数据库的恢复是将数据库从某一种"错误"状态恢复到某一个已知的正确状态。

1.数据丢失的方法

MYSQL服务运行期间，意外的停电，硬盘损坏，数据库管理员的误操作，服务器宕机等意外情况无法避免，且都会造成数据库一定程度的数据丢失。数据库管理员最为重要的工作莫过于：如何防止数据丢失，或者发生数据丢失后，如何确保数据库能够最大程度地恢复到"正确"状态。
（1）方法1：数据备份
防止数据丢失的最简单办法就是定期对原始数据进行备份，创建原始数据的副本。
MYSQL数据库的数据全部以文件的形式存储在硬盘上，因此最容易想到的数据备份方法就是备份数据库的各种文件，其中包括数据库目录，数据文件，索引文件，my.cnf配置文件以及日志文件等。
（2）方法2：使用二进制日志
当数据库中的更新操作较为频繁时，使用第一种方法不能实现数据库更细粒度的恢复，然而借助二进制日志文件可以实现数据库更细粒度的数据恢复。二进制日志记录了数据库的更新操作，数据丢失时，通过对完全备份进行二进制日志的重做，可以完成基于时间点或者操作点的恢复，继而实现数据库更细粒度的恢复。
（3）方法3：数据库复制
数据库复制实际上是通过二进制日志预防数据丢失，数据库复制可以实现数据库的异地备份和恢复。
数据库中防止数据丢失的方法较为复杂，没有任意一种单一的方法能够完美解决所有应用场景的需要，更多时候各个方法之间需要相互配合。

2.数据备份

数据备份的内容包括数据库表结构，数据库表记录以及其他数据文件(索引文件，日志文件，配置文件等)。文件备份的方法较为简单，只需复制，粘贴即可，这里不再赘述。然而由于文件备份的方法力度较粗，且不可编辑，因此需要更细粒度的备份方式。
（1）按照备份后产生的副本文件是否可以编辑，可以将MYSQL的备份方法分为逻辑备份以及物理备份。
使用逻辑备份时：数据库管理员通常可以直接查看，编辑副本文件中的内容。逻辑备份产生的副本有两种情形。情形一：副本是SQL脚本文件，该SQL脚本文件中包含了大量create table语句与insert语句，用于创建表以及向各数据库表插入记录，继而实现数据库表的备份；编辑SQL脚本文件，执行SQL脚本文件即可实现数据库的恢复。情形二：副本是指定格式的文本文件，该文本文件中的数据是数据库表的记录，文本文件中的数据以特定的分隔符进行分隔。编辑该文本文件，将文本文件的内容导入数据库表中即可实现数据库表的恢复。
使用物理备份时：产生的副本常常是二进制文件，通常不可编辑，例如数据库的各种文件。
（2）备份期间，按照是否需要停止MYSQL服务实例，可以将MYSQL的数据备份方法分为冷备份，温备份和热备份。
冷备份是指停止MYSQL服务的运行后再进行数据备份，这种备份方法想的容易，做起来难，因为真正的业务环境不允许停止MYSQL服务器的运行。该方式是简单将数据库文件拷贝到U盘，实现备份数据的随身携带。
温备份：允许MYSQL服务器继续运行，但借助读锁机制保证备份数据的过程中不允许新数据写入。例如执行MYSQL命令”flush tables with read lock;“。
热备份：不需要停止MYSQL服务实例的运行。如果数据库的更新操作较为频繁，在数据备份期间，备份过的数据可能早已发生变化，因此热备份的实现方法较为复杂。通常热备份需要借助第三方工具实现，例如MYSQL提供的自带工具mysqlhotcopy实现了MYISAM表的热备份，percona公司的Xtrabackup工具实现了innodb表的热备份。
（3）按照副本文件的缺失程度可以将数据备份分为完全备份和增量备份。
完全备份时一个完整的数据备份，仅仅依靠该副本文件就可以将数据库恢复到某个正确的状态。增量备份是指在完全备份的基础上，对更新的数据进行备份，数据恢复时需要借助完全备份产生的副本文件。目前，MYSQL没有提供真正的增量备份方法，数据库管理员可以使用热备份工具模拟实现增量备份，也可以通过重新执行二进制日志中的更新语句模拟实现增量备份。

3.逻辑备份与逻辑恢复

逻辑备份与逻辑恢复的最大优点在于，对于不同存储引擎的表，都可以采用”同样的逻辑备份方法“产生副本文件；采用”同样的恢复方法“将数据库恢复。如果数据库存在多种存储引擎的数据库表，选用逻辑备份恢复数据会简单一些。
（1）使用select … into outfile … 备份表数据
使用MYSQL命令可以将一个数据库表中满足特定条件的记录导出到指定格式的文本文件中，该文本文件使用特殊符号分隔各个字段的值。该命令的语法格式如下：
格式：
select语句 into outfile ‘文本文件’ [文本文件选项参数]
参数说明如下：
fields terminated by ‘字符串’：字段分隔符，默认是’\t’
fields escaped by ‘字符’：转移字符，默认是’’
fields [optionally] enclosed by ‘字符’：字段引用符，负责向字段值两边加上字段引用符。如果使用optionally选项，则表示只在char，varchar以及text字符串型字段上添加字段分隔符。
lines starting by ‘字符串’：每条记录前添加该字段字符串，默认为空字符’’
lines terminated by ‘字符串’：每条记录末尾添加该字符串，默认换行’\n’；

当使用该命令后，将在数据库目录中创建该文件，如果文件存在，则执行失败。
（2）恢复表数据
上述（1）得出的文件，可以用使用load data infile …或者mysqlimport命令将特定格式的文本文件中的数据导入数据库表。
load data infile …： 命令恢复表数据
格式：load data [local] infile ‘指定格式的文本文件名’ into table 表名 [选项参数]
（3）举例
select * from account into outfile ‘account.txt’ fileds terminated by ‘|’ enclosed by “” lines terminated by ‘\r\n’；
delete from account;
load data infile ‘account.txt’ into table account fields terminated by ‘|’ optionally enclosed by “” lines terminated by ‘\r\n’;
(4)使用mysqlimport命令
mysqlimport工具可以将指定格式的文本文件中的数据导入到某个数据库的数据库表中，mysqlimport的这项功能实际上是调用load data infile命令实现的。
格式：
mysqlimport -u root -p [–local] 数据库名，表名 指定格式的文本文件 [选项参数]
（5）使用mysqldump备份数据库
mysqldump是MYSQL转储数据库时常用的自带工具，mysqldump产生的副本文件有两种情形：指定格式的文本文件，MYSQL脚本文件。
方法1：备份指定的多个数据库
mysqldump -u 用户名 -p [其他选项参数] --database database1 [database2,…]
方法2：备份所有数据库
mysqldump -u 用户名 -p [其他选项参数] --all-database
方法3：备份指定的数据库，或者指定数据库中的某些表
mysqldump -u 用户名 -p [其他选项参数] database1 [table1 tabl2 …]
（6）mysqldump注意事项
如果备份数据包含中文简体字符，建议添加选项–default-character-set=gbk,否则有乱码的风险。
为了保证一致性，备份MYISAM表时，建议加上-l或者–lock-tables选项。而在备份innodb表时，建议加上–single-transaction选项，将整个备份过程封装在同一个事务中，以便实现一致性和完整性。
如果需要为所有数据库表施加读锁，可以加上-x或者–lock-all-tables。
如果仅仅希望SQL脚本文件仅仅包含create语句，加上-d或者–no-data
如果需要备份某个数据库表中满足特定条件的记录，加上–where=‘查询条件’
如果需要备份数据库中的存储过程和函数，加上-R或者–routines
如果需要备份数据库中的事件，加上-E或者–events
如果需要备份数据库中的触发器，加上–triggers
mysqldump暂时不能备份视图

4.物理备份与热备份

（1）MYISAM表的所有数据存放在数据库目录中，每个MYISAM表都会存在3个文件：frm文件，MYD文件以及MYI文件。备份MYISAM表时，只需要备份每个表的frm文件，MYD文件以及MYI文件即可。
（2）innodb表都会存在frm文件，表空间文件，由于innodb是一个事务安全的存储引擎，事务安全主要通过重做日志以及回滚日志实现，默认情况下，重做日志的信息记录在ib_logfile0和ib_logfile1文件中，不仅需要备份表空间文件，frm文件，还需要备份重做日志文件。
（3）进行物理备份时，关闭MYSQL服务实例，复制上面的文件到其他位置，即可实现备份。

5.MYSQL复制

MYSQL复制是将一台主服务器的数据更改，异步地复制到另一个服务器或者多台服务器的过程。
所谓异步，是指从服务器的数据更新与主服务器的数据更新存在一定时差，所谓复制，是指当主服务器的数据更改记录到二进制日志文件中后，从服务器将主服务器的数据更改通过二进制日志文件拷贝到服务器的中继日志中，而后从服务器重做中继日志中的数据更改，使得主从服务器数据保持同步。
通过复制功能，可以实现MYSQL数据库的一下特性：
高可用性：当主服务器停机维护，从服务器可以替代主服务器继续提供服务。
负载平衡：通过在多台服务器之间分散负载提供高性能。