MySQL体系结构与管理

2022-10-25 21:56:20

MySQL体系结构与管理

MySQL的C/S结构介绍
MySQL实例的构成
mysqld处理SQL的过程
- SQL语句的引用
- mysqld程序结构
MySQL逻辑存储结构
MySQL物理存储结构
用户和权限管理
MySQL的启停
初始化配置

MySQL的C/S结构介绍

两种连接方式

Tcp/IP：mysql -uroot -p1 -h 192.168.184.128 -P3306
Socket:：mysql -uroot -p1 -S /tmp/mysql.sock

MySQL实例的构成

实例：mysql + master thread + 干活的Thread + 预分配的内存
比喻
公司：老板 + 经理 + 员工 + 办公区

mysqld处理SQL的过程

SQL语句的引用

结构化查询语言的类型
DQL：数据查询语言
DDL：数据定义语言
DML：数据操作语言
DCL：数据控制语言

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.01 sec)

mysqld程序结构

MySQL逻辑存储结构

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.13 sec)

表
由列（字段）和数据行（记录）组成
列属性：字段类型
表属性：类似LIinux的文件属性

MySQL物理存储结构

[root@test01 data]# mkdir xiaocn
[root@test01 data]# pwd
/data01/mysql/data

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xiaocn             |
+--------------------+
5 rows in set (0.00 sec)

表
- Ny ISAM（存储引擎，相当于ext2）

[root@test01 mysql]# ll |tail -3
-rw-r-----. 1 mysql mysql   10816 Nov 30 03:59 user.frm
-rw-r-----. 1 mysql mysql     384 Nov 30 04:33 user.MYD
-rw-r-----. 1 mysql mysql    4096 Nov 30 04:33 user.MYI
[root@test01 mysql]# pwd
/data01/mysql/data/mysql

user.frm：存储的表结构（列，列属性）
user.MYD：存储的数据记录
user.MYI：存储索引

InnoDB（相当于XFS）

[root@test01 mysql]# ll |grep -e time_zone.frm -e time_zone.ibd
-rw-r-----. 1 mysql mysql    8636 Nov 30 03:59 time_zone.frm
-rw-r-----. 1 mysql mysql   98304 Nov 30 03:59 time_zone.ibd
[root@test01 mysql]# pwd
/data01/mysql/data/mysql

time_zone.frm：存储的表结构（列，列属性）
time_zone.ibd：存储的数据记录和索引
ibdata1：数据字典信息
InnoDB段区页
一般情况下除了分区表
一个表就是一个段
一个段由多个区构成
一个区在（16K），64个连续的页，1M大小

用户和权限管理

用户的作用

登录MySQL
管理My’SQL

用户的定义

用户名@‘白名单’
例：
wordpress@’%’
wordpress@‘localhost’
wordpress@‘127.0.0.1’
wordpress@‘10.0.0.%’
wordpress@‘10.0.0.5%’
wordpress@‘10.0.0.0/255.255.254.0’

用户的操作

创建一个允许192.168.184.0网段通过xiaocn用户访问数据库

mysql> create user xiaocn@'192.168.184.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user
    -> ;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| xiaocn        | 192.168.184.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
4 rows in set (0.00 sec)

修改用户密码

mysql> alter user xiaocn@'192.168.184.%' identified by '1';
Query OK, 0 rows affected (0.00 sec)

删除用户

mysql> drop user xiaocn@'192.168.184.%';
Query OK, 0 rows affected (0.00 sec)

权限管理

权限列表

权限名字	可以使用的命令
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

授权命令

grant all(权限) on *.*（作用目标） to xiaocn@'192.168.184.%'（用户） identified by '123'（密码） with grant option（超级管理员才具备的，给别的用户授权的功能）;

权限：可以填all或者具体命令，例：SELECT,INSERT, UPDATE
作用目标：

*.*                  所有的库和表
wordpress.*          只对wordpress下所有的表
wordpress.t1         只对wordpress下所有的t1表

show grants for xiaocn@‘192.168.184.%’ 查询xiaocn@'192.168.184.%'用户的权限

回收权限

回收xiaocn@‘192.168.184.%’ 用户的delete权限

revoke delete on *.* from xiaocn@'192.168.184.%'

MySQL的启停

MySQL的启动法国过程

日常启停,启动过程

/etc/init.d/mysqld start ----> mysqld_safe -----> mysqld
systemctl start mysqld.service -----> mysqld 需要依赖于/etc/my.cnf

维护性任务

mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行
也会读取/etc/my.cnf的内容，但如果冲突，命令行方式最高

初始化配置

作用
1. 影响数据库的启动
2. 影响到客户端的功能
初始化配置的方法
1. 初始化配置文件（例如/etc/my.cnf）
2. 启动命令行上进行设置（例如mysqld_safe、mysqld）
3. 预编译时设置（仅限于编译安装时设置）

初始化配置文件的书写格式

[标签]
xxx=xxx
[标签]
xxx=xxx

配置文件标签的归类

服务器端：
[mysqld]
[mysqld_safe]
[server] 包括上面所有不建议使用
客户端：
[mysql]
[mysqladmin]
[mysqldump]
[client] 包括上面所有不建议使用

配置文件设置样板

[root@test01 init.d]# cat /etc/my.cnf
#服务端配置
[mysqld]
user=mysql                                     #用户
basedir=/aplication/mysql                      #mysql安装路径
datadir=/data01/mysql/data                     #数据存放路径
socket=/tmp/mysql.sock                         #socket文件位置
server_id=6                                    #服务器id号
port=3306                                      #端口
#客户端配置
[mysql]
socket=/tmp/mysql.sock                         #socket文件位置，必须和服务端一致

配置文件读取顺序

[root@test01 init.d]# mysqld --help --verbose|grep -A 5 -B 5 my.cnf
Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-5.7
#默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。
#但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.

码农公寓