MySQL-体系结构和管理

1、MySQL的C/S模型

1>Server：mysqld
2>Client：

支持两种协议：

• socket：仅本地连接使用
• tcp/ip：应用连接使用（用的比较多）

TCP/IP方式（远程、本地）：
mysql -uroot -poldboy123 -h 10.0.0.51 -P3306
Socket方式(仅本地)：
mysql -uroot -poldboy123 -S /tmp/mysql.sock

2、实例

mysqld----->master thread-------> 干活的线程+预分配的内存结构
boss              经理                           员工               办公室

3、MySQL的程序结构

3.1 MySQL的专用管理和操作命令SQL语句

分为4大类：
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言

3.2 SQL语句的执行过程

1>连接层

• 提供连接协议（在Linux中提供两种连接协议socket和tcp/ip）
• 验证user、password、host、port等
• 提供连接线程
  • 查看连接线程：mysql> show [full] processlist;
  • 功能：负责接受SQL语句和返回结果

2>SQL层（重点）

• 语法检查和SQL_MODE检查
• 语义检查（判断SQL语句的类型）和权限检查
• 解析预处理，生成解析树（执行计划树）
• 优化器会根据自带算法，选择最优的方案（代价模型算法）-----不需要人为干预
  • 代价？cpu、io、mem
• 选择最优的方案进行执行SQL
• 提供查询缓存（默认是关闭的），会使用redis、tair替代查询缓存功能
• 提供日志记录，默认是关闭的

3>存储引擎层（简单介绍）

• 相当于文件系统，将数据取出，在交给SQL层结构化成表，返回给用户

4、MYSQL的逻辑结构（重要）

1>库（DATABASE,SCHEMA）

相当于Linux的目录
由库名字和库属性组成

2>表（TABLE,SEGMENT）

相当于Linux下的目录
由表名、表属性、表的数据行（又称为row、记录）、列（又称为字段）组成

3>用户（USER）

由用户名、白名单（主机范围）组成

4>OTHER（省略）

5、MYSQL的物理结构（重要）

1>库（DATABASE,SCHEMA）

物理存储就是Linux中的目录

2>表（TABLE,SEGMENT）

通过不同的引擎分为：

MyISAM（一种引擎）的表：
    *.MYI：索引相关信息
    *.MYD：存储数据行
    *.frm：列的信息
InnoDB(默认的存储引擎)的表：
    *.frm：列的信息
    *.ibd：数据行和索引（IOT）

5.1、InnoDB表底层存储结构引入（扩展）（重要）

• 段（SEGMENT）:一个非分区表就是一个段
• 区（EXTENT）：连续的64个page，固定大小1M
• 页（PAGE）：MySQL最小的IO单元，默认16KB

6、MySQL基础管理

6.1 用户

1>用户的作用

• 登录MySQL
• 管理MySQL的逻辑对象

2>用户的定义

• 用户名
• 白名单（主机列表），即可被允许的主机IP
• 用户定义的几种方式，如定义wordpress

  wordpress@'10.0.0.1'
  wordpress@'localhost'
  wordpress@'10.0.0.%'
  wordpress@'10.0.0.5%'
  wordpress@'10.0.0.0/255.255.254.0'
  wordpress@'oldguo.com'
  wordpress@'db01'
  wordpress@'%'
  

3>用户的操作管理

（1）创建用户

mysql> create user oldgril@'10.0.0.%';   #创建用户，不设置密码
Query OK, 0 rows affected (0.00 sec)

mysql> create user old@'10.0.0.%' identified by '123456';   #创建用户的同时设置密码
Query OK, 0 rows affected (0.00 sec)

（2）查询用户

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

（3）修改用户密码

mysql> alter user oldgril@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

（4）删除用户

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

注意：
(1)8.0以前,以上命令可以忽略,grant可以自动创建用户并授权.
(2)8.0以后必须先建用户后授权,grant只做授权功能.

6.2 权限

1>权限作用

开启用户的管理对象的能力

2>权限定义（8.0以前）

按命令进行定义，例如select、update、insert、drop、create……

3>授权范围

*.*：全库级别，一般是管理员
wordpress.*：单库级别，一般是应用或开发用户（常用）
wordpress.t1：单表级别，很少使用

4>授权管理命令

grant  all  on *.*  to oldguo@'10.0.0.%' identified by '123';
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 : 普通管理员
    ALL+ with grant option 超级管理员 .

5>授权管理生产需求

(1) 授权一个管理员用户admin ,密码admin,能够通过10网段管理数据库

mysql> grant all on *.* to admin@'10.0.0.%' identified by 'admin';

(2)开放一个wordpress应用的用户，密码123，nginx服务器使172.16.1.%网段

分析：应用用户需要什么权限？select,insert,update,delete

mysql> grant select,insert,update,delete on *.* to wordpress@'172.16.1.%' identified by '123'

