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

一. 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/

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.cnf

og-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秒