55 、mysql的存储引擎、备份恢复以及日志备份、恢复

🕗 发布于 2024-07-20 08:54 mysql 数据库

一、数据库的存储引擎：

1.1、存储引擎的概念

概念：存储引擎，就是一种数据库存储数据的机制，索引的机制，索引的技巧，锁定水平。

存储的方式和存储的格式。

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

1.2、mysql的存储引擎的分类：

mysql5.5之后默认开始使用innodb，事务型速记存储引擎。支持ACID，支持行锁定。
myisam：mysql5.5之前的存储引擎，插入的速度和查询速度很快，但是不支持事务。
memory：内存型存储引擎，数据在写时，都保存在内存当中，一旦重启，所有数据全部消失。
csv：逗号分割数据的存储引擎，数据文件.csv文件保存的，execl.保存的文件就是一个普通的文本文件。
不支持索引。

1.2.1、INNODB存储引擎：

show ENGINES;##查看存储引擎
/etc/my.cnf##配置文件也可以查看和更改
default-storage-engine=#更改默认的存储引擎

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

2、能够高效缓存数据和支持多种类索引。

3、表的索引的类型默认时BTREE

4、支持外键，支持全文索引。

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

6、行级锁定，会把行锁住，禁止操作。

1.2.2、模糊查询：

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

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

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

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);
show INDEX from student;
INSERT INTO student VALUES(1,'ly1',18,'男');
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;
SELECT * from student WHERE id=1;
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;

innodb行锁和索引的关系：

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

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

加了索引就是所行。

不加索引就是锁表。

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

oracle是提交才写入。

当指定条件是索引的时候，会锁住索引行。

1.3、innodb行锁和索引

CREATE table student(
id int(5),
name char(10),
age int(3),
sex CHAR(2)
);
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,'男');
ALTER table student add PRIMARY key(id);

xhell：

begin;

delete from student where id =1;


navicat:

UPDATE student SET age = 26 where name = '小明';
以上是操作索引列的行，导致整行锁定。

mysql> begin;##begin开始，commit提交才写入，若不begin，直接命令行，自动写入。
Query OK, 0 rows affected (0.00 sec)

mysql> delete from student where name ='小明';
Query OK, 1 row affected (0.00 sec)

navicat：
UPDATE student SET age=23 WHERE id = 3;
以上是操作非索引列的行，导致全文锁定。

delete from t1 where id =1;

update t1 set age=20 where id=1;

在这里插入图片描述

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

1.4、死锁

死锁：事务相互等待对方的资源，最后形成一个环路造成的。

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

mysql> begin;
Query OK, 0 rows affected (0.05 sec)

mysql> delete from t1 where id = 5;
Query OK, 1 row affected (0.00 sec)

begin;
SELECT * from t1 WHERE id =1 for UPDATE;
UPDATE t1 SET name = 'asd' WHERE id = 5;

mysql> delete from t1 where id = 1;
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

发生了死锁，数据会自动选择一个事务作为受害者，回滚该事务以解除死锁。

mysql会终止其中一个事务，但是不会回滚。

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

如何避免死锁的情况出现：

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

2、大事务尽量拆陈小事务

3、为表添加一个合理的索引

二、mysql的备份和恢复和日志管理（配置文件当中的设置）

1、备份的目的

备灾，在生产环境中，数据的安全性非常重要。

造成数据丢失的原因：

1、程序出错

2、人为的问题

3、磁盘的故障。

备份的分类：

物理备份：对磁盘或者文件直接进行备份。

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

热备份：

联机备份，不用关闭程序就可以对资料进行备份。
逻辑备份：根据数据库文件当中保存的sql语句，表结构，等等，以特定格式和命令对文件的内容进行还原。热备份的一种。只能对表备份，库没了没有办法恢复。主从复制可以恢复。

物理备份全量备份：

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

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





systemctl stop firewalld;
systemctl stop mysqld

cd /usr/local/mysql/data

在这里插入图片描述

yum -y install xz

`xz:`压缩工具，大文件的压缩比较快。

## 2.1、打包整个数据库

`tar -jcvf /opt/mysql_all_$(date +%F).xz /usr/local/mysql/data`##打包mysql整个文件的数据文件到/opt目录下。

