mysql8在生产环境中的配置
一，配置文件的位置
[root@yjweb ~]# ll /etc/my.cnf -rw-r--r-- 1 root root 935 Mar 11 16:52 /etc/my.cnf
说明：通常我们都会使用/etc/my.cnf这个文件
说明：刘宏缔的架构森林是一个专注架构的博客，
网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/20/mysqlschjdpzmysql-8-0-19/
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，my.cnf中的配置项：慢查询日志
#把未使用到索引的sql记录到慢查询日志
log_queries_not_using_indexes = 1
#是否打开慢查询sql日志
slow_query_log = 1
#慢查询sql日志的文件地址
slow_query_log_file = /data/mysql/log/mysql-slow.log
#慢查询执行的秒数，超过这个值则会被记录到慢查询日志
long_query_time = 1
三，my.cnf中的配置项：运行实例相关
#Mysql服务实例的唯一编号 每个mysql服务实例Id需唯一
server-id = 1
#服务端口号 默认3306
port = 3306
#socket文件的路径
socket=/data/mysql/var/mysql.sock
#pid文件的路径
pid-file=/data/mysql/var/mysqld.pid
四，my.cnf中的配置项：目录
#mysql安装根目录
basedir=/usr/local/soft/mysql
#mysql数据文件所在目录
datadir=/data/mysql/data
五,my.cnf中的配置项：字符集
#数据库默认字符集,注意不要再用utf8了
character-set-server = utf8mb4
#数据库字符集对应一些排序规则,要属于character-set-server对应值的集合内
collation-server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
六，my.cnf中的配置项：二进制日志
#二进制日志文件
log-bin = /data/mysql/binlog/mysql-bin
#mysql binlog日志文件保存的过期时间，过期后自动删除
#默认值是0，不限制,这样会占用空间太多
expire_logs_days = 365
#限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
max_binlog_size = 100M
七，my.cnf中的配置项：错误日志
#数据库错误日志文件
log_error=/data/mysql/log/mysqld.log
八，my.cnf中的配置项：操作用户
user=mysql
九，my.cnf中的配置项：超时
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭 MySQL默认的wait_timeout 值为8个小时,
interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800 wait_timeout = 1800
十，my.cnf中的配置项：禁用域名的解析
skip_name_resolve = 1
说明:dns慢的情况下会影响性能
十一，my.cnf中的配置项：innodb是否为每个表使用独立的表空间文件
innodb_file_per_table = 1
说明：这个参数在5.6以后已经默认开启了
开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，
而不是系统的表空间。
十二，my.cnf中的配置项：innodb缓冲池的大小设置
1,innodb_buffer_pool_size的默认大小是:128M
innodb_buffer_pool_chunk_size 定义了buffer中每个chunk的大小，
innodb_buffer_pool_instances 定义了buffer中chunk的数量
2,缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数
否则mysql会自动调整为相应的倍数
3,计算缓冲池大小的公式：
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
这个结果是指从磁盘上读取数据的百分比
说明： innodb_buffer_pool_reads 是InnoDB 进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数
innodb_buffer_pool_read_requests 是 InnoDB 完成的逻辑读请求数
4，查看两个值:innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 的大小的sql:
show status like 'innodb_buffer_pool_read%'
5,计算buffer pool的命中率:
InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
如果低于99%,可以增加innodb_buffer_pool_size
6,innodb_buffer_pool_size这个值的大小不要超过物理内存的50%
十三， my.cnf中的配置项：连接限制之最大并发连接数
1,参数设置
max_connections = 1024
说明：根据自己的运行情况设置，
过小会影响连接的数量，报Too many connections错误，
过大会导致服务资源用完无响应
2,查看当前的max_connections设置值
mysql> SHOW VARIABLES LIKE '%max_connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | +-----------------------+-------+ 2 rows in set (0.00 sec)
3,查看Max_used_connections状态值
mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 243 | +----------------------+-------+ 1 row in set (0.00 sec)
说明：Max_used_connections 是从这次mysql服务启动到现在，同一时刻并行连接数的最大值，可以参考这个值去配置max_connections,
通常可大于等于Max_used_connections的值
4,官方文档地址:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections
可以看到最大值不能超过100000
十四，连接限制之每用户最大并发连接数 max_user_connections
1，查看当前的设置值
mysql> SHOW VARIABLES LIKE '%max_user_connections%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_user_connections | 1990 | +----------------------+-------+ 1 row in set (0.00 sec)
2,max_user_connections表示的是每个用户的最大连接数，
注意此处的用户是以“用户名+主机名”为单位进行区分
3，如何设置：
如果有3个用户,max_connections=1000 建议值: max_connections/3 * 2 约等于 700
十五，连接限制之最大错误连接数max_connect_errors
1,查看当前的设置值
mysql> SHOW VARIABLES LIKE '%max_connect_errors%'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_connect_errors | 1000000 | +--------------------+---------+ 1 row in set (0.00 sec)
2，说明：如果错误连接达到该变量设置值， MySQL会阻止连接，直到
使用 FLUSH HOSTS命令或者 mysqladmin flush-hosts，MySQL才会解除阻止操作
3，什么是错误连接？
MySQL有个参数connect_timeout，该变量默认是10秒,它是MySQL服务端进程mysqld等待连接建立完成的时间，单位为秒
MySQL客户端与数据库建立连接需要发起三次握手协议，正常情况下，这个时间非常短，
但是一旦网络异常，网络超时等因素出现，就会导致这个握手协议无法完成。
如果超过connect_timeout时间，仍然无法完成协议握手，MySQL客户端会收到异常，
异常消息类似于: Lost connection to MySQL server at 'XXX', system error: errno.
4, 如何把被关闭连接的host释放出来？
Mysql> FLUSH HOSTS;
5,设置：如果业务服务器和数据库服务器之间网络不稳定，经常出现连接超时之类的情况，则此值可以加大，
如果只是在局域网中,网络情况良好，此值可以采用默认值100，或增加到1000
6,这个统计的错误数量记录在哪里可查看？
mysql> use performance_schema; mysql> select * from host_cache;
SUM_CONNECT_ERRORS 这个字段记录的就是连接错误的数量
7,官方文档地址:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connect_errors
十六，my.cnf中的配置项：最大允许的包 max_allowed_packet
max_allowed_packet = 16M
说明：这个默认值是64M,即67108864
但生产环境中很少会这么大的包，建议16M或更低一些
说明：导入数据时，如果sql文件大于这个值，会报过小的提示，
Packet for query is too large 或 Packet bigger than max_allowed_packet
可以临时调整这个值，使用如下两条sql
set global max_allowed_packet = 2*1024*10*10; show global variables like 'max_allowed_packet';
说明： 命令行修改时，max_allowed_packet的单位为字节，不能用M、G，
只能这算成字节数设置。
配置文件修改才允许设置M、G单位
十七，my.cnf中的配置项：数据安全：innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit = 1
1，innodb_flush_log_at_trx_commit的三个取值:
0, 每秒写一次log,并flush到磁盘
1, 每次事务提交时，写log,同时flush到磁盘
2, 每次事务提交时写log,每秒flush一次到磁盘
2,说明：
1最安全，速度最慢
0，2速度较快，但有一定丢失数据的风险
mysql给出的默认值也是1
十八，my.cnf中的配置项：客户端
[client]
#服务端口号 默认3306
port = 3306
#socket连接文件
socket = /data/mysql/var/mysql.sock
十九，说明
1，general log 这个配置项是查询日志，默认值是off,
这个不建议在生产环境中打开
2，慢查询在生产环境中打开时，
应当注意检查其内容，
对查询过慢的sql处理
二十，查看当前的mysql版本
[root@yjweb mysql]# /usr/local/soft/mysql/bin/mysqld -V /usr/local/soft/mysql/bin/mysqld Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
