MySQL数据库系列(七)-------日志管理、备份与恢复

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 的日志默认保存位置为/usr/local/mysql/data MySQL 的日志配置文件为 /etc/my.cnf,里面有个[mysqld]项。

一、MySQL日志管理

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

2345_image_file_copy_77.jpg

1.1 日志的分类

1.1.1错误日志

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

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

1.1.2 通用查询日志

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

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

1.1.3 二进制日志

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

vim /etc/my.cnf
#也可以 log_bin=mysql-bin
log-bin=mysql-bin 

1.1.4 慢查询日志

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

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

1.1.5 配置日志

1. #修改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
2. #重新mysql服务
systemctl restart mysqld.service

1.修改my.cnf配置文件  注意: mysql.sock的路径 根据本机而定

2345_image_file_copy_78.jpg

2.重新mysql服务

systemctl restart mysqld.service

1.2 日志的查询

#登入mysql
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; 

2345_image_file_copy_79.jpg

2345_image_file_copy_80.jpg

二、MySQL 完全备份与恢复

2.1 数据备份的重要性

备份的主要目的是灾难恢复

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

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

2.2 造成数据丢失的原因

程序错误

人为操作错误.

运算错误

磁盘故障

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

2.3 数据库备份的分类

2.3.1 从物理和逻辑角度分

①物理备份

        对数据库操作系统的物理文件(如数据文件、日志文件等)的备份

物理备份的方法 :

      冷备份(脱机备份) :是在关闭数据库的时候进行的;

      热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件;

     温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。

②逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份

2.4 数据库的备份策略

2345_image_file_copy_81.jpg

1)完全备份:每次对数据库进行完整的备份

是对整个数据库、数据库结构和文件结构的备份

保存的是备份完成时刻的数据库

是差异备份与增量备份的基础

优点:备份与恢复操作简单方便 缺点:数据存在大量的重复、占用大量的备份空间及备份与恢复时间长

2)差异备份:备份自从上次完全备份之后被修改过的文件

3)增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

use school;
create table if not exists info(
id int(4) not null auto_increment,
name varchar(10) not null,
genter char(10) not null,
hobby varchar(50),
primary key (id));
insert into info  values(1,'yxp','male','sing');
insert into info  values(2,'dhc','female','dance');

2.5 MySQL 完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件:

  • db.opt(表属性文件)
  • 表名.frm(表结构文件)
  • 表名.ibd(表数据文件)。

模拟环境如下:在school库中有info表

2345_image_file_copy_82.jpg

2.5.1 物理冷备份与恢复

  • 备份时数据库处于关闭状态,直接打包数据库文件
  • 备份速度快,恢复时也是最简单的
1. #关闭mysql,备份data目录
systemctl stop mysqld
yum -y install xz
cd /usr/local/mysql
#压缩备份data目录
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz data/
2. #登录mysql,删除school库 
systemctl start mysqld.service 
mysql -u root -p123123
3. #解压之前备份的数据库data目录,不用删除原目录,会自动替换
cd /opt
ls
cd /usr/local/mysql
tar Jxvf /opt/mysql_all_2021-11-28.tar.xz -C ./
4. #重启服务查看被删除的库 

1)关闭mysql,备份data目录

2345_image_file_copy_83.jpg

2)登录mysql,删除class库

2345_image_file_copy_84.jpg

 3)解压之前备份的数据库data目录,不用删除原目录,会自动替换

2345_image_file_copy_85.jpg

2345_image_file_copy_86.jpg

2345_image_file_copy_87.jpg

2345_image_file_copy_88.jpg

2.5.2 专用备份工具mydump 和mysqlhotocopy

  • mysqldump常用的逻辑备份工具
  • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
  • 1) 完全备份一个或多个完整的库(包括其中所有的表)
#导出的就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql

示例: 备份单个和多个库

#备份单个数据库
mysqldump -uroot -p --databases class > /opt/mysql_bak/school.sql
#备份多个数据库
mysqldump -uroot -p --databases class,stu > /opt/mysql_bak/school_info1.sql

