mysql 初始化权限表_day128:MySQL进阶:MySQL安装&用户/权限/链接/配置管理&MySQL的体系结构&SQL&MySQL索引和执行计划...

目录

3.MySQL的体系结构segmentfault

1.介绍和安装

1.1 数据库分类

RDBMS(关系型数据库):Relational Database Management Systemsocket

表明产品:Oracle  MySQL  MSSQL PG

NoSQL(非关系型数据库):Not Only SQL

表明做品:MongoDB  Redis  ES

NewSQL(对各类新的可扩展/高性能数据库的简称)

表明做品:spanner  PolarDB(X)  TDSQL  TiDB  高斯

1.2 MySQL分支

1.Oracle

2.Percona

3.MariaDB

4.云厂商

1.3 MySQL获取

www.mysql.com

1.4 MySQL安装

#1.解压

[root@localhost opt]#tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

#2.软链接

ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql#3.修改环境变量

vim /etc/profile

添加下面的内容

export PATH=/usr/local/mysql/bin:$PATH

source/etc/profile#4.建立用户 目录 配置文件

[root@localhost mysql]#useradd mysql

[root@localhost mysql]#mkdir -p /data/3306/data

[root@localhost mysql]#chown -R mysql.mysql /data

[root@localhost mysql]#vim /etc/my.cnf

[mysqld]

user=mysql

basedir=/usr/local/mysql

datadir=/data/3306/data

socket=/tmp/mysql.sock

[mysql]

socket=/tmp/mysql.sock#5.初始化数据

若是没有配置文件:

mysqld--initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

若是有配置文件:

[root@localhost data]#mysqld --initialize-insecure

#6.准备启动脚本

[root@localhost ~]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@localhost ~]#/etc/init.d/mysqld stop

Shutting down MySQL... SUCCESS!

[root@localhost~]#/etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

[root@localhost~]#/etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@localhost~]#/etc/init.d/mysqld status

SUCCESS! MySQL running (12134)

2.基础管理

2.1 用户管理

2.1.1 做用

1.用来登陆MySQL

2.用来管理MySQL对象[ps:什么是MySQL对象??]

2.1.2 定义

语法:用户名@'白名单'

什么是白名单??  答：IP地址范围

常见形式:

root@'%'

root@'localhost'

root@'10.0.0.%'

root@'10.0.0.2'

2.1.3 管理用户

1.查询用户

--查询用户

mysql> select user,host ,authentication_string ,plugin from mysql.user;+------------------+-----------+------------------------------------------------------------------------+-----------------------+

| user | host | authentication_string | plugin |

+------------------+-----------+------------------------------------------------------------------------+-----------------------+

| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| root | localhost | | caching_sha2_password |

+------------------+-----------+------------------------------------------------------------------------+-----------------------+

注意: plugin,加密插件,在8.0以后作了升级,caching_sha2_password,安全性增高了.

会致使不少老的客户端程序没法链接至MySQL.早期版本是mysql_native_password.

2.建立用户/修改用户/删除用户

--建立用户

mysql> create user root@'10.0.0.%' identified by '123';

mysql> create user user1@'10.0.0.%' identified with mysql_native_password by '123';--修改用户

mysql> alter user root@'10.0.0.%' identified with mysql_native_password by '123';

mysql> alter user user1@'10.0.0.%'account lock;

mysql> alter user user1@'10.0.0.%'account unlock;--删除用户

mysql> drop user user1@'10.0.0.%';

注意: 8.0 以后 ,只能先建用户后受权.

2.2 权限管理

2.2.1 权限列表

mysql> show privileges;

最多见的权限:

1.ALL ? 不包含 grant option

2.select ,insert ,update ,delete

2.2.2 受权和回收权限

--受权

mysql> grant all on *.* to root@'10.0.0.%';--查询权限

mysql> show grants for root@'10.0.0.%';--回收权限

mysql> revoke drop on *.* from root@'10.0.0.%' ;

关于权限做用范围:

*.* : 全部库下全部表

luffy.*: 单库下的全部表

luffy.user: 单表

2.3 链接管理

2.3.1 socket文件链接

--前提: 须要提早建立localhost白名单的用户

