首页  思维导图  详情

社区

2024-07-15 18:29:58   4  举报





AI智能生成

mysql基础，数据的增删改查，与扩展

mysql

redis 哨兵集群架构

作者其他创作

大纲/内容

mysql：磁盘存储型数据库

基础

mysql系统

SHOW VARIABLES LIKE 'character\_set\_%';

查看所有编码格式

show engines；：查看存储引擎

#查看系统变量
show global variables;

#查看会话变量
show session variables;

库

查看

show databases；：查看所有库

插入

创建库并指定字符序和字符编码：create datebase choose character set utf8mb4 collate utf8mb4_unicode_ci;

删除

drop database demo;

删除库，数据也会删除

修改

切换库

use 库名;

表

查

show tables；：查看所有表

增

创建表：create table name(id int, name varchar(50));

insert into t_name_1 select * from t_name_2;

复制表数据

改

修改表名：rename table t_student_info to t_student;

删

删除表数据：delete from demo;

删除表：drop table name;

删除表的所有数据：truncate table t_name;

表结构，表字段

增

create table t_name_1 select * from t_name_2;

复制表结构

复制的是表的所有数据，包括表的结构，和表的数据

添加表（字段）结构： alter table t_student_tmp add name varvhar(20) not null comment '注释';

添加约束(not null除外)：alter table t_student_info add constraint unique_con unique(stuno);

添加索引：create index sname_index on t_student_info(sname);

删

drop table demo;

删除表结构，数据也会删除

删除表字段：alter table t_name drop name;

删除约束(删除唯一约束的时候需要用index来标注删除)：alter table t_student_info drop index unique_con;

改

修改字段名字：alter table t_name change name1 name2 int(10) not null comment '注释';

修改字段类型：alter table t_student_info modify age int(4) comment '年龄'

查

desc 表名；

查看表结构

数据

增

插入数据：insert into demo(id,name) values(1,'nihao');

复制

insert into t_name_1 select * from t_name_2;

复制表数据

删

删除表所有数据： delete from t_name;

删除表的所有数据：truncate table t_name;

改

修改数据：update t_student_info set age=29 where sname='李四';

查

基本查询 where

指定查询：select * from t_student_info where id = 222;

指定查询：select * from t_student_info where id in (1,2,3,4,5,6);

排序查询：order by

升序

select * from t_score order by score;

select * from t_score order by score asc;

降序

select * from t_score order by score desc;

分组查询：group by

select sum(score),course_no from t_score group by course_no;

以course_no来分组，查询每一组的score的和and组名

连表查询：join

join：查询出所有能够对应的上的数据

select * from t_teacher t join t_department d on t.department = d.dept_no;

left join：以左表为基准，查询出左表所有的数据右表没有的，则填充null

select * from t_teacher t left join t_department d on t.department = d.dept_no;

left join：与left join正好相反，以右表为基准，查询出左表所有的数据右表没有的，则填充null

select * from t_teacher t right join t_department d on t.department = d.dept_no;

查询所有理工农的老师

select t.* from t_teacher t inner join t_department d on d.dept_no = t.department where d.dept_not_no = 'lgn';

查询分数表中所有年龄大于等于18岁的学生的详细(姓名，年龄，性别)信息和分数以及对应的课程

select stu.sname, stu.age, stu.sex, score.score, cou.cname, cou.course_no
from t_student_info stu join t_score score on stu.stuno = score.stuno
join t_course cou on score.course_no = cou.course_no
where stu.age = 18;

切片：limit

语法：select * from t_name limit' 从哪里开始'，'多少条数据';

去重查询：distinct

select distinct(stuno) from t_score;

统计查询：count

select count(*) from t_score;

模糊查询：like

select * from t_teacher where tname like '张%';

聚合查询：union

select tname, age, gender from t_teacher union select sname, age, sex from t_student_info limit 100;

需要两张表每条数据查询的数量一致（以上两表都为三个）

将两张表竖着排在一起打印

选择分支查询： case when

select
case
when score >= 90 then 'A'
when score >= 80 then 'B'
when score >= 70 then 'C'
when score >= 60 then 'D'
else 'F'
end as grade,stuno,course_no,score
from t_score;

运算符

# 逻辑运算符 and, or, not !
# 比较运算：>,>=,<,<=, =,!=
# 范围：in, between and

存储过程体·

创建

delimiter $$
create procedure name(参数（可写可不写）)
begin
mysql命令
end $$
delimiter ;

删除

drop procedure name;

改：删除 + 创建

实战

存储过程：删除其中id为100的学生信息，并且更新教师号为T003的老师的性别为 “女”

触发器

创建

delimiter $$
create trigger insert_teacher_logs after insert on t_teacher for each row
begin
insert into t_logs('insert', 'teacher插入数据');
end $$
delimiter ;

开始声明（我要创建触发器了）：delimiter $$

创建语法：create trigger inster_teacher_logs after insert on t_teacher for each row

after后面的内容：触发条件

t_teacher每插入一条内容执行一次：insert on t_teacher for each row

执行的命令

