mysql存储引擎

mysql存储引擎

存储引擎概念

存储引擎就是一种数据库存储数据的机制,索引的技巧,以及锁定水平.

存储的方式和存储的格式

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

mysql的存储引擎的分类

vim /etc/my.cnf
default-storage-engine=innodb
#mysql5.5之后默认开始使用innodb事务型速记存储引擎,支持acid(事务),支持行锁定


#5.5之前默认的存储引擎,插入的速度和查询速度很快,但是不支持事务.
#memory:内存型存储引擎索引,数据在写时都保存在内存当中,一旦重启所有数据全部消失.
#csv,逗号分割数据存储引擎,数据文件.csv文件保存的为了方便execl表格保存,保存的文件就是一个普通的文本文件,没有加密,也不支持索引.

innodb

1.读写阻塞(锁表)和事务的隔离级别.

2.能高效的缓存数据支持多种类的索引.

3.表的索引类型默认是btree

4.支持外键,支持全文索引

5.对硬件的资源要求比较高

6.行级锁定,会把行锁住,禁止操作

#模糊查询:like进行查询时,会进行全表扫描,在扫描的过程中,会锁定整个表,只能查

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

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

#工具
#查看存储引擎
show engines;
#更改存储引擎最直接的方法就是改配置文件,vim /etc/my.cnf

innodb行锁和索引的关系

行锁是通过索引来实现的.

如果没有索引,innodb会使用默认的隐藏索引来对记录进行加锁

加了索引就是锁行,不加索引就是锁表.

show engines;

create database xy102;

create table student (
	id int(5),
	name char(10),
	age int(3),
	sex char(2)
);

alter table student add primary key(id);

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,'男');

show index from student;


终端里
mysql -u root -p123456
use xy102;
begin;
delete from student where name='小明';

#commit;
工具里
update student set age=26 where name='小明'; 

#mysql默认就是自动提交写入

#oracle是提交才能写入,相当于sed 和sed -i 

当指定条件不是索引的时候,会锁住全表.

当指定条件是索引的时候,只会锁住单独的那一行.

死锁:事务相互等待对方的资源,最后形成一个环路造成的.

如果发生了死锁,数据库会自动选择一个事务做为受害者,这个受害者会回滚该事务以解除这个死锁.

mysql则会终止其中一个数据,但是不会回滚

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

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 t1;

终端
begin;
delete from t1 where id =5;

工具
begin;
select * from t1 where id =1 for update;
update t1 set name='asd' where id=5;

如何避免死锁的情况出现

1.以固定的顺序访问表和行

2.把大事务尽量拆分成小事务,一条语句不要复杂

3.为表添加合理的索引,比如

mysql数据库如何进行备份

mysql的备份和恢复和日志管理(配置文件当中的设置)

备份的目的

备灾,防止数据丢失.

在生产环境中,数据的安全性非常重要.

造成数据丢失的原因

1.程序出错

2.人为的问题

3.磁盘故障

备份的分类

备份的前提,数据的版本要一致

或者

低版本备份的数据备份传给高版本的

物理备份

对磁盘或者对文件直接进行备份

热备份

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

逻辑备份

根据数据库文件当中保存的sql语句,表结构,等等,以特定的格式和命令对文件的内容进行还原,属于热备份的一种,只能对表备份,库如果没了就没有办法恢复(主从复制除外)

冷备份

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

全量备份

把数据库的内容整个一次性的做备份.

全量备份示例

方法1

工具
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');


终端
#mysql 1
systemctl stop mysqld
#所有数据都默认保存在/data目录下
#vim mysql.cnf
#basdir=	安装目录
#datadir=	数据保存目录
#压缩工具
yum -y install xz
tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
cd /opt

#mysql 2
[root@test2 ~]# cd /opt/
[root@test2 opt]# scp root@192.168.118.10:/opt/mysql_all_2024-07-19.tar.xz /opt/
[root@test2 opt]# tar -xf mysql_all_2024-07-19.tar.xz 
[root@test2 opt]# cd /opt/usr/local/mysql/
[root@test2 mysql]# systemctl stop mysqld.service 
[root@test2 mysql]# rm -rf /usr/local/mysql/data/
[root@test2 mysql]# cp -a data/ /usr/local/mysql/
[root@test2 mysql]# cd /usr/local/mysql/data
[root@test2 data]# systemctl restart mysqld

工具
连接数据库查看

方法2

mysql自带的备份命令,可以备份库,也可以备份库里面的表

mysqldump

备份一个库

终端
mysql -u root -p123456
use xy102;
show tables;
exit;

mysqldump -u root -p123456 --databases xy102 > /opt/xy102.sql
mysql -u root -p123456 < /opt/xy102.sql

工具查看

备份多个库

mysqldump -u root -p123456 --databases xy102 xy103 > /opt/xy102-103.sql
mysql -u root -p123456 < /opt/xy102-103.sql

备份全部库

mysqldump -u root -p123456 --all-databases > /opt/all/sql
mysql -u root -p123456 < /opt/all.sql

备份表

mysqldump -u root -p123456 xy102 t1 > /opt/xy102_t1.sql
mysql -u root -p123456 xy102 < /opt/xy102_t1.sql

备份多个表

mysqldump -u root -p123456 xy102 t1 t2 > /opt/xy102_t1t2.sql
mysql -u root -p123456 xy102 < /opt/xy102_t12.sql

增量备份示例

前提:开启二进制日志功能

STATEMENT 基于sql语句:只是记录用户操作的sql语句,在高并发的情况下,记录操作的sql语句顺序可能会出错,导致恢复数据的时候就会有丢失,或者误差,但是他的效率高

ROW 基于行,记录每一行的数据,准确,高并发也不会出错,但是恢复的效率低,然后

MIXED 混合模式,正常情况下使用statement,高并发使用row,交由数据库自己判断.

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

vim /etc/mysql.cnf

#server-id =1 的下面添加,开启二进制功能

log -bin=mysql-bin

#记录二进制日志文件格式

binlog_format=MIXED

#开启通用查询日志
general_log=ON 

#查询日志的保存位置
general_log_file=/usr/local/mysql/data/mysql_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

:wq

systemctl restart mysqld
cd 

#查看一下二进制记录
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
#工具删表实验
#
#开启断点,让后续的操作都进入000002
mysqladmin -u root -p123456 flush-logs


mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001|mysql -u root -p

从开始位置恢复

mysqlbinlog --no-defaults --start-position='8224' mysql-bin.000001 | mysql -u root -p123456

从指定区间开始恢复

mysqlbinlog --no-defaults --start-position='8224' --stop-position='9042' mysql-bin.000001 | mysql -u root -p123456

从指定时间开始恢复

mysqlbinlog --no-defaults --start-datetime='2024-07-19 13:48:16' --stop-datetime='2024-07-19 13:49:31' mysql-bin.000001|mysql -u root -p123456

现有需求:本地数据库迁移到阿里云上.

库名xy102

表名 test01

​ 三条数据

​ test02

​ 三条数据