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

embedded/2024/12/22 23:39:53/

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/embedded/147931.html

相关文章

RabbitMQ消息队列的笔记

RabbitMQ消息队列的笔记

Rabbit与Java相结合 引入依赖 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-amqp</artifactId> </dependency> 在配置文件中编写关于rabbitmq的配置 rabbitmq:host: 192.168.190.132 /…
阅读更多...
汇编语言下的移位指令和串传送指令

汇编语言下的移位指令和串传送指令

移位指令 SHL&#xff08;Shift Left Logical&#xff09;&#xff1a;逻辑左移。SHR&#xff08;Shift Right Logical&#xff09;&#xff1a;逻辑右移。SAL&#xff08;Shift Arithmetic Left&#xff09;&#xff1a;算术左移&#xff08;与SHL相同&#xff09;。SAR&…
阅读更多...
Java重要面试名词整理（一）：性能调优

Java重要面试名词整理（一）：性能调优

文章目录 MySQL篇联合索引最左前缀法则索引下推并发事务处理带来的问题事务隔离级别Read Uncommitted&#xff08;读取未提交内容&#xff09;Read Committed&#xff08;读取提交内容 RC&#xff09;Repeatable Read&#xff08;可重读 RR&#xff09;Serializable&#xff08…
阅读更多...
Onvif服务端开发

Onvif服务端开发

实现了Onvif服务端的设备搜索和RTSP流的功能。用 ONVIF Device Manager 测试工具可以成功搜索到设备和获取到RTSP流&#xff0c;有的路由器可能不支持239.255.255.250组播&#xff0c;我一开始用的电信的那种光猫路由器二合一的&#xff0c;一直搜不到设备&#xff0c;后面用So…
阅读更多...
PyTorch实现的猫狗图像分类项

PyTorch实现的猫狗图像分类项

猫狗图像分类项目 这是一个使用PyTorch实现的猫狗图像分类项目。 项目结构 model.py: 定义了CNN模型结构train.py: 训练模型的脚本predict.py: 使用训练好的模型进行预测requirements.txt: 项目依赖 环境配置 创建虚拟环境&#xff08;推荐&#xff09; python -m venv v…
阅读更多...
C# 6.0 连接elasticsearch数据库

C# 6.0 连接elasticsearch数据库

在 C# 6.0 中连接 Elasticsearch 数据库，您可以使用官方的 Elasticsearch 客户端库 NEST。NEST 是一个高性能的 .NET 客户端，用于与 Elasticsearch 进行交互。以下是一个详细的步骤指南，帮助您在 C# 6.0 项目中连接和操作 Elasticsearch。 1. 安装 NEST 包 首先，您需要在您…
阅读更多...
Fgui世界坐标转ui坐标的问题

Fgui世界坐标转ui坐标的问题

在做玩家与3d物体交互的时候遇到一个问题&#xff0c;就是3d物体的世界坐标转换成Fgui的UI坐标&#xff0c;会有一点问题&#xff0c;在fgui的官方文档中是这么描述一个3d物体的世界坐标转换为fgui的ui坐标是这么描述的 这个应该是一个比较普遍的方案&#xff0c;在我的实际项目…
阅读更多...
【线性代数】理解矩阵乘法的意义（点乘）

【线性代数】理解矩阵乘法的意义（点乘）

刚接触线性代数时&#xff0c;很不理解矩阵乘法的计算规则&#xff0c;为什么规则定义的看起来那么有规律却又莫名其妙&#xff0c;现在参考了一些资料&#xff0c;回过头重新总结下个人对矩阵乘法的理解&#xff08;严格来说是点乘&#xff09;。 理解矩阵和矩阵的乘法&#x…
阅读更多...
最新文章

Copyright @ 2022~2023