mysql的事务和存储引擎+备份

ops/2024/12/21 9:52:05/

mysql的事务和存储引擎+备份

  • 一. mysql的事务
    • 1.1 mysgl支持事务四种隔离级别
    • 1.2 事务控制语句
    • 1.3 行锁和死锁
      • 1.3.1 行锁
      • 1.3.2 死锁
      • 1.3.3 如何避免死锁的发生
  • 二. msyql的备份和还原以及日志管理
    • 2.1 数据库备份的分类
    • 2.2 备份策略
      • 2.2.1 物理冷备份（全量）
      • 2.2.2 mysqldump的备份和恢复（热备份）
      • 2.2.3 对于数据的备份和恢复
      • 2.2.4 基于位置的恢复
      • 2.2.5 基于时间点恢复
    • 2.3 日志管理

mysql_2">一. mysql的事务

mysql的存储引擎innodb自带了事务的特性

  • 原子性
  • 一致性
  • 持久性
  • 隔离性

隔离性：每个事物执行的过程是独立的，互相不能被干扰。并发事务之间是独立的

  1. 脏读
    A事务在访问数据并且修改了数据，但是修改的结果没有提交到数据库
    B事务也访问了这条数据，而且看到了未提交的结果
  2. 不可重复读
    在一个事务之内，多次读同一数据。
    这A事务没有结束时，另一个B事务也访问该数据，由于在A事务中两次读取数据，在中间B事务修改了数据，导致A事务两次查询的结果是不一致的，这就是不可重复读
  3. 幻读
    A事务对数据进行了修改，B事务也对数据进行了修改，A事务发现数据还是有没有修改的数据，产生了幻觉。
    事务的隔离级别:

1.1 mysgl支持事务四种隔离级别

  1. 未提交读，允许脏读，可以看到未提交的修改 read uncommitted
  2. 提交读，read committed 提交读，只允许看到修改提交之后的数据
  3. 可重复读:mysgl的默认隔离级别，一个事务，在执行两次select语句，保证得到相同的结果
    Repeatable read
  4. 串行读:后一个事务必须等待前一个完成之后才能继续，在这个过程中表会完全锁住，读写都会阻塞。
set global transaction isolation level Read Uncommitted;
set session transaction isolation level Read Uncommitted;#刷新生效
mysql> flush privileges;show session variables like'%isolation%';

1.2 事务控制语句

  • BEGIN：显示开始一个事务
  • commit：提交事务，所有的修改都是永久性的。
  • rollback：对事务进行回滚，返回到上一次的操作，只能回滚正在进行未提交的修改。
  • savepoint 名称：在事务当中创建一个回滚点，回滚到指定的位置，一个事务中可以有多个回滚点。
    savepoint 在一个事务当中是一个临时的标记，在事务执行过程中的一个回滚点，多个回滚点，是按照顺序进行的，前面的回滚点一旦执行，后续的回滚点就回失效。
#创建表
create table cost (
id int(5) PRIMARY key,
name char(10),
money int(10)
);
insert into cost values(1,"test1",1000)
insert into cost values(2,"test2",1000)
select * from cost;

在这里插入图片描述

begin;   #开始一个事务
UPDATE cost set money=money+100 where id= 1;

在这里插入图片描述

#COMMIT;  
#提交后无法回滚
ROLLBACK;   #回滚

在这里插入图片描述

#savepoint回滚
begin;
update cost set money=money+200 where id= 1;
savepoint s1;
UPDATE cost set money=money+200 where id= 2;
savepoint s2;
insert into cost values(3,"test3",1000);
select * from cost;rollback to s1;

在这里插入图片描述

1.3 行锁和死锁

1.3.1 行锁

innodb通过给索引加锁来实现行级的锁定。

  • 如果没有索引，会使用隐藏的聚簇索引来是来实现锁定。
  • 如果A事务对索引字段进行操作，就会触发聚簇索引，锁定整行，B事务对这个行进行的操作就会被锁住。
create table if not exists  student(id int(5) PRIMARY KEY,name char(8),age int(3),sex char(2) 
);
alter table student add index name index(name);insert into student values(1,'小明',18,'男');
insert into student values(2,'小红',19,'女');
insert into student values(3,'小刚',19,'男');
insert into student values(4,'小绿',17,'女');
insert into student values(5,'小黑',20,'男');select * from student;#另外一个终端
mysql> begin;
mysql> delete from student where name="小明";update student set age =20 where id = l;

在这里插入图片描述

mysql> commit;  #提交则会立即完成

在这里插入图片描述

1.3.2 死锁

