mysql全配置解析
博主 默语带您 Go to New World.
✍ 个人主页—— 默语 的博客👦🏻
《java 面试题大全》
🍩惟余辈才疏学浅，临摹之作或有不妥之处，还请读者海涵指正。☕🍭
《MYSQL从入门到精通》数据库是开发者必会基础之一~
🪁 吾期望此文有资助于尔，即使粗浅难及深广，亦备添少许微薄之助。苟未尽善尽美，敬请批评指正，以资改进。！💻⌨
MySQL全配置解析与优化
摘要
在本文中，我们将深入解析MySQL配置文件，以及每个配置项的作用和优化建议。从基本设置、连接设置、缓存设置、日志设置、InnoDB设置到其他设置，我们将逐一讨论如何通过调整这些参数来提升MySQL性能。
引言
MySQL是广泛使用的关系型数据库管理系统，通过合理配置可以最大程度地发挥其性能优势。了解和优化MySQL配置是数据库管理员和开发人员的关键任务之一。本文将通过解析每个配置项，介绍如何优化MySQL配置以提高系统的稳定性和性能。
基本设置 🛠️
唯一标识和路径
port: MySQL服务器端口号，建议避免使用默认端口。server-id: 为每个MySQL服务器分配唯一标识，用于复制和分布式架构。pid-file: MySQL服务器进程的PID文件路径。socket: MySQL服务器监听客户端连接的套接字文件路径。datadir: 数据目录，存储和读取数据库文件的路径。
SQL规则和行为
sql-mode: 定义MySQL服务器应该遵循的SQL语法规则和行为模式。open_files_limit: MySQL服务器可以同时打开的文件描述符的最大数量。
连接设置 🔄
max_connections: 最大连接数。table_open_cache: 设置table高速缓存的数量，与max_connections相关。thread_cache_size: 线程缓存数量。back_log: 请求堆栈中可以存储的短时间内的请求数量。max_connect_errors: 最大连接异常次数，超过则阻止主机后续请求。wait_timeout: 空闲连接的超时时间。interactive_timeout: 交互式连接的超时时间。
缓存设置 🚀
临时表和存储引擎
tmp_table_size: 临时表的内存缓存大小。myisam_max_sort_file_size: MyISAM重建索引时允许使用的临时文件最大大小。myisam_sort_buffer_size: MyISAM设置恢复表时使用的缓冲区大小。
读取和键缓冲区
read_buffer_size: MySQL读入缓冲区大小。read_rnd_buffer_size: 随机读缓冲区大小。key_buffer_size: MyISAM存储引擎使用的键缓冲区大小。
InnoDB缓冲和其他
innodb_log_buffer_size: InnoDB写操作的缓冲区大小。join_buffer_size: Join缓存大小，提高join查询效率。max_allowed_packet: 每个连接的最大允许数据包大小。sort_buffer_size: Connection级参数，用于排序操作。query_cache_type: 查询缓存工作方式，0表示禁用。query_cache_size: 查询缓存内存大小，0表示禁用。
日志设置 📜
查询和慢查询日志
log-output: MySQL日志的输出方式。general-log: 是否启用常规查询日志。slow-query-log: 是否启用慢查询日志。slow_query_log_file: 慢查询日志文件路径。long_query_time: 慢查询的阈值时间。log-bin: 启用二进制日志记录。
错误日志和安全性
log-error: 错误日志文件路径。log_bin_trust_function_creators: 控制是否可以信任存储函数创建者。
InnoDB设置 ⚙️
default-storage-engine: 默认存储引擎。innodb_buffer_pool_size: InnoDB缓冲池大小，建议设置为系统可用内存的60%-80%。innodb_buffer_pool_instances: 缓冲池划分的区域数。innodb_file_per_table: 指定每个InnoDB表是否使用独立的表空间文件。innodb_flush_log_at_trx_commit: 控制log buffer何时写入磁盘。
其他设置 🔧
secure-file-priv: 限制加载或写入文件的目录。lower_case_table_names: 表名大小写忽略。group_concat_max_len: GROUP_CONCAT函数连接字符串的最大长度。character-set-server: 服务器端使用的字符集。collation-server: 服务器端使用的排序规则。
总结
通过深入了解和优化MySQL配置，我们可以提高数据库的性能和稳定性。不同的应用场景可能需要不同的调整，因此在调整配置时建议根据实际需求和负载进行细致的优化。通过合理配置，MySQL可以更好地适应特定的工作负载，提供高效的数据库服务。
整体配置如下：
[client]
port=3306
[mysql]
no-beep=
[mysqld]
# ----------------------------基本设置 start----------------------------
port=3306
# 唯一标识数据库服务器的身份.每个运行MySQL的服务器都应该具有唯一的server-id值，以便在复制和分布式架构中进行正确的数据同步和识别。
server-id=1
# 进程标识文件：指定MySQL服务器进程的PID（进程ID）文件的路径和名称
pid-file=/var/run/mysqld/mysqld.pid
# 套接字：指定MySQL服务器监听客户端连接的套接字文件的路径和名称。
socket=/var/run/mysqld/mysqld.sock
# 数据目录：datadir表示MySQL服务器将存储和读取数据库文件的目录
datadir=/var/lib/mysql
# 定义MySQL服务器应该遵循的SQL语法规则和行为模式
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# MySQL服务器可以同时打开的文件描述符的最大数量
open_files_limit=4161
# 指定MySQL服务器报告给MySQL监控工具的端口号
report_port=3306
# ----------------------------基本设置 end ----------------------------
# ----------------------------连接设置 start----------------------------
# 最大链接数
max_connections=151
# 设置table高速缓存的数量，由于每个客户端连接都会至少访问一个表，因此此参数的值与max_connections有关。
# 例如，对于 1000 个并行运行的连接，应该让表的缓存至少有 1000 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量
table_open_cache=2000
# 线程缓存数量
thread_cache_size=32
# 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
# back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效
# 查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog
back_log=80
# 设置最大连接异常次数(“阻塞”的连接错误的数量,而非密码输入错误次数)，如果超过次次数，MySQL服务器就会阻止这台主机后续的所有请求
# 如超过此次数错误异常，解决办法，调整此次数，或者执行flush hosts
# 以上两种办法只是临时解决问题，治标不治本，还得从网络等层面根本解决问题
max_connect_errors=100
# 指定空闲连接的超时时间
# 设置的是一个访问等待一个周期时长，非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等
wait_timeout=3153600
# 交互式连接超时时间(mysql工具、mysqldump等)
interactive_timeout=3153600
# ----------------------------连接设置 end----------------------------
# ----------------------------缓存设置 start----------------------------
# 临时表的内存缓存大小
tmp_table_size=232M
# mysql重建索引时允许使用的临时文件最大大小
myisam_max_sort_file_size=100G
# MyISAM 设置恢复表之时使用的缓冲区的大小
myisam_sort_buffer_size=451M
# MySQL读入缓冲区大小
read_buffer_size=64K
# 是MySql的随机读缓冲区大小
read_rnd_buffer_size=1M
# MyISAM存储引擎使用的键缓冲区大小,键缓冲区是一个内存区域，用于缓存MyISAM表的索引数据，以加快索引查找的速度。
key_buffer_size=256M
# 设置 InnoDB 用来往磁盘上的日志文件写操作的缓冲区的大小
innodb_log_buffer_size=8M
# join缓存大小，对于table join的一个重要的优化手段，可以极大提高join查询的效率,默认256k
join_buffer_size=8M
# 针对的是一个事务中的一行记录大小，当一行记录超过了限制的大小，将会报错
# 该值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败
max_allowed_packet=64M
# 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。
# 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源
sort_buffer_size=256K
# 指定查询缓存的工作方式,query_cache_type的值为0，表示查询缓存被禁用
query_cache_type=0
# 指定查询缓存的内存大小,值为0，表示查询缓存的内存大小为零，即禁用查询缓存
query_cache_size=0
# ----------------------------缓存设置 end----------------------------
# ----------------------------日志设置 start----------------------------
# 指定MySQL日志的输出方式
log-output=FILE
# 指定是否启用常规查询日志。设置为0表示禁用常规查询日志，不记录每个查询的详细信息。
general-log=0
# 指定是否启用慢查询日志。设置为1表示启用慢查询日志，记录执行时间超过long_query_time阈值的查询。
slow-query-log=1
# 指定慢查询日志文件的路径和名称
slow_query_log_file=/var/log/mysql/slow.log
# 指定慢查询的阈值时间（以秒为单位）。
long_query_time=10
# 启用二进制日志记录（Binary Logging）。设置为一个非空的值，如"mysql-bin"，表示启用二进制日志记录，并指定二进制日志文件的前缀名称。
log-bin="mysql-bin"
# 指定错误日志文件的路径和名称
log-error="/var/lib/mysql/mysql-error.err"
#当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数
#如果数据库没有使用主从复制，那么就可以将参数log_bin_trust_function_creators设置为1
log_bin_trust_function_creators=1
# ----------------------------日志设置 end----------------------------
# ----------------------------InnoDB设置 start----------------------------
# 默认存储引擎设置
default-storage-engine=InnoDB
# 设置InnoDB存储引擎使用的缓冲池大小。缓冲池是一个内存区域，用于缓存InnoDB表的数据和索引，以提高读取操作的性能。建议将innodb_buffer_pool_size设置为系统可用内存的60%-80%
innodb_buffer_pool_size=4G
# 缓冲池划分的区域数
innodb_buffer_pool_instances=8
# 5.6.6版本以后,指定每个InnoDB表是否使用独立的表空间文件。
innodb_file_per_table=1
# 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
# 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
# 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
# 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
# 当设置为1，该模式是最安全的， 但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
# 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失
innodb_flush_log_at_trx_commit=1
# 当自动扩展表空间被填满之时，为扩展而增加的尺寸（MB为单位）
innodb_autoextend_increment=64
# MySQL层和Innodb层交互的次数，超过次数后交出CPU使用权。
# 例如select查询10000条，实际上需要进入Innodb层10000次。当查询了5000条数据后将放弃CPU使用权，交给其它线程使用。其它线程使用完之后再继续刚才查询
innodb_concurrency_tickets=5000
# 用于读取到old列表中的页时需要等待多久才会被加入到LRU列表的首部。防止new列中的热点数据被刷出
innodb_old_blocks_time=1000
# 日志文件大小
innodb_log_file_size=256M
# 线程并发执行数量，默认是0(不限制并发数量)
innodb_thread_concurrency=33
# ----------------------------InnoDB设置 end----------------------------
# ----------------------------其他设置 start----------------------------
# 限制从哪个目录中可以加载或写入文件。它用于提高MySQL服务器的安全性，防止恶意用户滥用文件操作功能。
secure-file-priv="/var/lib/mysql-files/"
# 表名大小写忽略
lower_case_table_names=1
# 设置GROUP_CONCAT函数用于将多个字符串连接成一个字符串长度，如果过小会导致返回结果被截断
group_concat_max_len = 4294967295
# 指定服务器端使用的字符集
character-set-server=utf8mb4
# 指定服务器端使用的排序规则
collation-server=utf8mb4_unicode_ci
# ----------------------------其他设置 end----------------------------
🪁🍁 希望本文能够给您带来一定的帮助🌸文章粗浅，敬请批评指正！🍁🐥
如对本文内容有任何疑问、建议或意见，请联系作者，作者将尽力回复并改进📓；(联系微信:Solitudemind )
点击下方名片，加入IT技术核心学习团队。一起探索科技的未来，共同成长。
1904
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
