体系结构与管理

体系结构

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

两种连接方式：

TCP/IP方式（远程、本地）：

mysql -uroot -poldboy123 -h 10.0.0.51 -P3306

Socket方式(仅本地)：

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

MySQL实例的构成

公司：老板 + 经理 + 员工 + 办公区

实例： mysqld + master thread + 干活的Thread + 预分配的内存

mysqld程序结构

一条SQL语句的执行过程

连接层

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

SQL层（重点）　　

（1）接收上层传送的SQL语句
（2）语法验证模块：验证语句语法,是否满足SQL_MODE
（3）语义检查：判断SQL语句的类型 DDL ：数据定义语言 DCL ：数据控制语言 DML ：数据操作语言 DQL：数据查询语言 ...
（4）权限检查：用户对库表有没有权限
（5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.
（6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划代价模型：资源（CPU IO MEM）的耗损评估性能好坏
（7）执行器：根据最优执行计划，执行SQL语句，产生执行结果执行结果：在磁盘的xxxx位置上
（8）提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能
（9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

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

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

MySQL逻辑存储结构

库 ----》Linux目录

create database wordpress charset utf8mb4; ----》mkdir /wordpress
show databases; ----》ls /
use wordpress; ----》cd /wordpress

表 ----》Linux的文件

列（字段）无
列属性无
数据行（记录） ----》Linux数据行
表属性（元数据） ----》Linux 文件属性

物理存储结构引入

库的物理存储结构

表的物理存储结构

MyISAM（一种引擎）的表：
-rw-r----- 1 mysql mysql   10816 Apr 18 11:37 user.frm
-rw-r----- 1 mysql mysql     396 Apr 18 12:20  user.MYD
-rw-r----- 1 mysql mysql    4096 Apr 18 14:48 user.MYI

InnoDB(默认的存储引擎)的表：
-rw-r----- 1 mysql mysql    8636 Apr 18 11:37 time_zone.frm
-rw-r----- 1 mysql mysql   98304 Apr 18 11:37 time_zone.ibd
time_zone.frm：存储列相关信息
time_zone.ibd：数据行+索引

表的段、区、页（16k）

页：最小的存储单元，默认16k
区：64个连续的页，共1M
段：一个表就是一个段，包含一个或多个区

用户管理

用户作用：

登录MySQL
管理MySQL

用户定义：

用户名@'白名单'
白名单支持的方式？
wordpress@'10.0.0.%'    
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'

用户的操作

创建用户

create user oldboy@'10.0.0.%' identified by '123';

说明：

8.0以前，可以自动创建用户并授权

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

8.0 创建授权用户

# 创建用户
create user 'root'@'%' identified with mysql_native_password by 'mysql';
# 授权
grant all on *.* to 'root'@'%';
# 刷新权限
flush privileges;

查询用户

select user,host from mysql.user;

修改用户密码

alter user oldguo@'10.0.0.%' identified by '123456';

删除用户

drop user oldguo@'10.0.0.%' ;

权限管理

权限列表

ALL:
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 TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能

权限作用范围

*.*                  ---->管理员用户
wordpress.*          ---->开发和应用用户
wordpress.t1

授权命令

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

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

grant SELECT,INSERT, UPDATE, DELETE, CREATE on wordpress.* to 

作用目标:
*.*
wordpress.* 
worpress.t1

授权需求

1. 创建一个管理员用户root，可以通过10网段，管理数据库.

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

2 创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.

grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';

查看授权

show  grants for wordpress@'10.0.0.%';

回收权限

revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';
show  grants for wordpress@'10.0.0.%';

关于生产中开用户

（1）如何沟通开用户

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

（2）开发人员找你要root用户密码？

1. 走流程拒绝他
2. 如果是金融类的公司
（1）原则上是不允许任何非DBA人员持有或申请root
（2）如果有人私下索要root密码，即使举报

多种启动方式介绍

日常启停

mysql.server start ---> mysqld_safe ---> mysqld 
mysql.service      ---> mysqld 
需要依赖于 /etc/my.cnf

维护性的任务

我们一般会将我们需要的参数临时加到命令行.

也会读取/etc/my.cnf的内容,但是如果冲突,命令行优先级最高

mysqld_safe --skip-grant-tables --skip-networking &

当然也可以直接使用　mysqld_safe启动，但是这样关闭比较麻烦

# 启动
mysqld_safe &
# 关闭
mysqladmin -uroot -p123 shutdown

初始化配置

作用

控制MySQL的启动
影响到客户端的连接

初始化配置的方法

(1)初始化配置文件(例如/etc/my.cnf)
(2)启动命令行上进行设置(例如:mysqld_safe mysqld)
(3)预编译时设置(仅限于编译安装时设置)

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

[root@db01 ~]# mysqld --help --verbose |grep 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=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>

强制使用自定义配置文件

--defautls-file

mysqld_safe --defaults-file=/tmp/aa.txt &

MySQL的连接管理

提前应该将用户授权做好

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

客户端工具

sqlyog

navicat

多实例管理

准备多个目录

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

准备配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

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

初始化三套数据

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

systemd管理多实例

cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

修改mysqld3307.service 服务

vim mysqld3307.service
# 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf

修改mysqld3308.service 服务　　

vim mysqld3308.service
# 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf

修改mysqld3309.service 服务　　

vim mysqld3309.service
# 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

授权

chown -R mysql.mysql /data/*

启动　　

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

验证多实例

netstat -lnp|grep 330

使用scoket本地连接3307测试

mysql -S /data/3307/mysql.sock

posted @ 2020-02-23 21:21 Crazymagic 阅读(221) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Crazymagic