MySQL体系结构与管理

root
233
文章
0
评论
2020年3月6日18:46:18 评论 7678字阅读25分35秒

MySQL体系结构

体系结构

C/S(客户端/服务端)模型

mysql支持两种方式

  • TCP/IP(远程)
    • mysqk -uroot -pxxx -h 192.168.1.1 -P3306
  • Socket(本地)
    • mysqk -uroot -pxxx -S /tmp/mysql.sock

mysql实例的构成

mysqld+master thread + 干活的thread +预分配的内存

SQL语句引入

结构化的查询语言

  • DQL     数据查询语言
  • DDL      数据定义语言
  • DML     数据操作语言
  • DCL       数据控制语言

mysqld守护进程运行结构与原理

一条sql语句执行原理(mysqld处理sql语句的过程)

语言描述,要是描述的不正确,请指正一下

SQL语句先是用远程或者本地的方式连接到msyqld守护进程的连接层,连接层验证身份信息,确认无误后,通过一个专属线程连接用户,连接层把sql语句移交给sql层,sql层接收到sql语言后,显示进行sql语法检查和sql_MODE检查,透过后检查SQL语义,分析判断到底是DLL,DML,DCL,DQL,等哪一种,进行sql语句的预处理,把sql语句解析多种解决方案,优化这些方案并进行评估,通过优化器算法使cpu,mem,IO消耗最小化,优化器会选择一条最佳的执行方案,通过执行器,执行sql语句,sql层还提供数据查询缓存,把这些过程通过日志记录再审计日志和通用日志上,把执行结构发给存储引擎层,存储引擎收到sql层执行的结构去磁盘上找到对应的数据,这些数据是16进制的,返还给sql层,二位图标的形式,通过线程展现出来

mysql逻辑存储结构

库(相当于目录)

create database kk charset utf8mb4;-------------->mkdir xxx

show databases; --------------------->ls /

usr kk----------------------------cd xxx

表(相当于文件)

列(字段)

列属性

数据行(记录)

表总属性

mysql物理存储结构

数据存储位置/data/mysql/data

  • 直接存储在找个目录里/data/mysql/data

表: 

  • MyISAM(存储引擎)【相当于ext2】位置:
  • /data/mysql/data/mysql/ 
    • user.frm:存储的表结构(列,列属性)
    • user.MYD:存储数据记录
    • user.MYI:存储索引
  • InnoDB(存储引擎)[相当于xfs]位置
  • /data/mysql/data/mysql/ 
    • time_zone.frm:存储表结构(列,列属性)
    • time_zone.ibd:存储数据记录和数据索引【相当于一块磁盘】
      • 存储引擎会将找个time_zone.ibd做格式化分成16Kb的64个格子
      • mysql> select 16*64;
        +-------+
        | 16*64 |
        +-------+
        |  1024 |
        +-------+
        1 row in set (0.00 sec)
      • 最多就是1M,调32Kb就会分32个格子
      • 一个页叫page,默认16Kb
      • 一个区就是所有的页加起来,默认64个页加起来叫一个区
      • mysql的表,会有多个区构成,把多个区构成一个段,一个表就是一个段
    • bdata1: 数据字典信息

mysql中什么是段,区,页?

inoodb的存储引擎

一般情况下(非分区表)

一个页就是page,默认16Kb,总共有64个页

页的总和就是一个区,默认是64个页的总和,大小为1Mb

一个段就是一个表,由多个区构成一张表一个段

MySQL用户和权限管理

用户的作用:

  • 登陆mysql
  • 管理mysql

用户的定义:

  • 用户名@'白名单'
    • kk@'%'
    • kk@'localhost'
    • kk@'127.0.0.1'
    • kk@'10.0.0.%'
    • kk@'10.0.0.5%'----10.0.0.[50-59]
    • kk@'10.0.0.0/255.255.254.0'----添加具体的子网掩码

用户的操作:

  • 创建用户及用户密码:

create  user  kk@'192.168.5.%'  identified  by  '123';

mysql> create user kk@'192.168.5.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
  • 查看用户:

select  user,host  from  mysql.user;

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| kk            | 192.168.5.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
4 rows in set (0.00 sec)

 

8.0版本以前,可以自动创建用户并授权,以后版本就用不了这种方式了

grant all  on *.* to kk@'10.0.0.%'   identified  by  '123';

  • 修改用户

改不了用户名,删了重建,修改用户密码

alter  user  kk@'192.168.5.%'  identified by  '1234';

mysql> alter user kk@'192.168.5.%' identified by '12';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user kk@'192.168.5.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
  • 删除用户

drop  user  kk@'192.168.5.%'

mysql> drop user kk@'192.168.5.%';
Query OK, 0 rows affected (0.00 sec)

 

权限管理

权限列表:

  • ALL
  • with grant option(给别人授权的权限,本地管理员,必须加到末尾)
  • SELECT,INSERT,UPDATE,DELETE(应用用户增删改查)

ALL+whit grant option =超级管理员

ALL=管理员

