N86第六周作业

MySQL

一、关系型数据库

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

关系型数据库是一种广泛使用的数据库管理系统，它基于关系模型，由E.F. Codd在1970年提出。以下是关系型数据库中的一些核心概念：

关系（Relation）：

在关系型数据库中，关系指的是表格（table），它是由行（rows）和列（columns）组成的二维数据结构。
每个关系都有一个唯一的名称，用于在数据库中标识它。
关系模式（relation schema）定义了关系的结构，包括表名、列名和列的数据类型。
行（Row）：

行也称为元组（tuple），是关系中的一条记录。
每一行都包含了表中所有列的数据，每一列的值称为属性值。
在一个关系中，每一行都是唯一的。
列（Column）：

列是关系中的垂直单元，每一列都有唯一的名称和数据类型。
列定义了表中存储的数据种类，每一列的所有值都属于同一数据类型。
主键（Primary Key）：

主键是关系中的一列或列的组合，用于唯一地标识表中的每一行。

主键的值必须是唯一的，不能有重复，且不能为空。

主键用于确保表中每一行的唯一性，并用于建立不同表之间的关系。

唯一键（Unique Key）：

唯一键是一列或列的组合，用于确保一列或列组合中的值在整个表中是唯一的。

与主键不同的是，唯一键允许有空值，但空值也只能出现一次。

域（Domain）：

域是属性值的范围，它定义了列可以接受的所有合法值的集合。

二、数据库关联类型

在关系型数据库中，表与表之间的关系可以分为三种基本类型：一对一（1:1）、一对多（1:N）和多对多（M:N）关系。这些关系用于描述不同表中的记录如何相互关联。

2.1、一对一（1:1）关系

一对一（1:1）关系：

当一个表中的每条记录只与另一个表中的一条记录相关联，反之亦然时，存在一对一关系。
例如，假设有员工表和员工详细信息表，每个员工在员工详细信息表中只有一条记录，那么这两个表之间就存在一对一关系。
在数据库设计中，一对一关系可以通过在任一表中添加外键来实现，该外键是另一表的主键，并且是唯一的。

ID	NAME	ID number
YG001	小帅	1504260001
YG002	小美	1504260002

staff

ID	staf_id	sex	department
YGXX001	YG001	男	信息技术
YGXX002	YG002	女	人力资源

Employee Information

2.2、一对多（1:N）关系

一对多（1:N）关系：

当一个表中的每条记录与另一个表中的多条记录相关联，但另一个表中的每条记录只与第一个表中的一条记录相关联时，存在一对多关系。
例如，一个部门可以有多个员工，但每个员工只能属于一个部门，部门表和员工表之间就是一对多关系。
在数据库设计中，一对多关系通常通过在“多”方表中添加外键来实现，该外键指向“一”方表的主键。

ID	NAME	ID number	department_id
YG001	小帅	1504260001	BM001
YG002	小美	1504260002	BM002
YG003	小强	1504260003	BM001
YG004	小黑	1504260004	BM001

staff

ID	department_name
BM001	信息技术
BM002	人力资源

department

2.3、多对多（M:N）关系

多对多（M:N）关系：

当两个表中的每条记录都可以与另一个表中的多条记录相关联时，存在多对多关系。
例如，学生表和课程表之间就存在多对多关系，因为一个学生可以选修多个课程，一个课程也可以被多个学生选修。
在数据库设计中，多对多关系通常通过创建一个关联表来实现，该表包含两个外键，分别指向原始表的主键。

ID	NAME	SEX
STU001	小帅	男
STU002	小红	女

student

ID	NAME	TEACHER
COU001	Linux	小强
COU002	Python	小黑

COURSE

ID	STU_ID	SOU_ID
1	STU001	COU001
2	STU002	COU001
3	STU001	COU002
4	STU002	COU002

Student_course

三、mysql设计范式

在设计MySQL数据库时，可以遵循一系列规范，这些规范被称为范式。范式是数据库设计的标准，旨在减少数据冗余和依赖性，从而提高数据的一致性、稳定性和灵活性。以下是几个常见的范式：

3.1、第一范式（1NF）

第一范式（1NF）：

第一范式要求表中的所有字段都是原子性的，即每个字段都不能再拆分。
表中的每一列都是不可分割的最小数据单位，不能有重复的组。
每一行的数据都可以唯一地由主键标识。

3.2、第二范式（2NF）

第二范式（2NF）：

第二范式在第一范式的基础上，要求表中的所有非主键字段都必须完全依赖于主键。
没有部分依赖，即表中没有一个字段只依赖于主键的一部分。
通常通过将表拆分成多个表来实现第二范式，每个表都有一个独立的主键。

3.3、第三范式（3NF）

第三范式（3NF）：

第三范式在第二范式的基础上，要求表中的所有字段不仅完全依赖于主键，而且不存在传递依赖。
表中的字段不能依赖于其他非主键字段。这通常意味着需要进一步拆分表，以确保所有字段都直接依赖于主键。

四、Mysql安装加固配置

4.1、Mysql多种安装方式

4.1.1、包管理器安装

4.1.1.1、Debian/Ubuntu：使用apt包管理器安装

