mysql简单架构和用户管理

用户管理

创建账号

#创建账号wei，在10网段的主机上都能登录，密码为123456
create user wei@'10.0.0.%' identified by '123456';

#查看所有用户账号
select user,host from mysql.user;

删除用户

drop user wei@'10.0.0.%'

修改用户相关信息

#修改用户密码，提前知道密码并登录
alter user wei@'10.0.0.%'  identified by '123456';

授权

#授权all权限，on针对数据库zabbix.*里的所有资源，to赋权给
geant all on zabbix.* to wei@'10.0.0.%';

#刷新权限，有时得退出再登录才行
flush privileges;

取消授权

revoke delete on *.* from wei@'10.0.0.%'

查看权限

#查看该账号拥有的权限
show grants for wei@'10.0.0.%'

mysql简单架构原理

简单架构原理

MySQL Server 架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

服务层

服务层是MySQL的核心，包含系统管理和控制工具，连接池， SQL接口，解析器，查询优化器，缓存六个部分
连接池：负责存储和管理客服端连接，一个线程负责管理一个连接系统管理和控制工具：例如备份恢复、安全管理、集群管理等
SQL接口：用于接收客服端发送的SQL命令，并且返回查询结果。 比如 DML 、DDL 、存储过程、视图、触发器等。
解析器：负责将接收到的SQL解析成解析树，然后更加MySQL规则进行合法校验，生成新的解析树
查询优化器：当解析树通过后，将交由优化器转化为执行计划，然后与存储引擎进行交互
缓存： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

存储引擎层

存储引擎负责与系统文件进行交互， 对数据进行存储和读取， MySQL 存储引擎是插件式的， 服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现有 很多种存储引擎，各有各的特点，最常见的是 MyISAM 和 InnoDB 。每个表都可以设置不同的引擎

系统文件层

该层主要负责将数据库数据和日志存储在文件系统中， 并完成与存储引擎的交互， 是文件的物理存储层。 主要包含日志文件，数据文件，配置文件， pid 文件， socket 文件等。

MySQL是C/S 架构的，单进程多线程模型的，，因此，每个用户连接，都会创建一个单独的连接线程；

关系统型数据库相关概念

• 关系 ：关系就是二维表，其中：表中的行、列次序并不重要
• 行row：表中的每一行，又称为一条记录record
• 列column：表中的每一列，称为属性，字段，域field
• 主键PK Primary key：用于惟一确定一个记录的字段，一张表只有一个主键
• 域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值，人类的年龄只能0-150

存储引擎

查看支持的

show engines;      #也可查看默认选项

myisam和innodb的区别

myisam
#不支持事务
#表级锁定，当访问时锁定整张表；innodb访问时只锁定行
#不支持数据缓存
#不支持外键
#不支持聚簇索引
#不支持MVCC（多版本并发控制机制）高并发，有所操作（增删改）的标记记录，不同标记记录来访问会有相对应的不同的内容
#MySQL5.5.5前默认的数据库引擎
#磁盘存储最大支持256T，innodb最大支持64T

引擎文件的不同

myisam

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

innodb

• tbl_name.frm 表格式定义
• tbl_name.ibd 存储数据和索引

索引

不是必须存在，数据库量庞大时可用于性能优化，相当于大纲、目录，需占据额外空间且需实时更新

• 索引可以降低服务所需要扫描的数据量，减少了IO次数
• 查询无索引的字段不使用索引
• 查询姓名包含x,或以x结尾不使用索引，只有左前缀使用索引

索引类型

• B+ TREE、HASH、R TREE、FULL TEXT #hash精确查找，R 地理位置 ，FULL 全文搜索
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引：都在同一张表中为例，以主键列作为索引信息，二级索引的页不放数据，放的是主键列的信息，再通过主键索引查找主键列及其相关数据
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引：是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

B+tree索引

开头位置才可用，包含型和最后型不可用，适用于范围性查找，数据叶按顺序排序，临近的数据叶直接有链接指针相连

根节点和枝节点的5、28、65表示编号、索引，P1指针大小为6字节，具体数据放在叶子节点中，节点量多，编号数和指针数相同，磁盘块大小页为16K

计算记录量

单个节点指针数计算公式：页大小/（主键id大小+指针大小） 16k/（8+6）=1170个 假设编号的数据类型为bigint

一个指针指向一页，所以单节点总记录数为（两层高度）：16x1170=18720条记录 假设一条记录占1K

指针指向下一节点，总记录数计算公式（三层高度，如图所示）：16x1170x1170=21902400条记录

索引管理

创建索引

主键自带索引，单表中创建两三个即可，毕竟要占据额外空间

create index idx_name on students(name(4));     #在student表上创建索引名为idx_name作用在(name)列上，(name(4))指定name列的前四个字符作为索引信息，保证索引信息的唯一性

查看索引

show indexes from students\G      #Key_name索引名，Column_name作用在哪一列，Index_comment哪一类型的索引

#可用explain查看是否利用索引查找，
mysql> explain select * from students where stuid = 5;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#possible_keys可以使用的索引，不一定用，基于性能优化，key使用的索引，type性能类型

删除索引

drop index idx_name on students;

explain工具

#建议性能类型要达到range以上
NULL> system > const > eq_ref > ref > ref_or_null > index_merge >  range > index > ALL

类型说明

类型	说明
All	最坏的情况，全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序，但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索 引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描， 一个有限制的索引扫描。key列显示使用了哪个索引。当使用=、<>、>、 >=、<、<=、ISNULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以 使用range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq ref不同的是，它用在关 联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用 于使用=或<=>操作符的带索引的列。
eq ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生(高效)
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一 次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量(高效)
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引(高效 )

profile工具

#开启使用
set profiling = ON;

#查看语句执行所花费的时间
show profiling;
#查看4号命令语句具体阶段所花费的时间
show profile for query 4;

LAMP简单架构搭建

环境搭建：

五台主机
    1. 仅主机 192.168.0.130
    2. 仅主机 192.168.0.131
    3. 双网卡，仅主机和NAT  10.0.0.163 192.168.0.129 
	4. NAT 10.0.0.164
	5. NAT 10.0.0.165
4号和5号主机搭建好后就可以通过浏览器访问4号机的id了，之后再按照相关步骤傻瓜式操作下一步下一步即可

具体实现：

#1 仅主机 192.168.0.130
vim /etc/sysconfig/network-scripts/ifcfg-eth0
GATEWAY=192.168.0.131
DNS1=192.168.0.131
nmcli connection reload
nmcli connection up eth0

#2 仅主机 192.168.0.131
yum -y install bind bind-utils
vim /etc/sysconfig/network-scripts/ifcfg-eth0
DNS1=127.0.0.1
nmcli connection reload
nmcli connection up eth0
[root@rocky2]#cd /var/named/
touch wei.org.zone
chmod 640 wei.org.zone       
chgrp named wei.org.zone
[root@rocky2]#vim /etc/named.conf
options {
//    listen-on port 53 { localhost; };   
//	allow-query     { any; };            
[root@rocky2]#vim wei.org.zone 
$TTL 1D                                    
@         IN SOA master admin.wei.org. ( 20230503 3H 10M 1D 6H )
             NS  master                   
master       A   192.168.0.130
www          A   192.168.0.129

[root@rocky2]#vim /etc/named.rfc1912.zones
zone "wei.org" {                           
	type master;                           
	file "wei.org.zone";                    
};
systemctl start named

#3 双网卡，仅主机和NAT
[root@firewall ~]#hostname -I
10.0.0.163 192.168.0.129 
[root@firewall ~]#cat /etc/sysctl.conf 
net.ipv4.ip_forward=1
sysctl -p
#添加内网转发规则
iptables -t nat -A PREROUTING -d 192.168.0.129 -p tcp --dport 80 -j DNAT --to-destination 10.0.0.164:80

#4 NAT 10.0.0.164
yum -y install httpd php php-mysqlnd php-json
systemctl enable --now httpd
wget https://cn.wordpress.org/latest-zh_CN.zip
unzip wordpress-6.2-zh_CN.zip -d /opt
cd /opt/wordpress
mv * /var/www/html/
chown -R apache. /var/www/html/
vim /etc/sysconfig/network-scripts/ifcfg-eth0
GATEWAY=10.0.0.163
nmcli connection reload
nmcli connection up eth0

#5 NAT 10.0.0.165
yum list mysql-server
systemctl start mysqld
mysql
create database wordpress;
create user wordpress@'10.0.0.164' identified by '123456';
grant all on wordpress.* to wordpress@'10.0.0.164';