mysql配置及注意事项

1、注意事项

1、innodb_buffer_pool_size太大导致系统内存不足

  innodb_buffer_pool_size 是 InnoDB 存储引擎用于缓存数据和索引的缓冲池的大小。设置这个参数时，MySQL 会在启动时分配并锁定指定大小的内存。因此，设置为 16384M（即 16GB）意味着 MySQL 在启动时会预先分配 16GB 的内存供 InnoDB 使用，从而减少运行时的内存分配开销，提高性能。

      生产bug：mysql和redis安装在同一台服务器，程序连接redis经常报错连不上redis，实际上登陆服务器，redis正常运行

      后续发现系统内存偶尔占用100% ，其中mysql占用了80%多，实际上发现mysql配置的innodb_buffer_pool_size 的值过大，导致其它应用内存不住，redis无法创建连接

2、配置注释（AI注释）

# 客户端部分
[client]
port=3306  # MySQL 客户端连接的端口，默认是 3306
socket=/var/lib/mysql/mysql.sock  # MySQL 客户端连接的套接字文件

# MySQL 服务部分
[mysqld]
server-id=3  # 服务器唯一ID，用于主从复制环境中
#basedir=/usr/local/mysql  # MySQL 安装目录（默认注释掉，如果需要自定义路径可以启用）
datadir=/var/lib/mysql/data  # 数据存放目录
socket=/var/lib/mysql/mysql.sock  # MySQL 套接字文件路径
#user=mysql  # 运行 MySQL 服务的用户（推荐使用非 root 用户）
port=3306  # MySQL 服务器监听的端口
# character_set_server=UTF8MB4  # 默认字符集（建议使用 UTF8MB4 以支持更多字符）
default_time_zone="+8:00"  # 默认时区（例如，中国时区为 +8:00）
# symbolic-links=0  # 禁用符号链接，增加安全性

bind-address = 0.0.0.0  # 绑定所有 IP 地址，允许远程连接
secure_file_priv=''  # 禁用 LOAD DATA LOCAL INFILE 等操作，提高安全性
admin_address='127.0.0.1'  # 管理接口绑定地址，仅本地访问
admin_port=33306  # 管理接口端口

#lock_wait_timeout=3600  # 锁等待超时时间，单位秒（可以根据需要调整）

# 连接管理
max_connections = 256  # 最大连接数（根据服务器资源进行调整）
max_connect_errors=1000  # 最大连接错误数（防止暴力破解）
table_open_cache=200  # 表缓存大小（根据工作负载调整）
table_definition_cache=150  # 表定义缓存大小（根据工作负载调整）
thread_stack=1048576  # 线程堆栈大小，默认 1MB
sort_buffer_size=16M  # 排序缓冲区大小（根据查询需求调整）
join_buffer_size=16M  # 连接缓冲区大小（根据查询需求调整）
read_buffer_size=8M  # 顺序读缓冲区大小
read_rnd_buffer_size=16M  # 随机读缓冲区大小
bulk_insert_buffer_size=64M  # 批量插入缓冲区大小
thread_cache_size=768  # 线程缓存大小，减少线程创建开销
interactive_timeout=7200  # 交互式连接超时时间，默认 2 小时
#wait_timeout=600  # 非交互式连接超时时间，默认 10 分钟（根据需求启用）

# 临时表和缓存
tmp_table_size=96M  # 临时表大小限制
max_heap_table_size=96M  # 内存表大小限制
binlog_cache_size=4M  # 二进制日志缓存大小
max_binlog_cache_size=2G  # 最大二进制日志缓存大小
max_binlog_size=1G  # 二进制日志文件最大大小

transaction_isolation=REPEATABLE-READ  # 事务隔离级别，默认 REPEATABLE-READ
innodb_buffer_pool_size=16384M  # InnoDB 缓冲池大小，生产环境中可根据内存大小调整
innodb_buffer_pool_instances=4  # InnoDB 缓冲池实例数量（提升并发性能）
innodb_max_undo_log_size=4G  # 最大撤销日志大小

innodb_io_capacity=4000  # InnoDB IO 容量
innodb_io_capacity_max=8000  # InnoDB 最大 IO 容量
innodb_open_files=65535  # InnoDB 打开文件数限制
innodb_flush_method=O_DIRECT  # 刷新方法，避免操作系统缓存
innodb_lru_scan_depth=4000  # LRU 扫描深度
innodb_lock_wait_timeout=10  # 锁等待超时时间
innodb_rollback_on_timeout=1  # 超时回滚
innodb_print_all_deadlocks=1  # 打印所有死锁信息
innodb_online_alter_log_max_size=4G  # 在线 DDL 最大日志大小
innodb_print_ddl_logs=1  # 打印 DDL 日志
innodb_status_file=1  # 启用 InnoDB 状态文件

# 安全模式部分
[mysqld_safe]
log-error=/var/log/mysqld.log  # 错误日志文件路径
pid-file=/var/run/mysqld/mysqld.pid  # PID 文件路径
#tmpdir=/usr/local/mysql/tmp  # 临时文件目录（根据需求启用）

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
server-id=3
#basedir=/usr/local/mysql
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
#user=mysql
port=3306
# character_set_server=UTF8MB4
default_time_zone="+8:00"
# symbolic-links=0

bind-address = 0.0.0.0
secure_file_priv=''
admin_address='127.0.0.1'
admin_port=33306

#lock_wait_timeout=3600

# default 151
max_connections = 256
# default 100
max_connect_errors=1000
# default 4000
table_open_cache=200
# default 2000
table_definition_cache=150
# 1048576=1024KB
thread_stack=1048576
# default 262144=0.25M
sort_buffer_size=16M
join_buffer_size=16M
read_buffer_size=8M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
thread_cache_size=768
# default 28800s=8h
interactive_timeout=7200
# default 28800s=8h
#wait_timeout=600
# default 16M
tmp_table_size=96M
# 16M
max_heap_table_size=96M
# 32768=32K
binlog_cache_size=4M
max_binlog_cache_size=2G
max_binlog_size=1G

transaction_isolation=REPEATABLE-READ
# default 134217728=128M
#innodb_buffer_pool_size=22528M
innodb_buffer_pool_size=16384M
# default 1
innodb_buffer_pool_instances=4
# default 1G
innodb_max_undo_log_size=4G

# default 200
innodb_io_capacity=4000
# default 2000
innodb_io_capacity_max=8000
innodb_open_files=65535
innodb_flush_method=O_DIRECT
innodb_lru_scan_depth=4000
innodb_lock_wait_timeout=10
# OFF
innodb_rollback_on_timeout=1
# OFF
innodb_print_all_deadlocks=1
innodb_online_alter_log_max_size=4G
innodb_print_ddl_logs=1
innodb_status_file=1

[mysqld_safe]
log-error==/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#tmpdir=/usr/local/mysql/tmp