Justin-刘清政的博客

db/MySQL系列/02-MySQL系列之-MySQL体系结构与管理

2019-12-24

一 体系结构

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

image-20200812171707690
1
2
3
4
TCP/IP方式(远程、本地):
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地):
mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 实例介绍

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

1.3 mysqld程序运行原理

1.3.1 mysqld程序结构

1
# cd /app/database/mysql/bin 路径下的mysqld程序
image-20200812171734246

1.3.2 一条SQL语句的执行过程

1.3.2.1 连接层

1
2
3
4
5
6

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

1.3.2.2 SQL层 (重点)

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

1.3.2.3 存储引擎层(类似于Linux中的文件系统)

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

image-20200812175045993

1.4 逻辑结构

image-20200812171804012

1.4.1 库(类似于目录)

1
2
3
库名,库属性
# show databases;
# use mysql

1.4.2 表(类似于文件)

1
2
3
4
5
6
表名
属性
列:列名(字段),列属性(数据类型,约束等)
数据行(记录)
# show tables;
# desc user; # 查看表列的情况

1.5 物理存储结构引入

image-20200812171823222

1.5.1 库的物理存储结构

1
用文件系统的目录来存储

1.5.2 表的物理存储结构

1
2
3
4
5
6
7
8
9
10
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:数据行+索引

1.5.3 表的段、区、页(16k)(了解)

1
2
3
段:一个表就是一个段,可以由一个或者多个区构成
区/簇:一个区(簇),默认1M,连续的64个页(pages)
页:一个页,默认16k,连续的4个os的block,最小的存储单元

image-20200812181944720

二 基础管理

2.1 用户、权限管理

2.1.1 用户

作用:

1
登录,管理数据库逻辑对象

定义:

1
2
3
4
5
6
7
8
9
用户名@'白名单'
白名单支持的方式?
wordpress@'10.0.0.%' # wordpress用户可以通过10.0.0地址段的ip登陆
wordpress@'%' # wordpress用户可以通过所有ip登陆
wordpress@'10.0.0.200' # wordpress用户只能通过10.0.0.200ip登陆
wordpress@'localhost'# 本地
wordpress@'db02' # 通过主机名db02登陆
wordpress@'10.0.0.5%' #wordpress用户可以通过50--59的ip登陆
wordpress@'10.0.0.0/255.255.254.0' # 地址段

管理操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
# mysql库下user表,存储用户和授权信息
# 增:
create user lqz@'localhost';
create user lqz@'%' identified by '123';
# 查:
desc mysql.user; ----> authentication_string
select user ,host ,authentication_string from mysql.user
# 改:
alter user lqz@'%' identified by '456';
# 删:
drop user lqz@'%';

# 注意:8.0以前,可以通过grant命令,建立用户+授权,8.0以后不再支持,必须先建用户设置密码,再授权

2.1.2 权限

针对用户设置权限,权限是用户的属性

权限管理操作:

1
2
3
4
5
# 8.0以前
# grant 权限 on 对象 to 用户 identified by '密码';
# 8.0以后
# create user 用户 identified by '密码';
# grant 权限 on 对象 to 用户;

常用权限介绍:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
show privileges; # 查看所有权限
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, CRE ATE TABLESPACE
ALL : 以上所有权限,一般是普通管理员拥有的
with grant option:超级管理员才具备的,给别的用户授权的功能

###################权限:
ALL
SELECT,INSERT,UPDATE,DELETE
grant option
## 例子
grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123';
grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';
# all权限不包含grant option,需要单独设置。给其他用户授权
grant all on wordpress.* to wordpress@'10.0.0.%' with grant option;

权限作用范围:

1
2
3
4
# 对象:库,表
*.* # 所有库,所有表(管理员)
lqz.* # lqz库下的所有表(用的多)
lqz.article # lqz库下的article表

需求1:windows机器的navicat登录到linux中的MySQL,管理员用户。

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

需求2:创建一个应用用户app用户,能从windows上登录mysql,并能操作app库

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

2.1.3 开发人员用户授权流程

1
2
3
4
1.权限
2.对谁操作
3.你从哪来
4.密码要求

2.1.4 提示:8.0在grant命令添加新特性

1
2
3
建用户和授权分开了
grant 不再支持自动创建用户了,不支持改密码
授权之前,必须要提前创建用户。

2.1.5 查看授权

1
2
3
4
5
6
7
8
9
10
11
show grants for app@'10.0.0.%';

# 查看用户基本信息
select * from mysql.user\G; # N和Y表示

# mysql授权表mysql库下,每次数据库启动,会把数据加载到内存中
user: *.*范围,存放创建的用户密码包括全局实例级别管理权限
db: 库级别范围 lqz.*
tables_priv: 表级别范围 lqz.article
columns_priv: 列范围,字段级别
procs_priv: 存放存储过程的权限

2.1.6 回收权限

1
2
3
4
# 用户删了,权限也就没了
# oracle中删除用户,用户的表也没了,mysql删除用户只是权限没了,表和库还在
# 不能通过重复授权覆盖之前的权限,进行修改,只能回收权限,只回收某个权限,多次grants是叠加权限
revoke delete on app.* from app@'10.0.0.%'

2.1.7 本地管理员用户密码忘记.

1
2
3
4
5
6
7
8
9
10
11
12
13
# mysqld启动时,跳过授权表,跳过tcp/ip连接
--skip-grant-tables 跳过授权表
--skip-networking 跳过tcp/ip连接,不让远程用户登录,否则远程用户不用密码可以直接登录
#### ? service mysqld start --skip-grant-tables --skip-networking



[root@db01 ~]mysqld_safe --skip-grant-tables --skip-networking &
# 手工把授权表加载到内存
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';
[root@db01 ~]# pkill mysqld
[root@db01 ~]# systemctl start mysqld

2.2 连接管理

image-20200812200157223

2.2.1 自带客户端命令

mysql 常用参数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-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;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
# 数据库中必须先授权 root@'localhost' 用户
[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 |
+---------------+-----------+
| root | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
[root@db01 ~]#
[root@db01 ~]# mysql -uroot -p <world.sql
Enter password:


# 查看客户端连接情况,区分哪个是远程连接过来,哪个是本地连接过来
show processlist;

2.3 多种启动方式介绍

image-20200812171903857 image-20200812211358893

提示:

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

方式三四,可以客户端连进去,输入 shutdown关闭服务端
或者 mysqladmin -uroot -p123 shutdown

2.4 初始化配置

2.4.0 作用

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

2.4.1 初始化配置的方法

1
2
3
预编译
**配置文件(所有启动方式)**
命令行参数 (仅限于 mysqld_safe mysqld)

2.4.2 初始配置文件

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

1
2
3
4
5
6
[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时,以上的所有文件都不会读取.

配置文件的书写方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[标签]
配置项=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

2.5 多实例的应用

2.5.1 准备多个目录

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

2.5.2 准备配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.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=/app/database/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.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=/app/database/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

2.5.3 初始化三套数据

1
2
3
4
5
chown -R mysql.mysql /data /binlog
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql

2.5.4 systemd管理多实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service


# 脚本介绍
[Unit]
Description=MySQL Server # 描述服务
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target # 描述服务类别
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service] # 服务运行参数设置
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

2.5.5 授权

1
chown -R mysql.mysql /data/*

2.5.6 启动

1
2
3
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service

2.5.7 验证多实例

1
2
3
4
netstat -lnp|grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"
使用支付宝打赏
使用微信打赏

点击上方按钮,请我喝杯咖啡!

扫描二维码,分享此文章