Mysql8的优化(DBA)

Mysql8的优化

  • 1、Mysql的安装优化
    • 1.1 修改配置参数（命令行、配件文件）
      • 1.1.1 命令行修改配置参数
      • 1.1.2 参数持久化
      • 1.1.3 Mysql多实例启动，以及配置密码文件
    • 1.2 查询表的相关参数，以及表空间管理
  • 2、Mysql高级优化（SQL）
    • 2.1 SQL慢查询监控
    • 2.2 performance_schema(引擎)
      • 2.2.1基本监控
    • 2.3mysql 用户管理和授权
      • 2.3.1 认证
      • 2.3.2 授权
      • 2.3.3 角色激活：
      • 2.3.3 授权表：
      • 2.3.4 创建用户授权：
    • 2.4 mysql 安全
      • 2.4.1 启用SSL连接
      • 2.4.2 操作数据库防火墙
    • 2.5 mysql 优化和监控
      • 2.5.1 数据库锁
      • 2.5.2 数据库索引优化
      • 2.5.3 数据库监控（待补充）
    • 2.5 mysql 备份和恢复
      • 2.5.1 mysqldump 和 mysqlpump
      • binlog 恢复（待补充）

1、Mysql的安装优化

1.1 修改配置参数（命令行、配件文件）

#   -----------------------修改配置参数-------------------------------------

# 配置 innodb 

show VARIABLES like  '%innodb_buffer%';

# 内存 修改
set global innodb_buffer_pool_size = 144217728;



show  DATABASES;

#  innodb_buffer_pool_instances =  2 ，innodb_buffer_pool_size必须大于1G；那样可以分为两个子池


# 连接池,max_connections默认151，mysqlx_max_connections最大默认100； 和内核参数有关

show  VARIABLES like '%max_connections%';  


show  VARIABLES like '%port%';

1.1.1 命令行修改配置参数

# 命令行、或者配置文件；部分参数不能在内存中修改；

#   -----------------------修改配置文件--------------------------------------

# -----------------------动态修改参数（部分参数：set）：重启就会恢复原样--------------------------------------

set GLOBAL max_connections = 100;
show VARIABLES like '%connections%';


show VARIABLES like '%autocommit%';

set global autocomm=off;
 
# global 必须新的会话才可以生效

1.1.2 参数持久化

# -------------------------如何设置参数持久化：，mysql8的特性------------------


# 持久化到数据库的目录下：mysqld-auto.conf

set persist max_connections = 101;

show VARIABLES like '%connections%';
show   databases;

# 变量表
use `performance_schema`;


select  * from  performance_schema.variables_info where variable_name = 'max_connections';


set  persist max_connections= default;

show VARIABLES like '%connections%';

1.1.3 Mysql多实例启动，以及配置密码文件

# -----------------------配置客户端启动命令：可以在mysqld文件中

# 密码放到一个加密文件：    mysql_config_editor set --login-path =用户名 --user=root --PASSWORD


#客户端可以配置提示符


#  mysql 实例初始化  mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data1

mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data1
mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data2
mysqld  --no-defaults --initialize-insecure --user=mysql  --data = /mysql/data3


#  mysql 启动实例；连接远程tcp ，本地 udp

1.2 查询表的相关参数，以及表空间管理

# 查询表的相关参数
show  variables like '%table%';

# 创建表空间
create TABLESPACE general add datafile 'general.ibd';

create  table  t_demo(b int) TABLespace = general;


insert into t_demo  values(1);
insert into t_demo  values(2);
insert into t_demo  values(3);

select  * from t_demo;
#  查询表对应的表空间 ：CREATE TABLE `t_demo` ( `b` int DEFAULT NULL) /*!50100 TABLESPACE `general` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show  create table  t_demo;


# 外部文件通用表空间

show   VARIABLES like '%innodb_direct%';

#  删除表空间

drop  TABLESPACE  general;

