55 、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;

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