Mysql的培训文档

2021-06-22  本文已影响0人  苏水的北

一、Mysql数据库的搭建：

1、实施部署：

1.1 创建相关目录：

[root@db01 ~]# mkdir /application           ----> 软件的存放目录
[root@db01 /]# mkdir /data/3306/data -p     ----> 数据的存放位置

1.2 创建用户：

[root@db01 /]# useradd -s /sbin/nologin -M mysql

1.3 上传软件并解压处理：

[root@db01 application]# ll
total 1079692
-rw-r--r--  1 mysql mysql 644869837 Aug  5 09:31 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db01 /application]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64
[root@db01 /application]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@db01 application]# ll
total 1079692
drwxr-xr-x  9 mysql mysql       129 Aug  5 12:22 mysql
-rw-r--r--  1 mysql mysql 644869837 Aug  5 09:31 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
注：软件包可以去mysql.com官网下载

1.4 数据初始化（创建系统数据）：

[root@db01 ~]# rpm -qa|grep maria
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@db01 ~]# yum  remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
"注：由于centos7系统自带mariadb数据库，所以必须要将其删除。"
[root@db01 ~]# vim /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@db01 ~]# mysql -V   //次命令用于检查mysql版本信息
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@db01 data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3306/data  //数据库初始化命令
2019-08-05T08:41:34.391208Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-05T08:41:36.705525Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-05T08:41:36.910614Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-05T08:41:37.021365Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d63578ab-b75c-11e9-a331-000c29a71104.
2019-08-05T08:41:37.022771Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-05T08:41:37.023736Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

1.5 书写配置文件：

cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
prompt=wuxin[\\d]>   //wuxin变量可以自己定义
EOF

1.6 准备启动脚本：

[root@db01 ~]#chown -R mysql.mysql /data/* /application/mysql/*
[root@db01 ~]#cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld 
[root@db01 ~]# /etc/init.d/mysqld start   //启动mysql方法
Starting MySQL.Logging to '/data/3306/data/db01.err'.
SUCCESS!

1.7 使用systemd管理mysql:

[root@db01 ~]# /etc/init.d/mysqld start   //因为要用systemctl去管理，所以先按上一步的方法关闭mysql
[root@db01 ~]# cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
注意：将原来模式启动mysqld先关闭，然后再用systemd管理。

1.8 用systemctl管理启动数据库：

[root@db01 ~]#systemctl  start   mysqld

2、Mysql的体系结构：

MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为三层，即 MySQL Server 层、存储引擎层和文件系统层。MySQL Server 层又包括连接层和 SQL 层。如下是官方文档中 MySQL 的基础架构图：


image.png
image.png
image.png

Connectors 不属于以上任何一层，可以将 Connectors 理解为各种客户端、应用服务，主要指的是不同语言与 SQL 的交互。

  1. 连接层
    应用程序通过接口（如 ODBC、JDBC）来连接 MySQL，最先连接处理的是连接层。连接层包括通信协议、线程处理、用户名密码认证 3 部分。
    1.1、通信协议负责检测客户端版本是否兼容 MySQL 服务端。
    1.2、线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条 SQL 对应一个线程，一个线程对应一个逻辑 CPU，在多个逻辑 CPU 之间进行切换。
    1.3、密码认证用来验证用户创建的账号、密码，以及 host 主机授权是否可以连接到 MySQL 服务器。

Connection Pool（连接池）属于连接层。由于每次建立连接都需要消耗很多时间，连接池的作用就是将用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

  1. SQL层
    SQL 层是 MySQL 的核心，MySQL 的核心服务都是在这层实现的。主要包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。
    2.1、权限判断可以审核用户有没有访问某个库、某个表，或者表里某行数据的权限。
    2.2、查询缓存通过 Query Cache 进行操作，如果数据在 Query Cache 中，则直接返回结果给客户端，不必再进行查询解析、优化和执行等过程。
    2.3、查询解析器针对 SQL 语句进行解析，判断语法是否正确。
    2.4、预处理器对解析器无法解析的语义进行处理。
    2.5、查询优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的 API 接口，通过存储引擎层访问数据。
    Management Services & Utilities、SQL Interface、Parser、Optimizer 和 Caches & Buffers 属于 SQL 层，详细说明如下表所示。
