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 ...
06月14日 评论
Mysql数据类型 MySQL

Mysql数据类型

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

发表评论

匿名网友 填写信息

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