02.Mysql基础入门-部署与管理体系

# **1.5.7,8.0版本企业规范部署,启动**

## **1.1.准备**

- 硬件:服务器标准化

```sql
与公司基础运维团队提供采购建议,根据业务场景和压力选择适合
```

- 软件:系统版本与系统参数优化
- 数据库版本标准化

```sql
1.确认Supported Platforms
https://www.mysql.com/support/

2.确认安装版本
推荐:5.7.22 ,8.0.20以后的双数GA版本

3.获取MySQL软件包
https://downloads.mysql.com/archives/community/

4.MD5验证
```

- 数据库软件工具标准化
- **CMDB平台标准化-软件打包**

## 1.2.Mysql部署安装

https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

```sql
1.解压
tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

2.软连接
shell> ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql

3. 环境清理
shell> yum remove -y mariadb-libs
shell> yum install -y libaio-devel

4. 创建用户
shell> useradd mysql

5. 创建目录
shell> mkdir -p /data/3306/data/

6. 授权
shell> chown -R mysql.mysql /

7. 初始化数据
shell>  vim /etc/profile
shell> export PATH=/usr/local/mysql/bin:$PATH
shell> source /etc/profile
shell> mysqld --initialize-insecure --user=mysql --basedir=/chj/app/mysql-5.7.30-linux-glibc2.12-x86_64/  --datadir=/chj/data/mysql_test/

8. 配置文件
mkdir -p /chj/data/mysql_test/{log,etc,tmp}
vim /etc/my.cnf
[mysql]
# CLIENT #
no_auto_rehash
port                           = 3306
socket                         = /chj/data/mysql_3306/tmp/mysql.sock
default_character_set          = utf8mb4
prompt                         = "\u:\p:\d>"

[mysqld]
user                             = mysql
port                             = 3306
default_storage_engine           = InnoDB
character_set_server             = utf8mb4
init_connect                     = 'SET NAMES utf8mb4'
transaction_isolation            = REPEATABLE-READ
basedir                          = /chj/app/mysql-5.7.30-linux-glibc2.12-x86_64/
datadir                          = /chj/data/mysql_3306/var/
socket                           = /chj/data/mysql_3306/tmp/mysql.sock
pid_file                         = /chj/data/mysql_3306/var/mysql.pid
tmpdir                           = /chj/data/mysql_3306/tmp/
slave_load_tmpdir                = /chj/data/mysql_3306/tmp/

### LOGGING ###
#log_error_verbosity            = 2
log_error                      = /chj/data/mysql_3306/log/mysql.err
general_log                    = 0
general_log_file               = /chj/data/mysql_3306/log/mysql.log

9. 启动数据库
[root@ccsp-dbmgnt-binlogserver-1 mysql_3306]#  /chj/app/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqld_safe  --defaults-file=etc/my.cnf --user=mysql &

10.连接数据库
mysql -u -p -S ./tmp/mysql.sock   #端口连接
mysql -u -p -h -P                 #TCP链接

11.关闭数据库
mysqladmin  shutdown -S ./tmp/mysql.sock

12.创建用户和自定义监控平台后,打包数据库
tar zcvf   mysql_init.tar.gz    /chj/data/mysql_3306/
```

```sql
[mysql]
# CLIENT #
no_auto_rehash
port                           = 3306
socket                         = /chj/class/data/mysql57/mysql.sock
default_character_set          = utf8mb4
prompt                         = "\u:\p:\d>"

[mysqld]
user                             = mysql
port                             = 3306
default_storage_engine           = InnoDB
character_set_server             = utf8mb4
init_connect                     = 'SET NAMES utf8mb4'
transaction_isolation            = REPEATABLE-READ
basedir                          = /chj/app/mysql-5.7.30-linux-glibc2.12-x86_64/
datadir                          = /chj/class/data/mysql57/var/
socket                           = /chj/class/data/mysql57/tmp/mysql.sock
pid_file                         = /chj/class/data/mysql57/tmp/mysql.pid
tmpdir                           = /chj/class/data/mysql57/tmp/
slave_load_tmpdir                = /chj/class/data/mysql57/tmp/

### LOGGING ###
#log_error_verbosity            = 2
log_error                      = /chj/class/data/mysql57/log/mysql.err
general_log                    = 0
general_log_file               = /chj/class/data/mysql57/log/mysql.log
```

# **2.MySQL架构体系讲解**

## **2.1.客户端与服务端模式**