2、Mysql高级优化（SQL）

2.1 SQL慢查询监控

#设置慢查询的开启状态，默认是关闭状态
show   VARIABLES like "%slow%";
# 开启慢查询功能
set  GLOBAL slow_query_log = "ON";
# 查询慢查询的输出：默认是file
show   VARIABLES like "%log_output%";
# 设置慢查询记录到表中
set  GLOBAL log_output = "TABLE"
# 查询慢查询记录
select  * from mysql.slow_log;
# #设置慢查询的监控时间，默认是10秒
show   VARIABLES like "%long%";
#设置成0.5秒，注意这个重新开启会话才可以生效
set  GLOBAL long_query_time=0.5;

2.2 performance_schema(引擎)

MySQL的performance_schema是一个系统数据库，专门设计用于收集和展示服务器性能相关的数据。它的主要作用是：

 1. **资源监控：** performance_schema可以实时监控MySQL服务器内部的各种操作，包括但不限于内存、CPU、线程、文件系统I/O、表锁、阶段（stages）和事件（events）等资源的使用情况。
 2.  **性能分析：** 通过该库提供的视图，用户可以获取到详细的执行计划、查询时间、等待事件统计等信息，帮助开发人员和DBA分析应用程序或SQL语句的性能瓶颈。
 3.  **深度跟踪：** 它能够提供低级别的SQL执行细节，例如记录每个查询在MySQL内部各个组件中的执行时间和消耗资源情况，有助于进行更深入的诊断和优化工作。
 4.  **安全性监控：** 可以用来监视和控制诸如连接数、线程状态、账户活动等与安全性和稳定性相关的信息。
 5. **自定义监控：** 用户可以根据需要启用或禁用特定的监控点（instrument），并配置采样频率和其他参数，以便对特定的关注点进行定制化的性能监控。
总之，performance_schema为MySQL提供了丰富的内部运行时信息，是进行数据库性能调优的重要工具之一。通过它，DBA和开发者可以更好地理解数据库的工作方式，发现潜在问题，并采取相应措施提高系统的整体性能和效率。

2.2.1基本监控

#  ----------------------------------------------------`performance_schema`-------------------

show VARIABLES like  "performance_schema";

# `performance_schema`就是一个引擎

show  ENGINE `performance_schema` status;

  
use `performance_schema`;

# 查下看配置表

show TABLES like '%setup%';

# 查看自定义配置

select * from setup_consumers;

# 查看链接状态
select * from setup_actors;


# 查看每行的指标
select * from setup_instruments;


select * from setup_instruments  where  name like  "wait/io/table%";


# 查看I O的表
select  * from table_io_waits_summary_by_table;

#  记录每一张表的IO
select  * from table_io_waits_summary_by_table where object_schema="test_db" and  object_name ="t_demo";

2.3mysql 用户管理和授权

2.3.1 认证

在这里插入图片描述

1. username@localhost  等价于  ‘username’@‘localhost’  可以有单引号也可以没有
2. 主机名hostmane

在这里插入图片描述
3. 创建账户 5.7 和8之后不一样
在这里插入图片描述

不要创建没有密码的账户，也不要创建用户和授权

  1. 角色权限
    在这里插入图片描述

用户的权限可以通过角色授权

  • 示例： 当前用户@客户端的名字 root@localhost 在这里插入图片描述5. 维护修改账户、角色- 修改账户在这里插入图片描述

  1. 配置账户密码
    在这里插入图片描述

  2. mysql8新特性： 一个账户两套密码：
    在这里插入图片描述

手动添加第二套密码，之前的密码作为老密码；执行 discard 就会抛弃老密码。

  1. 密码过期：手动过期
    在这里插入图片描述

注意：过期密码可以连接，但是不能操作

  • **默认过期：**default_password_lifetime 可以作为默认过期的，另外 never 是永不过期；expire interval 30 day：30天后过期；
    在这里插入图片描述

  • 密码过期修改密码
    在这里插入图片描述

  • 示例：
    在这里插入图片描述