SELECT,INSERT,UPDATE,DELETE应用用户

授权命令:

grant  all  on  *.*  to  kk@'192.168.5.%'  identified  by  '123';

grant  权限  on  作用目标  to  用户  identified  by  '密码';

作用目标:

  • *.*(递归,所有库所有表授权)
  • 库.*(某个库下所有的表授权)
  • kk(库).t1(对kk库里的t1表进行授权)

授权需求:

1.创建管理员用户root,可以通过192.168网段,管理数据库

grant  all  on *.*  to  root@'192.168.0.%'  identified  by  '123'  whit  grant  option;

2.创建一个应用用户,可以通过192.168网段,在kk库下所有表进行增删改查

grant  SELECT,INSERT,UPDATE,DELETE on  kk.* to ww@'192.168.0.%'  identified  by  '123'

mysql> grant SELECT,INSERT,UPDATE,DELETE on *.* to kk@'192.168.5.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

权限的查询:

show  grants  for  kk@'192.168.5.%'

mysql> show grants for kk@'192.168.5.%';
+-------------------------------------------------------------------+
| Grants for kk@192.168.5.%                                         |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'kk'@'192.168.5.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

权限的回收:

revoke  delete on *.*  from  kk@'192.168.5.%'

mysql> show grants for kk@'192.168.5.%';
+-------------------------------------------------------------------+
| Grants for kk@192.168.5.%                                         |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'kk'@'192.168.5.%' |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke delete on *.* from kk@'192.168.5.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for kk@'192.168.5.%';
+-----------------------------------------------------------+
| Grants for kk@192.168.5.%                                 |
+-----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO 'kk'@'192.168.5.%' |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

生成环境开用户:

如何沟通:

  • 1.是否有邮件批复
  • 2.对哪些库和表做操作
  • 3.做什么操作
  • 4.从什么地址登陆

MySQL连接管理

提前将用户授权做好

mysql命令:

TCP/IP:

  • mysql -uroot  -p  -h  192.168.5.36 -P3306
[root@mysql ~]# mysql -ukk -p123 -h 192.168.5.36 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

socket:

  • mysql -uroot -p123 -S /tmp/mysql.sock(不加就是默认的方式)
[root@mysql ~]# mysql -uroot -p123 -S /tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

登陆的区别,可以明显看出区别

mysql> show processlist;
+----+------+-------------+------+---------+------+----------+------------------+
| Id | User | Host        | db   | Command | Time | State    | Info             |
+----+------+-------------+------+---------+------+----------+------------------+
| 23 | root | localhost   | NULL | Query   |    0 | starting | show processlist |
| 24 | kk   | mysql:59954 | NULL | Sleep   |   12 |          | NULL             |
+----+------+-------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

同时出现-h和-S,优先走的远程的方式

客户端工具

navicat

sqlyog

多实例管理

生产中应用广泛,一台机器跑多台数据库

示例:

准备多个目录

  • mkdir -p  /data/330{7,8,9}/data
[root@mysql ~]# mkdir -p /data/330{7,8,9}/data

准备多个配置文件

3307配置文件:

cat > /data/3307/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=9
log_bin=/data/3307/mysql-bin
x

[root@mysql ~]# cat > /data/3307/my.cnf <<x
> [mysqld]
> basedir=/server/tools/mysql
> datadir=/data/3307/data
> socket=/data/3307/mysql.sock
> log_error=/data/3307/mysql.log
> port=3307
> server_id=9
> log_bin=/data/3307/mysql-bin
> x

3308配置文件:

cat > /data/3308/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=9
log_bin=/data/3308/mysql-bin
x

[root@mysql ~]# cat > /data/3308/my.cnf <<x
> [mysqld]
> basedir=/server/tools/mysql
> datadir=/data/3308/data
> socket=/data/3308/mysql.sock
> log_error=/data/3308/mysql.log
> port=3308
> server_id=9
> log_bin=/data/3308/mysql-bin
> x

3309配置文件:

cat > /data/3309/my.cnf <<x
[mysqld]
basedir=/server/tools/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3308
server_id=9
log_bin=/data/3309/mysql-bin
x

[root@mysql ~]# cat > /data/3309/my.cnf <<x
> [mysqld]
> basedir=/server/tools/mysql
> datadir=/data/3309/data
> socket=/data/3309/mysql.sock
> log_error=/data/3309/mysql.log
> port=3308
> server_id=9
> log_bin=/data/3309/mysql-bin
> x

初始化数据库

mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3307/data

mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3308/data

mysqld --initialize --user=mysql --basedir=/server/tools/mysql --datadir=/data/3309/data

生成启动脚本分别写三个类推

cp -a /xxx/mysql/support-files/mysql.server  /etc/init.d/mysqld3307

chkconfig --add /etc/init.d/mysqld3307

chmod +x /etc/init.d/mysqld3307

chkconfig mysqld3307 on

chkconfig --list mysqld3307

授权:

chown -R mysql.mysql /data/

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2020年3月6日18:46:18
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: