【MySql】数据库备份

一、MySQL日志管理
备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。在备份、恢复中，日志起到了很重要的作用。

MySQL 的日志默认保存位置为**/usr/local/mysql/data**
MySQL 的日志配置文件为/etc/my.cnf，里面有个**[mysqld]**项。
 

1、日志类型与作用
1、redo （重做日志）
用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

达到事务一致性（每次重启都会重做）

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行 redo log 执行重做，达到事务一致性。

2、undo（回滚日志）
作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复和读取已提交隔离级别就是通过mvcc+undo实现。

3、errorlog（错误日志）☆
作用：mysql本身启动，停止，运行期间发生的错误信息

4、slow query log（慢查询日志）☆
作用：记录执行时间过长的sql语句，时间默认值为10s，可以配置，只记录执行成功，超过设置时间的sql语句，都会被存在到慢查询日志中。

还可以提醒优化，对于查询慢的语句进行操作（比如是网络问题，还是未添加索引等）

5、bin log （二进制日志）☆☆
作用：用于基于日志形式的数据恢复。用于主从复制，实现主从同步，记录的内容是：数据库中执行的sql语句

6、relay log（中继日志）☆☆
作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放。

7、general log（普通日志）
记录数据库的操作明细，默认关闭，开启后会降低数据库的性能

2、数据备份的重要性
备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因

① 程序错误

② 人为操作错误

③ 运算错误

④ 磁盘故障

⑤ 灾难(如火灾、地震)和盗窃

3、日志配置文件
 #修改my.cnf配置文件
 vim /etc/my.cnf
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log    
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin    
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#重新mysql服务
systemctl restart mysqld.service
 

二、备份的类型
数据库备份可以分为物理备份和逻辑备份

物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份，这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。

1、物理备份
物理备份又可以分成冷备份（脱机备份）、热备份（联机备份）和温备份。

1、冷备份（脱机备份）
是在关闭数据库的时候进行的备份，可以直接（tar）当正常关闭时会提供给我们一个完整的数据库。对于备份Oracle信息而言，冷备份是最快和最安全的方法。

冷备份的优点

使用tar命令直接打包数据库文件夹

直接替换现有mysql目录即可（/usr/local/mysql/data 数据目录）

是非常快速的备份方法（只需拷文件）

轻易归档（简单拷贝即可）

轻易恢复到某个时间点上（只需将文件再拷贝回去）

能与归档方法相结合，做数据库“最佳状态”的恢复。

低度维护，高度安全。

冷备份也有如下不足

① 单独使用时，只能提供到“某一时间点上”的恢复。
② 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
③ 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。
④ 不能按表或按用户恢复。

假如可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括：
１、 所有数据文件
２、 所有控制文件
３、所有联机REDO LOG文件
４、 Init.ora文件（可选）

值得注重的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。

2、热备份 (联机备份)
数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

3、温备份
数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）

物理备份的优点及使用场景：

优点：速度快，效率高。

场景：可用于停机维护及数据库物理迁移场景中。

2、逻辑备份 ☆☆
逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

① 完全备份
每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长，操作简单

② 差异备份
备份那些自从上次完全备份之后"被修改"过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份 备份有差异的数据

③ 增量备份
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失   备份增、删、改的内容

备份策略的比较

备份方式    完全备份    差异备份    增量备份
完全备份时的状态    表1、表2    表1、表2    表1、表2
第1次添加内容    创建表3    创建表3    创建表3
备份内容    表1、表2、表3    表3    表3
第2次添加内容    创建表4    创建表4    创建表4
备份内容    表1、表2、表3、表4    表3、表4    表4
3、逻辑备份的策略（增、全、差异）
如何选择逻辑备份策略（频率）
合理值区间 ☆ 
完全备份：3天一次的全备，全备的时间需要在不提供业务的时间区间进行 PM 10点 AM 5:00之间进行全备
增量备份：3天/2天/1天一次增量备份
差异备份：选择特定的场景进行备份
一个处理（NAS 就是采用nfs技术）提供额外空间给与mysql 服务器用

4、常见的备份方法
1、物理冷备
备份时数据库处于关闭状态，直接打包数据库文件（tar）
备份速度快，恢复时也是最简单的

2、专用备份工具 mysqldump 或 mysqlhotcopy 
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

3、启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志

4、第三方工具备份
免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup