第二阶段：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/mysqld.log（默认）
      • 查看错误日志
        • cat 文件  或者     在mysql   中  select @@log_error；

    • 二进制日志

      • 二进制日志用于记录所有对数据库进行更改的事件，如 INSERT、UPDATE、DELETE 等。它不记录 SELECT ,show查询。二进制日志的主要作用是数据恢复和主从复制。
      • 配置二进制日志（重启生效）：vim /etc/my.cnf    中  通过
        server_id = 1 # 设置为一个唯一的整数
        log_bin = /var/log/mysql/mysql-bin.log # 启用二进制日志
        • 
      • 查看二进制日志（每次重启服务都会生成一个新文件）
        • mysqlbinlog   /var/log/mysql_bin.log # 启用二进制日志（要先创建对应的文件文件夹,chown -R mysql:mysql 目录，给目录权限）
          • 
          • server_id 是 MySQL 中用于标识每个服务器实例的一个唯一整数标识符，主要用于 MySQL 主从复制（Replication）。每个参与复制的 MySQL 服务器（无论是主服务器还是从服务器）都需要设置唯一的 server_id，以便在数据同步时区分不同的服务器。（1 和 4294967295闭区间）

          • 主从复制：MySQL 的复制机制中，server_id 是用来唯一标识不同的 MySQL 实例的，防止数据混乱。如果主服务器和从服务器的 server_id 相同，会导致复制失败。

          • 二进制日志：当启用二进制日志时，server_id 也用于标识哪些更改事件是当前服务器自己生成的，以防止循环复制。

        • 查看二进制日志文件相关状况：show binary  logs；

          • ​​​​​​​

      • 二进制日志格式（my.cnf中添加binlog_format=ROW《默认row》）

        • select @@binlog_format;查看binlog格式

          • SBR: 记录 SQL 语句，适用于简单操作，日志较小，但可能导致不一致。
          • RBR: 记录行级变更，适用于复杂操作，保证一致性，但日志较大。
          • MBR: 结合 SBR 和 RBR，灵活应对不同场景，但可能导致复杂性。

    • 慢日志

      • 慢查询日志记录所有执行时间超过指定阈值（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=/压缩目录）
        # 最后恢复就可以了，记得授权

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