MYSQL 数据库配置优化

最新推荐文章于 2024-03-22 13:54:33 发布
最新推荐文章于 2024-03-22 13:54:33 发布
阅读量8.6k 收藏 28
点赞数 2
分类专栏： JAVA 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33923684/article/details/124191541
版权

1、skip_ssl

开启HTTPS后会出现内存不足，那是因为，在开启HTTPS访问时会在Apache中新开了一个开放了443端口的虛拟机。为了性能，通常我们会禁用SSL，添加如下参数skip_ssl;

  • 查看是否开启SSL： SHOW VARIABLES LIKE ‘%ssl%’; (have openssl表示是否支持SSL）
  • 修改配置文件my.cnf，加入以下内容：
    # disable_ssl
    skip_ssl
    重启MySQL: service mysqld restart

2、max_allowed_packet = 1024M

即最大允许传输包的大小，也就是查询出结果后，把结果发送到客户端时，每个网络包的最大大小。

  1. 默认为16M(16777216字节），最大为1G (1073741824字节）
  2. 修改时需要设为1024的整数倍
  3. 客户媏和服务端都有这个参数，最好保持一致

设置依据：此参数要根据业务实际需求设定

  1. 是否存在大字段(b1ob、text等），若存在需要适当调大
  2. 内存大小设置，innodb_buffer_pool是否足名大

影响范围：

  • 设置太大，会使用更多内存，很容易导致丟包，无法捕提异常大事物包SQL
  • 设置太小，可能会导致程序报错，备份失败，也会导致频繁的收发网络报，影响系统性能
  • 修改：一般32M足够
  • Mysql1] >set global max_allowed_packet =33554432;
  • 修改配置文件：
    [mysqld]
    max_allowed_packet=32M

3、skip-name-resolve

它将禁止 MysqL Server 对外部连接进行 DNS 解析，使用这一选项可以消除 MySqL 进行 DNS 解析的时间。
但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MysqL将无法正常处理连接请求。
如果开启 skip-name-resolve 选项，要确认 MysqL 是否采用过主机名的授权
参考文献：https://blog.csdn.net/u010584271/article/details/80835378

4、max_connections=3000

是指整个mysql服务器的最大连接数，值默认为151；
查询最大连接数：show variables like “max_connections$”;

5、max_user_connections

是指每个数据库用户的最大连接数，比如：虚拟主机可以用这个参数控制每个电拟主机用户的数据库最大连接数：（历史最大连接数）；

  • 命令：show global status like ‘Max_used_connections’;
  • max_used_connections / max_connections * 100% （理想值≈ 85%）

如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

  • 命令：show status like ‘Threads%’;
    在这里插入图片描述
  • Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
  • Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
  • Threads_created :代表从最近一次服务启动，已创建线程的数量。
  • Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。
  • Threads_connected 跟show processlist结果相同，表示当前连接数（当前最大连接数）。准确的来说，Threads_running是代表当前并发数（当前并发数）
  • 最好将thread_cache_size设置成与threads_connected一样。不过很少有情况将threads_cache_size设置成比200还大的数。

6、default-storage-engine=InnoDB

设置数据库引擎为InnoDB，mysql支持MyISAM、InnoDB、Memory

  1. MyISAM
  • 特性：①不支持事务。②表级锁定，并发性能大大降低。③读写互相阻塞。
  • 适用场景：①不支持事务。②并发相对较低，表锁定。③执行大量select语句操作的表。④count(*)操作较快。⑤不支持外键。
  • 注：查询速度快的原因：a.MyISAM存储的直接是文件的offset。b.不用维护mvcc。
  1. InnoDB
  • 特征：①良好的事务支持：支持事务隔离的四个级别。②行级锁定：使用间隙锁？？？？？？③外键约束。④支持丢失数据的自动恢复。
  1. Memory，
  • 在内存中，默认使用hash索引，等值条件查找快速快，范围查找慢，断电后数据丢失，但表结构存在

7、thread_cache_size = 16

用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

8、read_buffer_size = 32M

  • 是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。
  • read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。
  • 如下是对于16g内存的设置， read_buffer_size = 1M

9、sort_buffer_size = 16M

  • 是一个connection级参数，MySQL5.7，默认值是1048576字节，也就是1MB，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存；
  • Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源
  • 查看命令：show variables like ‘%sort_buffer_size%’;
  • 设置命令：SET GLOBAL sort_buffer_size = 1024*1024;

10、innodb_buffer_pool_size

  • 定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小；
  1. 数据缓存，InnoDB数据页面；
  2. 索引缓存，索引数据；
  3. 缓冲数据，脏页（在内存中修改尚未刷新写入到磁盘的数据）
  4. 内部结构，如自适应哈希索引，行锁等。

11、wait_timeout = 100

  • 服务器关闭非交互连接之前等待活动的秒数
  • 查看命令： show variables like ‘%timeout%’;(以秒为单位)
  • 设置命令： set global wait_timeout = 10;

12、interactive_timeout

  • 服务器关闭交互式连接前等待活动的秒数
  • 设置命令： set global interactive_timeout=28800;
  • 两者生效取决于：客户端是交互或者非交互的连接。
  • 在交互模式下，interactive_timeout才生效；非交互模式下，wait_timeout生效。

13、read_buffer_size

  • 是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

14、read_rnd_buffer_size

  • 这个参数用在sort查询之后 ，以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句，增长这值能够提升性能
  • read_rnd_buffer_size是很重要的参数，尤其工作在如下场景：
  1. sort_buffer中存的是行指针而不是要查询的数据。
  2. 查询的字段中包含Blob/Text字段。
  3. sort后有大量的数据行（limit 10并不能帮助你，因为MySQL是通过指针获取行数据的）

15、slow_query_log

打开慢查询，默认为关闭OFF

16、long_query_time = 1

定义慢查询时间，执行时间超过1s的sql才会记录到slow log

17、log_output = FILE

定义日志(general_log and slow_log)存放方式，FILE/TABLE/NONE

18、slow_query_log_file = D:\mysql\data\slow.log

慢日志存放的文件

19、log_queries_not_using_indexes = OFF

5.6.5版本之后新增，记录任何不使用索引的sql

20、log_throttle_queries_not_using_indexes = 0

5.6.5版本之后新增，用来限制相同sql每分钟写slow log的频率，默认0，不限制

21、log_slow_admin_statements = OFF

5.6.11版本后新增，记录执行慢的管理操作，如ALTER/CREATE INDEX/ANALYZE TABLE

22、min_examined_row_limit = 0

sql返回值小于设置的值则不记录到slow log，可设置为100

23、log_slow_slave_statements = OFF

在从服务器开启慢查询日志

24、datadir=/mnt/srm/mysql5.7.35/data

查看命令：show variables like ‘datadir’;

25、socket=/mnt/srm/mysql5.7.35/mysql.sock

  • mysql.sock是mysql的主机和客户机在同一host上的时候，使用unix domain socket做为通讯协议的载体，它比tcp快
  • 查看命令：show variables like ‘socket’
  • 参考文献：https://www.jianshu.com/p/d6c1e3458ca9

26、character-set-server=utf8

27、collation-server=utf8_general_ci

28、symbolic-links=0

  • 禁止mysql使用软连接，在mysqld 段落中配置symbolic-links=0，5.6及以上版本应该配置为skip_symbolic_links=yes，并重启mysql服务。
  • 一般在生产环境中，数据库data文件所在分区和系统分区是分开的，而使用软连接的情况下多数是保存在系统分区。而针对数据库加固的重要思想就是将系统分区和数据分区分开，防止数据库写入数据影响系统性能，也防止系统用户修改数据库数据
  • 当使用软连接，尤其是用root运行mysqld时，攻击者将可以通过数据库获得root权限，又由于数据同时存储在系统分区，将导致攻击者可以修改系统分区的任意文件；当使用mysql命令修改软连接对应的数据库，会修改存储在系统分区的实际文件，可能对系统内的其他文件进行删除和重命名。
  • 参考文献：https://zhuanlan.zhihu.com/p/51570114

29、redo log

  • 重写日志, redo log是一种基于磁盘的数据结构，用来在MySQL宕机情况下将不完整的事务执行数据纠正，redo日志记录事务执行后的状态。
  • 当事务开始后，redo log就开始产生，并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息，我们都知道数据库的更新操作会在内存中先执行，最后刷入磁盘。
  • redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。

30、undo log

  • 回滚日志, undo log主要用来回滚到某一个版本，是一种逻辑日志。undo log记录的是修改之前的数据，比如：当delete一条记录时，undolog中会记录一条对应的insert记录，从而保证能恢复到数据修改之前。在执行事务回滚的时候，就可以通过undo log中的记录内容并以此进行回滚。
  • undo log还可以提供多版本并发控制下的读取（MVCC）。

31、bin log

  • MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。简单来讲，就是当你的一条sql操作对数据库中的内容进行了更新，就会增加一条bin log日志。查询操作不会记录到bin log中。bin log最大的用处就是进行主从复制，以及数据库的恢复。
  • 查询命令：show VARIABLES like ‘%log_bin%’
  • 开启binlog的方式如下：
    log-bin=mysql-bin
    server-id=1
    binlog_format=ROW
  • 其中log-bin指定日志文件的名称，默认会放到数据库目录下；
    binlog_cache_size=32768
    binlog_checksum=NONE
    binlog_row_image=MINIMAL
    binlog_order_commits=ON
    binlog_rows_query_log_events=OFF
    binlog_stmt_cache_size=32768
    binlog_format = ROW

32、error log

  • 查看命令：show variables like “%log_error%”
  • error log主要记录MySQL在启动、关闭或者运行过程中的错误信息，在MySQL的配置文件my.cnf中，可以通过log-error=/var/log/mysqld.log 执行mysql错误日志的位置。

33、slow query log

  • 查看命令：show variables like “%slow_query%”;
  • 慢查询日志用来记录执行时间超过指定阈值的SQL语句，慢查询日志往往用于优化生产环境的SQL语句。
  • slow_query_log=1 #是否开启慢查询日志，0关闭，1开启
  • slow_query_log_file=/usr/local/mysql/mysql-8.0.20/data/slow-log.log #慢查询日志地址（5.6及以上版本）
  • long_query_time=1 #慢查询日志阈值，指超过阈值时间的SQL会被记录
  • log_queries_not_using_indexes #表示未走索引的SQL也会被记录

34、general log

  • general log 记录了客户端连接信息以及执行的SQL语句信息，通过MySQL的命令
  • 查询命令：show variables like ‘%general_log%’
  • general log 可通过配置文件启动，配置参数如下：
    general_log = on
    general_log_file = /usr/local/mysql/mysql-8.0.20/data/hecs-78422.log
  • 普通查询日志会记录增删改查的信息，因此一般是关闭的

35、pid-file=/mnt/srm/mysql5.7.35/mysqld/mysqld.pid

  • mysqlmysql pid 文件记录的是当前mysql进程的pid， pid亦即Process ID。
  • 未指定pid文件时， pid文件默认名为主机名.pid，存放的路径在默认mysql的数据目录。
  • 通过mysqld_safe启动mysql时，mysqld_safe会检查pid文件，如果pid文件不存在，不做处理；如果文件已存在，且pid已占用则报错；如果文件存在且pid未占用，则删除pid文件。
  • mysqlid启动后会通过create_pid_file函数新建pid文件，通过getpid（）获取当前进程pid并将pid写入pid文件。
  • 因此，通过mysqld_safe启动时，mysql pid文件的作用是防止同一个数据库被启动多次

36、expire_logs_days = 0

  • 主要用来控制binlog日志文件保留时间， 二进制日志字段删除的天数。默认值为0，表示没有自动删除
丶一派胡言丶
关注
  • 2
    点赞
  • 踩
  • 28
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql5.7 skip ssl_MySQL 5.7 的SSL加密方法
weixin_34364239的博客
02-05 2466
MySQL 5.7 的SSL加密方法MySQL 5.7.6或以上版本(1)创建证书开启SSL验证--安装opensslyum install -y opensslopenssl versionOpenSSL 1.0.1e-fips 11 Feb 2013--安装证书/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/my...
MySQL数据库优化技术之配置技巧总结
09-10
主要介绍了MySQL数据库优化技术之配置技巧,较为详细的总结分析了MySQL进行硬件级软件优化的相关方法与注意事项,需要的朋友可以参考下
MYSQL数据库优化秘籍
03-22
MYSQL数据库优化秘籍，大牛出的，值得你反复研读 MySQL在Linux环境下的安装 文件引擎MyISAM与InnoDB比较 LOAD DATA INFILE/mysqldump DBA的分析命令 MySQL的系统配置参数、诊断操作系统的状态 MySQL的分库分表，分区 MySQL的索引 SQL语句优化的分析方法 MySQL的主从复制镀锡分类 总结
关于 连接mysql报错“ssl connection error：unknow error number” 的解决方法
长沙创微智能科技官方博客
03-21 1万+
在公司2台电脑做了测试没有问题，但是现场连接发现问题ssl问题，而公司的服务器也是开启了ssl没有问题，目标机ssl也是yes但是有问题，报错”ssl connection error：unknow error number“。
MySQL系统参数配置实战：生产环境优化
知行同学
03-22 554
在生产环境中，应当充分理解参数的意义，结合业务特征和硬件条件进行精细化调整，并配合持续的监控与调优，才能使数据库真正发挥其潜能，为业务提供强有力的支持。本文旨在深入探讨MySQL的核心系统参数，并提供一份面向生产环境的配置建议，以帮助运维人员更好地优化数据库性能，提高业务系统的响应速度与可靠性。配置变更后务必进行严格的回归测试，确保不影响现有业务，并周期性回顾参数配置，适应业务增长与变化。在追求高性能的同时，确保数据库的稳定性与数据安全性，例如合理设置事务隔离级别和日志刷新策略。
mysql 客户端SSL错误2026 (HY000)
热门推荐
tmaczt的博客
05-10 2万+
背景：客户端连接mysql8.x出现“ERROR 2026 (HY000): SSL connection error: unknown error number” mysql -h 10.233.117.225 -P3306 -uroot -p 方案一（已过时）： mysql -h10.233.117.225 -P3306 -uroot -p --skip-ssl 方案二（推荐）： mysql -h10.233.117.225 -P3306 -uroot -p --ssl-mode=DISABLED
MySQL 开启SSL无效问题
baidu_16250655的博客
08-02 1269
MySQL开启SSL失败
MySql数据库配置优化 .pdf
11-20
MySql数据库配置优化 .pdfMySql数据库配置优化 .pdf
Mysql 优化的几种方法
兴趣是最好的老师，勤能补拙
05-15 5444
MySQL优化是指通过调整MySQL的各项参数和配置，以及优化SQL语句的编写和执行，提高MySQL系统的性能和可用性。MySQL优化的主要目标是尽可能快地响应用户请求，提高系统的处理能力和并发性能，减少资源消耗和硬件成本。MySQL优化涉及多个方面，包括：1.硬件配置：包括CPU、内存、磁盘等硬件资源的选择和配置，以满足系统的负载和性能需求。2.软件配置：包括MySQL的参数设置和调整，如缓存大小、连接数、并发处理数等，以适应不同的负载和访问模式。
【MySQL调优】如何进行MySQL调优？一篇文章就够了！
vincewm的博客
06-06 9138
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
mysql 5.7开启ssl
waplys的博客
04-03 2030
文章目录环境操作验证服务端是否默认开启ssl创建必须使用ssl登录的用户系统用户表user与ssl修改用户不需要ssl修改用户需要ssl 环境 CentOS6.6 X86_64 操作 使用脚本工具生成密钥文件 bin/mysql_ssl_rsa_setup 执行完成后,data目录下生成密钥文件 -rw-r--r--. 1 root root 451 11月 28 12:26 pub...
MySQL数据库配置优化
weixin_57435957的博客
11-23 5268
MySQL参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系， 优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。 下面列出了对性能优化影响较大 的主要变量，主要分为连接请求的变量和缓冲区变量 连接请求的变量： max_connections MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适
简单实现MySQL服务器的优化配置方法
12-15
公司网站访问量越来越大，MySQL自然成为瓶颈，因此最近我一直在研究 MySQL 的优化，第一步自然想到的是 MySQL 系统参数的优化，作为一个访问量很大的网站（日20万人次以上）的数据库系统，不可能指望 MySQL 默认的系统参数能够让 MySQL运行得非常顺畅。以下的文章主要介绍的是对MySQL服务器的优化配置的时机操作步骤，同时本文也介绍了MySQL服务器的优化配置的实际应用代码，如果你对其相关的实际应用感兴趣的话，你就可以点击以下的文章对其进行了解。 你能用这个命令得到MySQL服务器缺省缓冲区大小： shell> MySQL(和PHP搭配之最佳组合)d –help 这个命令生成一张
MySQL优化之配置文件
12-14
MySQL优化之配置文件 文章目录MySQL优化之配置文件前言优化配置如下 前言 通常默认的 my.cnf 配置文件无法发挥出 MySQL 最高的性能，所以需要根据不同的硬件进行优化，配置文件的优化也是重点，下面是物理内存为 32G 的数据库优化参数，具体从全局、二进制日志、主从、innodb、myisam 几个方面优化，仅供参考。 优化配置如下 1. default-time-zone=+8:00 #默认 mysql 使用的是系统时区，修改为北京时间，也就是所说的东八区。 2. interactive_timeout = 120 #服务器关闭交互式连接前等待活动的秒数。 3. wait_
mysql 之通过配置文件链接数据库
12-25
mysql 之通过配置文件链接数据库 配置文件jdbc.properties ##MySQL driver=com.mysql.jdbc.Driver url=jdbc\:mysql\:///ake?useUnicode\=true&characterEncoding\=UTF-8 username=root password=1234 ##Oracle #driver=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@127.0.0.1:1521:orcl #username=scott #password=tiger 简单的讲
JDBC：Java连接MySQL数据库（优化版）
12-14
Java连接MySQL数据库（优化版） 目的： 当需要把应用程序从开发环境移植到生产环境时，不用修改源代码，只需要修改mysql.ini配置文件 方法： 不直接把数据库连接信息写在程序里，而是单独使用一个properties（也就是mysql.ini）文件，来保存数据库连接信息另外，连接数据库和向数据库添加数据操作可以进行方法封装。 在目录创建配置文件： info.properties配置文件内容如下： 配置文件参数解释： "com.mysql.jdbc.Driver"：MySQL的驱动类名的字符串 "jdbc:mysql://127.0.0.1:3306/dome" jdbc:mysq
MySQL数据库运维.pdf
06-16
技术成就梦想 @ DataGuru 专业数据分析社区 网址：edu.dataguru.cn 1 MySQL 数据库运维 MySQL 数据库作为世界上最流行的开源数据库，以简单... 数据库配置--根据 IO 写入特点，配置 MySQL 数据库。 第 2 课 安装部署 源
数据库学习-mysql安装配置教程
最新发布
04-15
5. MySQL数据库的安全性和性能优化 本资料内容全面，结构清晰，从MySQL的安装到配置，再到基本操作，手把手教你搭建自己的数据库环境。此外，我们还提供了丰富的实例和操作截图，让你在实际操作中轻松掌握所学知识。...
MYSQL关闭SSL的方法分享
hdxx2022的博客
07-25 5118
以上为个人经验，希望能给大家一个参考，也希望对你有所帮助。微点阅读。
MySQL数据库的优化
04-04
MySQL数据库的优化主要包括以下几个方面： 1. 硬件优化：对于高负载的MySQL数据库，硬件优化是必不可少的。包括增加CPU、内存、硬盘等硬件设备的容量，以及优化服务器的网络带宽，提高系统的响应速度。 2. 数据库结构优化：通过优化数据库的表结构，可以提高数据访问的效率。包括选择合适的数据类型、设计合理的索引、避免使用过多的关联查询等。 3. 查询优化：通过优化SQL查询语句，可以提高查询的执行效率。包括使用合适的查询语句、避免使用通配符查询、避免使用子查询等。 4. 缓存优化：通过使用缓存技术，可以减少数据库的访问次数，提高系统的响应速度。包括使用MySQL自带的查询缓存、使用内存缓存等。 5. 系统配置优化：通过对MySQL的系统配置进行优化，可以提高系统的性能和稳定性。包括调整MySQL的缓冲区大小、调整MySQL的线程数等。 6. 数据库备份优化：通过优化数据库备份的方式和策略，可以减少备份过程对系统的影响，提高备份效率。包括使用增量备份、定期清理无用备份等。 7. 安全性优化：通过加强数据库的安全性，可以保护数据库的数据不被恶意攻击和非法访问。包括使用强密码、限制数据库用户的权限等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值