2 MySQL-示例结构体系及基础管理

最新推荐文章于 2024-01-09 17:51:21 发布

最爱下一站

最新推荐文章于 2024-01-09 17:51:21 发布

阅读量341

点赞数

分类专栏： MySQL MySQL-从入门到精通文章标签： MySQL 结构体系基础

本文链接：https://blog.csdn.net/qq_34646546/article/details/89004517

版权

MySQL 同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

MySQL-从入门到精通

16 篇文章 4 订阅

订阅专栏

MySQL示例结构体系及基础管理

体系结构

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

在这里插入图片描述
TCP/IP方式（远程、本地）：

mysql -uroot -p123 -h 10.0.0.51 -P3306

Socket方式(仅本地)：

mysql -uroot -p123 -S /tmp/mysql.sock

实例介绍

实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存
公司=老板+经理+员工+办公室

mysqld程序运行原理

mysqld程序结构
在这里插入图片描述
一条SQL语句的执行过程

连接层

（1）提供连接协议：TCP/IP 、SOCKET
（2）提供验证：用户、密码，IP，SOCKET
（3）提供专用连接线程：接收用户SQL，返回结果
通过以下语句可以查看到连接线程基本情况

mysql> show processlist;

SQL层（重点）

（1）接收上层传送的SQL语句
（2）语法验证模块：验证语句语法
（3）语义检查：判断SQL语句的类型

DDL ：数据定义语言（Data Definition Language）
DCL ：数据控制语言（Data Control Language）
DML ：数据操作语言(Data Manipulation Language)
DQL：数据查询语言（Data Query Language）

（4）权限检查：用户对库表有没有权限
（5）解析器：进行SQL的预处理，产生执行计划
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划
代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果

执行结果：在磁盘的xxxx位置上
（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

存储引擎层（类似于Linux中的文件系统）

负责根据SQL层执行的结果，从磁盘上拿数据。将16进制的磁盘数据，交由SQL架构化成表，由连接层的专用线程返回给用户。

逻辑结构

库：库名，库权限，库数据类型等

表（二维表）

列：字段
行：记录
MySQL为了将来数据的准确、规范、有意义，对于传统认知的二维表添加了更多的功能
表属性：权限，存储引擎，字符集
列属性：数据类型、约束、其他属性（默认值，自增长，注释）

物理存储结构引入

库的物理存储结构
用文件系统的目录来存储

表的物理存储结构

MyISAM（一种引擎）的表：

user.frm：列的相关信息
user.MYD：数据行
user.MYI：索引信息

InnoDB(默认的存储引擎)的表：

time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引
表的段、区、页（16k）（了解）
页：最小的存储单元
区：1个或多个连续的页
段：一个表就是一个段，包含一个或多个区

基础管理

用户、权限管理

用户
作用：
登录，管理数据库对象
定义：
用户@‘白名单’
白名单？
允许哪些IP能够登录数据库

支持的方式？

app@’%’ 所有地址
app@‘10.0.0.%’ 10.0.0.0/24 网段的地址
app@‘localhost’ 本地主机
app@‘10.0.0.5%’ 10.0.0.50~10.0.0.59
app@‘10.0.0.0/255.255.254.0’ 子网掩码不是24位的

管理操作：
增：

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

查：

mysql> select user ,host ,authentication_string from mysql.user

改:

mysql> alter user app@'10.0.0.%' identified by '456';

删：

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

权限

介绍

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPAC

ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
应用用户：SELECT,INSERT, UPDATE, DELETE

权限管理操作：
语法:

需求例子：windows机器的navicat登录到linux中的MySQL，管理员用户。
grant   all    on      对象     to     用户      identified by      '密码'；
mysql> grant all on \*.* to root@'10.0.0.%' identified by '123';

对象：

*.* —>chmod 755 -R / ----》针对管理员
app.* chmod 755 -R /app ----->应用用户
app.t1 chmod 755 -R /app/t1

需求：创建一个应用用户app用户，能从windows上登录mysql，并能操作app库。

mysql> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';

开发人员用户授权流程:
1.权限
2.对谁操作
3.你从哪来
4.密码要求

提示：8.0在grant命令添加新特性
建用户和授权分开了
grant 不再支持自动创建用户了，不支持改密码
授权之前，必须要提前创建用户。
查看授权：

mysql> show grants for app@'10.0.0.%';

回收权限：

revoke  delete on app.*  from app@'10.0.0.%'；

连接管理

自带客户端命令
mysql 常用参数：

-u 用户
-p 密码
-h IP
-P 端口
-S socket文件
-e 免交互执行命令
< 导入SQL脚本

登录方法一——远程登录：

[root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:

登录方法二——本地登录：

mysql> select @@socket;  &emsp;&emsp;&emsp;  查看socket位置
+-----------------+
| @@socket        |
+-----------------+
| /tmp/mysql.sock |
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
Enter password:
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| abc          | 10.0.0.%  |
| app          | 10.0.0.%  |
| root          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys    | localhost |
| root          | localhost |
+---------------+-----------+
[root@db01 ~]#
[root@db01 ~]# mysql -uroot -p <world.sql
Enter password:
[root@db01 ~]#

多种启动方式介绍

在这里插入图片描述
提示：

以上多种方式，都可以单独启动MySQL服务
mysqld_safe和mysqld一般是在临时维护时使用。
另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库

初始化配置

初始化配置的方法
预编译
配置文件
命令行

初始配置文件

初始化配置文件的默认读取路径

[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

初始化配置文件读取顺序

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

默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
但是，如果启动时加入了–defaults-file=xxxx时，以上的所有文件都不会读取

配置文件的书写方式

[标签]
配置项=xxxx

标签类型：服务端、客户端
服务器端标签：

[mysqld]
[mysqld_safe]
[server]

客户端标签：

[mysql]
[mysqldump]
[client]

配置文件的示例展示：

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

[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>

多实例的应用

准备多个目录

mkdir -p /data/330{7,8,9}/data

准备配置文件

vim /data/3307/my.cnf

[mysqld]
basedir=/data/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7

vim /data/3308/my.cnf

[mysqld]
basedir=/data/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8

vim /data/3309/my.cnf

[mysqld]
basedir=/data/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.lo
port=3309
server_id=9

初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/data/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/data/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/data/mysql

systemd管理多实例

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
vim mysqld3307.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

授权

chown -R mysql.mysql /data/*

启动

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

验证多实例

[root@db01 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1231/sshd           
tcp6       0      0 :::3307                 :::*                    LISTEN      2749/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      2784/mysqld         
tcp6       0      0 :::3309                 :::*                    LISTEN      2816/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      1231/sshd      


mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"

在这里插入图片描述

最爱下一站

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
2 MySQL-示例结构体系及基础管理

体系结构1.1 C/S(客户端/服务端)模型介绍TCP/IP方式（远程、本地）：mysql -uroot -poldboy123 -h 10.0.0.51 -P3306Socket方式(仅本地)：mysql -uroot -poldboy123 -S /tmp/mysql.sock1.2 实例介绍实例=mysqld后台守护进程+Master Thread +干活的Thread+预...
复制链接

扫一扫