第二阶段：mysql（学完就隐藏版）

第一章：部署数据库系统（注意关闭防火墙，selinux安装）

•   安装mysql配置的相关文件：yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm（centos9：yum install https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm）
• 安装mysql，建议关闭校验（yum install mysql-community-server）
• 启动mysql（要先启动服务，才会有/var/log/mysqld.log，这个文件才有初始密码）：systemctl start mysqld
• 查看密码：grep password | /var/log/mysqld.log
• 登录mysql：mysql -u 用户名 -p（exit退出mysql）
• 运行 MySQL 安全设置：使用临时密码登录 MySQL（看看可不可以用），然后在shell中运行安全设置程序来配置 root 密码等安全选项：mysql_secure_installation（先完成密码重置）
• 表数据一般，放在/var/lib/mysql中
• 我想设置简单密码，但是必须要完成密码重置后才可以
  • 登录 MySQL：查看密码策略设置：SHOW VARIABLES LIKE 'validate_password%';
  • 修改密码策略（可选）：
    • 设置密码策略为 LOW：SET GLOBAL validate_password.policy = LOW;设置最小密码长度为 6：SET GLOBAL validate_password.length = 6;可以选择降低对特殊字符和数字的要求：SET GLOBAL validate_password.mixed_case_count = 0; SET GLOBAL validate_password.number_count = 0; SET GLOBAL validate_password.special_char_count = 0;更改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY '简单密码';

第二章：sql数据库操作语言ddl

• sql语言分类

  • sql：结构化查询语言：用于存取数据，查询数据，更新数据，管理关系型数据库系统{sql语言由ibm开发}

  • 分类

    • DDL：数据定义语言：数据库，数据表，视图，索引，存储过程
    • DML：数据操纵语言：插入insert，删除delete，更新update
    • DQL：数据查询语言：查询select
    • DCL：数据控制语言：控制用户各类权限

• 名词解释

  • 数据库服务器

    • 运行数据库的物理设备：硬件+系统+mysqlsever

  • 数据库（show databases；显示当前数据库管理系统的默认库）

    • 默认数据库（管理表的单元）

      • information_schema

        • 类型：虚拟库
        • 功能：该库存储了系统中的数据库对象信息。它不包含实际的数据，而是提供了对数据库结构（元数据）的查询接口。
        • 内容：表、列、权限、约束、触发器等的元数据。
        • 用途：通常用于查询数据库结构或对象的信息，例如查询所有表的列表、列的详细信息等。

      • mysql

        • 类型：系统库
        • 功能：存储 MySQL 数据库服务器的核心数据，包括用户权限、数据库、表的定义、权限管理、时间区信息等。
        • 内容：用户、授权、系统变量、存储过程等信息。
        • 用途：与安全性和访问控制密切相关，所有关于用户权限的更改都会影响 mysql 数据库中的表。

      • performance_schema

        • 类型：虚拟库
        • 功能：用于监控 MySQL 服务器的性能，提供低开销的方式来分析服务器的执行情况。
        • 内容：它包含与 MySQL 性能相关的各种事件、状态、等待锁定等信息。
        • 用途：用于调试和优化 MySQL 的性能，通过收集和显示性能数据来帮助分析查询、锁定、内存使用等。

      • sys

        • 类型：视图库
        • 功能：MySQL 5.7 开始引入的系统库，基于 performance_schema 和 information_schema，为 DBA 提供了更为友好的性能分析视图。
        • 内容：简化了对性能数据的查询，提供了易于理解的视图和帮助调优的建议。
        • 用途：为数据库管理员提供有用的系统和性能监控信息的简单视图，常用于性能调优和问题排查。

  • 表

    • 管理记录的单元

  • 记录（行）

    • 信息的载体，数据的管理单元

  • 字段（列）

    • 字段名，类型（长度），约束条件（可选）。数据的属性。

  • 类型

    • 对于不同的数据进行分类，比如“你好”是文本，12是数字，这两个类型就不一样了，或者图片和文本，音频和数字。为了方便区分，另外节省空间就有了类型。

  • 约束

    • 对字段的一些强行命令，比如姓名字段不可为空，就是说必须要有个名字。

  • 图示

    

