MySQL 备份恢复

1.1 MySQL日志管理

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。

数据库中数据丢失或被破坏可能原因：

误删除数据库
数据库工作时，意外断电或程序意外终止
由于病毒造成的数据库损坏或丢失
文件系统损坏后，系统进行自检操作
升级数据库时，命令语句不严格
设备故障等等
自然灾害
盗窃

1.1.1 MySQL日志类型

MySQL有几个不同的日志文件，可以帮助你找出mysql内部发生的事情：

日志类型	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新。如果你正使用MySQL复制功能，从复制服务器将维护更多日志文件，被称为接替日志。

在mysql中，执行SQL语句：

FLUSH LOGS

在shell中，通过mysqladmin命令执行日志刷新：

mysqladmin flush-logs
mysqladmin refresh

1.1.2 错误日志

错误日志主要记录如下几种日志：

服务器启动和关闭过程中的信息
服务器运行过程中的错误信息
事件调度器运行一个时间是产生的信息
在从服务器上启动从服务器进程是产生的信息

错误日志定义：

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）

查看当前错误日志配置:

mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
是否记录警告日志：
mysql> SHOW GLOBAL VARIABLES LIKE '%log_warnings%';

1.1.3 通用查询日志

启动开关：general_log={ON|OFF}

日志文件变量：general_log_file[=/PATH/TO/file]

全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用

记录类型：log_output={TABLE|FILE|NONE}：

因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。

看看上述几个值的默认配置：

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';

mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';

1.1.4 慢查询日志

MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

默认与慢查询相关变量：

# 默认没有启用慢查询，为了服务器调优，建议开启
mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';

# 开启方法,当前生效，永久有效配置文件中设置
SET GLOBAL slow_query_log=ON;   

# 使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志
# mysqldumpslow slow.log
# 那么多久算是慢呢？
# 如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

1.1.5 二进制日志

日志查看

查看有哪些二进制日志文件：mysql> SHOW BINARY LOGS;

查看当前正在使用的是哪一个二进制日志文件：mysql> SHOW MASTER STATUS;

执行一下删表操作后再查看一下正在使用的日志文件 可以观察到记录

查看二进制日志内容：mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002';

##该语句还可以加上Position（位置），指定显示从哪个Position（位置）开始：
mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002' FROM 203;

使用命令mysqlbinlog查看二进制日志内容：mysqlbinlog [options] log-files

二进制日志还原数据

使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：

mysqlbinlog mysqld-binlog.000002 >/root/temp_date.sql

如果报编码错误：mysqlbinlog: [ERROR] unknown variable 'default-character-set

原因：mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令。

有两种方式解决：

添加 --no-defaults 参数

mysqlbinlog --no-defaults binlog.000069 >c:/a.sql # 注意需要指定binlog的位置，如果是当前路径，则可以直接使用名称即可。
修改配置文件my.cnf

default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是需要重启MySQL服务。

1.2 MySQL备份

1.2.1 备份类型

根据服务器状态，可以分为热备份、温备份、冷备份

热备份：读、写不受影响；
温备份：仅可以执行读操作；
冷备份：离线备份；读、写操作均中止；

从对象来分，可以分为物理备份与逻辑备份

物理备份：复制数据文件；
逻辑备份：将数据导出至文本文件中；

从数据收集来分，可以完全备份、增量备份、差异备份

完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；

逻辑备份优缺点

逻辑备份的优点：

在备份速度上两种备份要取决于不同的存储引擎

物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表

逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理

逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高

逻辑备份也对保持数据的安全性有保证

逻辑备份的缺点：

逻辑备份要对RDBMS产生额外的压力，而裸备份无压力

逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩

逻辑备份可能会丢失浮点数的精度信息

1.2.2 备份内容

数据文件
日志文件（比如事务日志，二进制日志）
存储过程，存储函数，触发器
配置文件（十分重要，各个配置文件都要备份）
用于实现数据库备份的脚本，数据库自身清理的Crontab等

1.2.3 备份工具

MySQL自带的备份工具

mysqldump，是mysql数据库管理系统，自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。

#备份DOS：
#备份所有数据库:
mysqldump -uroot -p --all-databases > h:/myschool.sql
#mysqldump -uroot -p --all-databases > /usr/local/myschool.sql
------------------------------------------------------------------------------
#备份myschool数据库的[student及result]表
mysqldump -uroot -p myschool [student result] > h:/myschool.sql
#mysqldump -uroot -p myschool [student result] > /usr/local/myschool.sql
#mysqldump -uroot -p myemployees departments  > /usr/local/sqlmessage/departments.sql
------------------------------------------------------------------------------
#创建数据库命令  mysqladmin -uroot -p create db_name;

#恢复1:
mysql -uroot -p;
USE db_name;
source h:/myschool.sql;
#source /usr/local/myschool.sql 
#source /usr/local/sqlmessage/departments.sql

#恢复2:
mysql -uroot -p db_name<h:/myschool.sql;
#mysql -uroot -p db_name</usr/local/myschool.sql;
#mysql -uroot -p test  < /usr/local/sqlmessage/departments.sql

文件系统备份工具

cp命令，冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。