首页 > 数据库 >MySQL日志管理,备份和恢复

MySQL日志管理,备份和恢复

时间:2022-12-02 20:55:07浏览次数:39  
标签:log 数据库 MySQL mysql 日志 备份

一.MySQL日志管理

1.1mysql日志概述

  • 备份的主要目的是灾难恢复,备份还可以测试应用、回滚数据修改、查询历史数据、审计等。
  • 在备份、恢复中,日志起到了很重要的作用。
  • MySQL 的日志默认保存位置: /usr/local/mysql/data
  • 开启日志的方法:①修改配置文件开启②通过命令进行开启(临时的)
vim /etc/my.cnf    //配置文件
[mysqld]     //服务端配置

1.2错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启

vim /etc/my.cnf
[mysqld]
log-error=/usr/local/mysql/data/mysql_error.log

1.3通用查询日志

用来记录MySQL的所有连接和语句,默认是关闭的

vim /etc/my.cnf
general_log=ON //ON开启/off关闭 general_log_file=/usr/local/mysql/data/mysql_general.log

1.4二进制日志

用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启

vim /etc/my.cnf
log-bin=mysql-bin      
或
log_bin=mysql-bin
######开启后重启服务在data目录中就会看到二进制日志######

  cd /usr/local/mysql/data
  ls
  mysql-bin.000001    //开启二进制日志时会产生一个索引文件及一个索引列表

  mysql-bin.index     //二进制日志文件的索引

  索引文件:记录更新语句
  索引文件刷新方式:
  ①重启mysql的时候会更新索引文件,用于记录新的更新语句
  ②刷新二进制日志



1.5中继日志

一般情况下它在Mysql主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志

1.6慢查询日志

用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便提醒优化,默认是关闭的

s1ow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5        //设置超过5秒执行的语句被记录,缺省时为10秒

1.7查询日志状态和路径

##开启日志,设置默认路径##
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


systemctl restart mysqld
mysql -u root -P
show variables like 'general%';       //查看通用查询日志是否开启
show variables like 'log_bin%';       //查看二进制日志是否开启
show variables like '%slow%';         //查看慢查询日功能是否开启
show variables like 'long_query_time';    //查看慢查询时间设置
set global slow_query_log=ON;         //在数据库中设置开启慢查询的方法

PS:variables 表示变量 like 表示模糊查询

#xxx(字段)
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx   精准查询

1.7.1开启日志

 

 

 1.7.2查看日志状态和路径

①查看通用日志是否开启

 

 ②查看二进制日志是否开启

 

 ③查看慢查询日志是否开启

 ④查看慢查询时间设置

 

 

 

二.数据库的备份和恢复

2.1数据备份的重要性

①备份的主要目的是灾难恢复
②在生产环境中,数据的安全性至关重要
③任何数据的丢失都可能产生严重的后果
④造成数据丢失的原因

 2.2数据损坏,丢失的原因

①程序错误
②人为操作错误
③运算错误
④磁盘故障
⑤灾难 (如火灾、地震) 和盗窃

2.3数据备份的类型

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

2.3.1物理备份

  • 物理备份是对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。
  • 物理备份又可以成为冷备份(脱机备份)、热备份(连接备份)和温备份

① 冷备份 (脱机备份) :是在关闭数据库的时候进行的(tar)
② 热备份 (联机备份) :数据库处于运行状态,依赖于数据库的日志文件(mysqlhotcopy mysqlbackup)
③ 温备份 :数据库锁定表格(不可写入但可读)的状态下进行备份操作(mysqldump)

2.3.2逻辑备份

  • 逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
  • 这种类型的备份适用于可以编辑数据值或表结构

从数据库的备份策略角度来看,备份又可分为完全备份,差异备份和增量备份。

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

每次都进行完全备份,会导致备份文件占用空间巨大,并且有大量的重复数据,恢复时,直接使用完全备份的文件即可


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

每次差异备份,都会备份上一次完全备份之后的数据,可能会出现重复数据。恢复时,先恢复完全备份的数据,再恢复差异备份的数据

 

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

每次增量备份都是备份在上一次完全备份或者增量备份之后的数据,不会出现重复数据的情况,也不会占用额外的磁盘空间
恢复数据,需要按照次序恢复完全备份和增量备份的数据

2.4常见的备份方法

①物理冷备

  • 备份时数据库处于关闭状态,直接打包数据库文件(tar)
  • 备份速度快,恢复时也是最简单的

②专用备份工具 mysqldump 或 mysqlhotcopy

  • mysqldump 常用的逻辑备份工具
  • mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

③启用二进制日志进行增量备份

  • 进行增量备份,需要刷新二进制日志
  • MySQL支持增量备份,进行增量备份时必须启用二进制日志。
  • 二进制日志文件为用户提供复制,对执行备份点后进行的数据库更改所需的信息进行恢复。
  • 如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。

