mysql 5.7 的 /etc/my.cnf 参数

最新推荐文章于 2025-03-16 10:01:53 发布

数据库人生

最新推荐文章于 2025-03-16 10:01:53 发布

阅读量2.7w

点赞数 6

分类专栏： # mysql parameter 文章标签： my.cnf

本文链接：https://blog.csdn.net/ctypyb2002/article/details/79757070

版权

mysql parameter 专栏收录该内容

24 篇文章

订阅专栏

本文详细介绍了 MySQL 5.7 的配置文件 /etc/my.cnf 中的各项参数设置，包括数据库服务的基本配置、连接参数、字符集设置、二进制日志、GTID、InnoDB 存储引擎等关键配置项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

下面是 mysql 5.7 的 /etc/my.cnf 的一些常用参数，自己记录一下，随时补充完善。
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

[client]
port = 3306
default-character-set=utf8mb4
socket=/tmp/mysql.sock

[mysql]
#prompt="\\u@\\h :\\d \\r:\\m:\\s> "
prompt="\\u@\\h \\R:\\m:\\s [\\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8mb4
socket=/tmp/mysql.sock

[mysqld]
##########################
# summary
##########################
skip_name_resolve = on
#skip-grant-tables
#skip-networking

bind-address = 0.0.0.0
port = 3306
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
tmpdir = /tmp
pid-file=/tmp/mysqld.pid

explicit_defaults_for_timestamp=1
lower_case_table_names=1

table_open_cache = 8000
transaction_isolation = 'REPEATABLE-READ'

##########################
# time out
##########################
connect_timeout = 20
wait_timeout = 300
interactive_timeout = 300

##########################
# connection
##########################
max_connections = 2000
max_user_connections = 1900
max_connect_errors = 100000
max_allowed_packet = 1G

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_bin

##########################
# log bin
##########################
server-id = 1
log_bin = mysql-bin
# ROW、STATEMENT、MIXED
binlog_format = row
sync_binlog = 1
expire_logs_days = 7
binlog_cache_size = 128m
max_binlog_cache_size = 512m
max_binlog_size = 256M
master_info_repository=TABLE
log_slave_updates=ON
binlog_checksum=none
binlog_gtid_simple_recovery=on

#binlog_ignore_db=information_schema
#binlog_ignore_db=mysql
#binlog_ignore_db=performation_schema
#binlog_ignore_db=sys
#binlog_do_do=
#replicate_ignore_db=information_schema
#replicate_ignore_db=mysql
#replicate_ignore_db=performance_schema
#replicate_ignore_db=sys
#replicate_do_db=

##########################
# gtid
##########################
gtid_mode = on
enforce_gtid_consistency = on

##########################
# slave parallel
##########################
slave_net_timeout=60
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4

##########################
# log relay
##########################
relay_log_info_repository=TABLE
relay_log = mysql-relay-bin
relay_log_purge = on
relay_log_recovery = on
max_relay_log_size = 1G

##########################
# log error
##########################
log_error=/data/mysql/mysqld_error.log

##########################
# log slow
##########################
slow_query_log = on
slow_query_log_file = /data/mysql/mysqld_slow.log
long_query_time = 2
log_queries_not_using_indexes = on

##########################
# log general
##########################
general_log = on
general_log_file = /data/mysql/mysqld_general.log


##########################
# thread pool,Enterprise edition
##########################
#thread_handling=pool-of-threads
#thread_handling=one-thread-per-connection
#thread_pool_oversubscribe=8 

##########################
# innodb
##########################
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
innodb_adaptive_flushing=on
innodb_file_per_table=1
innodb_log_file_size=1024M
innodb_log_buffer_size=64M
max_prepared_stmt_count=150000
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=50

innodb_io_capacity=1000
innodb_io_capacity_max=10000

innodb_flush_method=O_DIRECT
##########################
# password policy
# after init 
##########################
#validate_password_policy=0
#validate_password_length=4         
#validate_password_mixed_case_count=0
#validate_password_number_count=0
#validate_password_special_char_count=0