week5
- SUDO,PAM配置规范说明
- chrony搭建私有ntp服务
- 说明CDN原理
- 搭建智能DNS，实现不同地域客户端解析到不同主机
- 解释DNS解析流程
- iptables 5表5链解释
- iptables/firewalld/nftable 实现主机防火墙。5000-6000端口仅192.168.0.0/24网段内的主机访问
- mysql的各发行版有哪些 ？
- mysql索引的作用
- mysql安全加固？
1.SUDO,PAM配置规范说明
sudo机制
root用户允许普通用户执行一些命令，或者直接代替/root去执行所有命令
su catyer：切换用户
su：切换到root用户执行
sudo配置：一般是去到/etc/sudoers配置对应的用户，用户上配置对应的权限，或者是执行的命令
需要注意的是：生产过程中一般给不同的业务人员不同的权限，创建对应的系统用户，或者对不同的用户授予不同的命令权限
授权给catyer用户，代表可以代替root用户执行ls命令，但是在执行的时候需要加上sudo；但是除了ls命令（对root用户权限下）的操作外，其他的操作需要额外的授权，如果是ALL，则是全部命令
##查看有哪些用户有登陆权限，授权给catyer用户执行ifconfig的权限
grep /bin/bash /etc/passwd
root:x:0:0:root:/root:/bin/bash
catyer:x:1000:1000:catyer:/home/catyer:/bin/bash
vim /etc/sudoers
[root@master etc]#ll sudoers
-r--r----- 1 root root 4328 Sep 30 2020 sudoers
##需要修改sudoers文件的写权限
[root@master etc]#chmod u+w sudoers
[root@master etc]#ll sudoers
-rw-r----- 1 root root 4328 Sep 30 2020 sudoers
##修改sudoers文件
vim sudoers
##默认允许root用户执行所有的命令
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
#root ALL(在哪个主机)=(代表的用户，可以写root) ALL(COMMAND指令)
#which +command：表示查找这个命令在哪里
which ls
catyer ALL=(root) /usr/bin/ls #允许catyer代替root用户执行ls /root命令
catyer ALL=(root) /usr/bin/ls #允许catyer代替root用户执行ls /root命令
catyer ALL=(root) /usr/bin/ls /root,/usr/bin/mount /dev/sdb /mnt/mount #
##测试root权限，访问到ls /root用户，OK
sudo ls /root/tmp
0.txt 2.txt 6.txt 7.txt 99944111.txt 99944113.txt 999442.txt 999444.txt
1.txt 3.txt 77777.txt 999000.txt 99944112.txt 999441.txt 999443.txt 999.txt
但是想执行cd命令就不行了，需要添加/bin/cd /root/tmp这个权限，权限颗粒度到这个级别了
sudo cd /root/tmp
Sorry, user catyer is not allowed to execute '/bin/cd /root/tmp' as root on master.
pam机制
PAM：属于一种验证性的模块，或者叫做验证性的接口，可以给到其他程序或者服务调用pam认证模块去实现认证功能，只关注如何验证用户的API
PAM机制自己有统一规范的API接口，供各程序调用
查看主配置文件目录：ls /etc/pam.d
案例：查看某个服务是否支持PAM认证
##查看sshd服务支持pam库
ldd `which sshd` | grep libpam
libpam.so.0 => /lib64/libpam.so.0 (0x00007f3627884000)
PAM认证流程
1.某个命令或者程序执行，比如ssh 10.0.0.128
2.找到sshd这个服务对应的libpam.so.d
3.pam库调用对应的验证模块进行验证
4.pam决定下一步该怎么走
2.chrony搭建私有ntp服务
搭建chrony NTP服务器，chrony：NTP同步时间服务
客户端程序：chronyc
服务端程序：chronyd
##环境：chrony服务器1 10.0.0.128
##chrony客户端 10.0.0.132
##服务端配置
rpm -q chrony || yum -y install chrony
systemctl enable --now chronyd
vim /etc/chrony.conf
##配置各大云厂商的NTP服务器
server time1.cloud.tencent.com iburst
server time2.cloud.tencent.com iburst
##配置允许10.0.0.0段的机器访问，其余默认
allow 10.0.0.0/16
systemctl restart chronyd
##关闭防火墙
systemctl disable --now firewalld
##查看本地的时区
[root@master ~]#timedatectl
Local time: Tue 2022-08-30 23:19:04 CST
Universal time: Tue 2022-08-30 15:19:04 UTC
RTC time: Tue 2022-08-30 15:19:04
Time zone: Asia/Shanghai (CST, +0800)
NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a
##查看有哪些时区可选
timedatectl list-timezones | grep "Asia/Shanghai"
##设置本地时区
timedatectl set-timezone Asia/Shanghai
##查看本地的时间时区
date
Mon Sep 5 21:37:25 CST 2022
客户端chronyc配置
vim /etc/chorny.conf
server 10.0.0.128 iburst
systemctl restart chronyd
##查看本地的时区以及配置
[root@rocky ~]#timedatectl
Local time: 二 2022-08-30 15:28:35 UTC
Universal time: 二 2022-08-30 15:28:35 UTC
RTC time: 二 2022-08-30 15:28:35
Time zone: n/a (UTC, +0000)
System clock synchronized: no
NTP service: active
RTC in local TZ: no
##配置时区
[root@rocky ~]#timedatectl set-timezone Asia/Shanghai
[root@rocky ~]#timedatectl
Local time: 二 2022-08-30 23:34:03 CST
Universal time: 二 2022-08-30 15:34:03 UTC
RTC time: 二 2022-08-30 15:34:03
Time zone: Asia/Shanghai (CST, +0800)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
chronyc客户端命令实例
##查看源服务器状态
chronyc sources -V
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 10.0.0.128 3 6 377 4 +84us[ +108us] +/- 16ms
##服务端查看已经连接的客户端
[root@master ~]#chronyc clients
Hostname NTP Drop Int IntL Last Cmd Drop Int Last
===============================================================================
10.0.0.132 43 0 6 - 42 0 0 - -
10.0.0.129 17 0 6 - 58 0 0 - -
##显示当前时间源信息(客户端)
[root@rocky ~]#chronyc sources
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 10.0.0.128 3 6 377 4 +84us[ +108us] +/- 16ms
[root@rocky ~]#chronyc sources -v
.-- Source mode '^' = server, '=' = peer, '#' = local clock.
/ .- Source state '*' = current best, '+' = combined, '-' = not combined,
| / 'x' = may be in error, '~' = too variable, '?' = unusable.
|| .- xxxx [ yyyy ] +/- zzzz
|| Reachability register (octal) -. | xxxx = adjusted offset,
|| Log2(Polling interval) --. | | yyyy = measured offset,
|| \ | | zzzz = estimated error.
|| | | \
MS Name/IP address Stratum Poll Reach LastRx Last sample
===============================================================================
^* 10.0.0.128 3 6 377 55 +32us[ +53us] +/- 16ms
3.说明CDN的原理
关于CDN的原理：内容分发网络（Content Delivery Network，CDN）是建立并覆盖在承载网上，由不同区域的服务器组成的分布式网络。将源站资源缓存到全国各地的边缘服务器，供用户就近获取，降低源站压力。
本地用户访问方向：
1.在本地找网站/图片缓存，本地浏览器
2.没有的话找到附近DNS服务器，通过全局的GSLB算法返回到客户端的最佳CDN节点
3.CDN节点上没有找到对应要访问的内容(.jpg/png，.html，.pdf等)，回源站拉源站数据，回源站拉数据需要一定的时间
4.从源站获取到数据，返回到客户端
5.客户端建立缓存，CDN节点建立缓存，下次访问的时候直接读缓存
CDN使用方式
1.在源站搭建好相关的WEB服务，源站类型可以为服务器IP地址、OSS等
2.配置CDN服务，创建CDN域名，配置CDN回源到对应的源站：如果是服务器，则配置公网IP地址；如果是OSS，则添加OSS对应的对外访问域名
3.要启用CDN服务，需要公网DNS解析添加对应CDN加速域名的CNAME记录解析，访问到CNAME，访问到CDN加速域名的时候，即启用了CDN服务；同时如果CDN解析到源站是IP地址的话，将CDN的CNAME地址解析到对应的源站IP地址
xxx.ctfmall.com--->xxx.ctfmall.com.w.kunluncan.com
xxx.ctfmall.com.w.kunluncan.com--->IP地址
4.确保CDN访问到源站，对应的服务要起来，对应的资源路径要正确
5..访问到源站站点，访问正常，第一次如果是第一次配置访问，则会有点慢，因为需要回源站拉内容；第二次刷新访问正常，已经是读缓存内的内容了
4.搭建智能DNS
架构：搭建DNS服务器，从内网实现访问到公网
环境：主DNS服务器 10.0.0.128
从DNS服务器+客户端 10.0.0.129
4.1 配置DNS主服务器，配置named.conf配置文件
bind，bind-utils(dns测试工具，可以不装)
bind的测试工具包括：dig(最全面的域名解析工具)，host(简易版DNS解析测试)，nslookup(windows风格的)，最常用的就是dig，显示内容最多
rpm -ql bin-utils：查看这个包中有哪些工具
yum -y install bind bind-utils
启动DNS服务：systemctl enable --now named
修改默认dns配置文件，监听为指定网段10.0.0.0/24，vim /etc/named.conf
options {
//监听哪些机器：本地所有的IP
listen-on port 53 { localhost; }; //监听本地所有的IP 53端口
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
recursing-file "/var/named/data/named.recursing";
secroots-file "/var/named/data/named.secroots";
//允许哪些机器通过这个DNS查询:any所有机器
allow-query { 10.0.0.0/24; };
}
##包含了这两项
include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";
使用到的用户+用户组：root，named；group：named，安装了named服务后就会生成这个named的用户和用户组
id root
uid=0(root) gid=0(root) groups=0(root)
id named---记得修改域配置文件所属组为named
uid=25(named) gid=25(named) groups=25(named)
rndc命令：对于dns缓存，配置的使用，刷新Linux机器的DNS缓存
rndc --help
rndc reload ##重新加载dns配置
rndc flush ##刷新缓存
各种资源记录(RR：resource record)，文件路径：/var/named/named.localhost，/var/named/catyer.cn.zone(自已命名)，后续可以在这里写域名解析的文件
4.2 写主DNS服务器配置(DNS记录)
SOA记录：一般写在首行，一条记录，作为起始的授权记录
NS：解析，正向解析记录
A记录：域名---IPV4地址
AAAA记录：域名---IPV6地址
TXT：文本记录
CNAME：别名记录(CDN，WAF等)
PTR：反向解析，指针
vim /var/named/catyer.cn.zone #文件标准格式：其实也是个文件，只需要记得写入zone配置文件就好了
$TTL 1D
@ IN SOA master admin.catyer.cn ( ;SOA记录:起始授权记录
20220719 ; serial,序列号，数据库文件的序列号
3H ; refresh,DNS刷新记录,从节点拉取
10M ; retry,服务器网络有问题,重试时间
1W ; expire,DNS记录过期时间
3H )
@ IN NS master. ;主DNS服务器解析
master A 10.0.0.128
www A 10.0.0.128
k8s A 10.0.0.128
linux A 10.0.0.200
主从DNS服务器的同步机制：主服务器一般是有新的更新就推送，从服务器一般是定时拉更新的数据
主从DNS同步依赖条件：serial，类似MySQL主从的binlog文件版本号的变化，binlog_000003类似这样的文件名
PS：一定要记住修改catyer.cn.zone文件的所属组，不然这个文件是不生效的(本地域名解析)
chgrp named /var/named/catyer.cn.zone
ll /var/named/catyer.cn.zone
[root@master ~]#ll /var/named/catyer.cn.zone
-rw-r----- 1 root named 435 Sep 5 22:45 /var/named/catyer.cn.zone
4.3 写主DNS域配置文件/etc/named.rfc1912.zones
将zone的配置信息(catyer.cn)配置到统一的域配置文件内，这里面是/etc/named.frc1912.zones
##写好主域的信息，type类型为master主域，file为catyer.cn.zone这个配置文件
vim /etc/named.rfc1912.zones
zone "catyer.cn" IN {
type master; ##DNS服务器类型是主服务器，主节点
file "catyer.cn.zone"; ##域配置文件：指向catyer
allow-update { none; };
};
##检查DNS配置文件以及域配置文件有无正确
named-checkconf
named-checkzone catyer.cn /var/named/catyer.cn.zone
rndc reload：重新加载dns配置文件zone
rndc flush：刷新本地DNS缓存
##本地网卡DNS解析指向自己
ONBOOT="yes"
IPADDR=10.0.0.128
NETMASK=255.255.255.0
GATEWAY=10.0.0.2
#DNS1=114.114.114.114
#DNS2=8.8.8.8
DNS1=10.0.0.128
DNS2=10.0.0.129
##重启网络服务
systemctl restart network
分别都可以解析到IP，即本地的DNS服务器搭建成功，访问本地服务。访问到k8s.catyer.cn这个域名服务的，添加了DNS A记录后，不需要添加本地HOST文件就可以访问到解析的域名了
4.4 搭建从DNS服务器(DNS2)
安装dns服务：yum -y install bind bind-utils
注释掉named.conf配置文件内的监听限制，不然这个slave只会监听本地
options {
// listen-on port 53 { 127.0.0.1; };
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
recursing-file "/var/named/data/named.recursing";
secroots-file "/var/named/data/named.secroots";
// allow-query { localhost; };
编辑域配置文件，写上是catyer.cn主DNS(主域)的从域，type为slave
vim /etc/named.rfc1912.zones
zone "catyer.cn" {
type slave;
masters {10.0.0.128;};
file "catyer.cn.zone.slave";
};
systemctl restart named
编辑好后，重启named服务，配置网卡文件DNS2(DNS从服务器)
vim /etc/sysconfig/network-scripts/ifcfg-ens33
DNS1=10.0.0.128
DNS2=10.0.0.129
systemctl restart named
systemctl restart network
##刷新本地缓存
rndc reload
rndc flush
##停止dns1的服务
systemctl stop named
尝试解析k8s.catyer.cn这个域名，发现承载的dns服务器是10.0.0.129，证明停止了DNS1，DNS2成功生效工作，k8s.catyer.cn是在10.0.0.128上面做的A记录，但是承载本地DNS解析的是129这台地址，k8s.catyer.cn这个地址本身的DNS解析就是10.0.0.128，从DNS搭建成功
在slave1上测试：dig k8s.catyer.cn
4.5 从指定的DNS服务器(IP)上查询该解析+DNS服务器地址
##windows环境
nslookup api.ctfmall.com 100.100.2.136
##Linux环境，+@符号
dig k8s.catyer.cn @10.0.0.128
dig k8s.catyer.cn @10.0.0.129
host k8s.catyer.cn @10.0.0.8
dig k8s.catyer.cn
5.解释DNS解析流程
DNS解析流程：
想要访问www.baidu.com这个域名，使用迭代查询的方式去查询这个域名的解析的IP地址
1.找本地地址(host文件)--->本地配置的DNS服务器(DNS缓存(浏览器))，不用每次都去找根服务器；DNS服务器有缓存，本地也有缓存；
windows服务器有DNS缓存，使用ipconfig /flushdns可以刷新本地缓存；Linux服务器的DNS客户端没有缓存
2.DNS服务器找到根服务器(互联网的根服务器)，开始迭代查询，根服务器：.，其实完整的域名应该是www.baidu.com.
3.找顶级域名，因为baidu.com是.com，所以找.com的顶级域名服务器里面找解析
4.找一级域名baidu.com这个的DNS解析
5.从baidu.com这个解析里面就有www.baidu.com这个二级域名配置了记录集，传回到用户，实现www.baidu.com这个域名解析到百度后端的某个IP地址
递归查询：指的是请求到了，服务器无法反馈结果，会去找到结果并且最终反馈---处理到底；DNS服务器必须返回一个准确的结果给到客户端
迭代查询：找别人查找，自己不处理---找别的DNS服务器查询解析，根域名---顶级域名---一级域名
DNS查询的工具
windows：nslookup
linux：安装bind-utils，工具包有dig，nslookup，host等工具，比较常用的就是dig，能够显示比较完整的信息，host比较简洁，解析出IP地址出来
rpm -ql bind-utils
6.iptables 5表5链解释
iptables5表指的是：filter：过滤规则，允许/拒绝某些流量流入，比如INPUT，OUPUT等，NAT：地址转换表，包括SNAT和DNAT，mangle：修改数据规则表，raw：加快通过防火墙的速度
security：强制安全规则，由SELINUX实现，/etc/config/selinux
5表中最为常用的就是filter(过滤表)，和nat(地址转换表)
5链（chain）：PREROUTING（总入口），INPUT（入方向的包），OUTPUT（出方向的包），FORWARD（经过内部转发，转发机制），POSTROUTING（总出口）
##查看防火墙规则
iptables -L
[root@master ~]#iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT udp -- anywhere anywhere udp dpt:domain
ACCEPT tcp -- anywhere anywhere tcp dpt:domain
ACCEPT udp -- anywhere anywhere udp dpt:bootps
ACCEPT tcp -- anywhere anywhere tcp dpt:bootps
ACCEPT udp -- anywhere anywhere udp dpt:domain
ACCEPT tcp -- anywhere anywhere tcp dpt:domain
ACCEPT udp -- anywhere anywhere udp dpt:bootps
ACCEPT tcp -- anywhere anywhere tcp dpt:bootps
ACCEPT tcp -- 172.16.0.128 10.0.0.0/24
7.iptables/firewalld/nftable 实现主机防火墙。5000-6000端口仅192.168.0.0/24网段内的主机访问
环境：外网业务机器开放5000-6000业务端口，只允许公司内网192.168.0.0/24网段访问，目前先实现80业务端口访问
防火墙过滤：iptables。使用SNAT做流量转发，仅允许内网网段访问，需要从POSTROUTING chain转发出去，一般使用动态IP地址转换，
防火墙主机：10.0.0.128 网卡1：NAT模式（网关10.0.0.2） 172.16.0.10 网卡2：仅主机模式（网关172.16.0.2）
内网访问客户端：10.0.0.129
ubuntu外网机器：172.16.0.135 网卡2：仅主机模式（网关172.16.0.2）服务：nginx web服务器
前提条件：开启IPV4转发
[root@master ~]#grep ipv4 /etc/sysctl.conf
net.ipv4.ip_forward=1
sysctl -p
配置基本的iptables snat转发
##ubuntu主机安装nginx服务，ubuntu默认直接启动
apt -y install nginx
##iptables节点
##专线网络,to-source表示当前固定的公网IP
iptables -t nat -I POSTROUTING -s 10.0.0.0/24 ! -d 10.0.0.0/24 -j SNAT --to-source 100.9.6.4
##动态IP，查看NAT表的信息，除了公网的10.0.0.0/24网段，其他全部转发，从POSTROUTING出去;-I表示在NAT表前面插入，如果加 -A POSTROUTING表示在表的最后添加这条规则，-j MASQUERADE表示是转化到动态IP，而不是固定公网IP
iptables -t nat -I POSTROUTING -d 10.0.0.0/24
iptables -t nat -I POSTROUTING -s 10.0.0.0/24 ! -d 10.0.0.0/24 -j MASQUERADE
iptables -t nat -nL / iptables -t nat -nL --line-numbers ##显示行号
Chain POSTROUTING (policy ACCEPT)
target prot opt source destination
MASQUERADE all -- 10.0.0.0/24 !10.0.0.0/24
##删除NAT表的第一条规则
iptables -t nat -D POSTROUTING 1
##配置iptables机器的双网卡，要和ubuntu主机同一个网段；开启网卡2的仅主机模式，需要在VMware内配置
cp ifcfg-ens33 ifcfg-ens36
IPADDR=172.16.0.10
NETMASK=255.255.255.0
GATEWAY=172.16.0.2
##ubuntu nginx默认web目录，修改nginx目录
root@ubuntu:/etc/nginx/sites-available# pwd
vim /etc/nginx/sites-available/default
server {
listen 80 default_server;
listen [::]:80 default_server;
root /data/nginx;
# Add index.php to the list if you are using PHP
index index.html index.htm index.nginx-debian.html;
server_name _;
}
nginx -t：检查Nginx服务配置服务
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
systemctl restart nginx
##在ubuntu上tail日志查看访问IP，获取到源端IP了；去掉POSTROUTING规则就无法curl通了，slave1的gateway是10.0.0.128，出口网关，所以在nginx日志中获取的是出口网关的IP
curl 172.16.0.135/nginx.txt
curl 172.16.0.135--->nginx服务
tail -f /var/log/nginx/access.log
172.16.0.10 - - [01/Aug/2022:13:56:24 +0000] "GET / HTTP/1.1" 200 612 "-" "curl/7.29.0"
##curl一个大一点的文件，查看ubuntu端的tcp连接状态(ESTABLISHED)，ss -nt，peer查看远端的地址，证明TCP链接已经建立了
dd if=/dev/zero of=/var/www/html/test2.txt bs=1M count=1024
State Recv-Q Send-Q Local Address:Port Peer
ESTAB 0 1152608 172.16.0.135:80 172.16.0.10:53704
8.mysql的各发行版有哪些 ？
MySQL：5.6 、5.7、8.0版本（linux 8.0 release），分为社区版和企业版
mariaDB：5.6、5.5、10.3（linux 8.0 release），MariaDB是由MySQL初始创建者在MySQL被Oracle收购之后，又独立成立了一家公司，来开发的数据库。MariaDB并不能完全和MySQL官方版完全兼容，但大多数功能是兼容的。
percona：Percona是由MySQL最初开发者组成的一个社区，Percona MySQL是基于官方社区版本二次开发的，在MySQL官方社区版的基础上增加了一些MySQL官方企业版才有的功能，同时对官方社区版进行了一些优化，从目前公布的数据来看，Percona MySQL 性能是要优于MySQL官方社区版的。
percona还是免费的，对于percona出了一个专业级别的mysql备份工具xtrabackup，和MySQL商业版的备份工具功能类似，还不用钱
| MySQL | Percona MySQL-server | MariaDB | |
|---|---|---|---|
| 是否开源 | 开源 | 开源 | 开源 |
| 事务型存储引擎 | InnoDB | XtraDB | XtraDB |
| 监控工具 | 企业版监控工具，社区版不提供 | Percona Monitor工具 | Monyog |
9.mysql索引的作用
MySQL索引一般是用于加速表的查询的，索引一般分为
1.主键索引：基于主键做的索引，比如订单表的编号是自动增长的(auto_increment)，同时订单号又是主键，可以做基于订单号的索引
2.聚簇索引：数据data和索引index放在一起，形成一个磁盘上的文件
3.B+TREE索引：索引上只存放索引信息，基本分为一层根节点+一层分支节点，下面都是数据了，查询效率最高，一般的MySQL表都是BTREE索引，同时下面的数据都是加了链表的，数据都是有链接起来的，查询速度快，节省性能--->一般来说都是b_tree索引
查询某张表的索引情况
[hellodb]>show index from stu\G;
*************************** 1. row ***************************
Table: stu
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
查询是否有使用index索引来进行查询，显示只遍历了一行，而不加索引字段进行查找，需要遍历整张表，需要遍历25行
[hellodb]>explain select * from hello.stu where stuid=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | stu | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
explain select * from hellodb.students where age=22;
[hellodb]>explain select * from hellodb.stu where age=22;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | stu | NULL | ALL | NULL | NULL | NULL | NULL | 25 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
show profiles; --可以查看sql语句的执行速度
索引的优势：加速表的查询，不用遍历整张表，对某个字段加了索引，加快查询
索引的劣势：如果表数据经常更新（insert、update等），不建议用索引，因为需要时常更新索引，占用一定的库性能；而且索引的磁盘文件.idx也会占用一定的磁盘空间
特殊查询：只能查询左前缀相同的，按照比如订单号都是2022开头的，按这个次序来排序
10万条记录表创建索引优化查询速度
大表尽量避免做全表扫描，之前由存储过程生成的10w条记录的表testlog
存储过程：自动化的插入、更新同一张表的数据
##用现有的存储过程
call sp_testlog;
##普通查询，使用limit限制从第三行开始，显示5行
[hellodb]>select * from testlog limit 3,5;
+----+-----------+--------+
| id | name | salary |
+----+-----------+--------+
| 4 | wang65321 | 323142 |
| 5 | wang65606 | 310887 |
| 6 | wang58624 | 998574 |
| 7 | wang23413 | 174936 |
| 8 | wang17228 | 336622 |
+----+-----------+--------+
5 rows in set (0.03 sec)
##加入where条件
select * from testlog where salary=489141;
explain select * from testlog where salary=489141;
##创建salary的索引
create index idx_salary on testlog(salary);
##查看sql语句的执行情况，可以看到以索引来查询比没有索引的速度快了将近20倍
set profiling=ON;
show profiles;
show profiles for query ID;
##show profiles：查看sql语句的执行速度
遍历100万条记录查询出一个salary的值，使用索引是0.0003s，不使用索引是0.01s，速度有很大的差别了
(root@localhost) [hellodb]>show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00030300 | select * from testlog where salary=489141 |
| 2 | 0.00007425 | drop index idx_salary from testlog |
| 3 | 0.00987600 | drop index idx_salary on testlog |
| 4 | 0.01724175 | select * from testlog where salary=489141 |
+----------+------------+-------------------------------------------+
10.mysql安全加固？
在MySQL 8.0以前的版本，一般都会使用mysql_secure_installation这个脚本，这个脚本的执行方式在于MySQL客户端的执行命令
mysql_secure_installation
##执行步骤
1.输入现有root用户的密码，可以到安装MySQL的日志里面找到temporary密码
grep temporary mysql.log
2.重置MySQL密码
3.删除匿名用户
4.删除test库(8.0后没有test库了)
5.done
mysql8.0的密码策略+密码验证组件
可以查看user表的password plugin插件，以及MySQL自带的密码验证组件validate_password component，这个密码验证组件在安装MySQL的时候默认开启
使用caching_sha2_password 这个密码插件的话，需要遵循MySQL8.0的新的密码特性，比较复杂
show variables like 'default_authentication_plugin%';：默认的MySQL身份插件
--->在生产中可以考虑密码策略，需要更高的强度
查看密码管理策略
[hellodb]>show variables like '%caching_sha2_password%';
+----------------------------------------------+-----------------+
| Variable_name | Value |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_digest_rounds | 5000 |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
+----------------------------------------------+-----------------+
4 rows in set (0.00 sec)
##查看密码插件
[hellodb]>select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | caching_sha2_password |
| wp | % | caching_sha2_password |
| catyer | 10.0.0.% | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
| wpuser | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
8 rows in set (0.00 sec)
[hellodb]>alter user root@'%' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
//需要加上with mysql_native_password
alter user root@'%' identified with mysql_native_password by '123';
[hellodb]>show variables like 'default_authentication_plugin%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
MySQL密码验证组件：validate_password component
指定复杂的密码策略：https://blog.csdn.net/zy_1995/article/details/102706728
如果不想要这个密码策略可以删除这个组件，卸载组件，参考官方文档可以删除
[(none)]>show variables like 'validate_password%';
Empty set (0.00 sec)
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
##删除组件
UNINSTALL COMPONENT 'file://component_validate_password';
##安装组件
INSTALL COMPONENT 'file://component_validate_password';
[(none)]>show variables like 'validate_password%';
Empty set (0.00 sec
##可以使用简单密码策略了
[(none)]>alter user root@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
修改MySQL的密码策略plugin
1.可以在/etc/my.cnf内指定密码策略，重启mysqld服务生效
2.在修改某个user@'host'的时候，加上with mysql_native_password
vim /etc/my.cnf
default_authentication_plugin=mysql_native_password
systemctl restart mysqld
##成功完成
[(none)]>show variables like 'default_authentication_plugin%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.15 sec)
本文作者：catyer
本文链接：https://www.cnblogs.com/catyer/p/16686847.html
版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步