begin
insert into t_logs('insert', 'teacher插入数据');
end $$

声明结束：delimiter ;

删除

drop trigger insert_teacher_logs;

数据安全

创建用户

create user 用户名 identified by '密码';

授权

给用户yunt_teacher表的查询权限：GRANT SELECT ON t_teacher TO yun;

数据备份

备份表

在同一个库里面备份表：create table t_student_info_tmp select * from t_student_info;

备份表为.txt文件

注意

注意：

使用SELECT ... INTO OUTFILE时，MySQL服务器必须有写权限到指定的文件路径。
导出的文件必须不存在，因为MySQL不会覆盖现有文件。
这个语句不会在客户端生成文件，而是在MySQL服务器上生成文件

所以，在运行前，先得看一下mysql默认可以导出的文件路径在哪里
show variables like 'secure_file_priv';

也可在my.cnf的[mysqld]下更改secure_file_priv="..."的值

SELECT * INTO OUTFILE 'file_name';
SELECT *
INTO OUTFILE '/var/lib/mysql-files/t_course.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_course;

将数据导入到数据库

load data infile 文件路径

备份库

整库备份：mysqldump -u root -p mydatabase > mydatabase_backup.sql

整库导入：source mysql库文件路径

mysql主从-配置

1.修改my.cnf配置文件

在[mysqld]下加入：
log-bin=mysql-bin
server-id=101
binlog_checksum=none

2.重启mysql

systemctl restart mysqld

3.设置mysql

1.登录MySQL：mysql -u root -p

2.创建slave用户：# CREATE USER 'slave'@'%' IDENTIFIED BY 'abc123';

3.给予slave用户对所有库和所有表得读的权限

grant REPLICATION SLAVE on *.* to 'slave'@'%';

4.更改用户slave的登录密码验证方式为使用MySQL的原生密码认证插件进行身份验证，密码为abc123

alter user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

5.刷新权限：flush privileges;

6.查看master状态：show master status;

4.设置从服务器

1.修改my.cnf配置文件

在[mysqld]下加入：
server-id=101

2.登录mysql ：mysql -u root -p
关闭slave：stop slave;

3.连接主服务器：
change master to master_host='10.0.0.23',master_user='slave',master_password='abc123', master_log_file='mysql-bin.000005',master_log_pos=156;

4.启动主从复制：start slave;
5.查看状态是否连接成功：show slave status;

数据库事务的四大特性

原子性

一致性

持久性

隔离性

读写锁：lock

锁：lock table name read / write;

read：读（没用，锁了也能查询数据，因为读锁是共享锁，大家都能看）
write：写

解锁：unlock tables;

mysql查询的优化方式

分库，分区，分页

数据分区：根据数据的访问模式或业务逻辑将数据表分区，可以提高查询效率和管理灵活性。

数据分页：对于大数据量的查询，使用LIMIT来限制返回的数据量，以减少网络传输和处理时间。

连表查询

join

创建索引

创建索引：在经常作为查询条件（WHERE子句）、排序（ORDER BY子句）和分组（GROUP BY子句）的列上创建索引可以显著提高查询效率。

避免过多索引：虽然索引可以提高查询速度，但它们也会减慢写操作（INSERT、UPDATE、DELETE），并占用更多的磁盘空间。

索引维护：定期检查和维护索引，确保它们保持高效。

读写分离

连接和并发

增加最大连接数：根据需要调整max_connections的值，以确保数据库能够处理足够的并发连接。

redis

什么是穿透，雪崩

缓存穿透

缓存穿透是指查询一个不存在的数据，由于缓存中没有数据，所以这个查询请求会直接穿过缓存层，到达数据库层，从而增加了数据库的压力。这种情况通常发生在恶意攻击或者系统设计不当的情况下。

原因

1.恶意攻击：攻击者有意请求不存在的数据，以使得系统频繁访问数据库，造成拒绝服务攻击。
2.系统设计不当：系统设计时未对查询参数进行校验，导致查询了不存在的数据。

解决方案

1.参数校验：在查询缓存之前，先对请求的参数进行合法性检查，如过滤非法字符、判断参数范围等。
2.布隆过滤器：在缓存层前使用布隆过滤器，对所有可能的数据建立一个布隆过滤器，用于快速判断一个请求的数据是否存在于数据库中。
3.缓存空值：当查询的数据在数据库中不存在时，也将这个空值缓存起来，并设置一个较短的过期时间，这样可以减少后续请求对数据库的访问。
4.增强ID复杂度：避免被猜测ID规律，增加ID的复杂度和随机性。
5.做好数据的基础格式校验：确保传入的数据格式符合预期。
6.加强用户权限校验：确保只有有权限的用户才能访问数据。

缓存雪崩

缓存雪崩是指在某一时刻，缓存中大量的缓存数据同时失效或过期，导致大量的请求直接打到数据库或后端系统，从而造成数据库负载激增，甚至导致数据库宕机或系统崩溃的现象。

原因

