当前位置：MATT-学习永不放弃 数据库 正文

Mysql体系结构与日常管理

2023-06-27 分类：数据库 阅读(455) 评论(0)

1.Mysql8.0体系结构整体预览

2.Mysql8.0 Server层结构

Connectors

分为Mysql Protocol(socket本地、TCP/IP远程)和API(本地和第三方开发工具的应用程序、开发)

Mysql Server

mysql处理数据时,会由mysqld服务守护进程产生多个线程(主线程+工作线程)在内存中具体操作,并且会在内存中单独申请独立的内存空间进行处理.统称为实例.

Mysql内存结构

Mysql线程

连接层功能

1.负责提供连接协议:socket、TCP/IP
2.提供用户认证:授权表
3.提供一个专用的连接线程:show processlist;

SQL层功能

1.解析预处理


2.优化


3.执行

InnoDB

IDB每个数据页默认16KB,占用4个磁盘Block.连续的64个数据页分一个1MB大小的区,形成连续的Block,提升IO性能.
如果要调整数据页大小等参数,需要在数据库建立时进行设置,一旦数据库运行后无法再修改.

存储结构

文件存储结构

表:数据行(记录) + 元数据(表属性、表列属性、表明)
8.0表存储方式:每张表的数据都存储在ibd中;5.7表存储方式:数据行——ibd 元数据——frm+idbata

表存储结构

Mysql 8.0用户及安全管理

用户的组成

用户名@’白名单’ 例如:matt@’%’、matt@’10.0.0.0.1’、matt@’10.0.0.%’、matt@’localhost’

创建用户

create user matt@'10.0.0.%'identifiedby'123';
sha2:8.0新的特性
native:兼容老版本
create user test@'10.0.0.%' identified with mysql_native_passwordby'123';

查询用户

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| root | localhost | $A$005$Ey!%WPv/Wy
Uk^suxGMQtr1AgJKWRa8TEqBn7LXZ.XZ6loabFSIFimXC6OLM1 | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

此命令可以查询mysql中所有存在用户的相关信息

删除用户

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

修改用户

mysql> alter user matt@'10.0.0.%' identified with mysql_native_password by '123456';

用户资源管理

密码重用

password_history=6
password_reuse_interval=365

锁定用户

ALTERUSER'old_app_dev'@'localhost'ACCOUNTLOCK;

密码过期时间

select @@default_password_lifetime;
SET PERSIST default_password_lifetime=180;
SET PERSIST default_password_lifetime=0;
CREATE USER'matt'@'localhost' PASSWORD EXPIRE INTERVAL 90DAY;
ALTER USER'matt'@'localhost' PASSWORD EXPIRE INTERVAL 90DAY;
CREATE USER'matt'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER'matt'@'localhost' PASSWOR DEXPIRE NEVER;
FAILED_LOGIN_ATTEMPTS N

忘记root管理原密码处理方式

1.关闭数据库
/etc/init.d/mysqld stop
2.安全模式启动数据库
[root@localhostdata]# mysqld_safe --skip-grant-tables --skip-networking &
3.登陆数据库
mysql
4.刷新授权表
flush privileges;
5.修改密码
mysql>alter user root@'localhost' identified with mysql_native_passwordby '123';
6.重启数据库到正常模式
[root@localhost data]# /etc/init.d/mysqld restart

密码管理规范

1.123、123456、admin、root等弱口令禁止使用
2.使用三种不同形式的字符,如大小写字母、数字、特殊字符
3.密码长度大于12位

8.0用户方面的新特性

1.密码插件,在8.0中替换为了sha2模式
2.在8.0中不支持grant直接创建用户并授权，必须先建用户后grant授权。
目前，关于密码插件sha2使用客户端工具:navicat、sqlyog工具,不支持（无法连接）主从复制，MGR，不支持新的密码插件的用户老的驱动无法连接数据库

解决方法：
create with mysql_native_password 
alter with mysql_native_password
vim /etc/my.cnf
default_authentication_plugin=mysql_native_password

权限级别

GRANT 权限列表 ON 对象 TO 用户;
例如:grant all on *.* to matt@'localhost';
*.*:全库级别——管理员
———>mysql.user
test.*:单库级别——业务层面
———>mysql.db
test.t1:单表级别
———>mysql.tables_priv
select(id,name):列级别
———>mysql.columns_priv
mysql> show privileges; #可以查询所有的权限列表信息

授权管理

生产库：

管理员：ALL权限中不包含Grant option
业务用户：select insert delete update
开发用户—->开发库测试人员—->测试库
开发人员—->SQL审核平台—->生产库管理员