登入的用户username@（mysql所在的客户端机器）选 C
dbserver2： 这个是db服务器 用户名@主机

  1. mysql 认证插件
    在这里插入图片描述

**注意：**mysql 5.7 使用的 前两种； mysql 8 之后添加了第三种，将密码放到内存中，舍弃了第一种。

  1. 几种连接认证方式：客户端连接数据库
    在这里插入图片描述
    在这里插入图片描述
  • PAM认证
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

不用登录、或者文本插件、或者企业级PAM

2.3.2 授权

1. 为什么授权：
在这里插入图片描述

2. 授权类型
在这里插入图片描述
在这里插入图片描述

3. 授权管理权限
在这里插入图片描述

4. 动态权限
在这里插入图片描述

5. 制定或者自定义权限：
在这里插入图片描述

usage：默认就是连接权限，创建好就有

6. 语句类的权限
在这里插入图片描述
在这里插入图片描述

7. 授权角色：
在这里插入图片描述

with admin option: 授权管理权限
8. 查询权限
在这里插入图片描述
在这里插入图片描述

9. 权限收回：revoke
在这里插入图片描述在这里插入图片描述
10. mysql8 新特性：收回部分权限，但是必须打开部分收回的权限，partial_revokes=on
在这里插入图片描述

  • 使用权限的限制
    在这里插入图片描述

. 所有库下面的所有表

  1. 示例：
    在这里插入图片描述

a :锁定 不能登录 ；b：过期可以登录； c：删除不可以登录；usage：可以登录

2.3.3 角色激活：

1. 几种类型:服务器、用户、会话层面
在这里插入图片描述
2. 设置激活角色：
在这里插入图片描述

3. 设置默认角色：
在这里插入图片描述

4. 会话级别：
在这里插入图片描述

5. 强制持久化
在这里插入图片描述

2.3.3 授权表：

1. 权限类型：
在这里插入图片描述
2. 用户表授权
在这里插入图片描述
在这里插入图片描述
3. 修改权限
在这里插入图片描述

注意：ddl 不需要刷新的： dml；在内存中需要刷新flush；不是所有的都需要刷新。
DDL和DML的区别：
DDL (Data Definition Language) 和 DML (Data Manipulation Language) 是 SQL 中两种不同类型的语句，它们在数据库操作中有明确的区别：
DDL (数据定义语言)：
作用：用于创建、修改和删除数据库中的结构或模式对象。
功能：包括创建表（CREATE TABLE）、修改表结构（ALTER TABLE）、删除表（DROP TABLE）、创建索引（CREATE INDEX）、删除索引（DROP INDEX）以及创建视图（CREATE VIEW）、删除视图（DROP VIEW）等与数据库架构相关的操作。
特点：DDL 语句通常是自动提交的，并且一旦执行，对数据库结构的改变立即生效，无法通过事务回滚来撤销。
DML (数据操作语言)：
作用：用于插入、更新、删除数据库表中的实际数据行，以及查询这些数据。
功能：主要包括插入数据（INSERT INTO）、更新数据（UPDATE）、删除数据（DELETE FROM）以及从表中选择数据（SELECT）的操作。
特点：DML 语句通常是在事务内执行的，可以控制事务的开始、提交和回滚。这意味着执行一系列 DML 操作后可以选择是否将更改永久保存到数据库中。
总结来说，DDL 主要关注数据库的物理结构设计，而 DML 则是针对已存在结构中的数据进行具体的操作和管理。

2.3.4 创建用户授权：

#  创建用户
create  user  jan@localhost IDENTIFIED by 'jan123';

#创建两个权限
create role r_mgr,r_emp;

# 把表的查询权限授权给角色
grant select  on test_db.t_demo to  r_emp;

# 把表的ddm权限付给角色
grant  insert,update,delete on test_db.* to r_emp;

