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物理存储结构

• 库：使用FS上的目录来表示

[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大小

用户和权限管理

用户的作用

1. 登录MySQL
2. 管理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)

权限管理

权限列表

授权命令

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的启动法国过程

1. 日常启停,启动过程

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

2. 维护性任务

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时，以上的所有文件都不会读取.