第六周

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

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

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

一对一联系(1:1)****：在表A或表B中创建一个字段，存储另一个表的主键值，如: 一个人只有一个身份证
*一对多联系(1:n)*：外键，如: 部门和员工
多对多联系(m:n)****：增加第三张表，如: 学生和课

三、总结mysql设计范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。

一般数据库只需满足第三范式（3NF）即可

第一范式：1NF

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

说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库

第二范式：2NF

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

第三范式：3NF

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

四、总结Mysql多种安装方式，及安全加固，并总结mysql配置文件。

安装方式介绍

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

安全加固

初始化脚本提高安全性
运行脚本：mysql_secure_installation
*设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库*

配置文件

服务器端配置文件：类ini格式,集中式的配置，能够为mysql的各应用程序提供配置信息

*/etc/my.cnf #Global选项*

*/etc/mysql/my.cnf #Global选项*

*~/.my.cnf #User-specific 选项*

配置文件格式：
*[mysqld]*

*[mysqld_safe]*

*[mysqld_multi]*

*[mysql]*

*[mysqladmin]*

*[mysqldump]*

*[server] [client]*

客户端 mysql 的配置文件

/etc/my.cnf.d/mysql-clients.cnf

五、完成将server和client端的mysql配置默认字符集为utf8mb4;

设置服务器默认的字符集

vim /etc/my.cnf

[mysqld]

*character-set-server=utf8mb4*

设置mysql客户端默认的字符集

vim /etc/my.cnf

*#针对mysql客户端*

[mysql]

*default-character-set=utf8mb4*

#针对所有MySQL客户端

[client]

*default-character-set=utf8mb4*

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

获取SQL命令的帮助: HELP KEYWORD

客户端命令 \h 可以查到
服务器端命令 help contents 查到分类，然后再help 分类

例如：help Account Management

添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）
 
mysql> create database testdb character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.02 sec)
 
mysql> show create database testdb;
+----------+---------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                           |
+----------+---------------------------------------------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
 
mysql> use testdb
Database changed
mysql> create table host (
    -> id int unsigned auto_increment primary key,
    -> host varchar(20),
    -> ip varchar(15),
    -> cname varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> desc host;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| host  | varchar(20)  | YES  |     | NULL    |                |
| ip    | varchar(15)  | YES  |     | NULL    |                |
| cname | varchar(20)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

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

DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

创建数据库
18:46:19(root@localhost) [(none)]> create database db1;
Query OK, 1 row affected (0.02 sec)
 
修改数据库
19:04:52(root@localhost) [(none)]> ALTER DATABASE db1 character set utf8 COLLATE utf8_bin;
Query OK, 1 row affected, 2 warnings (0.02 sec)
 
删除数据库
19:06:09(root@localhost) [(none)]> drop database db1;
Query OK, 0 rows affected (0.02 sec)
 
创建表
19:58:50(root@localhost) [test]> create table student (
    -> id int unsigned auto_increment primary key,
    -> name varchar(20) not null,
    -> age tinyint unsigned,
    -> gender enum('M','F') default 'M'
    -> )engine=innodb auto_increment=10 default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
修改表
20:45:34(root@localhost) [test]> alter table student rename st;
Query OK, 0 rows affected (0.02 sec)
 
删除表
20:49:31(root@localhost) [test]> drop table st;
Query OK, 0 rows affected (0.04 sec)
 
INSERT 语句
21:05:21(root@localhost) [test]> insert st values(0,'lisi',20,'f');
Query OK, 1 row affected (0.01 sec)
 
UPDATE 语句
22:56:31(root@localhost) [test]> update st set age=13 where name='lisi' and gender='M';
Query OK, 1 row affected (0.01 sec)
 
DELETE 语句
21:09:20(root@localhost) [test]> delete from st where name='lisi';
Query OK, 0 rows affected (0.03 sec)

八、总结mysql架构原理

*第一层：连接层*。所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等等。

第二层：核心服务层 。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。

第三层：存储引擎层。通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。

第四层：****数据存储**层**。主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

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

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

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

mysql索引作用

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

以下查询不会使用到索引

索引列参与计算，不走索引
索引列使用函数，可能不走索引
索引列使用 like 语句，可能不走索引
数据类型隐式转换，字符串列与数字直接比较，不走索引
尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引
where id !=2 或者 where id <> 2，不走索引！
is null，is not null也无法使用索引，不走索引！
索引列使用 in 语句，可能不走索引

十一、总结事务ACID事务特性

ACID特性：

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

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

*事务日志：transaction log*

*redo log：*记录某数据块被修改后的值，数据更新前先记录redo log( WALWrite Ahead Log ),可以用来恢复未写入data file的已成功事务更新的数据
*undo log：*保存与执行的操作相反的操作,即记录某数据被修改前的值，可以用来在事务失败时进行rollback

十三、总结mysql日志类型，并说明如何启动日志。

mysql日志类型

事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log

启用日志 *SET GLOBAL general_log = 'ON';*

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

二进制日志记录三种格式

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

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

备份类型

完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
增量和差异备份的基础和前提是完全备份
注意：二进制日志文件不应该与数据文件放在同一磁盘
冷、温、热备份
冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

mysqldump备份工具是MySQL客户端命令，通过mysql协议连接至mysql服务器进行备份
开启二进制日志
[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-2023-11-30.sql.gz
[root@centos8 ~]mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2023-11-30.sql
MariaDB [(none)]> set sql_log_bin=on;
 
 
Xtrabackup备份工具是percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
1 安装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进行增量备份

备份脚本: 
[root@centos8 ~]#cat mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=magedu
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers --single-transaction --master-
data=2 --default-character-set=utf8 -q -B $DB | gzip >
${DIR}/${DB}_${TIME}.sql.gz


分库备份并压缩
[root@centos8 ~]#for db in `mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db |
gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema\sys)$'|while read db;do
mysqldump -B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv
'^(Database|information_schema|performance_schema|sys)$' | sed -rn 's#
(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn
'/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1
| gzip > /backup/\1.sql.gz#p' |bash

posted @ 2024-02-25 14:26 美食家哇哈哈阅读(42) 评论(0) 收藏举报

刷新页面返回顶部

第六周

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

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

三、总结mysql设计范式

四、总结Mysql多种安装方式，及安全加固，并总结mysql配置文件。

五、完成将server和client端的mysql配置默认字符集为utf8mb4;

设置服务器默认的字符集

设置mysql客户端默认的字符集

*#针对所有MySQL客户端*

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

获取SQL命令的帮助: HELP KEYWORD

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

DDL: Data Defination Language 数据定义语言

*CREATE，DROP，ALTER*

DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

八、总结mysql架构原理

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

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

以下查询不会使用到索引

索引列参与计算，不走索引

索引列使用函数，可能不走索引

索引列使用 like 语句，可能不走索引

数据类型隐式转换，字符串列与数字直接比较，不走索引

尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引

where id !=2 或者 where id <> 2，不走索引！

is null，is not null也无法使用索引，不走索引！

索引列使用 in 语句，可能不走索引