MySQL体系结构与基础管理

目录

• 一、体系结构
  • 1.1 C/S模型
  • 1.2 实例介绍
  • 1.3 Mysql程序运行原理
    • ① Mysql程序结构
    • ② 一条SQL语句的执行过程
  • 1.4 逻辑结构
  • 1.5 物理结构
    • ① 表的物理存储结构
    • ② Mysql的物理结构
  • 二、基础管理
  • 2.1 用户、权限管理
    • ① 用户
    • ② 权限
  • 2.2 管理员密码忘记解决方案
    • ① 关闭数据库
    • ② 使用安全模式启动
    • ③ 更改root用户密码
    • ④ 重启数据库到正常模式
  • 2.3 连接管理
    • ① 自带客户端命令
  • 2.4 初始化配置
    • ① 作用
    • ② 初始化配置的方法
    • ③ 初始化配置文件
  • 2.5 Mysql多实例应用
    • ① 创建需要目录
    • ② 创建配置文件
    • ③ 初始化数据
    • ④ 准备启动脚本
    • ⑤ 启动多实例

一、体系结构

1.1 C/S模型

客户端连接数据的方法：
1）TCP/IP连接方式（远程和本地）；
2）Socket连接方式（仅本地）；

mysql -uroot -p123.com -h 192.168.1.12 -P3306
#TCP/IP连接方式
mysql -uroot -p123.com -S /tmp/mysql.sock
#Socket连接方式

1.2 实例介绍

实例 = mysqld后台守护进程 + Master Thread（主线程） + 干活的Thread（工作的线程） + 预分配的内存

1.3 Mysql程序运行原理

① Mysql程序结构

② 一条SQL语句的执行过程

（1）连接层

1）提供连接协议：TCP/IP 、SOCKET；
2）提供验证：用户、密码，IP，SOCKET；
3）提供专用连接线程：接收用户SQL，返回结果；

mysql> show processlist;
#查看到连接线程基本情况

（2）SQL层

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

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

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

1.4 逻辑结构

1.5 物理结构

① 表的物理存储结构

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：数据行+索引

② Mysql的物理结构

• 段：一个表就是一个段，可以有1个或多个区构成；
• 区：一个区（簇），默认是1M，连续的64个pages；
• 页：一个页，默认是16KB，连续的4个OS block，最小的IO单元；

二、基础管理

2.1 用户、权限管理

① 用户

（1）作用

登录，管理数据库逻辑对象；

（2）定义

用户名@'白名单'
白名单支持的方式:
lzj@'192.168.1.%'    
lzj@'%'
lzj@'192.168.1.200'
lzj@'localhost'
lzj@'db02'
lzj@'192.168.1.5%'
lzj@'192.168.1.0/255.255.254.0'

（3）管理操作

mysql> create user lzj@'192.168.1.%' identified by '123';
#增（添加一个用户）
mysql> select user ,host ,authentication_string from mysql.user;
#查（查询用户）
mysql> alter user lzj@'192.168.1.%' identified by '456';
#改（更改用户的密码）
mysql> drop user lzj@'192.168.1.%';
#删（删除指定的用户）

② 权限

（1）权限管理

mysql>  grant all on app.* to lzj@'192.168.1.%' identified  by '123';
#授予lzj用户对app库下的所有表所有的权限

（2）常用权限介绍

mysql> show privileges;
#查看所有的权限及详细说明

两个特殊的权限：

• ALL：所有权限，一般是普通管理员拥有的；
• with grant option：超级管理员才具备的，给别的用户授权的功能；

示例：

mysql> grant all on *.* to root@'192.168.1.253' identified by '123.com';
#允许root用户在192.168.1.253主机上对数据库拥有所有的权限
mysql> grant select,update,insert,delete on app.* to lzj@'192.168.1.%' identified by '123';
#赋予lzj用户在192.168.1.0网段上对mysq数据库的app库有查询、更新、插入、删除的权限

（3）8.0在grant命令添加新特性

• 建用户和授权分开了；
• grant 不再支持自动创建用户了，不支持改密码；
• 授权之前，必须要提前创建用户；

（4）查看权限

mysql> show grants for lzj@'192.168.1.%';
#查看用户对应的权限

（5）回收权限

注意：在mysql中不能重复授权，修改权限，只能通过回收权限的方式进行修改！

mysql> revoke delete on app.* from lzj@'192.168.1.%';
#去除lzj用户的删除权限

（6）Mysql授权表

mysql库下：
user：存放创建的用户和密码包括全局实例级别管理权限设置
db：存放设置的数据库级别的权限设置
tables_priv：存放表级别的权限设置
columns_priv：存放字段级别的权限设置

2.2 管理员密码忘记解决方案

① 关闭数据库

[root@db01 ~]# systemctl stop mysqld

② 使用安全模式启动

[root@db01 ~]# /etc/init.d/mysqld start --skip-grant-tables --skip-networking 
或者
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables：跳过授权表
--skip-networking ：跳过TCP/IP链接

③ 更改root用户密码

mysql> flush privileges;
#手工加载授权表
mysql>  alter user root@'localhost' identified by '123456';

④ 重启数据库到正常模式

[root@db01 ~]# systemctl restart  mysqld

2.3 连接管理

① 自带客户端命令

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

[root@db01 ~]# mysql -uroot -p -h 192.168.1.12 -P3306
#正常登录
[root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock
#通过socket的文件链接
[root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;"
#免交互执行数据库命令
[root@db01 ~]# mysql -uroot -p <world.sql
#导入SQL脚本

2.4 初始化配置

① 作用

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

② 初始化配置的方法

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

③ 初始化配置文件

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

[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/database/mysql   #软件的安装位置
datadir=/data/3306         #数据的存放位置
server_id=6              #标识节点的唯一编号（主从复制会用到）
port=3306                #端口号
socket=/tmp/mysql.sock       #套接字文件
[mysql]                 #客户端标签
socket=/tmp/mysql.sock       #读取socket文件的位置

2.5 Mysql多实例应用

① 创建需要目录

[root@db01 ~]# mkdir -p /data/330{7..9}/data
[root@db01 ~]# mkdir -p /binlog/330{7..9}

② 创建配置文件

[root@db01 ~]# 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=/binlog/3307/mysql-bin
EOF

[root@db01 ~]# 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=/binlog/3308/mysql-bin
EOF

[root@db01 ~]# 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=/binlog/3309/mysql-bin
EOF

③ 初始化数据

[root@db01 ~]# chown -R mysql.mysql /data /binlog
[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql
[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql
[root@db01 ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql

④ 准备启动脚本

[root@db01 ~]# 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

[root@db01 ~]# 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

[root@db01 ~]# 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

⑤ 启动多实例

[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# systemctl start mysqld3309
[root@db01 ~]# netstat -anpt | grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      4530/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      4493/mysqld         
tcp6       0      0 :::3309                 :::*                    LISTEN      4565/mysqld