• ddl（命令不区分大小写，但是参数区分）

  • ddl-库

    • 定义数据库

      • 创建数据库

        • create database 数据库名;(分号不能忘)

      • 查看当前dbms（数据库管理系统）中的所有数据库：show databases;

    • 选择/进入数据库

      • use 数据库名;   选择并使用数据库

      • select database（）：查看当前位于的数据库

      • 查看创建过程：show create table/database 表名/数据库名

    • 删除数据库

      • drop database  数据库名；

    • 系统中的位置

      • /var/lib/mysql（使用yum安装的，mysql实际文件夹位置）

  • ddl-表

    • 目的

      • 表是数据库存放数据的基本单位，由若干个字段构成，用记录（行）存放数据。

    • 创建表

      • create table 表名 【这里有空格】（
        列名1 类型（【长度】） 【完整性约束】，
        列名1 类型（长度） 【完整性约束】，
        …………
        ）【数据表的一些相关设置】；

    • 查看：当前数据库的所有表：show tables；对应表表结构：desc 表名；

  • 数据类型（mysql8.0）

    • 数值类型

      • 整型
        • int（有符号-214,783,648到214,783,647，无符号0到4,294,967,295）
        • tinyint（有符号-128到127，无符号0到255）
        • unsigned用法，在创建表的时候   类型   unsigned。
        • zerofill 当不满足类型的最小宽度自动填充0，（同时会自动将列设置为 UNSIGNED），用法   类型（最小宽度）   zerofill。比如：int（最小宽度6） zerofill。
      • 浮点型（小数收到长度约束四舍五入）
        • float（总位数《整数加小数位数》，小数点后位数）：不写默认为float（10,3）双精度形式。{由于 FLOAT 是近似值存储，可能会出现精度损失。例如，在财务计算或精度要求较高的场合，不建议使用 FLOAT}
        • 精确小数decimal（总位数《整数加小数位数》，小数点后位数）：不写默认decimal（7，0）{精确存储，适合财务等精确场景}

    • 字符串类型

      • 字符型
        • char：定长字符串类型（默认1字符），适用于存储长度固定的字符串0-255字符{会删除末尾空格}
        • varchar：变长字符串类型（默认1字符），适合存储长度不一致的字符串65,535 字符（在行长度限制下）{不会删除末尾空格}
      • 枚举与集合（选项为字符串类型）
        • 枚举enum（单选）：只能选择一个预定义的字符串值（最多 65535 个不同的值）（以整数形式存储，映射到对应的字符串值）【enum('value1', 'value2', 'value3', ...)】
        • 集合set（多选）：可以选择多个预定义的字符串值（最多 64 个不同的值）（以位图形式存储，多个值用位标识）【set('value1', 'value2', 'value3', ...)】{插入集合的时候insert into 表 values（‘枚举’，‘集合1，集合2……’）}

    • 时间日期类型（插入数据时用引号包起来）

      • 日期data（YYYY-MM-DD）
      • 时间time（HH:MM:SS）
      • 日期时间datatime（YYYY-MM-DD  HH:MM:SS）
      • 时区默认时间timestamp（自动管理插入和更新时间，使用这个类型，不添加值就默认当前时区时间）
      • 当前
        • curtime（）当前时间，curdate（）当前日期，now（）当前日期时间
        • 可以通过select单独查看，也可以直接作为参数
        • 

  • 完整性约束

    • 默认值，是否空值：default，【not】null

      • create table ly （id int default 1 not null，name varchar（50） default ‘李叶’  not null）；

    • 设置主键：primary key（数据在表中唯一，且不可以为空，主键唯一但是主键字段不唯一）

      • create table ly （id int default 1 primary key auto_increment）；

    • 设置唯一性约束：unique（标志该字段值唯一不可以重复，可以为空<空可以重复>，一个表可以有多高unique）

      • create table ly （ id int ,name varchar(50） unique);
      • create table ly （ id int ,name varchar(50）, unique(id,name));

    • 设置外键约束：foreign key（将多表联动起来，a表改变名字，对应b表也发生改变）（外键就像是硬链接，两个文件一个inode，而且作用只对对应字段值的行整体有用，比如删除主表一行，对应子表行删除，但是不能通过主表对子表行的某一项操作）

      • 父表：create table ly (name varchar(50) not null,mail varchar(40),primary key(name));
      • 子表：create table ly1（student_name varcah(50），id int not null auto_increment，primary key（id），foreign key（name）reference ly（name）on update cascade on delete cascade）；
      • 注意子表通过foreign key设置外键，通过reference声明与父表ly（name）关联（ly中的name是ly的主键才可以），on xxx表示在哪些权利上共享。
      • 注意
        • 外键引用的字段：外键必须引用另一个表中的主键或唯一键（Primary Key 或 Unique Key）
        • 字段类型必须匹配：外键字段和被引用字段的数据类型必须兼容。
        • 允许NULL值：外键字段可以允许为NULL，这意味着某条记录可以不引用父表中的任何记录。
        • 父表的记录必须存在：外键要求引用的父表中的主键记录必须已经存在。

    • 设置复合主键约束：primary key

      • create table ly(id int,name char(10),sex char(2),primary key(id,name，【字段名】));

    • 设置字段自增：auto_increment(默认加1）

      • create table ly （id int auto_increment）；

第三章：sql数据库操作语言DML

• 对数据的管理，写入，删除，更新。

• 插入：insert into 表

  • insert into 表（表内字段）values （对应字段的值）；
  • 不给定字段就表示表内所有字段：insert 表 values （按照顺序一次插入）；
  • 多条数据插入：insert into 表（表内字段）values （对应字段的值），（对应字段的值）；
  • 注意值的数据类型

• 更新：update 表 set 

  • update 表 set 列名=值 where 条件；（按照条件行，修改对应列单元格值，如果没有条件就是整列）

• 删除：delete from 表（删除行数据）

  • delete from 表 where 条件；（不加条件删整个表的数据）

第四章：sql数据库查询语言DQL

• 简单查询

  • select */列名（逗号隔开） from 表名；
  • select 列名*6 from 表名；（还可以四则运算查询）

• 条件查询where

  • 单条件

    • select 查看的列 from 表 where 条件;
    • 可以使用=，<,>,>=,<=,!=/<>
    • 

  • 多条件and/or

    • select 查看的列 from 表 where 条件1 and/or 条件2……；and表示同时满足，or表示满足一项就可以了。

    • 

  • 关键字between and闭区间

    • select 查看的列名 from 表名 where 列名 between 开始 and 结束;在范围之中。
    • 

  • 关键字in集合查询。

    • select 查看列 from 表 where 条件列  in （匹配值，逗号分开）；

    • 

  • 关键字is null/is not null，查询是否为空的值

    • select 查看列 from 表 where 条件列 is null；（注意空格也是字符，可以通过length（列），查看空格多少，中英文1个字符）

    • 

  • 关键字like模糊查询（%表示任意个（0到任意），_任意一个和shell脚本一样）

    • select 查看列 from 表 where 列 like “%”
    • 

• 查询排序

  • select 查看列 from 表 order by 排序的列名（可以多列）asc（升序）/desc（降序）【limit 数字（表示排序到后的前几位，limit可以单独用但是要放到最后，比order还要后）】；（要知道，因为是排序，所以肯定是最后操作，所以排序放在其它操作最后）
  • 

• 子查询

  • 将select语句的结果看做参数用（无论关键字是否有了（）比如in，都建议在子查询外添加括号）包括，比如select * from （select * from 表 limit 30）；这里将子查询的结果当做了表
  • 

• 多表查询

第五章：sql数据库控制语言DCL

• DCL

  • 权限级别

    • global level：全局权限，所有表所有库

    • database level：数据库权限

    • table level：数据表权限

    • column level：表中某个字段的权限

  • mysql用户管理

    • 在 MySQL 数据库中，用户账户的信息存储在 mysql 数据库中的 user 表中。user 表中保存了所有 MySQL 用户的权限、认证方式、密码以及其他相关信息。（root下）

    • 创建用户：

      • create user '用户名'@'指定登录主机'   identified  by '密码';
        • 用户名：是你要创建的用户名。
        • 指定登录主机：表示允许该用户从哪个主机进行连接。常用的选项：
          • 'localhost'：只允许从本地主机连接。
          • '%'：允许从任何主机连接。
        • 密码：必要大小写数字符号且8位

    • 删除用户：

      • drop user '用户名'@'指定主机'；
      • 

    • 修改用户密码：

      • select user（）;查看当前登录用户
      • 方法1：alter  user  '用户名'@'指定主机'  identified[译为验证]  by  '新密码';
      • 方法2：set  password  for   '用户名'@'指定主机'= '新密码';
      • 方法3：mysqladmin -u root -p'与-p没有空格'  password ‘新密码有空格’；
      • 丢失root密码怎么办？
        • vim  /etc/my.cnf添加skip-grant-tables（启动mysql时跳过授权密码）
        • 重启mysqld服务
        • 进入mysql -u root
        • update mysql.user set authentication_string='' where user='root';修改系统用户密码为空
        • vim /etc/my.cnf取消之前的skip-grant-tables
        • 进入系统修改root密码
          • 方法1：set password for '用户'@'localhost'='密码'；
          • 方法2：alter user ‘用户名’@‘主机’ identified by ‘密码’；

    • 登录mysql：mysql -u username -p ‘密码’  【数据库名，直接进入数据库】

      • 如果需要从远程登录 MySQL，请确保 MySQL 服务器的配置允许外部连接，并使用对应的 host 参数：mysql -u username -p -h IP地址
      • -P：端口，mysql默认3306端口
      • -e  ‘sql命令’：免登录mysql直接执行数据库后面是命令

  • mysql权限原理

    • grant（给予）/revoke（撤销） 权限列表 on  数据库名.表名 to/from(取消用from)    '用户名'@'主机' [IDENTIFIED BY '密码'] [WITH GRANT OPTION];{使用*可以设置整个数据库或者这个dbms中的所有数据库}
      • 
      • 
    • revoke/grant 权限 (列名)   on  数据库名.表名   to/from(取消用from)  '用户名'@'主机';

      • 权限列表：指定要授予的权限，可以是以下任意一个或多个：

        • ALL PRIVILEGES（all也可以）：授予所有权限。
        • SELECT：允许查询数据。
        • INSERT：允许插入数据。
        • UPDATE：允许更新数据。
        • DELETE：允许删除数据。
        • CREATE：允许创建新数据库和表。
        • DROP：允许删除数据库和表。
        • GRANT OPTION：允许将自己的权限授予其他用户。

      • ON 数据库名.表名：指定权限应用于哪个数据库和表。

        • 如果是对整个数据库授予权限，可以使用 数据库名.*。

      • TO '用户名'@'主机'：指定要授予权限的用户及其主机。

        • 用户名：要授予权限的用户名。
        • 主机：指定用户可以从哪个主机连接，常用的有 localhost（仅本地主机）和 %（任意主机，ip，%，网段+%都可以）。

      • [IDENTIFIED BY '密码']：可选项，通常在创建用户时使用，表示设置用户的密码。注意：在使用 GRANT 语句时，如果用户已经存在，则通常不需要这个选项。

      • [WITH GRANT OPTION]：可选项，通常用于授予 GRANT OPTION 权限，允许用户将他们所拥有的权限授予其他用户。

        • 转授的限制：

          • 用户只能转授他们自己拥有的权限。例如，如果某用户只拥有 SELECT 权限，则他们不能授予其他用户 UPDATE 或 DELETE 权限。

        • 安全风险：

          • 授予 WITH GRANT OPTION 可能带来安全风险，因为它允许用户自行分配权限。因此，谨慎使用，确保只对可信任的用户启用该选项。

        • 撤销权限时的影响：

          • 如果你撤销某用户的权限，并且该用户使用 WITH GRANT OPTION 授予了其他用户权限，系统将自动撤销该用户所转授的权限。

    • 刷新：flush privileges;查看：show   grants  for '用户名'@'主机'（show grants查看当前用户权限）;

• MySQL日志

  • 错误日志

    • 错误日志记录 MySQL 服务器的启动、停止以及在运行过程中发生的任何错误信息。它是管理员排查问题的关键日志文件，尤其是在 MySQL 启动失败或发生重大错误时。
    • 配置错误日志（重启生效）：vim /etc/my.cnf    中  通过log_error=/var/log/mysql/error.log（默认）
    • 查看错误日志
      • cat 文件  或者     在mysql   中  select @@log_error；

  • 二进制日志

    • 二进制日志用于记录所有对数据库进行更改的事件，如 INSERT、UPDATE、DELETE 等。它不记录 SELECT 查询。二进制日志的主要作用是数据恢复和主从复制。
    • 配置二进制日志（重启生效）：vim /etc/my.cnf    中  通过log_bin = /var/log/mysql/mysql-bin.log（默认）
    • 查看二进制日志
      • mysqlbinlog /var/log/mysql/mysql-bin.000001

  • 慢日志

    • 慢查询日志记录所有执行时间超过指定阈值（long_query_time）的查询语句。它是优化 MySQL 性能的重要工具，用于发现执行缓慢的查询。

第六章：sql数据库备份管理

• 备份技术

  • 冷备份

    • 冷备份是指在备份数据时，系统或数据库完全停止运行，并且不接受任何读写操作。整个系统处于“离线”状态，这种备份方式相对安全，因为没有用户活动或数据库事务在进行，数据的一致性得以保证。
    • 关闭数据库服务——tar数据库数据目录——cp/scp到备份文件存放位置
    • scp 本地文件路径 用户名@远程主机:远程路径
    • 

  • 热备份

    • 备份是指在系统或数据库正常运行、不断服务的情况下进行的数据备份。它不需要停机，系统可以在备份过程中继续处理用户请求和事务。为了保证备份数据的一致性，通常会使用锁定机制或事务日志。

• 备份模式

  • 完备备份

    • 完备备份是指将指定的所有数据、文件或数据库完整地备份，无论数据是否发生变化。这是最基础、最全面的一种备份方式。

  • 增量备份

    • 增量备份是指自上次备份（无论是完备备份还是增量备份）之后，仅备份发生了变化的数据。每次增量备份只包含自上次备份后新增或修改的数据。

  • 差异备份

    • 差异备份是指自上次完备备份之后，所有发生了变化的数据，不论中间是否有过增量备份或差异备份。每次差异备份的内容包括自上次完备备份以来所有的修改数据。

• 备份操作

  • （percona-xtrabackup80，mysql8.0），（percona-xtrabackup24，mysql5.7）

  • 安装：

    • yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpmhttps://repo.percona.com/yum/percona-release-latest.noarch.rpm（无法使用就换版本比如：https://repo.percona.com/yum/percona-release-1.0-9.noarch.rpmhttps://repo.percona.com/yum/percona-release-1.0-9.noarch.rpmhttps://repo.percona.com/yum/percona-release-1.0-9.noarch.rpm）https://repo.percona.com/yum/percona-release-latest.noarch.rpm（仓库配置）yum makecache（刷新一下）
      • 
    • yum install percona-xtrabackup-80（安装软件）

  • 备份

    • # 1. 完整备份
      xtrabackup 【--defaults-file=配置文件位置，如果为/etc/my.cnf就可以不写】 --backup --target-dir=完整备份目录（要先创建） --user=mysql用户 --password=密码
      # --backup：指定进行备份操作
      # --target-dir：设置备份数据存放的目录
      # --user（简写-u用户名）：用于连接 MySQL 的用户名
      # --password（简写-p密码）：连接 MySQL 的密码
      
      # 2. 增量备份
      xtrabackup 【--defaults-file=配置文件位置，如果为/etc/my.cnf就可以不写】 --backup --target-dir=增量备份目录 --incremental --incremental-basedir=完全备份目录 --user=mysq用户  --password=密码
      # --incremental：启用增量备份
      # --incremental-basedir：指定上次完整备份的目录
      
      # 进行第二次增量备份
      xtrabackup 【--defaults-file=配置文件位置，如果为/etc/my.cnf就可以不写】 --backup --target-dir=增量备份目录2  --incremental --incremental-basedir=完全备份目录  --user=mysql目录  --password=密码
      
      # 3. 准备完整备份
      xtrabackup --prepare --target-dir=已经备份的完整备份目录
      # --prepare：准备备份，使其可用于恢复
      
      # 4. 准备增量备份
      xtrabackup --prepare --apply-log-only --target-dir=完全备份目录 --incremental-dir=某个增量备份
      # --apply-log-only：仅应用日志，但不标记为完全恢复（意思是告诉系统，我还有要加的，别急，最后一次增量不写，表示告诉系统可以了，我完整了，是一个完整备份，不要在来数据了，来了也不要。）
      
      # 5. 恢复备份（恢复要关mysqld）
      xtrabackup 【--defaults-file=配置文件位置，如果为/etc/my.cnf就可以不写】 --copy-back --target-dir=完全备份目录
      # --copy-back：将准备好的备份文件复制回 MySQL 数据目录
      
      # 6. 权限修复
      chown -R mysql:mysql /var/lib/mysql
      # 确保 MySQL 数据目录的权限正确
      
      # 7. 启动 MySQL 服务
      systemctl start mysqld
      
      # 排错：
      # 如果提示版本问题，就添加--no-server-version-check
      
      # 差异备份
      # 简单的说，就是基于完全备份+最后一次增量备份就可以了（注意不要有--apply-log-only）
      
      # 压缩与解压备份
      # --compess（放到备份/增量备份语句中xtrabackup --backup --compress --compress --target-dir=/压缩备份 --user=mysql_user --password=your_password）
      # --compress-threads=4：设置压缩时使用的线程数，默认为 1，设置为 4 代表用 4 个线程压缩。
      # --decmpress（xtrabackup --prepare --target-dir=/压缩目录）
      # 最后恢复就可以了，记得授权

第七章：数据库代理服务与集群管理