[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock

2.3.2 TCP/IP方式

--前提 须要将登录客户端IP加入白名单

[root@localhost ~]# mysql -uroot -p123 -h 10.0.0.111 -P3306

2.4 配置管理

2.4.1 离线配置

--配置文件应用顺序

[root@localhost ~]# mysqld --help --verbose |grep my.cnf

/etc/my.cnf ---> /etc/mysql/my.cnf ---> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf

--配置文件结构

[root@localhost ~]# cat /etc/my.cnf[mysqld]

user=mysql

basedir=/usr/local/mysql

datadir=/data/3306/data

socket=/tmp/mysql.sock[mysql]socket=/tmp/mysql.sock

注意:修改配置文件,重启数据库.

2.4.2 在线配置

--经过专用配置命令进行修改.

mysql> set global innodb_buffer_pool_size=16777216;

--查看全部能够在线配置的参数

show variables

show variableslike '%date%';

3.MySQL的体系结构

很少说,直接上图.

1.链接层

链接层做用有三:

1.提供链接协议: Socket文件,TCP/IP

2.验证用户身份/受权表.

3.链接层提供了一个与sql层交互的线程

2.SQL层

SQL层做用有六:

1.接收链接层传过来的sql语句

2.验证sql语句的语法

3.验证sql语句的语义(DDL,DQL,DML,DCL)

4.解析器：解析sql语句，生成执行计划

5.优化器：从执行计划中选择最优的一条

优化???? 优化(逻辑优化,物理优化(索引)

6.执行器：执行选出来的SQL计划

1.与存储引擎层创建交互的线程

2.将要执行的sql语句传到存储引擎层

3.Engine层

做用:负责和磁盘交互

4.SQL

4.1 什么是SQL?

关系型数据库通用的语言.结构化查询语言.[ps:什么是结构化查询语言?]

4.2 SQL_MODE

经常使用SQL_MODE参数:[ps:SQL_MODE详解]

mysql> select @@sql_mode;+-----------------------------------------------------------------------------------------------------------------------+

| @@sql_mode |

+-----------------------------------------------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

+-----------------------------------------------------------------------------------------------------------------------+

重点: ONLY_FULL_GROUP_BY ,5.7版本新特性.

4.3 数据类型

特别注意:

char(10) 定长

varchar(10) 变长

4.4 字符集

utf8 最多存三字节字符

utf8mb4 最多存四字节字符 [emoji表情]

4.5 SQL种类

DDL : 数据定义语言

DML : 数据操做语言

什么是DDL(数据定义语言?),什么是DML(数据操做语言?):[ps:DDL和DML的定义]

4.6 DDL的应用规范

4.6.1 对库(Database)的操做

库:

create database

drop database

alter database

规范:

a. 生产系统禁用drop 操做

b. 库名不要使用系统预留字符,不要大写字母,不要数字开头.

c. 建库是显式设置字符集.

4.6.2 对表(Table)的操做

create table

规范:

表名不要使用系统预留字符,不要大写字母,不要数字开头.ob_user;不要超过18字符.

数据类型:

合适的 简短的 足够的

注意点:

每一个表要有主键.

每一个列尽量非空,或者设置默认值

每一个列要加注释.

存储引擎使用InnoDB 字符集 utf8mb4

alter table

主要用途:

添加列

删除列

加索引

删索引

该类型

关于alter table还须要知道的一个点:

8.0之前:Online DDL 须要业务低估期间作. 或者使用PT-OSC.

8.0以后:添加列能够直接作.

drop table 非必要不要使用.

4.7 关于DDL/DML和MySQL执行阶段的问题

prepare MDL X 阻塞全部DML写入 DDL

exec     S 降级共享锁 不阻塞DML , 阻塞DDL

commit  MDL X 阻塞全部DML写入 DDL

5.索引和执行计划

5.1 什么是索引?

至关一本书中的目录.优化查询(select update  delete)

5.2 索引类型

BTREE *****

RTREE(空间数据索引)

HASH(哈希索引)

FTEXT(全文索引)

5.3 BTREE 结构认识

b-tree

b+tree(b*tree) -->增强版

5.4 MySQL中如何应用BTREE?

5.4.1 聚簇索引

1.什么是聚簇索引?

InnoDB中，表数据文件自己就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将汇集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

2.聚簇索引的构造条件?

Primary Key / Unique+not null / RowID

3.聚簇索引的构造细节?

叶子节点(Leaf): 在录入数据时,会按照聚簇索引逻辑顺序,存储到物理上连续的多个数据页. 从而生成了叶子节点. 而且存储相邻叶子节点的双向指针.

枝节点(No-Leaf): 选取叶子节点的ID的范围+指针.

根节点(ROOT) : No-leaf节点的ID范围+指针

4.聚簇索引的优化效果?

经过ID列做为查询条件时,会起到优化效果.

5.4.2 辅助索引

...