MySQL日志管理、备份与恢复

日志管理

MySQL 的日志默认保存位置为 /usr/local/mysql/data

MySQL常用的日志类型及开启

日志文件有两种开启方式，一种是通过命令临时修改日志文件，这种临时的修改在关闭或者重启服务后则会关闭；另一种是通过配置文件永久开启。
常用的日志文件可以在/etc/my.cnf中[mysql]客户端配置中修改

常用的MySQL日志文件有错误日志、二进制日志、中继日志和慢查询日志

1.错误日志

错误日志用于记录MySQL启动、停止或运行时产生的错误信息，默认开启
可以通过在配置文件中添加以下字段进行更新

log-error=/usr/local/mysql/data/mysql_error.log
指定日志的保存位置和文件名

2.二进制日志

二进制日志用来记录所有更新的数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启。
可以通过在配置文件中添加以下字段来开启

log-bin=mysql-bin
或
log_bin=mysql-bin

3.中继日志

一般情况下中继日志在MySQL主从同步（复制）、读写分离集群的从节点才开启。主节点一般不需要这个日志。

4.慢查询日志

慢查询日志用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的。
开启方式是通过在配置文件中添加以下内容

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
指定文件路径和名称
long_query_time=5
设置执行超过5秒的语句会被记录，缺省时为10秒

重启服务

查看日志

查看二进制文件是否开启

查看慢查询日志功能是否开启

在数据库中临时开启慢查询

查看慢查询时间设置

MySQL备份与恢复

目的

• 灾难恢复
• 测试应用
• 回滚数据修改
• 查询历史数据
• 审计

重要性

在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此数据的安全性和可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。
通常情况下造成数据丢失的原因有如下几种：

1. 程序错误
2. 人为操作错误
3. 运算错误
4. 磁盘故障
5. 灾难（如火灾、地震）和盗窃

备份类型

从物理与逻辑的角度分类

数据库备份可以分为物理备份和逻辑备份。

1. 物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种备份适用于在出现问题时需要快速恢复的大型数据库。物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。
   （1）冷备份：在数据库关闭状态下进行备份操作
   （2）热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件
   （3）温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

2. 逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（create database；create table 语句）和内容（insert语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据。

从数据库的备份策略角度分类

从数据库的备份策略角度可以分为完全备份、差异备份和增量备份。

1. 完全备份
   每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份和增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份时间也很长。
2. 差异备份
   备份那些自从上次完全备份之后被修改过的所有文件，备份时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需要恢复上次的完全备份与最近的一次差异备份。
3. 增量备份
   只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

常用的备份方法

MySQL数据库的备份可以采用很多种方法，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等

物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹（/usr/local/mysql/data）来实现备份。

专用备份工具mysqldump或mysqlhotcopy

mysqldump是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库，对其进行备份或传输到远程SQL服务器。mysqldump更为通用，因为它可以备份各种表。
mysqlhotcopy仅适用于某些存储引擎。

通过启用二进制日志进行增量备份MySQL

支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。
通过第三方工具备份Percona XtraBackup是一个免费的MySQL热备份软件，支持在线热备份InnoDB和XtraDB，也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况下进行。

备份恢复实验

1.创建表结构、表数据

2.MySQL完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt（表属性文件）、表名.frm（表结构文件）、表名.ibd（表数据文件）。

物理冷备份与恢复

如果想要恢复，就把压缩的文件解压恢复就可以了

mysqldump备份与恢复

一、完全备份一个或者多个完整的库（包括其中所有的表）

二、完全备份 MySQL 服务器中所有的库

三、完全备份指定库中的部分表

3.完全备份恢复

恢复数据库

恢复数据表

MySQL增量备份与恢复

增量备份

1.开启二进制日志功能

二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
binlog_format=STATEMENT 默认
每一条涉及到被修改的sql都会记录在binlog中。
缺点：日志量过大，如sleep（）函数，last_insert_id()>，以及user-defined functions(udf)、主从复制等架构记录日志时会出现问题

shell binlog_format=ROW
只记录变动的记录，不记录sql的上下文环境。
缺点：如果遇到update....set....where true，那么就binlog的数据量变大

binlog_format=MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存取

2.每周对数据库或表进行完全备份

3.每天进行增量备份

每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)

4.插入新数据，模拟数据变更

5.再次刷新生成新的二进制文件

刷新前的步骤4中的操作都会被保存在mysql-bin.000003中，刷新后数据库数据再次发生的改变则保存到新生成的mysql-bin.000004中

6.查看二进制日志文件的内容

使用64位编码机制去解码按行读取详细内容：

增量恢复

1.一般恢复

2.模拟丢失所有数据的恢复

3.断点恢复

使用64位编码机制去解码并按行读取二进制文件000002的详细内容

• 基于位置恢复
  仅恢复到操作id为612之前的数据，即第三行数据
  
  
  仅恢复操作id为612之后的数据记录，即第四行数据记录
  
• 基于时间点恢复
  
  仅恢复第三行数据记录，即恢复到2021-04-15 14:57:37之前的数据
  
  仅恢复第四行数据记录，跳过第三行的数据恢复
  

总结

1. 常用的MySQL日志文件有错误日志、二进制日志、中继日志和慢查询日志
2. 数据库备份从物理和逻辑的角度可以分为物理备份和逻辑备份；从数据库的备份策略角度可以分为完全备份、差异备份和增量备份。
3. 数据库的备份有很多种方法，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。