```sql
客户端遵从MySQL Protocol,一套用来连接MySQL client和MySQL Server端的协议，用来支持
- Connectors (Connector/C, Connector/J, and so forth)
- MySQL Proxy
- Communication between master and slave replication servers

连接方式:
  Socket (Unix Scoket)
  TCP/IP
  API:C、PHP、JDBC、ODBC、.NET、Python、Go...
```

- MySQL基架的几大模块组件
    - MySQL向外提供的交互接口（Connectors）
        - 对外交互组件,API,如java,python,go等语言可以通过该组件连接数据库
    - 管理服务组件和工具组件(Management Service & Utilities)
        - 集成对MySQL的管理,比如备份，恢复等
    - 连接池组件(Connection Pool)
        - 负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。
        - 每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。
    - SQL接口组件(SQL Interface)
        - 接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户
    - 查询分析器组件(Parser)
        - 分析SQL语法合法
        - SQL命令分解成数据结构
        - 失败则提示语法错误
    - 优化器组件（Optimizer）
        - 对SQL命令按照标准流程进行优化分析
    - 缓存主件（Caches & Buffers）
        - 缓存和缓冲组件
    - 插件式存储引擎（Pluggable Storage Engines）
        - MySQL对表的创建,数据的存储与检索靠引擎来实现
    - 物理文件（File System）
        - data
        - 日志
        - 配置文件等

