2024年第六周_n50周启皓语录-CSDN博客

本文链接：https://blog.csdn.net/m0_50742755/article/details/139202712

1. 总结关系型数据库相关概念，关系，行，列，主键，惟一键，域。

关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主
键, 主键字段不能为空NULL
唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而且UK字段可以为NULL
域domain：属性的取值范围，如，性别只能是’男’和’女’两个值，人类的年龄只能0-150

2. 总结关联类型，1对1，1对多，多对多关系。可以自行设计表进行解释。

2.1 1对1

ID	学生	身份证号
1	张三	1234
2	李四	2345

身份证号	性别	住址
1234	男	上海
2345	女	南京

2.2 1对多

ID	姓名	课程ID
1	张三	1
2	李四	2

课程ID	课程名称	价格
1	云计算运维	18888
2	Python	16888

2.3 多对多

ID	NAME	课程ID
1	zhangsan	1
2	lisi	2
3	wangwu	3

ID	NAME	学生ID
1	Linux	1
2	Python	2
3	Golang	3

ID	STU_ID	COURSE_ID
1	1	1
2	2	2
3	3	3

3.总结mysql设计范式

3.1 第一范式：1NF

无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列。

3.2 第二范式：2NF

第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键。

3.3 第三范式：3NF

满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系。

4.总结Mysql多种安装方式，及安全加固，并总结mysql配置文件。1. 完成将server和client端的mysql配置默认字符集为utf8mb4;

4.1 安装方式

程序包管理器管理的程序包
源代码编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

4.2 安全加固

设置数据库管理员root口令
禁止root远程登录
禁止anonymous登录
删除test数据库

4.3 mysql配置文件

[mysqld]、[mysqld_safe]、[mysqld_multi]、[mysql]、[mysqladmin]、[mysqldump]、[server]、[client]

修改配置文件
vim /etc/my.cnf 
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
重启MySQL服务
systemctl restart mysqld.service

5.掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）

获取帮助
mysql> help creat database
创建testdb库，字符集utf8，排序集合utf8_bin
mysql> create database testdb character set utf8 collate utf8_bin; 
创建host表
mysql> CREATE TABLE host (
  id INT AUTO_INCREMENT PRIMARY KEY,
  host VARCHAR(255) NOT NULL UNIQUE,
  ip VARCHAR(255) NOT NULL UNIQUE,
  cname VARCHAR(255)
);

6.根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

6.1 DDL

6.1.1 创建表 `CREATE TABLE`

CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
#height DECIMAL(5,2),
gender ENUM('M','F') default 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

6.1.2 查看表 `SHOW TABLES [FROM db_name]`

MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
+---------------+
1 row in set (0.000 sec)

6.1.3 修改和删除表

修改表

ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name] 
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

删除表

DROP TABLE [IF EXISTS] 'tbl_name';

6.2 DML语句

6.2.1 INSERT语句

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

mysql> insert student values(0,'wang',18,default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | wang |   18 | M      |
+----+------+------+--------+
1 row in set (0.00 sec)

6.2.2 UPDATE语句

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

6.2.3 DELETE语句

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count] #可先排序再指定删除的行数

7.总结mysql架构原理

在这里插入图片描述

连接池：负责监听来自客户端的连接请求，建立连接后将其传递给管理服务。
管理服务和工具：包括日志管理、错误管理、安全管理、用户账户管理等。
SQL接口：接收用户的SQL命令，并传递给解析器。
解析器：对SQL命令进行语法分析和语义分析，生成解析树。
优化器：对解析树进行逻辑优化，如重写查询、选择运行计划等。
缓存：存储经常使用的数据结果，减少对磁盘的访问。
存储引擎：负责数据的存储和提取，如InnoDB、MyISAM等。

8.总结myisam和Innodb存储引擎的区别。

8.1 myisam存储引擎

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5 前默认的数据库引擎

8.2 innodb存储引擎

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

9.总结mysql索引作用，同时总结哪些查询不会使用到索引。

9.1 mysql索引的作用

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O

9.2 查询不会使用到索引

where条件中（包括group by以及order by）里用不到的字段不需要创建索引
数据量小的表
有大量重复数据的列上
经常更新的表
无序的值
删除不再使用或者很少使用的索引
不要定义冗余或者重复的索引

10.总结事务ACID事务特性

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定
律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离
级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

11.总结事务日志工作原理

事务开始时，InnoDB会在undo log中保存事务开始前的数据版本。
事务进行修改数据页操作时，InnoDB会在redo log中记录这次改变，并在buffer pool中对数据页做出改动。
事务提交时，InnoDB会将redo log中的记录写入磁盘，并更新页的LSN（Log Sequence Number，日志序列号）。
如果在事务提交前发生崩溃或者系统故障，InnoDB可以通过redo log中的记录来恢复未写入磁盘的数据页变更。
如果是正常关闭数据库，redo log的内容会被清空，但是在下次启动时，InnoDB会根据最后一个checkpoint之后的redo log来恢复buffer pool中的数据页，并将这些页的变更应用到磁盘上的数据文件中。

12.总结mysql日志类型，并说明如何启动日志

事务日志：transaction log，事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging，事务日志文件： ib_logfile0， ib_logfile1。默认启动。
错误日志：error log，启用：log_error = /var/log/mysql/error.log。
通用日志：general log，启用：general_log = ON 和 general_log_file = /var/log/mysql/mysql.log。
慢查询日志：slow query log，启用：slow_query_log = ON 和 slow_query_log_file = /var/log/mysql/mysql-slow.log 和 long_query_time = 2。
二进制日志：binary log，启用：log_bin = /var/log/mysql/mysql-bin.log
中继日志：reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件。

13.总结二进制日志的不同格式的使用场景

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少。
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

14.总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。

14.1 备份类型

完全备份，部分备份

完全备份：整个数据集

部分备份：只备份数据子集，如部分库或表

完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂。

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单增量和差异备份的基础和前提是完全备份。

冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务

温备：读操作可执行；但写操作不可执行

热备：读、写操作均可执行

MyISAM：温备，不支持热备

InnoDB：都支持

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快。

逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度。

14.2 mysqldump备份与恢复

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin 
#备份
[root@centos8 ~]#mysqldump -uroot -pmagedu -A -F --single-transaction --masterdata=2 |gzip > /backup/all-`date +%F`.sql.gz
#还原
[root@centos8 backup]#dnf install mariadb-server
[root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;

14.3 xtrabackup备份与恢复

1 安装xtrabackup包 #先安装MySQL5.7和xtrabackup包
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
2 在原主机做完全备份到/backup
[root@centos8 ~]#mkdir /backup
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
#目标主机无需创建/backup目录,直接复制目录本身
[root@centos8 ~]#scp -r /backup/   目标主机:/
3 在目标主机上还原
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start

15.编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

crontab -e
0 0 * * * mkdir -p /backup/mysql/$(date '+\%Y-\%m-\%d') && mysqldump -u xie -p111111 --all-databases --tables --single-transaction > /backup/mysql/$(date '+\%Y-\%m-\%d')/backup.sql

crontab -e
#!/bin/bash
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
 
30 2 * * 1,5    xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
30 2 * * 2-4     xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base