普通权限授权
mysql>grant all on *.* to test@'10.0.0.%';
mysql>grant select,update,delete,insert on *.* to matt@'10.0.0.%';
mysql>grant select,update,delete,insert on test.* to test@'10.0.0.%';
角色创建及授权
mysql>create role dev@'10.0.0.%';
mysql>grant select on* .* to dev@'10.0.0.%';
mysql>grant dev to user2@'10.0.0.%';
mysql.role_edges;
information_schema.user_privileges;

生产中用户类型规范

管理员:ALL
开发:Create,Create routine,Create temporary tables,Create view,Delete ,Event,Execute,Insert,References,Select,Showdatabases,Showview,Trigger,Update
监控:select,replicationslave,clientsupper
备份:ALL
主从:replicationslave
业务:insert,update,delete,select

查看权限

show grants for test@'10.0.0.%';
mysql.user 存放*.*级别的权限
mysql.db 存放db.*级别的权限

权限回收

grant授权是叠加的,并不覆盖之前赋予的权限
mysql>revoke delete on *.* from matt@'10.0.0.%';
Query OK, 0 rows affected(0.01sec)
mysql>show grants for matt@'10.0.0.%';
mysql>revoke select(id) on test.t1 from user1@'10.0.0.%';

MySQL多种连接方式介绍

Socket(本地)

前提:必须创建和授权localhost相关用户
mysql>create user matt@'localhost' identified by '123';
mysql>grant all on *.* to matt@'localhost'withgrantoption;
[root@db01~]# mysql-uroot -p123 -S /tmp/mysql.sock #-S 手动指定sock文件连接数据库

TCP/IP(远程)

前提:必须创建和授权远程网段相关用户
mysql>create user matt@'10.0.0.%'identified by '123';
mysql>grant all on *.* matt@'10.0.0.%'with grant option;
[root@db01]# mysql -umatt -p123 -h 10.0.0.51 -P 3306
如何查看和监控连接情况：
show process list;
select * from information_schema.processlist\G

客户端工具

sqlyog、navicat、workbench
注意:生产中不要用绿色版软件;mysql_native_password插件5.4基于SSL的安全连接

MySQL 8.0初始化配置方式

  • 初始化配置文件应用
    • 配置示例:
      [root@db01]#cat/etc/my.cnf
      [mysqld]user=mysql#管理用户
      #basedir=/usr/local/mysql57
      basedir=/usr/local/mysql8 #软件路径
      datadir=/data/mysql/data_3306 #数据路径
      socket=/tmp/mysql.sock #socket文件位置
      server_id=6#服务器ID,主从时标识不同主机log_bin=/data/mysql/binlog_3306 #二进日志
      port=3306 #端口
      [mysql]
      socket=/tmp/mysql.sock
    • 标签项:
      [mysqld]、[mysql]
      影响生效的程序,表现下根的是此标签项程序的相关配置信息
      种类:服务端标签和客户端标签
      服务器标签:[mysqld]、[mysqld_safe]
      客户端标签:[mysql]、[mysqldump]、[client]
    • 配置项:
      user=mysql #用户
      port=3306 #端口
      server-id=51 #服务ID
      basedir=/usr/local/mysql #数据库程序位置
      datadir=/data/3306/data #数据位置
      socket=/tmp/mysql.sock #socket文件位置
      在某个标签下相关配置信息,影响到此标签程序的相关功能
  • 关于配置文件的调用
    • 默认读取位置
      [root@db01~]# mysqld --help --verbose|grep my.cnf
      /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
      修改默认读取路径
      mysqld_safe --defaults-file=/opt/matt.cnf &
    • 指定配置文件应用
      mysqld_safe --defaults-file=/opt/oldguo.cnf &

    MySQL8.0启动和关闭流程

    • 启动方式介绍
      systemd—->/etc/init.d/mysqld —->mysql.server—-> mysqld_safe &—->mysqld &
      mysqld &
      启动日志全部会打印到屏幕—->可以进行问题排查
      mysqld_safe &
      当mysqld异常crash会尝试去启动mysqld
      关闭方式：
      [root@db01~]#mysqladmin -uroot -p123 shutdown
      db01 [(none)]>shutdown;
    版权声明：本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
    文章名称：《Mysql体系结构与日常管理》
    文章链接：https://wrlog.cn/2023/06/1392/
    本站资源仅供个人学习交流，请于下载后24小时内删除，不允许用于商业用途，否则法律问题自行承担。
    分享到

    相关推荐