(3) 中小公司,开发人员leader,需要开发用户dev通过10网段对wordpress进行开发和管理

分析：用户需要什么权限？
这里给了最大权限，但到底什么权限，根据实际情况来定

mysql> grant SELECT,INSERT, UPDATE, DELETE,CREATE,ALTER,CREATE VIEW,SHOW VIEW,CREATE ROUTINE, ALTER ROUTINE,EVENT, TRIGGER on  wordpress.* to dev@'10.0.0.%' identified by '123';

6>权限回收

（1）查看用户有哪些权限？

mysql> show grants for wordpress@'172.16.1.%';
+-----------------------------------------------------------------------------------+
| Grants for wordpress@172.16.1.%                                                   |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'172.16.1.%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'wordpress'@'172.16.1.%' |
+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（2）回收用户的delete,update权限

mysql> revoke delete,update on wordpress.* from wordpress@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wordpress@'172.16.1.%';
+-------------------------------------------------------------------+
| Grants for wordpress@172.16.1.%                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'172.16.1.%'                    |
| GRANT SELECT, INSERT ON `wordpress`.* TO 'wordpress'@'172.16.1.%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

7>本地管理员用户密码忘记（面试题）

（1）关闭数据库

/etc/init.d/mysqld stop

（2）将数据库启动到无验证模式

mysqld_safe --skip-grant-tables --skip-networking &
参数说明：
    --skip-grant-tables：跳过授权表
    --skip-networking：跳过TCP/IP

（3）修改密码

mysql>flush privileges;
mysql>alter user root@'localhost' identified by '123';

（4）重启数据库为正常模式

/etc/init.d/mysqld restart

（5）测试新密码

[root@db01 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

6.3 连接管理

1>MySQL自带命令

（1）mysql参数

-u：指定用户名
-p：指定连接mysql服务器的密码
-h：指定要连接的ip地址
-P：指定端口号，与-h配合使用，可以省略，但不能单独使用
-S：可以指定socket文件，实现本地登录
    如：mysql -uroot -p123 -S  /tmp/mysql.sock
-e：免交互式执行mysql里的命令
    如：mysql -uroot -p123 -e 'show databases'
-V：查看mysql命令的版本信息
<：恢复数据使用
    如：mysql -uroot -p123 <world.sql

（2）socket连接：

确认socket文件位置：socket=/tmp/mysql.sock
mysql -uroot -p123 -S  /tmp/mysql.sock

注意：本地登录的用户，需要提前授权localhost相关用户

（3）TCP/IP连接：

mysql -uoldguo -p  -h10.0.0.51 -P3306

注意：
优先级：socket连接和TCP/IP连接都存在时，优先走TCP/IP连接

2>客户端工具连接

SQLyog工具：https://sqlyog.en.softonic.com/
navicat for mysql工具：https://www.navicat.com.cn/products/navicat-for-mysql

6.4 MySQL启动和关闭方式

1> sys-v

/etc/init.d/mysqld  [start|stop|restart]
service mysqld  [start|stop|restart]

前提是需要把软件存放目录下的mysql/support-files/mysql.server 拷贝到/etc/init.d/下

2> systemd

systemctl [start|stop|restart] mysqld

3> mysqld &

只提供了启动的方法

4> mysqld_safe &

一般用于调试
    如：mysqld_safe --skip-grant-tables --skip-networking &
注：只提供了启动的方法

注：万能的关闭方法，以上都适用：mysqladmin -uroot -p123456 shutdown

6.5 MySQL初始化配置

1> 提供的方法

预编译
初始化配置文件
命令行
注意：优先级（命令行---->初始化配置文件---->预编译）

2>初始化配置文件的默认读取顺序

[root@db01 ~]# mysqld  --help --verbose |grep my.cnf
/etc/my.cnf--->/etc/mysql/my.cnf --->/usr/local/mysql/etc/my.cnf---> ~/.my.cnf

注意：一旦使用--defaults-file，以上的默认配置就不会生效了

[root@db01 ~]# mysqld_safe --defaults-file=/opt/oldguo.cnf &

3> 初始化配置文件作用

• 数据库的启动
• 客户端的登录

4> 初始化配置文件格式

（1）书写格式

[标签1]
xxxx=yyyy
[标签2]
xxxx=yyyy
[标签3]
xxxx=yyyy

（2）标签

服务端
    常用标签
        [mysqld]
        [mysqld_safe]
客户端
    [mysql]
    [mysqldump]
    [mysqladmin]
    [client]：代表所有的客户端标签

5> 配置文件模板

[mysqld]
user=mysql
server_id=6   
port=3306
basedir=/application/mysql
datadir=/data/3306/data
socket=/data/3306/mysql.sock
[mysql]
socket=/data/3306/mysql.sock
prompt=wenjuan[\\d]>

未完……