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之后不一样
不要创建没有密码的账户,也不要创建用户和授权
用户的权限可以通过角色授权
- 示例: 当前用户@客户端的名字 root@localhost 5. 维护修改账户、角色- 修改账户
-
配置账户密码
mysql8新特性: 一个账户两套密码:
手动添加第二套密码,之前的密码作为老密码;执行 discard 就会抛弃老密码。
注意:过期密码可以连接,但是不能操作
-
**默认过期:**default_password_lifetime 可以作为默认过期的,另外 never 是永不过期;expire interval 30 day:30天后过期;
密码过期修改密码
示例:
登入的用户username@(mysql所在的客户端机器)选 C
dbserver2: 这个是db服务器 用户名@主机
**注意:**mysql 5.7 使用的 前两种; mysql 8 之后添加了第三种,将密码放到内存中,舍弃了第一种。
- PAM认证
不用登录、或者文本插件、或者企业级PAM
2.3.2 授权
1. 为什么授权:
2. 授权类型
3. 授权管理权限
4. 动态权限
5. 制定或者自定义权限:
usage:默认就是连接权限,创建好就有
6. 语句类的权限
7. 授权角色:
with admin option: 授权管理权限
8. 查询权限
9. 权限收回:revoke
10. mysql8 新特性:收回部分权限,但是必须打开部分收回的权限,partial_revokes=on
- 使用权限的限制
. 所有库下面的所有表
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 恢复(待补充)
- 使用权限的限制
文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。
-
- 示例: 当前用户@客户端的名字 root@localhost 5. 维护修改账户、角色- 修改账户
还没有评论,来说两句吧...