④第三方工具备份

  • 免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup

三.MySQL完全备份和恢复

3.1完全备份概述

  • 是对整个数据库,数据库结构和文件结构的备份
  • 保存的是备份完成时刻的数据库
  • 是差异备份与增量备份的基础

优点:备份和恢复数据简单方便

缺点:数据存在大量的重复,占用了空间,备份和恢复的时间比较长

3.2数据库完全备份分类

①物理冷备份与恢复

  • 关闭MySQL数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换现有MySQL目录即可
  • 注意:替换目录要更改属主和属组

②mysqldump备份与恢复

  • MySQL自带的备份工具,可方便实现对MySQL的备份
  • 可以将指定的库、表导出为SQL 脚本
  • 使用命令mysq|导入备份的数据

四.MySQL增量备份和恢复

4.1增量备份概述

  • 要开启二进制日志功能
  • 二进制日志的三种记录格式:STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT

4.2数据库增量恢复方法

①一般恢复

  • 将所有备份的二进制日志内容全部恢复

②基于位置恢复

  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作
  • 发生错误节点之前的一个节点,上一次正确操作的位置点停止

③基于时间点恢复

  • 跳过某个发生错误的时间点实现数据恢复
  • 在错误时间点停止,在下一个正确时间点开始

 

五.总结

MySQL数据库需要去备份数据才能保障数据不会丢失

1.冷备份(需要关闭数据库,一般用不到,不如在做数据迁移的时候用)

2.温备份,完全备份有助于数据能够去恢复

3.一般在完全备份的基础上,每天去做增量备份,能减少空间的占用

标签:log,数据库,MySQL,mysql,日志,备份
From: https://www.cnblogs.com/suoluo212/p/16932964.html

相关文章

  • mysql 快速迁移百万数据 mysqldump
    从目标库导出数据C:\ProgramFiles\MySQL\MySQLServer8.0\bin>mysqldump-uroot-ptestdb_db>c:/testdb.dump导入目标库C:\ProgramFiles\MySQL\MySQLSe......
  • mysql5.6迁移报错记录
    一、说明服务器到期需要删除,将mysql5.6进行迁移二、步骤1、原库备份(有函数)mysqldump-uroot-p'123456'-A-R-E>/tmp/backupall.sqlscp到新数据库服务器2、新库搭建(忽略)m......
  • mysql case when then用法(转)
    mysqlcasewhenthen用法(转)原文:https://www.cnblogs.com/Richardzhu/p/3571670.htmlcase具有两种格式。简单case函数和case搜索函数。--简单case函数casesexwhe......
  • Docker 使用PXC搭建MySQL集群(MySQL:5.7.24)
    关于MySQL集群,常用的有两种模式:​​Replication集群架构(主从复制)​​​和​​PXC集群架构​​PXC集群方案所有节点都是可读可写的,Replication从节点不能写入,因为主从同步......
  • Docker swarm 安装MySQL集群
    这里直接采用的是5.7.24版本的MySQL初始化dockerswarm:dockerswarminit正常会显示当前服务器作为leader,然后打印出来加入此dockerswarm的token。我这里执行过了,使用d......
  • 【MySQL】多表查询、分组求和、并对和排序、取top n
    查漏补缺MySQL的相关知识​​1.有一个用户资金流水表(如上的sql代码),找出流水金额最多的前10个用户:​​​​2.查询分组后的最大值,最小值所在的整行记录或者分组后的topn行......
  • MySQL中关于金额数据进行运算求和/整数时精度丢失/清空表数据,自增从1开始
    关于MySQL的使用总结:1.金额字段类型为String时,进行求和运算2.查询DECIMAL类型数据小数点后精度缺失丢失为0的小数3.清空表的数据,id自增从1开始的方法1.金额字段类......
  • Mysql中的explain查看执行计划
    Mysql中的explain查看执行计划1、explain是什么?查看执行计划。2、怎么使用?explain+sql语句。3、执行计划包含的信息:id,select_type,table,type,possible_ke......
  • 云小课|基于华为云WAF的日志运维分析,构筑设备安全的城墙
    阅识风云是华为云信息大咖,擅长将复杂信息多元化呈现,其出品的一张图(云图说)、深入浅出的博文(云小课)或短视频(云视厅)总有一款能让您快速上手华为云。更多精彩内容​​请单......
  • 数据库面试题--Mysql
    MySQL1.什么是索引?2.索引是个什么样的数据结构呢?3.Hash索引和B+树索引有什么区别或者说优劣呢?4.在建立索引的时候,都有哪些需要考虑的因素呢?5.了解过哪些存储引......