cd /opt

在这里插入图片描述

### 2.1.1、scp的备份

scp root@192.168.168.11:/opt/mysql_all_2024-07-19.xz /opt/##远程复制

cd /opt/

tar -xf mysql_all_2024-07-19.xz 

systemctl stop mysqld.service ##物理备份需要关闭服务。

cd /usr/local/mysql/

rm -rf data/

cd /opt/usr/local/mysql/

cp -a data/ /usr/local/mysql/

systemctl restart mysqld

2.2、mysql自带的备份

mysql自带的备份命令。可以备份库，也可以备份库里面的表。热备份

备份数据库xy102

mysqsldump -u root -p --databases xy102 > /opt/xy102.sql##必须要以sql为结尾。

备份完成，删除表

mysql -u root -p < /opt/xy102.sql
Enter password: 123456

刷新即可完成恢复。

备份多个数据库

mysqldump -u root -p --databases xy102 xy103 > /opt/xy_all.sql
Enter password: 123456
[root@mysql1 opt]# mysql -u root -p < /opt/xy_all.sql 
Enter password: 123456

备份全部的库和表

 mysqldump -u root -p --all-databases > /opt/all.sql
Enter password: 123456

[root@mysql1 opt]# mysql -u root -p < /opt/all.sql 
Enter password: 123456





去另外一台mysql

scp root@192.168.168.11:/opt/all.sql /opt/
root@192.168.168.11's password: 123

mysql -u root -p < /opt/all.sql 
Enter password: 123456

备份库里面的单表

mysqldump -u root -p 库名 表名 > /opt/xy102_t1.sql

mysqldump -u root -p xy102 t1 > /opt/xy102_t1.sql
Enter password: 
[root@mysql1 opt]# mysql -u root -p < /opt/xy102_t1.sql 
Enter password: 
ERROR 1046 (3D000) at line 22: No database selected
[root@mysql1 opt]# mysql -u root -p xy102 < /opt/xy102_t1.sql 
Enter password:

备份库里面的多个表

mysql -u root -p xy102 < /opt/xy102_t1_student.sql 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@mysql1 opt]# mysql -u root -p xy102 < /opt/xy102_t1_student.sql

备份的表在其他库恢复表

mysql -u root -p xy103 < /opt/xy102_t1_student.sql 
Enter password: ##xy102里面的t1表和student表在xy103库里面恢复。

2.3、增量备份：1、2、3

2.3.1、开启二进制日志的功能：

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

/etc/my.cnf

log-bin=mysql-bin
#开启二进制日志功能
binlog_format=MIXED
#记录二进制日志的文件格式

statement基于sql语句：只是记录用户操作的sql语句，高并发的情况下，记录操作的sql的语句的顺序可能会出错。导出数据时，就会有丢失，或者误差。效率高。

row基于行，记录每一行的数据，准确，高并发也不会出错，但是恢复的效率低。

mixed混合模式，正常情况下使用statement，高并发使用row，智能判断。

cd /usr/local/mysql/data/

在这里插入图片描述

mysqladmin -u root -p flush-logs
##刷新生成新文件进行记录mysql日志文件


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');
insert into info1 values(3,'user3','female','singing');
insert into info1 values(4,'user4','female','singing');
insert into info1 values(5,'user5','female','singing');
insert into info1 values(6,'user6','female','singing');
insert into info1 values(7,'user7','female','singing');
insert into info1 values(8,'user8','female','singing');

mysqladmin -u root -p flush-logs
##设置断点，再次刷新，日志更换保存文件
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p
##恢复数据到数据库中，之前怎么建的，就怎么恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001##使用解码工具对二进制日志文件查看

2.3.2、指定位置恢复

从指定位置1956恢复到结束

mysqlbinlog --no-defaults --start-position='1956' mysql-bin.000005 | mysql -u root -p
Enter password: 123456
##恢复从指定位置1956到结束

从1956-2774恢复

 mysqlbinlog --no-defaults --start-position='1956' --stop-position='2774' mysql-bin.000005 | mysql -u root -p
Enter password: 123456

2.3.3、按时间位置恢复

