MySQL(四)基于日志的备份与恢复
目录
一、MySQL日志
1、日志类型与作用
①redo 重做日志:达到事务一致性(每次重启都会重做)
②undo 回滚日志
③erroring 错误日志
④slow query log慢查询日志
⑤bin log 二进制日志
⑥relay log 中继日志
⑦general log 普通日志
2、数据备份的重要性
3、日志配置文件
①错误日志
②二进制日志
③慢查询日志
④通用查询日志
4、日志文件查询
①查看通用查询日志是否开启
②查看二进制日志是否开启
③查看慢查询日志是否开启
二、数据库备份
1、物理备份
①冷备份
②热备份
③温备份
2、逻辑备份
①完全备份
②差异备份
③增量备份
3、常见的备份方法
①物理冷备
②专用备份工具
③启用二进制日志进行增量备份
④第三方工具备份
三、完备备份及恢复方式
1、物理冷备及恢复
2、mysqldump备份
3、mysqldump恢复
①source恢复
②mysql语句
四、增备备份及恢复方式
1、恢复方式
①一般恢复
②基于位置恢复
③基于时间点恢复
2、备份过程
①开启二进制日志功能
②二进制日志的三种不同的记录格式
3、查看二进制文件的内容
4、二进制日志的关注点
①at
②end_log_pos
③时间戳
④sql语句
3、恢复方式
①基于位置点恢复
②基于时间点恢复
一、MySQL日志
1、日志类型与作用
①redo 重做日志:达到事务一致性(每次重启都会重做)
作用:确保日志的持久性,放置在发生故障,脏页未写入硬盘。重启数据库会进行redo log执行重做,达到事务一致性;
②undo 回滚日志
作用:保证数据的原子性,记录事务发生之前的一个版本,用于回滚,innodb事务可重复读和读取已提交,隔离级别就是通过mvcc+undo实现;
③erroring 错误日志
作用:mysql本身启动、停止、运行期间发生的错误信息;
④slow query log慢查询日志
作用:记录执行时间过长的sql,时间阙值(10s)可以配置,只记录执行成功;
另一个作用在于提醒优化;
⑤bin log 二进制日志
作用:用于主从复制,实现主从同步;
记录着数据库中执行的sql语句;
⑥relay log 中继日志
作用:用于数据库主从同步,将主库发来的bin log保存在本地,然后从库进行回放;
⑦general log 普通日志
作用:记录数据库的操作明细,默认关闭,开启后会降低数据库的性能。
2、数据备份的重要性
别分的主要目的是丢失恢复。在生产环境中,数据的安全性至关重要,任何数据的丢失都有可能产生严重的后果。
数据丢失原因:
①程序错误
②人为操作失误
③运算错误
④磁盘故障
⑤自然灾害等意外
3、日志配置文件
①错误日志
log-error=/usr/local/mysql/data/mysql_error.log
②二进制日志
log-bin=mysql-bin
会出现一个mysql_bin.index 文件存放其地址,为了给我们和从服务器看
③慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 设置超过5s执行的语句被记录,缺省时间为10s
④通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
4、日志文件查询
①查看通用查询日志是否开启
②查看二进制日志是否开启
③查看慢查询日志是否开启
二、数据库备份
1、物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。
①冷备份
(脱机备份)是在关闭数据库的时候进行;
②热备份
(联机备份)数据库处于运行状态,依赖于数据库的日志文件;
③温备份
数据库锁定表格(不可写入但刻度)的状态下进行备份操作。
2、逻辑备份
对数据库路逻辑组件(如:表等数据库对象)的备份。
①完全备份
备份完成时刻的数据库。是差异备份与增量备份的基础;
对整个数据库、数据库结构和文件结构的备份;保存的是备份完成时刻的数据库
1)优点
备份与恢复操作简单方便
2)缺点
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长
②差异备份
基于上一次完备为起始点。备份自从上次完全备份之后被修改过的文件。(sql语句);
恢复时从上一次完备开始到最后一次差异备份;
③增量备份
以上一次完备或增备为起始点。备份变化的部分。如果语句对数据不产生影响,则不备份。 比如变了之后在变回来;
恢复时需要从上一次完备时开始恢复,依次恢复每次一增备数据。如果中间某次的备份数据损坏,将导致数据的丢失。
3、常见的备份方法
①物理冷备
备份时数据库处于关闭状态,直接打包数据库文件;
备分速度快,恢复最简单;
②专用备份工具
mysqldump或mysqlhotcopy
mysqldump 常用的逻辑备份工具 (温备)
mysqlhotcopy 仅拥有备份myisam和archive表
③启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
④第三方工具备份
免费的热备份软件 Percona XtraBackup mysqlbackup
三、完备备份及恢复方式
1、物理冷备及恢复
在数据库关闭的情况下打包一份数据库文件保存,需要的时候直接解压替换原文件。
2、mysqldump备份
单个
查看内容
全部
指定库中的表
-d 表示只保存数据库的表结构,不加 -d 表示备份内容包括表数据
3、mysqldump恢复
①source恢复
②mysql语句
四、增备备份及恢复方式
1、恢复方式
①一般恢复
将所有备份的二进制日志内容全部恢复;
②基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作,可以基于精准的位置跳过错误的操作,在发生错误节点之前的一个节点,到上一次正确的操作的位置点停止;
③基于时间点恢复
跳过某个发生错误的时间点实现数据恢复,在错误时间点停止,在下一个正确时间点开始。
2、备份过程
①开启二进制日志功能
vim /etc/my.cnf
binlog_format=MIXED 可选,指定二进制日志的记录格式为mixed混合输入
②二进制日志的三种不同的记录格式
1)statement 基于sql语句
每一条涉及到被修改的sql都会记录在binlog中
缺点:日志量过大、主从复制等架构记录日志时会出现问题
2)row 基于行
只记录变动的记录,不记录sql的上下文环境
缺点:如果遇到update/ set/ where true/ 那么binlog数据量会越来越大
3)mixed 混合模式
一般的语句使用statement 函数使用row方式存储
默认格式为statement
3、查看二进制文件的内容
通常情况下,二进制文件的内容我们没办法正常浏览,所以需要解码器翻译给我们看。
mysqlbinlog --no-defaults --base64-output=decode-rows -v 地址
--base64-output=decode-rows:使用64位解码(decode)的方式进行解码并按行读取
-v:显示详细内容
--no-defaults:默认字符集(不加会报UTF-8的错误)
4、二进制日志的关注点
①at
开始的位置
②end_log_pos
结束的位置
③时间戳
④sql语句
3、恢复方式
①基于位置点恢复
mysqlbinlog --no-defaults -base64-output
跳过从678到875的操作
恢复从4到678的操作,不执行后面的sql
②基于时间点恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志(位置) |mysql -uroot -p
恢复从22-09-23 0:31:07开始到22-09-23 0:59:53的所有操作