MySQL-体系结构和管理

作者: 文娟_狼剩 | 来源:发表于2019-08-05 22:35 被阅读0次

MySQL-体系结构和管理
MySQL-体系结构
MySQL体系结构和管理(用户，连接，SQL语句执行过程)+多实
MySQL存储引擎InnoDB1
3物理内存管理：连续内存管理
Linux相关
MySQL-日志管理
MySQL-日志管理
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]>

未完……

MySQL-体系结构和管理
1、MySQL的C/S模型 1>Server：mysqld2>Client：支持两种协议：socket：仅本地连...
MySQL-体系结构
体系结构网络连接层最上层是一些客户端和链接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类...
MySQL体系结构和管理(用户，连接，SQL语句执行过程)+多实
MySQL体系结构和管理多实例：
MySQL存储引擎InnoDB1
来源：MySQL技术内幕：InnoDB存储引擎（第2版）一：体系结构 Mysql体系结构连接池组件，管理服务和工...
3物理内存管理：连续内存管理
5.1计算机体系结构和内存层次计算机体系结构内存层次操作系统的内存管理存储管理要达到效果是抽象，把线性的物...
Linux相关
一 Linux的体系结构体系结构主要分为用户态和内核态;内核 : 本质是一段管理计算机硬件设备的程序;系统调用 ...
MySQL-日志管理
1、错误日志 1.1 作用记录MySQL启动及工作过程中，状态、报错、警告。 1.2 怎么设置？ 1> 修改配置...
MySQL-日志管理
如果您对数据库感兴趣，可以添加 DBA解决方案QQ群：855439640 1.错误日志(log_error) 1....
MySQL-表管理
MySQL表管理表字段的操作添加字段删除字段修改数据类型表重命名练习表记录管理删除表记录更新表记...
网络管理考试总结
1 简述SNMPv1体系结构的组成和功能 1，SNMP管理站：一般由专用设备构成，配置Manager实体和一组管理...

MySQL-体系结构和管理

1、MySQL的C/S模型

2、实例

3、MySQL的程序结构

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

3.2 SQL语句的执行过程

1>连接层

2>SQL层（重点）

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

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

1>库（DATABASE,SCHEMA）

2>表（TABLE,SEGMENT）

3>用户（USER）

4>OTHER（省略）

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

1>库（DATABASE,SCHEMA）

2>表（TABLE,SEGMENT）

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

6、MySQL基础管理

6.1 用户

1>用户的作用

2>用户的定义

3>用户的操作管理

6.2 权限

1>权限作用

2>权限定义（8.0以前）

3>授权范围

4>授权管理命令

5>授权管理生产需求

6>权限回收

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

6.3 连接管理

1>MySQL自带命令

2>客户端工具连接

6.4 MySQL启动和关闭方式

1> sys-v

2> systemd

3> mysqld &

4> mysqld_safe &

6.5 MySQL初始化配置

1> 提供的方法

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

3> 初始化配置文件作用

4> 初始化配置文件格式

5> 配置文件模板

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读