MySQL备份恢复

MySQL日志管理

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

MySQL日志类型

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

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

在MySQL中，执行sql语句

FLUSH LOGS;

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

mysqladmin -uroot -p flush-logs;
输入密码
或者
mysqladmin -uroot -p refresh;
输入密码

错误日志

错误日志主要记录如下几种日志：
①服务器启动和关闭过程中的信息
②服务器运行过程中的错误信息
③事件调度器运行一个时间时产生的信息
④在从服务器上启动从服务器进程时产生的信息

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

查看当前错误日志的配置

SHOW GLOBAL VARIABLES LIKE '%log_error%';

mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
+----------------------------+----------------------------------
| Variable_name              | Value                            
+----------------------------+----------------------------------
| binlog_error_action        | ABORT_SERVER                     
| log_error                  | /var/log/mysqld.log              
| log_error_services         | log_filter_internal; log_sink_int
| log_error_suppression_list |                                  
| log_error_verbosity        | 2                                
+----------------------------+----------------------------------
5 rows in set (0.00 sec)

从上表可以看出错误日志文件的目录为/var/log/下的mysqld.log

是否记录警告日志

SHOW GLOBAL VARIABLES LIKE '%log_warnings%';

通用查询日志

查看当前通用查询日志的配置

SHOW GLOBAL VARIABLES LIKE '%general_log%';
或者
SHOW GLOBAL VARIABLES LIKE '%log_output%';

启动开关： general_log={ON||OFF};
日志文件变量： general_log_file[=/PATHTO/file]
全局日志开关： log={ON||OFF} 该开关打开后，所有日志都会被启用
记录类型： log_output={TABLE|FILE|NONE}:
因此，要启用通用日志查询，需要至少配置general_log=ON,log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name_log。

慢查询日志

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

默认与慢查询相关变量的配置

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

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

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

二进制日志

# 二进制日志启动开关
log_bin[=file_name]

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。

语句以“事件”的形式保存，他描述数据更改。二进制日志还包含了关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。

二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能的更新数据库（即时点恢复），因为二进制日志包含了包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给服务器的语句。

二进制日志是记录执行的语句还是执行后的结果数据呢？分三种情况
1、加入一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加1000：

UPDATE sales.january SET amount=amount+1000;

此时如果要记录执行后的结果数据的话，日志会非常大。因此在这种情况下应记录执行语句。这种方式就是基于语句的二进制日志。
2、如果向某个字段插入的是当前的时间呢？如下

INSERT INTO tb SET Birthdate=CURRENT_TIME();

此时就不能记录语句了，因为不同时间执行的结果是不一样的。这时影噶记录这一行的值，这种就是基于行（row）的二进制日志。
3、在有些情况，可能会结合两种方式来记录，这种叫做混合方式的二进制日志。
二进制日志的管理
日志滚动。在my.cnf中设定max_binlog_size=200M，表示限制二进制日志最大容量为200M，超过200M后进行滚动。MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大于1的日志用于记录最新的日志，而原日志名字不会被改变，则使用命令FLUSH LOGS;
日志查看

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

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

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

# 查看二进制日志内容：
SHOW BINLOG EVENTS IN 'mysqld-binlog.00002' FROM  203;

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

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

MySQL备份

备份类型

根据服务器状态，可以分为热备份、温备份、冷备份
①热备份：读写不受影响
②温备份：仅可以执行读操作
③冷备份：离线备份；读写操作均中止

从对象来分，可以分为物理备份与逻辑备份
①物理备份：复制数据文件；
②逻辑备份：将数据导出至文本文件中。

从数据收集来分，可以分为完全备份、增量备份、差异备份
①完全备份：备份全部数据
②增量备份：仅备份上次完全备份或增量备份以后变化的数据
③差异备份：仅备份上次完全备份以来变化的数据。

逻辑备份的优缺点
优点：
在备份速度上两种备份要取决于不同的存储引擎。
物理备份的还原速度非常快，但是物理备份的最小粒度只能做到表。
逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理。
逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高。
逻辑备份也对数据的安全性有保证。
缺点：
逻辑备份要求对RDBMS产生额外的压力，而裸备份无压力。
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩。
逻辑备份可能会丢失浮点数的精度信息。

备份内容

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

备份工具

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

需求： 将test中的student表进行逻辑备份，转储在目标目录内

# mysqldump
mysqldump -u root -p 数据库名[表名 表名 表名] > sql转储的地址
mysqldump -u root -p test student > /usr/local/mysqlbackups/test.sql

需求： 将test.sql恢复

sql命令
SOURCE 目录
SOURCE /usr/local/mysqlbackups/test.sql;

或者

shell命令
mysql -uroot -p 数据库名 < 目录
[root@localhost /]# mysql -u root -p test2 < /usr/local/mysqlbackups/test.sql
Enter password:

工具方式：
例如使用Navicat Premium工具

备份：

恢复：


然后进行选择即可。