负责网站建设推广,开发软件系统,全国信用信息公示系统,seopeixun com cn一、数据库的存储引擎#xff1a;
1.1、存储引擎的概念
概念#xff1a;存储引擎#xff0c;就是一种数据库存储数据的机制#xff0c;索引的机制#xff0c;索引的技巧#xff0c;锁定水平。
存储的方式和存储的格式。
存储引擎也属于mysql当中的组件#xff0c;实…一、数据库的存储引擎
1.1、存储引擎的概念
概念存储引擎就是一种数据库存储数据的机制索引的机制索引的技巧锁定水平。
存储的方式和存储的格式。
存储引擎也属于mysql当中的组件实际上操作的执行的就是数据的读写I/O。
1.2、mysql的存储引擎的分类
mysql5.5之后默认开始使用innodb事务型速记存储引擎。支持ACID支持行锁定。myisammysql5.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 id1;
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);xhellbegin;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 age23 WHERE id 3;
以上是操作非索引列的行导致全文锁定。
delete from t1 where id 1;
update t1 set age20 where id1;
update t1 set age20 where id1; 当指定条件不是索引的时候会锁住全表。
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 mysqldcd /usr/local/mysql/datayum -y install xzxz:压缩工具大文件的压缩比较快。## 2.1、打包整个数据库tar -jcvf /opt/mysql_all_$(date %F).xz /usr/local/mysql/data##打包mysql整个文件的数据文件到/opt目录下。cd /opt### 2.1.1、scp的备份scp root192.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 mysqld2.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
[rootmysql1 opt]# mysql -u root -p /opt/xy_all.sql
Enter password: 123456备份全部的库和表 mysqldump -u root -p --all-databases /opt/all.sql
Enter password: 123456[rootmysql1 opt]# mysql -u root -p /opt/all.sql
Enter password: 123456去另外一台mysqlscp root192.168.168.11:/opt/all.sql /opt/
root192.168.168.11s password: 123mysql -u root -p /opt/all.sql
Enter password: 123456备份库里面的单表
mysqldump -u root -p 库名 表名 /opt/xy102_t1.sqlmysqldump -u root -p xy102 t1 /opt/xy102_t1.sql
Enter password:
[rootmysql1 opt]# mysql -u root -p /opt/xy102_t1.sql
Enter password:
ERROR 1046 (3D000) at line 22: No database selected
[rootmysql1 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 rootlocalhost (using password: YES)
[rootmysql1 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.cnflog-binmysql-bin
#开启二进制日志功能
binlog_formatMIXED
#记录二进制日志的文件格式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-outputdecode-rows -v mysql-bin.000001##使用解码工具对二进制日志文件查看
2.3.2、指定位置恢复
从指定位置1956恢复到结束
mysqlbinlog --no-defaults --start-position1956 mysql-bin.000005 | mysql -u root -p
Enter password: 123456
##恢复从指定位置1956到结束从1956-2774恢复 mysqlbinlog --no-defaults --start-position1956 --stop-position2774 mysql-bin.000005 | mysql -u root -p
Enter password: 1234562.3.3、按时间位置恢复
根据时间时间外部格式####-#-## mysqlbinlog --no-defaults --start-datetime####-#-## ##:##:## --stop-datetime####-#-## ##:##:## mysql-bin.000005 | mysql -u root -p
Enter password: 123456mysqlbinlog --no-defaults --start-datetime2024-7-20 1:27:20 mysql-bin.000005 | mysql -u root -p
##开始到结束mysqlbinlog --no-defaults --start-datetime2024-7-20 1:27:20 --stop-datetime2024-7-20 1:27:23 mysql-bin.000005 | mysql -u root -p
##时间间隔/etc/my.cnf
log-binmysql-bin #开启二进制日志功能 binlog_formatMIXED #记录二进制日志的文件格式
打开通用查询日志
general_logON
查询日志的保存位置
general_log_file/usr/local/mysql/data/mysql_general.log
错误的保存位置错误日志默认是开启的
log-error/usr/local/mysql/data/mysql_error.log
开启慢查询日志
slow_query_log0N
设置慢查询的位置
slow_query_log_file/usr/local/mysql/data/mysql_slow_query.log
#默认的慢查询时间是10秒超过5秒的记录都会保存。
long_query_time5