根据时间，时间外部格式####-#-##

 mysqlbinlog --no-defaults --start-datetime='####-#-## ##:##:##' --stop-datetime='####-#-## ##:##:##' mysql-bin.000005 | mysql -u root -p
Enter password: 123456

mysqlbinlog --no-defaults --start-datetime='2024-7-20  1:27:20' mysql-bin.000005 | mysql -u root -p
##开始到结束

mysqlbinlog --no-defaults --start-datetime='2024-7-20  1:27:20' --stop-datetime='2024-7-20  1:27:23' mysql-bin.000005 | mysql -u root -p
##时间间隔

/etc/my.cnf

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=0N

设置慢查询的位置

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

#默认的慢查询时间是10秒，超过5秒的记录都会保存。

long_query_time=5

在这里插入图片描述

原文地址：https://blog.csdn.net/m0_74149099/article/details/140563081

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：特征工程方法总结
下一篇：达梦数据库的系统视图v$arch_status

网络安全入门教程（非常详细）从零基础入门到精通_网路安全教程
1.入行网络安全这是一条坚持的道路，三分钟的热情可以放弃往下看了。2.多练多想，不要离开了教程什么都不会了，最好看完教程自己独立完成技术方面的开发。3.有时多百度，我们往往都遇不到好心的大神，谁会无聊
阅读更多2024-11-13
【MySQL】MySQL中的函数之JSON_UNQUOTE
在 MySQL 中，函数用于去除 JSON 值周围的双引号，并将其转换为普通的字符串或数值。这个函数特别适用于从 JSON 文档中提取的值，因为这些值通常会被包含在双引号中。
阅读更多2024-11-13
西门子S120伺服驱动器F1910故障报警原因分析及处理
西门子S120伺服驱动器F1910故障报警原因分析及处理
阅读更多2024-11-13
(不看后悔系列二)python网络爬虫爬取网络视频
Python网络爬虫带你爬取下载网络视频
阅读更多2024-11-13
elementUI input 禁止内容两端存在空格，或者是自动去除两端空格
项目中有需求：输入框中禁止内容两端存在空格，或者是自动去除两端空格。
阅读更多2024-11-13
JavaWeb——Web入门（7/9）-Tomcat-介绍（Tomcat 的简介：轻量级Web服务器，支持Servlet/JSP少量JavaEE规范）
比如我们前面介绍的 XML，以及我们后面要学习的 JDBC，这些都是 Java EE 的规范。还有像 Servlet、JSP 这样的技术，现在基于 Servlet 进行项目开发呢，基本上已经很少很少了
阅读更多2024-11-13
就是这个样的粗爆，手搓一个计算器：存储单位换算计算器
作为程序员，没有合适的工具，就得手搓一个：在线计算器，存储单位换算计算器，PC端，移动端均可适用。废话不多说，直接上代码。
阅读更多2024-11-13
关于我、重生到500年前凭借C语言改变世界科技vlog.18——内存函数
内存函数是用于的一组函数，它们可以对内存进行复制、移动、设置和比较等操作。这些函数主要在头文件中声明，其操作对象通常是字节序列，不管这些字节代表的是字符、整数还是其他数据类型。
阅读更多2024-11-13
Warped Universe游戏即将在Sui上推出，为玩家提供多样化的游戏体验
Warped Games利用Sui实现玩家拥有的资产和多类型游戏玩法。
阅读更多2024-11-13
测试用例设计方法之场景法
场景法也叫流程图法, 使用流程图描述用户的使用场景, 然后通过覆盖流程路径来设计测试用例。
阅读更多2024-11-13

55 、mysql的存储引擎、备份恢复以及日志备份、恢复

一、数据库的存储引擎：

1.1、存储引擎的概念

1.2、mysql的存储引擎的分类：

1.2.1、INNODB存储引擎：

1.2.2、模糊查询：

1.3、innodb行锁和索引

1.4、死锁

二、mysql的备份和恢复和日志管理（配置文件当中的设置）

2.2、mysql自带的备份

备份数据库xy102

备份多个数据库

备份全部的库和表

备份库里面的单表

备份库里面的多个表

备份的表在其他库恢复表

2.3、增量备份：1、2、3

2.3.1、开启二进制日志的功能：

2.3.2、指定位置恢复

2.3.3、按时间位置恢复

相关文章