# 把表的ddm权限付给角色
grant  insert,update,delete on test_db.* to r_mgr;


# 授权角色给用户
grant r_emp,r_mgr to jan@localhost;

# 查看用户
select  * from mysql.`user`;

# 查看角色
select  * from  mysql.role_edges;

# 授权：把表的查询权限授给用户
grant select  on test_db.t_demo to jan@localhost;
#  查看当前用户角色
select  current_role();
#  查看授权
show grants;

# 把角色授权给所有用户 
set role all;

# 查看当前用户
select  current_user();

select  current_role();

show grants;

2.4 mysql 安全

2.4.1 启用SSL连接

# SSL 默认是开启的，可以关闭 skip_ssl ;本地tcp和ip

show  VARIABLES like  "%have_ssl%";

# --ssl-modle = disable 加这个参数可以不使用ssl


show  global VARIABLES like  "%tls%";


show  session VARIABLES like  "%ssl%";
#  证书连接  --ssl-cert =?  --ssl-key=? ssl_type = X509
show  create table test_db.t_demo;

2.4.2 操作数据库防火墙

show  create  table  mall.cms_help;



SHOW   global status like "%firewall%";

2.5 mysql 优化和监控

2.5.1 数据库锁

#  排他锁；

prompt 1;

# 设置锁
SET  innodb_lock_wait_timeout = 3600;

begin;

# 启动事务
start transaction;
# 自动提交
show  variables like "%autocommit%";


select * from mall.cms_help for update;


show processlist;

use  sys;

select  * from sys.innodb_lock_waits;


show  create table sys.innodb_lock_waits;

# 阻塞，KILL 到pid

2.5.2 数据库索引优化

select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;

# 创建索引

create index idx_price on pms_product_full_reduction(full_price);

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100;



select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

# 创建复合索引  ,单个字段不要在多个索引中出现，容易出现死锁问题

create index idx_price_2 on pms_product_full_reduction(full_price,reduce_price);

explain select  id,product_id,full_price,reduce_price from mall.pms_product_full_reduction where full_price >100 and reduce_price >100;

# 查询索引
show  index FROM mall.pms_product_full_reduction

# 删除索引
drop index idx_price_3 on mall.pms_product_full_reduction;



show  tables;

# 设计索引一定要考虑组合索引；需要尝试和调试；调试完成后删除多余的索引
create index idx_price_3 on pms_product_full_reduction(full_price,reduce_price);


#  mysql  查询分析器

END;

2.5.3 数据库监控（待补充）

2.5 mysql 备份和恢复

2.5.1 mysqldump 和 mysqlpump

#   secure_file_priv
show  variables  like "%secure%";

# mysqldump
# mysqlpump
# binlog  日志恢复数据库

binlog 恢复（待补充）

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/448095.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

编译支持国密的抓包工具 WireShark

编译支持国密的抓包工具 WireShark

目录 前言WireShark支持国密的 WireShark小结前言 在上一篇文章支持国密的 Web 服务器中，我们搭建了支持国密的 Web 服务器，但是，我们使用 360 安全浏览器去访问，却出现了错误： 是我们的 Web 服务器没有配置好？在这里插入图片描述还是 360 安全浏览器不支持国密？还是两…
阅读更多...
SSL证书：构建网络安全的基石

SSL证书：构建网络安全的基石

🤍 前端开发工程师、技术日更博主、已过CET6 🍨 阿珊和她的猫_CSDN博客专家、23年度博客之星前端领域TOP1 🕠 牛客高级专题作者、打造专栏《前端面试必备》 、《2024面试高频手撕题》 🍚 蓝桥云课签约作者、上架课程《Vue.js 和 E…
阅读更多...
Linux学习——线程的控制

Linux学习——线程的控制