sudo apt update
sudo apt install mysql-server

4.1.1.2、Red Hat/CentOS：使用yum包管理器安装

sudo yum install mysql-server

4.1.2、二进制包安装

#准备用户
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql

#准备二进制包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mysql-5.7.24 mysql
chown -R root:root /usr/local/mysql/

#准备配置文件
cd /usr/local/mysql
cp -b support-files/my-default.cnf /etc/my.cnf
vim /etc/my.cnf
#mysql语句块中添加以下三个选项
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on #禁止主机名解析，建议使用


#初始化数据库
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql


#准备服务脚本，并启动服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

4.1.3、源码编译安装

#环境准备
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake

#准备源码包
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.35.tar.gz

#解压源码包
tar -zxvf mysql-boost-5.7.35.tar.gz

#进入解压后的目录
cd mysql-5.7.35

#配置编译选项
cmake . \   
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \   
-DMYSQL_DATADIR=/usr/local/mysql/data \    
-DMYSQL_TCP_PORT=3306 \  
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \  
-DDEFAULT_CHARSET=utf8mb4 \   
-DDEFAULT_COLLATION=utf8mb4

#编译源码
make

#安装
make install

4.1.4、容器化安装

#安装Docker
#Debian/Ubuntu：使用apt包管理器安装
sudo apt-get update  
sudo apt-get install docker.io

#Red Hat/CentOS：使用yum包管理器安装
yum install -y docker-ce

#拉取MySQL Docker镜像
docker pull mysql

#启动MySQL
docker run --name mysql\  
  -e MYSQL_ROOT_PASSWORD=your_password \  
  -p 3306:3306 \  
  -d mysql

#注意，Red Hat/CentOS需要提前配置服务器的yum源等信息

4.2、MySQL安全加固

4.2.1、更改root密码

更改root密码：安装完成后立即更改root用户的密码。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

4.2.2、限制远程访问

root用户默认限制远程访问权限，其他管理员用户的远程访问权限，可配置仅允许特定的IP地址或主机访问。

CREATE USER 'admin'@'172.25.10.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'172.25.10.1';

4.3、MySQL配置文件

#配置文件路径
/etc/mysql/my.cnf 

#配置文件格式
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqladmin]
[mysqldump]
[server] 
[client]

#将server和client端的mysql配置默认字符集为utf8mb4;
[mysqld]  
character-set-server=utf8mb4  
collation-server=utf8mb4_unicode_ci

[client]  
default-character-set=utf8mb4

五、SQL命令帮助

#获取SQL命令帮助
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.7/en/create-database.html


#添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）
mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.01 sec)

mysql> USE testdb; 
Database changed
mysql> CREATE TABLE host (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    ->     host VARCHAR(255) NOT NULL,
    ->     ip VARCHAR(15) NOT NULL,
    ->     cname VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

六、DDL、DML用法示例总结

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

6.1、DDL用法示例总结

6.1.1、创建库 CREATE DATABASE

CREATE DATABASE Student_Management_System;

6.1.2、创建表 CREATE TABLE

use Student_Management_System;

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.3、查看表 SHOW TABLES [FROM db_name]

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

6.1.4、 删除表

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]

mysql> update student set age = 19 where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | wang |   19 | M      |
+----+------+------+--------+
1 row in set (0.00 sec)

6.2.3、DELETE

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

七、总结mysql架构原理

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

八、总结myisam和Innodb存储引擎的区别

	myisam	Innodb
事务	支持	支持事务，适合处理大量短期事务
锁	表级锁定	行级锁
读写阻塞	读写相互阻塞，写入不能读，读时不能写	读写阻塞与事务隔离级别相关
缓存	只缓存索引	可缓存数据和索引
外键	不支持外键约束
聚簇索引	不支持聚簇索引	支持聚簇索引
MVCC	不支持MVCC（多版本并发控制机制）高并发	支持MVCC高并发
崩溃恢复性	崩溃恢复性较差	崩溃恢复性更好
数据库引擎	MySQL5.5.5 前默认的数据库引擎	从MySQL5.5后支持全文索引 从MySQL5.5.5开始为默认的数据库引擎

九、mysql索引

9.1、mysql索引的作用

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

9.2、不会使用到索引的查询

全表扫描
查询条件与索引不匹配
函数或计算操作
范围查询
OR条件连接不同的索引列
LIKE查询以通配符开头
NOT IN、<> 或 !=

十、总结事务ACID事务特性

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

十一、总结事务日志工作原理

事务开始时，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中的数据页，并将这些页的变更应用到磁盘上的数据文件中。

十二、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，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

十三、总结二进制日志的不同格式的使用场景

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

十四、mysql备份

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

14.1、mysql备份类型

完全备份，部分备份
​ 完全备份：整个数据集

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

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

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

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

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

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

​ MyISAM：温备，不支持热备

​ InnoDB：都支持

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

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

14.2、mysqldump备份恢复

#备份数据库
mysqldump -u root -p --all-databases > /home/mysql/all_databases.sql

#恢复数据库
mysql -u root -p < /home/mysql/all_databases.sql

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

十五、定时备份

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

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