两个事物相互等待对方的资源，形成了一个环路导致的。

#终端
mysql> begin;
mysql> delete from student where id = 5;
#触发行锁#客户端
begin;
select * from student where id=1 for update;
for update：设置一个排他锁，在当前事务未提交前，禁止事务的写入和更新操作。#终端
mysql> delete from student where id = 1;
#触发排他锁#客户端
update student set name =  'abc' where id = 5;

在这里插入图片描述

死锁发生会自定选择一个事务当中的语句进行执行，直接终止其中的一个事务，回滚另外一个事务，以解除死锁（随机的）
在这里插入图片描述

1.3.3 如何避免死锁的发生

  1. 按照固定的顺序对表和行进行访问
  2. 大事务拆成小事务，业务允许的情况下
  3. 调整事务的默认隔离级别，如果业务允许，隔离级别越低越好。
  4. 要给表添加合适的索引(不是经常需要访问的字段，最好不要设置索引)

二. msyql的备份和还原以及日志管理

备份的目的就是灾难恢复

2.1 数据库备份的分类

1、物理备份
冷备份：关闭数据库进行备份的，全量备份。

2、逻辑备份:
热备份：数据库可以正常运行，不影响数据库的操作，在这个条件可以实现备份和恢复

2.2 备份策略

  • 全量备份
  • 增量备份
  • 冷备份只能进行全量备份，逻辑备份既可以实现全量，也可以实现增量。
  • mysqldump，常用的逻辑备份的工具，mysgl自带的。

2.2.1 物理冷备份（全量）

/usr/local/mysql/data

#压缩
tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压opt下的压缩包
cd /opt/usr/local/mysql
cp -a data/ /usr/local/mysql/

mysqldump_195">2.2.2 mysqldump的备份和恢复（热备份）

musqldump是针对库和表的全量和增量，是热备份。
把备份的库和表，以文件的形式导出，再从导出的文件进行恢复。

#库的备份，多个库之间用空格隔开
mysqldump -u root -p123456 --databasess 库名 另一个库 > /opt/01.sql#库的恢复
mysql -u root -p123456 < /opt/01.sql

#表的备份，多个表之间用空格隔开
mysqldump -u root -p123456 库名 表名 另一个表 > /opt/03.sql
#表的恢复
mysql -u root -p123456 库名 < /opt/03.sql

#不进入操作
mysql -u root -p123456 -e 'select * from 库名.表名'

2.2.3 对于数据的备份和恢复

全量备份，热备份
使用mysql的二进制日志的方式来是备份，对数据的全量或者是增量

先要开启二进制日志的功能

vim /etc/my.cnf
#添加
log-bin=mysql-bin
#开启二进制日志的功能，二进制文件的名称mysql-bin.000001
binlog_format=MIXED
#二进制日志的记录格式：1、ROW  基于行记录每一行的数据，准确，但是恢复效率的低2、STATEMENT 基于sql语句按照顺序对sql语句进行记录，高并发的情况下，sql的记录顺可能会出错，可能会导致数据的记录有偏差，虽然恢复的效率比较高3、MIXED  混合正常情况下使用STATEMENT，高并发自动切换ROW的记录方式。systemctl restart mysqld#二进制文件存放在data中
cd /usr/local/mysql/data
#解析查看日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001#恢复所有执行过的操作，包括错误的
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -u root -p123456
#所以需要断点操作#二进制文件的刷新（断点）
mysqladmin -u root -p123456 flash-logs#断点是按天来执行的，可以按天对数据库进行增量的备份。

2.2.4 基于位置的恢复

mysqlbinlog --no-defaults --start-position='1338' mysql-bin.000005 | mysql -u root -p123456
从指定位置开始恢复数据，一直恢复到文件的结尾。mysqlbinlog --no-defaults --stop-position='1338' mysql-bin.000005 | mysql -u root -p123456
从开始恢复到结束位置mysqlbinlog --no-defaults --start-position='2364' --stop-position='3235' mysql-bin.000005 | mysql -u root -p123456
从指定位置开始，到指定的位置结束

2.2.5 基于时间点恢复

#同步时间
ntpdate ntp.aliyun.com mysqlbinlog --no-defaults --start-datetime='2024-12-20 13:47:17' mysql-bin.000005 | mysql -u root -p123456
从指定时间开始恢复数据，一直恢复到文件的结尾。mysqlbinlog --no-defaults --stop-datetime='2024-12-20 13:47:38' mysql-bin.000005 | mysql -u root -p123456
从开始恢复到结束时间mysqlbinlog --no-defaults --start-datetime='2024-12-20 13:47:17' --stop-datetime='2024-12-20 13:47:38' mysql-bin.000005 | mysql -u root -p123456
从指定时间开始，到指定的时间结束