目录 ​编辑 一，线程的创建 二，线程的退出 1，在子线程内return 2,使用pthread_exit(void*) 三，线程等待 四，线程获取自己的id值 五，线程取消 六，线程分离 一，线程的创建 在对…
阅读更多...
全面的 DevSecOps 指南：有效保护 CI/CD 管道的关键注意事项

全面的 DevSecOps 指南：有效保护 CI/CD 管道的关键注意事项

数字化转型时代带来了对更快、更高效、更安全的软件开发流程的需求。DevSecOps：一种将安全实践集成到 DevOps 流程中的理念，旨在将安全性嵌入到开发生命周期的每个阶段 - 从代码编写到生产中的应用程序部署。DevSecOps 的结合可以带来许多好处&#xff0…
阅读更多...
抖音视频提取gif怎么做？分分钟帮你生成gif

抖音视频提取gif怎么做？分分钟帮你生成gif

通过将视频转换成gif动图的方式能够方便的在各种平台上分享、传播。相较于视频文件，gif动图的体积更小，传播起来更方便，能够吸引大众的注意力。下面，就来给大家分享一个gif图片制作（https://www.gif.cn/）的…
阅读更多...
mybatisplus的条件构造器

mybatisplus的条件构造器

条件构造器wrapper，主要用于构造sql语句的where条件，他更擅长这个，但也可以用于构造其他类型的条件，比如order by、group by等。 条件构造器的使用经验： 基于QueryWrapper的查询 练习1. void testQueryWrapper(){Q…
阅读更多...
服务器集群 -- nginx配置tcp负载均衡

服务器集群 -- nginx配置tcp负载均衡

当面临高流量、高可用性、水平扩展、会话保持或跨地域流量分发等需求时，单台服务器受限于硬件资源、性能有限不能满足应用场景的并发需求量时，引入负载均衡器部署多个服务器共同处理客户端的并发请求，可以帮助优化系统架构，提高系…
阅读更多...
猫咪挑食怎么治？排行榜靠前适口性好的主食冻干推荐

猫咪挑食怎么治？排行榜靠前适口性好的主食冻干推荐

在如今，养猫人士几乎都将自己的小猫咪视作珍宝，宠溺有加。但宠爱过度有时也会导致猫咪养成挑食的坏习惯。猫咪挑食怎么治呢？今天，我要分享一个既能让猫咪不受苦，又能纠正挑食问题的方法。 一、为什么猫会挑食呢&#x…
阅读更多...
Linux调试器--gdb的介绍以及使用

Linux调试器--gdb的介绍以及使用

文章目录 1.前言 ✒️2.介绍gdb✒️3.Debug模式和Release模式的区别✒️4.如何使用gdb✒️1️⃣.在debug模式下编译2️⃣.进入调试3️⃣ .调试命令集合⭐️⭐️ 1.前言 ✒️ 🕗在我们之前的学习中已经学会了使用vim编译器编写c/c代码，但是对于一个程序员…
阅读更多...
ThreadLocal出现内存泄露原因分析

ThreadLocal出现内存泄露原因分析

ThreadLocal 导致内存泄漏的主要原因是它的工作方式。在 Java 中，ThreadLocal 通过维护一个以 Thread 为键，以用户设置的值为值的映射来工作。每个线程都拥有其自身的线程局部变量副本，不同线程间的这些变量互不干扰。这个映射是存储在每个 T…
阅读更多...
sql-mysql可视化工具Workbench导入sql文件

sql-mysql可视化工具Workbench导入sql文件

mysql可视化工具Workbench导入sql文件 1、打开workbench2、导入sql文件3、第一行加上库名4、开始运行 1、打开workbench 2、导入sql文件 3、第一行加上库名 4、开始运行
阅读更多...
Java学习笔记------拼图游戏

Java学习笔记------拼图游戏

图形化界面GUI GUI：Graphical User Interface（图像用户接口），指采用图形化的方式显示操作界面 两套体系：AWT包中和Swing包中 组件 JFrame：最外层的窗体 JMenuBar：最上层菜单 JLaber&#…
阅读更多...
微信小程序开发系列（二十四）·wxml语法·列表渲染·wx:for-item 和 wx:for-index

微信小程序开发系列（二十四）·wxml语法·列表渲染·wx:for-item 和 wx:for-index

目录 1. 如果需要对默认的变量名和下标进行修改，可以使用wx:for-item 和 wx:for-index 2. 将 wx:for 用在 标签上，以渲染一个包含多个节点的结构块 方法一 方法二 3. 总结 3.1 wx:for-item 和 wx:for-index总结 3.2 总结 1. 如果需要对默…
阅读更多...
从mysql 数据库表导入数据到elasticSearch的几种方式

从mysql 数据库表导入数据到elasticSearch的几种方式

从MySQL数据库导入数据到Elasticsearch有几种方式，主要包括以下几种： 1. 使用Logstash： Logstash是一个开源的数据收集引擎，可以用来从不同的数据源导入数据到Elasticsearch。它具有强大的数据处理能力和插件生态系统&…
阅读更多...
xlsx.js读取本地文件,按行转成数组数据

xlsx.js读取本地文件,按行转成数组数据

1.下包 //1. npm install xlsx //2. yarn add xlsx2.结构 <template><input type"file" change"onFileChange" /> </template>3.代码 <script> import * as XLSX from xlsxexport default {methods: {onFileChange (event) {/…
阅读更多...
【“双碳”目标】Acrel-2000Z分布式光伏发电监测系统解决方案

【“双碳”目标】Acrel-2000Z分布式光伏发电监测系统解决方案

1 概述 “十四五”期间&#xff0c;随着“双碳”目标提出及逐步落实&#xff0c;本就呈现出较好发展势头的分布式光伏发展有望大幅提速。就“十四五”光伏发展规划&#xff0c;国家发改委能源研究所可再生能源发展中心副主任陶冶表示&#xff0c;“双碳”目标意味着国家产业结…
阅读更多...
【JS逆向学习】猿人学 第五题 js混淆 乱码

【JS逆向学习】猿人学 第五题 js混淆 乱码

逆向目标 网址&#xff1a;https://match.yuanrenxue.cn/match/5接口&#xff1a;https://match.yuanrenxue.cn/api/match/5?page2&m1709806560791&f1709806560000参数&#xff1a; Cookie(m、RM4hZBv0dDon443M)payload(m、f) 逆向过程 老规矩&#xff0c;上来先分…
阅读更多...
Java后端八股文之Redis

Java后端八股文之Redis

文章目录 1. Redis是什么&#xff1f;2. Redis为什么这么快&#xff1f;3. 为什么要使用缓存&#xff1f;4. Redis几种使用场景&#xff1a;5. Redis的Zset底层为什么要使用跳表而不是平衡树、红黑树或者B树&#xff1f;6.Redis持久化6.1 什么是RDB持久化6.1.1RDB创建快照会阻塞…
阅读更多...
修改Android打包apk的名字和目录

修改Android打包apk的名字和目录

app打包生成apk后通常需要进行备份&#xff0c;但是要区分好哪个apk是什么版本的、什么时候打包的&#xff0c;以方便以后区分使用。 最开始的想法是把版本号、创建时间这些加在apk文件名上即可&#xff0c;但是公司要求apk使用一个固定的名称&#xff0c;那我怎么保存版本号信…
阅读更多...
OpenGL学习——19.模板测试

OpenGL学习——19.模板测试

前情提要&#xff1a;本文代码源自Github上的学习文档“LearnOpenGL”&#xff0c;我仅在源码的基础上加上中文注释。本文章不以该学习文档做任何商业盈利活动&#xff0c;一切著作权归原作者所有&#xff0c;本文仅供学习交流&#xff0c;如有侵权&#xff0c;请联系我删除。L…
阅读更多...
最新文章

Copyright @ 2022~2023