专职DBA-MySQL体系结构与基本管理 作者:周万春 微信:lovemysql3306 MySQL体系结构-C/S架构 C/S(客户端/服务端)模型介绍 创建账号: [dba@localhost:mysql.sock] [(none)]> create user 'dev'@'10.0.0.%' identified by '123'; TCP/IP方式(远程/本地): [root@db01 ~]# mysql -udev -p -h10.0.0.11 -P3306 [dev@10.0.0.11:3306] [(none)]> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | dev@10.0.0.11 | dev@10.0.0.% | +---------------+----------------+ 1 row in set (0.00 sec) socket方式(仅本地localhost): [root@db01 ~]# mysql -udba -p -S /data/mysql/mysql3306/sock/mysql.sock [dba@localhost:mysql.sock] [(none)]> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | dba@localhost | dba@localhost | +---------------+----------------+ 1 row in set (0.00 sec) 实例介绍 MySQL实例 = mysqld后台守护进程 + Master Thread + 干活的Thread + 预分配的内存 公司 = 老板 + 经理 + 员工 + 办公室 MySQL出现故障或者性能问题80%以上是人为的。 mysqld程序运行原理 mysqld程序结构: [应用程序]---》连接层---》SQL层---》存储引擎层(磁盘、内存、网络) 一条update SQL在MySQL中结束生命历程 https://www.cnblogs.com/zhouwanchun/p/13141382.html 一条select SQL语句的执行过程 1、连接层: (1).提供连接协议:tcp/ip、socket (2).提供验证:用户、密码、ip、socket (3).提供专用连接线程:接收用户SQL,返回结果。 查看连接线程show processlist; 数据库为什么连接不上? 没启动、账号密码错误、ip、port、socket、分配不了连接线程等等。 SQL_MODE:就是为了执行SQL语句时,更加规范。 2、SQL层 (1).接收上层传送的SQL语句。 (2).语法验证模块:验证语句语法,是否满足sql_mode (3).语义检查:判断SQL语句的类型 DDL:数据定义语言 DCL:数据控制语言 DML:数据操作语言 DQL:数据查询语言 (4).权限检查:用户对库表有没有权限 (5).预处理 解析器:对语句执行前,进行预处理,生成解析树(执行计划),说白了就是生成多种执行计划。 优化器:根据解析器得出的多种执行计划,进行判断,选择最优的执行计划。 代价模型:资源(CPU IO MEM)的耗损评估性能好坏,选择代价最低的执行计划。 (6).执行器:根据最优的执行计划,执行SQL语句,产生执行结果。 执行结果:在磁盘的xxx位置上。 (7).提供查询缓存(默认是没开启的):会使用redis、tair替代查询缓存功能。 (8).提供日志记录:binlog。 3、存储引擎层(类似于Linux中的文件系统) 负责根据SQL层执行的结果,从磁盘上拿数据。 将取出的16进制的磁盘数据,交给SQL层结构化成表,由连接层的专用线程返回给用户。 存储引擎是充当不同表类型的处理程序的服务器组件。 存储引擎用于: 存储数据 检索数据 通过索引查找数据 双层处理: 上层包括SQL解析器和优化器 下层包含一组存储引擎 SQL层不依赖于存储引擎: 引擎不影响SQL处理 有一些例外 数据库逻辑结构: 库: 库名、属性。 表: 表名:表属性(存储引擎、字符集)。 字段:字段名、属性、数据类型、约束。 行(记录): 字段名称对应的数据。 关系: 库---》表---》字段、行记录。 物理存储结构引入: 库:操作系统下的目录 表:多个文件组成 MyISAM引擎表:.frm表结构、.MYD数据行、.MYI索引信息 InnoDB引擎表:.frm表结构、.ibd数据行+索引 关系:表---》段,一个表就是一个段,包含一个多个区---》区,一个或多个连续的页---》页,最小的IO单元,16K。 MyISAM引擎的表 /data/mysql/mysql3306/data/mysql/user.frm /data/mysql/mysql3306/data/mysql/user.MYD /data/mysql/mysql3306/data/mysql/user.MYI InnoDB默认的存储引擎的表 /data/mysql/mysql3306/data/app01/db.opt /data/mysql/mysql3306/data/app01/t1.frm /data/mysql/mysql3306/data/app01/t1.ibd 表的段、区、页(16k) 页:最小的存储单元,最小的IO单元,默认16K (page) ---block(4KB) ---> sector(512B) 区:64个连续的页,是一个区,大小是1M 段:一个表就是一个段(分区表除外),包含一个或多个区构成。 基础管理 用户权限管理 用户:登录和管理数据库的逻辑对象 '用户名'@'白名单' 白名单支持的方式? 'dev'@'%' 'dev'@'10.0.0.%' 'dev'@'10.0.0.1' 'dev'@'localhost' 'dev'@'db01' 'dev'@'192.168.56.1%' 'dev'@'192.168.56.0/255.255.255.0' 创建账号 create user 'app01'@'10.0.0.%' identified by '123'; 查看账号信息 desc mysql.user; select user,host,plugin,authentication_string from mysql.user; 修改账号信息 alter user 'app01'@'10.0.0.%' identified by '456'; 删除账号 drop user 'app01'@'10.0.0.%'; 创建行号 create user 'dba'@'10.0.0.%' identified by '123'; 账号授权 grant all privileges on *.* to 'dba'@'10.0.0.%'; 查看账号权限 show grants for 'dba'@'10.0.0.%'; 查看权限 show grants; show grants for dba; show grants for 'dba'@'%'; show grants for 'dba'@'localhost'; 回收权限 revoke grant option on *.* from 'dev'@'10.0.0.%'; revoke drop,delete on *.* from 'dev'@'10.0.0.%'; 注意: 1.不要将公司任何账号,给非本公司人员。 2.不要流程范围外,授予非业务部门业务系统用户密码信息。 3.所有核心文档在通过互联传输时,都要脱敏处理。 4.不能给开发和应用系统库的权限。 开发人员用户授权流程: 1.要权限 2.对谁操作 3.你从哪来 4.密码要求 MySQL8.0在grant命令添加新特性 1.建用户和授权分开了 2.grant不再支持自动创建用户了,不支持改密码。 3.授权之前,必须要提前创建用户。 有哪些权限 show privileges; all privileges是以上所有权限,一般是DBA管理员拥有的。 with grant option:是DBA管理员才具备的,给别的用户授权的功能。 *.* 给管理员用户 app01.* 给开发和应用用户 需求1:Mac机器的Navicat/MySQL Workbench连接到Linux中的MySQL,管理员dba用户。 需求2:创建一个应用用户app01用户,能从Mac上连接登录MySQL,并能操作所有库。 本地管理员用户密码忘记 [root@db01 ~]# pkill mysqld(多实例不可以这样pkill) [root@db01 ~]# mysqld --defaults-file=/data/mysql/mysql3306/conf/my3306.cnf --skip-grant-tables --skip-networking & [root@db01 ~]# ps -ef | grep mysqld mysql 2991 2787 5 01:02 pts/1 00:00:00 mysqld --defaults-file=/data/mysql/mysql3306/conf/my3306.cnf --skip-grant-tables --skip-networking [root@db01 ~]# mysql -S /data/mysql/mysql3306/sock/mysql.sock [root@:mysql.sock] [(none)]> select user(),current_user(); +--------+-----------------------------------+ | user() | current_user() | +--------+-----------------------------------+ | root@ | skip-grants user@skip-grants host | +--------+-----------------------------------+ 1 row in set (0.00 sec) [root@:mysql.sock] [(none)]> set global super_read_only=off; [root@:mysql.sock] [(none)]> flush privileges; [root@:mysql.sock] [(none)]> alter user 'root'@'localhost' identified by 'lovemysql3306'; [root@:mysql.sock] [(none)]> shutdown; [root@db01 ~]# mysqld.start [root@db01 ~]# mysql -S /data/mysql/mysql3306/sock/mysql.sock -p Enter password:lovemysql3306 建议配置MySQL的安全登录工具 [root@db01 ~]# mysql_config_editor print --all [dba] user = dba password = ***** socket = /data/mysql/mysql3306/sock/mysql.sock [zhouwanchun] user = zhouwanchun password = ***** host = 10.0.0.11 port = 3306 [root@db01 ~]# ls -l .mylogin.cnf -rw------- 1 root root 268 Oct 18 00:34 .mylogin.cnf 此文件中存储的MySQL账号可以通过Python的myloginpath模块打印出来。 [root@db01 ~]# vim print_password.py #!/usr/bin/env python3 import myloginpath conn_user = input("please input mysql username: ").strip() mylogin = myloginpath.parse(conn_user) print(mylogin, type(mylogin)) [root@db01 ~]# python3 print_password.py please input mysql username: zhouwanchun {'user': 'zhouwanchun', 'password': '123', 'host': '10.0.0.11', 'port': 3306} <class 'dict'> [root@db01 ~]# python3 print_password.py please input mysql username: dba {'user': 'dba', 'password': '123', 'socket': '/data/mysql/mysql3306/sock/mysql.sock'} <class 'dict'>