2.3 日志管理

vim /etc/my.cnfog-error=/usr/local/mysql/date/mysql_error.log
#记录mysql在启动，停止或者是运行时，产生错误的日志
general_log=ON general_log_file=/usr/local/mysql/data/mysql_general.log
#开启mysql在使用过程中的记录日志
slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log 
long_query_time=5
#开启慢查询日志，用来记录所有查询的时间超过long_query_time=5的记录，查询的执行时间超过5秒钟就会记录。
不注明，默认是10秒

http://www.ppmy.cn/ops/143725.html

相关文章

番外篇 Git 的原理与使用

番外篇 Git 的原理与使用

PS&#xff1a;本篇是个长篇&#xff0c;但是阅读完&#xff0c;可以基本了解 Git 在实际开发中的绝大部分常用操作。 前言&#xff1a;什么是Git 我们在日常工作 / 学习时&#xff0c;对于某些文档 / 代码&#xff0c;可能会存在多个版本需要维护&#xff0c;但是随着版本的…
阅读更多...
Mac iOS、Android、Flutter、React Native开发环境配置

Mac iOS、Android、Flutter、React Native开发环境配置

1.安装XCode https://apps.apple.com/cn/app/xcode/id497799835?mt12 2.安装Android Studio https://developer.android.google.cn/studio 3.安装brew 参考地址&#xff1a;https://www.jianshu.com/p/22122a1d4474 /bin/zsh -c "$(curl -fsSL https://gitee.com/cunk…
阅读更多...
abc 383 C (bfs 最短路 )D(唯一分解定理，欧拉筛)

abc 383 C (bfs 最短路 )D(唯一分解定理，欧拉筛)

C 题&#xff1a; 首先暴力的想&#xff0c;对于每一个加湿器的位置去 上下左右扩展是 nm 的复杂度 。最多会有 nm 个加湿器。所以复杂度到达了n^3 。肯定超时了。 我们可以发现 对于一个点 会标记很多次&#xff0c;这回导致超时。 可以采用类似 bfs 求最短路的形式&#xff…
阅读更多...
《Vue3实战教程》13：Vue3侦听器

《Vue3实战教程》13：Vue3侦听器

如果您有疑问&#xff0c;请观看视频教程《Vue3实战教程》 侦听器​ 基本示例​ 计算属性允许我们声明性地计算衍生值。然而在有些情况下&#xff0c;我们需要在状态变化时执行一些“副作用”&#xff1a;例如更改 DOM&#xff0c;或是根据异步操作的结果去修改另一处的状态。…
阅读更多...
Postman前置脚本使用案例

Postman前置脚本使用案例

背景 由于我们的服务接口需要进行验签&#xff0c;每次通过Postman手动调用接口时都显得颇为繁琐。为了简化这一过程&#xff0c;我们可以充分利用Postman提供的脚本功能&#xff0c;自动为接口请求生成所需的签名。 案例 在Scripts中写生成接口签名的脚本。 以下是一个实用…
阅读更多...
Spark-Streaming receiver模式源码解析

Spark-Streaming receiver模式源码解析

一、上下文 《Spark-Streaming初识》博客中我们用NetworkWordCount例子大致了解了Spark-Streaming receiver模式的运行。下面我们就通过该代码进行源码分析&#xff0c;深入了解其原理。 二、构建StreamingContext 它是Spark Streaming功能的主要入口点。并提供了从各种输入…
阅读更多...
Day28 C++ 命名空间

Day28 C++ 命名空间

2024.12.20 C 命名空间 假设这样一种情况&#xff0c;当一个班上有两个名叫 Zara 的学生时&#xff0c;为了明确区分它们&#xff0c;我们在使用名字之外&#xff0c;不得不使用一些额外的信息&#xff0c;比如他们的家庭住址&#xff0c;或者他们父母的名字等等。 同样的情况…
阅读更多...
【Spring】Spring的模块架构与生态圈—数据访问与集成(JDBC、ORM、Transactions)

【Spring】Spring的模块架构与生态圈—数据访问与集成(JDBC、ORM、Transactions)

在企业级应用中&#xff0c;数据的存储和访问是核心功能之一。Java开发语言通过Spring框架提供了多种方式来实现数据访问和集成&#xff0c;包括JDBC&#xff08;Java Database Connectivity&#xff09;、ORM&#xff08;对象关系映射&#xff09;以及事务管理。这些技术的有效…
阅读更多...
最新文章

Copyright @ 2022~2023