Mysql存储引擎

Jessica小戴

于 2024-07-24 16:35:30 发布

阅读量371

点赞数 5

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_84592402/article/details/140666857

版权

Mysql存储引擎、备份恢复以及日志管理

一、Mys存储引擎

1、概念

存储引擎，是一种数据库存储数据的机制、索引的技巧、以及锁定水平，即存储的方式和存储的格式

存储引擎也属于mysql当中的组件，实际上操作的、执行的就是数据的读写I/O

2、分类

cat /etc/my.cnf

# 可以看到mysql的配置文件中出现
default-storage-engine=INNODB

innodb：是事务型速记存储引擎，支持ACID，支持行锁定，mysql5.5之后默认开始使用

MyISAM：是5.5之前默认的存储引擎，插入的速度和查询速度很快，但是不支持事务

Memory：内存型存储引擎，数据在写时都保存在内存当中，一旦重启所有数据全部消失

CSV：逗号分割数据的存储引擎，数据文件以.csv文件保存的，为了和execl表格兼容，保存的文件就是一个普通的文本文件，也不支持索引

3、innodb存储引擎

3.1 读写阻塞（锁表）和事务的隔离级别

3.2 能够高效的缓存数据支持多种类的索引

3.3 表的索引的类型默认是Btree

3.4 支持外键，支持全文索引

3.5 对硬件的资源要求比较高

3.6 行级锁定，会把行锁住，禁止操作，尤其是模糊查询

4、模糊查询

模糊查询：like 进行查询时，会进行全表扫描，在扫描的过程中会锁定整个表

如果没有创建索引的列，进行查询时，也会锁定整个表

使用的是索引列，锁定条件的行，行锁定

show engines;
# 可以查看当前默认的索引引擎

5、innodb的行锁和索引的关系

行锁是通过索引来实现的

如果没有索引，innodb会使用默认的隐藏索引来对记录进行加锁（加了索引就是锁行，不加索引就是锁表）

6、mysql和oracle操作命令区别

mysql操作命令运行了就是自动提交写入数据库

oracle操作命令运行后需要提交才能写入数据库，否则就是临时的，使用oracle一定要注意运行后提交

7、mysql死锁实验

# mysql1-7
# 在xy102库中创建表为student
create table student (
id int(5),
name char(10),
age int(3),
sex char(2)
);

alter table student add primary key(id);
# 为表student中的id添加索引
show index from student;
# 展示索引命令 ,会在navicat下的结果Index_type中出现BTREE信息，

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 -u root -p123456
use xy102;
select * from student;

begin;
delete from student where name = '小明';

# mysql
select * from student where name '小明';
update student set age=20 where name = '小明';

#终端操作
commit;

# mysql
update student set age=26 where name = '小明';
# 运行是会出现[SQl]update student set age = 26 where name = '小明'; 
# 说明表已经锁住，因为name不是索引，所以会锁住整个表，会显示在处理中，注意:这个不是报错，语法没有问题，说明此时有人在对表进行操作

# 只要点停止，又可以继续进行操作了
# 终端
commit
# 又可以继续进行操作了

# 当指定条件不是索引的时候，会锁住全表，当条件是索引行的时候，锁住索引行

# 死锁:事务互相等待对方资源，最后形成一个环路造成的
# 如果发生死锁，数据库会自动选择一个事务作为受害者，这个受害者的事务会被回滚，以解除死锁
# mysql会终止其中一个事务，但是不会回滚

# navicat
create table t1(id int primary key, name char(3), age int);
insert into t1 values(1,'aaa',22);
insert into t1 values(2,'bbb',23);
insert into t1 values(3,'aaa',24);
insert into t1 values(4,'bbb',25);
insert into t1 values(5,'ccc',26);
insert into t1 values(6,'zzz',27);

select * from student;

# 终端
select * from t1;
begin;
delete from t1 where id=5;

# navicat
begin;
select * from t1 where id-1 for update;

# 终端
delete from t1 where id =5；

#navicat
update t1 set name='asd' where id =5;
commit;

#终端
commit;
select * from t1;

#navicat
update t1 set name='asd' where id =5; 
commit;

#终端
commit;
select * from student;
# 此时1和5消失

# for update 排他锁，当一个事务操作未完成时，其他事务可以读取但是不能写入，写锁

# 如何避免死锁的情况出现
1、以固定的顺序访问表和行，
2、大事务尽量拆分成小事务
3、为表添加合理的索引

二、数据库的备份和恢复

数据库的备份和恢复以及日志管理（配置文件当中的设置）

1、备份和恢复