![1](https://img.sunrisenan.com/img/2024/03/18/200026844.png)

## **2.2.一条SQL语句的执行**

```
查询语句的执行分为以下几步：

1. 查询缓存
2. 解析器生成解析树
3. 预处理再次生成解析树
4. 查询优化器
5. 查询执行计划
6. 查询执行引擎
7. 查询数据返回结果
```

![Untitled](https://img.sunrisenan.com/img/2024/03/18/200339985.png)

### **2.2.1.查询缓存**

```
mysql> show variables like 'query_cache%';
1.MySQL 拿到一个查询请求后先会在查询缓存中看看是否执行过此语句，之前执行的语句会以 key-value 的形式缓存在内存中，key 是缓存的语句，value 是查询的结果
2.如果命中缓存则直接将结果返回,如果没有命中则继续执行后面

**额外补充**生产环境中我一般会关闭cache，why?
1.命中率低:请求sql与环境(连接的数据库、协议版本、字符集等)要与缓存完全一致
2.资源消耗大:QC需要缓存最新的数据,所以在数据发生变化时(比如增删改操作)QC就会刷新,这导致缓存会频繁的写入、擦除,消耗过多资源
3.QC适用场景少:以下场景无效
  读取mysql的系统表
  子查询或者外层查询
  查询语句中用到随机函数,或者now这种函数,给查询结果带来不确定性(每次查询结果会有差异)
  查询中用到了视图,临时表
  存储过程,触发器中调用的sql也不适合
  查询语句加了SQL_NO_CACHE
  查询语句未引用任何表
  类似select into这种sql,显示加锁的sql都不会去缓存
  验证用户的访问权限(用户无权限则拒绝)
  **全局锁控制**每次更新QC的内存块都需要进行锁定，查询结果必须大于
  MySQL 8.0 直接删除查询缓存

最为重要的是，在MySQL里QC是由一个全局锁在控制，每次更新QC的内存块都需要进行锁定。
例如，一次查询结果是20KB，参数query_cache_min_res_unit 值设置为 4KB），查询结果共需要分为5次写入QC，每次都要锁定，而且容易造成block碎片

所以QC适合表数据量少,更新频率低,查询比较多,比如配置表、权限表。

关闭方法很简单,有两种:
1、同时设置选项 query_cache_type = 0 和 query_cache_size = 0；
2、如果用源码编译MySQL的话，编译时增加参数 --without-query-cache 即可；

**生产环境怎么办**
缓存数据给第三方处理,比如redis
```

![3](https://img.sunrisenan.com/img/2024/03/18/200449389.png)

![4](https://img.sunrisenan.com/img/2024/03/18/200643803.png)

**课后作业**

```
show status;
show variables;

是两个DBA运维MySQL最常用的两个查看命令，尝试去概览一遍大概的变量，没有基础的同学可以挑几个感兴趣的值去官方文档或者百度一下，有基础的同学建议导图进行功能分类,比如
 show variables like '%innodb%';
 show status like '%Com%'; Com_select Com_insert Com_delete
```

### **2.2.2.解析器与解析树**

1.语法解析

```
语法解析是解析你的语句是不是满足 MySQL 语法标准，如果不对则会 ：
ERROR 1064 (42000): You have an error in your SQL syntax … 关于错误码在官网有说明

官方地址:
<https://dev.mysql.com/doc/refman/8.0/en/error-message-elements.html>
<https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_open_as_readonly>

#中文对照表
<https://www.cnblogs.com/Nedved/p/10472467.html>
```

2.词法解析

```
select name from user_info where sex=1 and age>20
```

![5](https://img.sunrisenan.com/img/2024/03/18/200821527.png)

### **2.2.3.生成解析树**

1.语法解析

```
语法解析是解析你的语句是不是满足 MySQL 语法标准，如果不对则会 ：
ERROR 1064 (42000): You have an error in your SQL syntax … 关于错误码在官网有说明

官方地址:
<https://dev.mysql.com/doc/refman/8.0/en/error-message-elements.html>
<https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_open_as_readonly>

#中文对照表
<https://www.cnblogs.com/Nedved/p/10472467.html>
```

2.词法解析

```
select name from user_info where sex=1 and age>20
```

### **2.2.4.查询优化器**

1.生成执行计划

```
生成一个执行计划,要做一些事情：
  选择最合适的索引；
  选择表扫还是走索引；
  选择表关联顺序；
  优化 where 子句；
  排除管理中无用表；
  决定 order by 和 group by 是否走索引；
  尝试使用 inner join 替换 outer join；
  简化子查询，决定结果缓存；
  合并视图；
```

2.查询执行计划

查询最后一次查询的消耗,选择开销最小的计划进行

```
show status like 'Last_query_cost';
```

### **2.2.5.查询执行引擎与返回结果**

这里执行器会先对权限做一个判断，如果有权限，才会执行以下步骤，否则跑出权限异常：

```
调用 Innodb 引擎接口获取这个表或索引的第一行，筛选sex=1和age>20的结果集
如果不是跳过，如果是则存在结果集中；
引擎执行下一行，重复判断相同的逻辑，直到最后一行；
最后将满足结果的结果集返回；
```

## **2.3.MySQL内部结构**

## **2.3.1.文件结构**

![6](https://img.sunrisenan.com/img/2024/03/18/200933173.png)

## **2.3.2.内存结构**

- 共享内存:整个数据库实例共享

```
show variables where variable_name in (       'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size'       );

参数说明：
innodb_buffer_pool

Innodb 引擎最重要的缓存区域,性能决定性参数之一。
其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。
优点:提升数据库的性能、加快 SQL 运行速度
缺点:故障发生后值越大恢复速度较慢

innodb_log_buffer
存放redo log
innodb_additional_mem_pool
该部分主要存放 InnoDB 内的一些数据结构.
key_buffer
MyISAM 表的重要缓存区域,索引缓存再此

query_cache
查询缓存
```

- Session私有内存:每个线程连接是独有

```
  show variables where variable_name in (       'read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size'       );
  ```

共享内存+1000Xsession内存

## **2.3.3.线程结构**

![7](https://img.sunrisenan.com/img/2024/03/18/201030873.png)

![](/media/202403/2024-03-18_201123_0817210.5685391819989968.png)

### **2.1.3 MySQL 8.0的"新姿势"**

- Metadata 结构变化
  - 5.7 版本问题
    - 两套数据字典信息（Server层 frm，InnoDB 数据字典）
    - DDL无原子化
    - frm和innodb层会出现不一致
    - 并发处理需要小心处理（MDL,dict_sys::mutex,dict_sys::rw_lock）
    - 崩溃无法恢复
  - 8.0 变化
    - 支持事务性DDL，崩溃可以回滚，保证一致。
    - 保留一份数据字典信息，取消frm数据字典。
    - 数据字典存放至InnoDB表中
    - 采用套锁机制，管理数据字典的并发访问（MDL）
    - 全新的Plugin支持
      - 8.0.17+ 加入Clone Plugin,更好的支持MGR，InnoDB Cluster的节点管理
    - 安全加密方式改变
      - 改变加密方式为caching_sha2_password
      - SSL 将支持到 TLSv1.3 版本。
    - 用户管理及认证方式改变
      - 改变授权管理方式
      - 加入role角色管理
      - 添加更多权限
    - 原子性DDL
      - 支持原子性DDL
    - Cache && Buffer的变化
      - 取消Query Cache