2)完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

示例:备份所有库

mysqldump -uroot -p --all-databases > /opt/mysql_bak/all.sql
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
21天前
|
SQL 运维 关系型数据库
深入探讨MySQL的二进制日志(binlog)选项
总结而言,对MySQL binlogs深度理解并妥善配置对数据库运维管理至关重要;它不仅关系到系统性能优化也是实现高可靠性架构设计必须考虑因素之一。通过精心规划与周密部署可以使得该机能充分发挥作用而避免潜在风险带来影响。
56 6
|
21天前
|
缓存 Java 应用服务中间件
Spring Boot配置优化:Tomcat+数据库+缓存+日志,全场景教程
本文详解Spring Boot十大核心配置优化技巧,涵盖Tomcat连接池、数据库连接池、Jackson时区、日志管理、缓存策略、异步线程池等关键配置,结合代码示例与通俗解释,助你轻松掌握高并发场景下的性能调优方法,适用于实际项目落地。
219 4
|
2月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
161 10
|
4月前
|
存储 关系型数据库 MySQL
【赵渝强老师】使用select...into outfile语句备份MySQL
本文介绍了MySQL中使用`SELECT...INTO OUTFILE`语句将表数据导出为文本文件的方法。通过示例演示了如何备份员工表(emp)的数据,包括创建存储目录、设置权限、配置参数`secure_file_priv`以及解决相关错误的过程。字段分隔符和行终止符可自定义,确保数据格式符合需求。最后展示了备份文件的内容,验证操作成功。
277 36
|
2月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL数据库的WAL日志与数据写入的过程
PostgreSQL中的WAL(预写日志)是保证数据完整性的关键技术。在数据修改前,系统会先将日志写入WAL,确保宕机时可通过日志恢复数据。它减少了磁盘I/O,提升了性能,并支持手动切换日志文件。WAL文件默认存储在pg_wal目录下,采用16进制命名规则。此外,PostgreSQL提供pg_waldump工具解析日志内容。
177 0
|
4月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mysqldump备份MySQL
本文介绍了 MySQL 自带的逻辑备份工具 mysqldump 的使用方法。通过 mysqldump,可以将数据库中的数据转换为对应的 SQL 插入语句,便于备份和还原。文章详细说明了如何备份所有数据库、指定数据库及特定表,排除某些表不备份的操作,以及删除数据库后如何通过备份文件恢复数据。同时提供了视频讲解和具体命令示例,帮助用户更好地理解和应用该工具。
182 5
|
5月前
|
SQL 监控 关系型数据库
MySQL日志分析:binlog、redolog、undolog三大日志的深度探讨。
数据库管理其实和写小说一样,需要规划,需要修订,也需要有能力回滚。理解这些日志的作用与优化,就像把握写作工具的使用与运用,为我们的数据库保驾护航。
214 23
|
4月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mydumper备份MySQL
本文介绍了使用mydumper工具进行MySQL数据库备份与恢复的操作方法。相比单线程工作的mysqldump,mydumper支持多线程,速度提升可达10倍。其功能包括事务性表快照、快速压缩、导出binlog等,并提供详细的参数说明和操作步骤。文章通过实例演示了安装mydumper、创建存储目录、全库备份、指定数据库及表备份、删除数据库以及使用myloader恢复数据的完整流程,并附带视频讲解,帮助用户更好地理解和应用该工具。
119 0
|
6月前
|
存储 关系型数据库 MySQL
利用Cron表达式实现MySQL数据库的定时备份
以上就是如何使用Cron表达式和mysqldump命令实现MySQL数据库的定时备份。这种方法的优点是简单易用,而且可以根据需要定制备份的时间和频率。但是,它也有一些限制,例如,它不能备份MySQL服务器的配置文件和用户账户信息,也不能实现增量备份。如果需要更复杂的备份策略,可能需要使用专门的备份工具或服务。
137 15

推荐镜像

更多