1.1 备份的目的：备灾。（在生产环境中，数据的安全性是非常重要的）

1.2 造成数据丢失的原因：程序出错、人为问题、磁盘故障

1.3 备份的分类：物理备份和逻辑备份

物理备份：对磁盘或者文件直接进行备份，又分为热备份和冷备份

冷备份：脱机备份，先把指定的程序关闭然后对资料进行备份

热备份：联机备份，不用关闭程序就可以对资料进行备份

逻辑备份：根据数据库文件当中保存的sql语句、表结构等等，以特定的格式和命令对文件的内容进行还原

逻辑备份属于热备份的一种，只能对表，库没了没有办法

主从复制可以恢复

1.4 全量备份的两种方式

物理备份（全量备份）：把数据库的内容整个一次性的备份（冷备份）

mysql自带的备份命令：也可以备份库，也可以备份库里面的表（命令：mysqldump）（热备份）

2、全量备份

2.1 物理备份

物理备份-----冷备份

终端1 mysql1-7 192.168.100.17

终端2 mysql2-8 192.168.100.18

# mysql1-7
create database xy103;

create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
sex char(10) not null,
hobby varchar(50),
primary key (id));

insert into info1 values(1,'user1','male','running');
insert into info1 values(2,'user2','female','singing');

# 终端1
systemctl stop mysqld

cd /usr/local/mysql/data
# mysql下的data是mysql的默认数据保存目录
# 如果需要修改在创建数据库的时候就要修改
# vim my.cnf  ##basedir是mysql的安装目录  ##datadir是mysql的保存数据目录

[root@localhost opt]# yum -y install xz
# 安装xz,xz是一个高效率的数据压缩工具
[root@localhost opt]# tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
#  mysql_all_$(date +%F)表示名称和显示当前的日期


# 终端2  
scp root@192.168.100.17:/opt/mysql_all_2024-07-19.tar.xz /opt/
# 注意mysql_all_2024-07-19.tar.xz和上面的解压文件后的文件保持一致
yes
输入密码:123           
# 此时查看/opt下出现mysql_all_2024-07-19.tar.xz

tar -xf mysql_all_2024-07-19.tar.xz
# 查看/opt下出现usr，查看/usr/local/mysql目录下出现data

cd /opt/usr/local/mysql   
# 此时查看出现data目录，ll查看一下权限是mysql

systemctl stop mysqld

cp -a /opt/usr/local/mysql/data /usr/local/mysql
# 因为上面已经远程传输过来data目录，如果一直重复yes的话，可以删除data目录，再-a保留权限复制
rm -rf /usr/local/mysql/data
# 注意路径!! /usr/local/mysql/data是mysql2-8默认存储路径,查看为空;/opt/usr/local/mysql/data,为刚刚scp过来的，查看有mysql1-7的表

systemctl restart mysqld
# 打开navicate，刷新mysql2-8可以看见里面出现mysql1-7传输过来的数据

###  注意此方法为冷备份，核心是一定要关闭mysql服务，

2.2 mysqldump

mysqldump命令（mysql自带的备份----热备份）

# 终端1
systemctl start mysqld
mysql -u root -p123456

# 终端1数据库
use xy102;
show tables;      # 出现两个我们上面创建的表student和t1、info1
exit

1、# 备份xy102库的文件
mysqldump -u root -p --databases xy102 > /opt/xy102.sql
#/opt/xy102.sql,前面的xy102为文件名可以自己定义，但是文件名的后缀必须以.sql为结尾
输入数据库的密码:123456
# 在/opt下查看，此时多了一个文件是xy102.sql，备份成功!!!
# 进入navicate删除xy102的数据库
# 备份还原，使用重定向追加
mysql -u root -p < /opt/xy102.sql
输入数据库的密码:123456
# 进入navicate，刷新mysql1-7下的sys，出现xy102以及库下面的表，还原成功

2、# 备份xy102和xy103库的文件
mysqldump -u root -p --databases xy102 xy103 > /opt/xy102_103.sql  
# 查看是否备份成功，进入navicate删除xy102和xy103的数据库
# 备份还原，使用重定向追加
mysql -u root -p < /opt/xy102_103.sql
# 进入navicate，刷新mysql1-7下的sys，出现xy102以及库下面的表，还原成功

3、# 备份全部的库
mysqldump -u root -p --all-databases > /opt/all.sql

mysql -u root -p < /opt/all.sql

4、# 如何备份库里面的表
mysqldump -u root -p xy102 t1 > /opt/xy102_t1.sql
mysql -u root -p xy102 < /opt/xy102_t1.sql