名称 说明
Management Services & Utilities MySQL 的系统管理和控制工具，包括备份恢复、MySQL 复制、集群等。
SQL Interface（SQL 接口） 用来接收用户的 SQL 命令，返回用户需要查询的结果。例如 SELECT FROM 就是调用 SQL Interface。
Parser（查询解析器） 在 SQL 命令传递到解析器的时候会被解析器验证和解析，以便 MySQL 优化器可以识别的数据结构或返回 SQL 语句的错误。
Optimizer（查询优化器） SQL 语句在查询之前会使用查询优化器对查询进行优化，同时验证用户是否有权限进行查询，缓存中是否有可用的最新数据。它使用“选取-投影-连接”策略进行查询。 例如 SELECT id, name FROM student WHERE gender = "女";语句中，SELECT 查询先根据 WHERE 语句进行选取，而不是将表全部查询出来以后再进行 gender 过滤。SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
PCaches & Buffers（查询缓存） 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等。

  1. 存储引擎层
    Pluggable Storage Engines 属于存储引擎层。存储引擎层是 MySQL 数据库区别于其他数据库最核心的一点，也是 MySQL 最具特色的一个地方。主要负责 MySQL 中数据的存储和提取。
    因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

  2. 文件系统层
    文件系统层主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。

思考问题：Mysql 一条sql查询语句是如何执行的？
第一步：应用程序把查询SQL语句发送给服务器端执行。
第二步：查询缓存，服务器在解析一个查询语句之前，在数据库的查询缓存中找是否有相对应的查询数据，如果存在，那么在返回查询结果之前，MySQL会检查一次用户权限，权限没问题就会返回查询结果。如果语句不在查询缓存中，就会继续后面的执行阶段。
第三步：查询优化处理，生成执行计划 (没有命中缓存情况下会到这一步)
这个阶段包括：解析SQL、预处理、优化SQL生成执行计划（其中任何一个阶段出错都会导致查询进行不下去）。
第四步：Mysql根据相应的执行计划完成整个查询。
第五步：将查询结果返回客户端。


image.png

3、Mysql的存储引擎：

3.1、 InnoDB存储引擎表底层存储结构：
段 : 一个表就是一个的段(非分区表)
区 : 固定大小1M, 连续的64个页
页 : 最小的IO单元,默认16K
3.2、 InnoDB存储引擎事务的ACID特性：
A（原子性）：所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
例如：发红包，必须一个加钱一个减钱，这两条语句不可拆分。
C（一致性）：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
例如：数据必须是一致的，一个加50，一个减50，总数还是不变，这就是一致性。
I（隔离性）：事务之间不相互影响。
例如：比如抢到一张票30分钟内支付就可以，在这个时候我占用，别人就不会再获取这张票，这就是隔离性。
D（持久性）：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
3.3、SQL常用分类：
DDL（数据定义语言）：create、alter、drop、truncate
DCL（数据控制语言）：grant、revoke
DML（数据操作语言）：insert、delete、update
DQL（数据查询语言）：select
3.4、Mysql常用查询sql：
查库：show databases
查表：show tables
查字符集：show charset
查询mysql数据库下的用户：select user,host from mysql.user;
创建用户：create user wuxin@'10.0.0.%'；
删除用户：drop user wuxin@'10.0.0.%'；
建库：create database test charset utf8mb4；
3.5、Mysql数据库约束：
主键（非空且唯一）：primary key
非空：not null
唯一：unique key
表的约束建议：

  1. 一个表有且一个主键列,最好是一个无关列数字列,一般会在表中设置自增长的id列；
  2. 尽量每个列非空,如果无法保证,可以追加默认值。
    3.6、建库、建表规范：
    建库的规范:
  3. 库名是小写.
  4. 库名不能是数字开头.
  5. 库名要和业务有关.
  6. 建库时要添加字符集.
    建表规范:
  7. 表名小写,无数字开头,与业务有关.
  8. 必须要有主键,一般是一个自增长的无关列
  9. 选择合适的数据类型,字符长度要适中
  10. 每个列都非空,并设定默认值
  11. 每个列必须要有注释
  12. 必须设置存储引擎和字符集
    3.7、库授权的授权范围：
    创建zabbix用户，把所有权限给zabbix库：
    grant all on zabbix.* to zabbix@'10.0.0.%' identified by 'zabbix';
    库授权范围：
    全库级别：.
    单库级别:zabbix.*
    单表级别:zabbix.t1
上一篇下一篇

猜你喜欢

热点阅读