1.大量缓存同时设置相同的过期时间：如果在某一时刻设置了大量的缓存数据，并为它们都设置了相同的过期时间，那么在未来的某一时刻，这些缓存数据都会同时失效。
2.Redis宕机：如果因为某种原因，Redis服务器突然宕机，那么所有的缓存数据都会消失，导致所有的请求都直接访问数据库。
3.误删缓存数据：如果由于某种原因，大量的缓存数据被误删除，那么也会引发缓存雪崩。

解决方案

1.设置随机过期时间：为了防止大量的缓存同时失效，可以为每个缓存设置一个随机的过期时间，这样就可以确保不会有太多的缓存同时失效。
2.使用多级缓存：可以在客户端和数据库之间设置多级缓存，如本地缓存、Redis缓存等。当Redis缓存失效时，可以先查询本地缓存，如果本地缓存也没有，再查询数据库。
3.实现缓存数据的自动刷新：通过定时任务或事件触发等方式，在缓存数据即将过期时，自动从数据库中查询最新的数据并更新到缓存中。
4.限流和熔断：在系统中加入限流和熔断机制，当数据库请求到达一定的阈值时，直接拒绝部分请求，以保护数据库不被过度访问。
5.监控和报警：建立监控系统，实时监控缓存和数据库的状态，一旦发现异常情况，立即报警并采取相应的措施。

redis安装部署

redis安装部署文档：https://www.cnblogs.com/hunanzp/p/12304622.html

1.需要的环境：yum install gcc tcl

2.上传安装包 && 解压缩：tar -zxvf name.tar.gz

3.编译，编译安装：make && make install

4.启动，cd到redis目录下，有redis.conf即可
cd redis/
redis-server redis.conf

关于redis.conf的参数

启动ip：bind

本地回环地址：127.0.0.1

所有地址：0.0.0.0

远程连接，主从备份，哨兵模式，需要

端口：port

默认端口：6379

保护模式：protected-mode

是否在后台运行：daemonize

yes

备份文件地址：logfile

登录密码：requirepass

主从备份主机（master）密码：masterauth

主从备份主机参数（ip 端口）：replicaof

主机ip 端口

redis配置主从备份

关闭防火墙：systemctl stop firewalld

开机自动关闭：systemctl disable firewalld

1.主服务器配置文件需要修改的参数

vim redis.conf

bind：0.0.0.0
port：6379
protected-mode：no
daemonize：yes
logfile：./redis.log
requirepass：abc123
masterauth：abc123

2.从服务器配置文件需要修改的参数

vim redis.conf

bind：0.0.0.0
port：6379
protected-mode：no
daemonize：yes
logfile：./redis.log
requirepass：abc123
masterauth：abc123
replicaof 192.168.169.100 6379

3.启动（重新启动）

1.在redis目录下

2.启动：redis-server redis.conf

3.登录：redis-cli

4.密码验证：auth 密码

5.查看主从配置信息：info replication

主从信息对比

主服务器（master）

# Replication
role:master
connected_slaves:2
slave0:ip=10.0.0.99,port=6379,state=online,offset=718552,lag=1
slave1:ip=10.0.0.100,port=6379,state=online,offset=718552,lag=1
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:eb25e838d821ab5552b37e4c3e59a70ed3f6cc52
master_repl_offset:718686
second_repl_offset:180448
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:156657
repl_backlog_histlen:562030

从服务器（slave）

# Replication
role:slave
master_host:10.0.0.23
master_port:6379
master_link_status:up
master_last_io_seconds_ago:1
master_sync_in_progress:0
slave_read_repl_offset:715458
slave_repl_offset:715458
slave_priority:100
slave_read_only:1
replica_announced:1
connected_slaves:0
master_failover_state:no-failover
master_replid:04004ddac58721497e67a6897469b5406072dd7b
master_replid2:0000000000000000000000000000000000000000
master_repl_offset:715458
second_repl_offset:-1
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:202094
repl_backlog_histlen:513365

redis哨兵模式配置（主从所有服务器都一样）（在redis目录下）

1.关闭防火墙：systemctl stop firewalld

开机自动关闭：systemctl disable firewalld

2.编辑配置文件

1.删除哨兵模式配置文件：rm -rf sentinel.conf

2.编辑哨兵模式配置文件：vim sentinel.conf

port 16379
# 本机端口
bind 0.0.0.0
# 本机所有ip
protected-mode yes
# 是否开启保护模式
daemonize yes
# 是否在后台运行
logfile "./sentinel.log"
# 哨兵模式日志文件路径
sentinel monitor mymaster 192.168.169.100 6379 2
# 主服务器ip redis-server运行端口
sentinel auth-pass mymaster abc123
# 主服务器redis-server的密码
sentinel down-after-milliseconds mymaster 3000
3000毫秒检查一次
sentinel resolve-hostnames yes
是否解析主机名

3.全部服务器配置完成之后，启动所有服务器redis哨兵模式（在redis目录下）

redis-sentinel sentinel.conf

4.登录

redis-cli -p 16379

5.查看哨兵模式信息

info sentinel

6.开始检测

关闭主服务器redis-server服务

查看其他服务器信息

info sentinel