【Mysql】事务日志备份存储引擎

whr888123

于 2022-12-02 20:12:50 发布

阅读量701

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_55015185/article/details/128153424

版权

一.事务

1.概念/特性

概念：一个不可分割的操作序列
特性：原子性（无法分割，始终如一），隔离性（独立，保证数据安全），一致性（事务的目的，除了过程会不一样，始末都一样），持久性（最终效果，保存即不变）

2.隔离级别

在这里插入图片描述
脏读：一个事务可以看到其他事务未提交的修改
不可重复读：第一个事务未结束时，恰巧别的事务修改了对应的数据，那么第一个事务刷新再读取的时候，数据就会变为被修改后的数据（可重复读：确保一个事务中执行的select语句都能得到相同结果）
幻读：第一个事务修改所有数据的同时，恰巧别的事务添加了新的事务，此时第一个事务并不会修改别的事务刚添加的数据
丢失更新：第一个事务读取数据修改的同时，第二个事务也在打开修改数据，此时第二个事务先保存，第一个事务后保存，那么第一个事务就会把第二个事务修改的数据覆盖掉，等同于第二个事务没有进行任何操作
注：mysql默认的事务处理级别是repeatable read （可重复读）, 而Oracle和SQL Server是 read committed（已提交读）。

3.命令

在这里插入图片描述

二.日志

日志是维护中非常重要的存在，通过日志，我们可以快速得到自己想要的东西，比如错误日志产生，效率低下，数据恢复
MySQL 的日志默认保存位置为 /usr/local/mysql/data

1.日志分类

mysql的日志存储路径默认为：/usr/local/mysql/data，默认配置文件为：/etc/my.cnf配置文件中的[mysqld]子配置中
在这里插入图片描述

2.查看是否开启日志

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; #在数据库中设置开启慢查询的方法

三.备份

数据备份的重要性
1、备份的主要目的是灾难恢复
2、在生产环境中，数据的安全性至关重要
3、任何数据的丢失都可能产生严重的后果
4、造成数据丢失的原因

1.数据备份分类

1、物理备份

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

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

２、逻辑备份

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

2.完全备份

特点：备份/恢复时间长，备份数据全面（对数据库进行完整的备份与恢复），多次备份会产生大量的多余数据，只需要完全备份一次初始数据即可

1.物理冷备份与恢复

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

#备份
systemctl stop mysqld													#关闭mysql数据库服务
tar zcf /mnt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data			#压缩备份mysql数据并重命名为.tar.gz结尾

#恢复
tar zxf mysql_all-2022-06-05.tar.gz -C /mnt								#解压数据压缩包
\cp -rf ./data /usr/local/mysql/										#将解压后的数据包移动/复制过去即可
systemctl start mysqld													#启动mysql数据库服务

2.mysqldump备份与恢复

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

mysqldump -u root -p密码 --databases 库 >备份路径								#备份库
mysqldump -u root -p密码 [-d] 库名 [表名1] [表名2] >备份路径    					#备份表（-d表示只备份数据表结构）

查看备份文件：grep -v "^--" 备份路径|grep -v "^-"|grep -v "^$"					#通过过滤查看备份的内容

mysql -u root -p密码 <备份文件													#恢复库
mysqldump -u root -p密码 库 <备份文件											#恢复表

3.增量备份

特点：通过二进制日志间接实现（flush logs刷新日志），没有冗余、备份时间短、恢复时间长

1.种类

1.一般恢复

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

2.基于位置恢复

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

3.基于时间点恢复

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

2.记录格式

STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大
MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。

3.备份命令

mysqladmin -u root -p密码 flush-logs									#备份日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v 备份路径		#查看备份

4.需要关注的部分

1、at ：开始的位置点
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30
4、SQL语句

5.恢复命令

在这里插入图片描述

通过结束位置或者时间进行基于位置和时间的恢复

四.存储引擎

存储引擎概念介绍
MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
在这里插入图片描述

1.MyISAM

MyISAM适用的生产场景
公司业务不需要事务的支持
单方面读取或写入数据比较多的业务
MyISAM存储引擎数据读写都比较频繁场景不适合
使用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务一致性要求不是非常高的业务
服务器硬件资源相对比较差

MyIsam：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

1.存储方式

在这里插入图片描述

2.应用场景

1.单独读取/写入较多的场景
2.不需要事务支持的场景

2.innodb

1.InnoDB特点

支持事务，支持4个事务隔离级别（数据不一致问题）
MySQL从5.5.5版本开始，默认的存储引擎为 InnoDB
5.5 之前是myisam （isam）默认

读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表与主键以簇的方式存储
支持分区、表空间，类似oracle数据库（5.5 ——》5.6 和5.7 第三代数据库8.0后版本）
支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

2.应用场景

需要事务支持
需要行级锁定
业务数据更新频繁
业务数据一致性要求高

3.死锁

死锁原因
不同事务相互等待对方资源，最后形成环路造成死锁

1.表行锁

MyISAM :表级锁定
innodb :行级锁定
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

2.查看及修改存储引擎

查看系统支持的存储引擎
show engines;

查看表使用的存储引擎
show table status from 库名 where name='表名'\G；

修改存储引擎
方法一：通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;

通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

systemctl restart mysqld.service
修改完记得重启mysql服务
#注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

方法三：通过 create table 创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;