当前位置 博文首页 > iR-Poke：day128:MySQL进阶:MySQL安装&用户/权限/连接/配置

最大化 缩小

    iR-Poke：day128:MySQL进阶:MySQL安装&用户/权限/连接/配置

    作者：iR-Poke 时间：2021-01-21 22:04

    目录

    1.介绍和安装

    2.基础管理

    　　2.1 用户管理

    　　2.2 权限管理

    　　2.3 连接管理

    　　2.4 配置管理

    3.MySQL的体系结构

    4.SQL

    5.索引和执行计划

    1.介绍和安装

    1.1 数据库分类

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

    代表产品: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 variables like '%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) 变长

    其他详细的数据类型:[ps:MySQL中的数据类型]

    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 辅助索引

    ...

     

    上一篇：求知久久技术：[分享] 最流行的 10 个 JavaScript 库
    下一篇：没有了