5、# 备份多个表
mysqldump -u root -p xy102 student t1 > /opt/st1.sql
mysql -u root -p xy102 < /opt/st1.sql

6、# 如何把备份表还原到指定库
mysql -u root -p xy103 < /opt/xy102_t1.sql
# 把上面xy102_t1.sql的备份表还原到xy103的库里面,把库名换成指定库名即可

3、增量备份

如何进行增量备份？

1、2、3、4…

前提：开启二进制日志的功能

binlog 逻辑备份，会生成一个文件，这个文件里面包含了sql语句，要使用特定方式和语句才能够恢复

# 终端1
vim /etc/my.cnf
# 在server-id = 1 下添加
log-bin=mysql-bin
# 表示开启二进制日志功能
binlog_format=MIXED
# 记录二进制日志的文件格式
# (STATEMENT 基于sql语句：只是记录用户操作的sql语句，高并发的情况下，记录操作的sql语句的顺序可能会出错，导致恢复数据时，就会有丢失或者误差，但是效率高
# ROW 基于行：记录每一行的数据，准确，高并发也不会出错，但是恢复的效率低
# MIXED 混合模式：正常情况下，使用statement，高并发使用row。
# MIXED如何判断？智能判断)

wq!
systemctl restart mysqld
cd /usr/local/mysql/data
# 查看此时会形成一个mysql-bin.000001的文件，表示二进制日志文件开启成功

# mysql1-7
CREATE TABLE `info2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `sex` char(10) NOT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

insert into info2 values(1,'user1','male','running');
insert into info2 values(2,'user2','female','singing');
insert into info2 values(3,'user1','male','running');
insert into info2 values(4,'user2','female','singing');
insert into info2 values(5,'user1','male','running');
insert into info2 values(6,'user2','female','singing');
insert into info2 values(7,'user1','male','running');
insert into info2 values(8,'user2','female','singing');

select * from info2;

# 终端1
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
# 可以在终端查看刚刚创建的info2的信息

1、模拟恢复语句
# 到mysql1-7删除xy102库下的info2表
# 终端1
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p
# 为什么没有还原?
# 在mysql1-7中删除xy102中的info2表的操作记录在日志文件中，操作上述还原步骤会一并把删除操作再执行一遍，可以使用查看操作，可以看见删除记录

# 此时把mysql1-7中，info2再操作一遍，生成info2

mysqladmin -u root -p flush-logs
# 查看，在/usr/local/mysql/data生成mysql-bin.000002
# 此时在mysql1-7中删除info2，这个时候删除记录生成在mysql-bin.000002中，mysql-bin.000001中只有生成记录
# 此时操作以下命令，因为mysql-bin.000001中是生成命令，所以info2恢复
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p

注意:在二进制日志文件中，'# at 数字' 表示位置
1.1 通过位置恢复
mysqlbinlog --no-defaults --start-position='数字' mysql-bin.000001 | mysql -u root -p
# 表示恢复这个数字开始到结束的数据

1.2 恢复指定位置
mysqlbinlog --no-defaults --start-position='数字' --stop-position='数字' mysql-bin.000001 | mysql -u root -p
# 如果比指定数据少，往下移动相应的顺序即可

1.3 根据时间恢复数据
mysqlbinlog --no-defaults --start-datatime='时间' --stop-datatime='时间' mysql-bin.000001 | mysql -u root -p
# 注意时间需要写完整格式，如二进制日志中记录:'240719 13:48:16' 则需要写成'2024-7-19 13:48:16'

# 注意:如果删除mysql-bin.000002，刷新后则会生成mysql-bin.000002
# 如果删除的是:mysql-bin.000004、mysql-bin.000005,刷新后生成的则是4开始

三、mysql配置文件的日志格式

vim /etc/my.cnf

general_log=ON
# 开启通用查询日志
general_log_file=/usr/local/mysql/data/mtsql_general.log
# 查询日志的保存位置
log-error=/usr/local/mysql/data/mysql_error.log
# 错误日志的保存位置，错误日志默认是开启的
slow_query_log=ON
# 开启慢查询日志
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
# 设定慢查询日志的位置
long_query_time=5
# 默认的慢查询时间是10秒，超过5秒的记录都会保存
wq!
systemctl restart mysqld

cd /usr/local/mysql/data
# ls查看一下,上面的日志都生成成功了

tail -f mysql_general.log
# 看一下查询日志，到mysql1-7中,运行seletct * form info2;则可查看记录

慢日志可以用行锁锁住操作看看是否会生成