参考配置
RHEL
单机 - 精简配置
bash
[mysqld]
# 端口
port=3306
# 安装目录和数据目录
# basedir=/data/app/tools/mysql/server
datadir=/data/app/data/mysql/data
# 表名不区分大小写
lower_case_table_names=1
# 编码
character-set-server=utf8mb4
# 字符集
collation-server=utf8mb4_0900_ai_ci
# 指定时区
default-time-zone='+8:00'
# 错误日志
log-error=/data/app/data/mysql/logs/mysql.log
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=/data/app/data/mysql/logs/mysql-slow.log
# 修改为记录5秒内的查询，默认为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
#
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#
# 索引缓存区
key_buffer_size=256M
# 查询排序缓冲区，线程独占
sort_buffer_size=32M
# 查询排序缓冲区，线程独占
read_buffer_size=4M
# 联合查询缓冲区，线程独占
join_buffer_size=4M
# 临时表
tmp_table_size=128M
# 最大连接数
max_connections=256
[mysqldump]
max_allowed_packet=512M主从 - 精简配置
主库
bash
# 主库配置
[mysqld]
server-id=1
#
log_bin=/data/app/data/mysql/mysql-master-bin
log_bin_index=/data/app/data/mysql/mysql-master-bin.index
#
# 数据目录
datadir=/data/app/data/mysql/data
# 最大文件打开数
open_files_limit=1024
# 使用旧版的认证，兼容传统客户端
default-authentication-plugin=mysql_native_password
# 表名不区分大小写
lower_case_table_names=1
# 默认编码
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 指定时区
default-time-zone='+8:00'
# InnoDB
innodb_buffer_pool_size=8G
innodb_log_file_size=2G
innodb_log_buffer_size=4M
join_buffer_size=4M
sort_buffer_size=4M
read_buffer_size=8M
read_rnd_buffer_size=16M
key_buffer_size=128M
max_allowed_packet=16M
tmp_table_size=128M
table_open_cache=1024
max_connections=2500
# 错误日志
log-error=/data/app/data/mysql/logs/mysql.log
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=/data/app/data/mysql/logs/mysql-slow.log
# 修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqldump]
max_allowed_packet=8M从库
bash
# 从库配置
[mysqld]
server-id=2
#
relay-log=/data/app/data/mysql/mysql-slave-relay-bin
relay-log-index=/data/app/data/mysql/mysql-slave-relay-bin.index
#
# 数据目录
datadir=/data/app/data/mysql/data
# 最大文件打开数
open_files_limit=1024
# 使用旧版的认证，兼容传统客户端
default-authentication-plugin=mysql_native_password
# 表名不区分大小写
lower_case_table_names=1
# 默认编码
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
# 指定时区
default-time-zone='+8:00'
# InnoDB
innodb_buffer_pool_size=8G
innodb_log_file_size=2G
innodb_log_buffer_size=4M
join_buffer_size=4M
sort_buffer_size=4M
read_buffer_size=8M
read_rnd_buffer_size=16M
key_buffer_size=128M
max_allowed_packet=16M
tmp_table_size=128M
table_open_cache=1024
max_connections=2500
# 错误日志
log-error=/data/app/data/mysql/logs/mysql.log
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=/data/app/data/mysql/logs/mysql-slow.log
# 修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqldump]
max_allowed_packet=8MWindows
单机 - 精简配置
bash
[mysqld]
#
port=3310
#
pid-file=D:\Tools\MySQL\Logs\Main\mysql.pid
socket=D:\Tools\MySQL\Logs\Main\mysql.sock
#
basedir=D:\Tools\MySQL\Server
datadir=D:\Tools\MySQL\Data\Main
# 错误日志
log-error=D:\Tools\MySQL\Logs\Main\mysql.log
# 表名不区分大小写
lower_case_table_names=1
# 启用传统密码策略认证，兼容更多客户端
# default_authentication_plugin=mysql_native_password
# 默认编码
character-set-server=utf8mb4
# 默认字符集
# collation-server=utf8mb4_0900_ai_ci
# collation-server=utf8mb4_unicode_ci
collation-server=utf8mb4_0900_ai_ci
#
init_connect='SET NAMES utf8mb4'
# 指定时区
default-time-zone='+8:00'
#
[client]
#
default-character-set=utf8mb4
[mysql]
#
default-character-set=utf8mb4主从 - 精简配置
主库
bash
# 主库
[mysqld]
# ID
server-id=1
# 端口
port=3306
# 二进制日志
log_bin=mysql-master-log-bin
log_bin_index=mysql-master-log-bin.index
# 记录日志的数据库
binlog-do-db=lms
binlog-do-db=platform
binlog-do-db=test
# 不记录日志的数据库
binlog-ignore-db=information_schema,performance_schema,mysql,sys
#
pid-file=D:\Tools\MySQL\Logs\Master\mysql.pid
socket=D:\Tools\MySQL\Logs\Master\mysql.sock
# 安装目录和数据目录
basedir=D:\Tools\MySQL\Server
datadir=D:\Tools\MySQL\Data\Master
# 错误日志
log-error=D:\Tools\MySQL\Logs\Master\mysql.log
# 使用旧版的认证，兼容传统客户端
# default-authentication-plugin=mysql_native_password
# 表名不区分大小写
lower_case_table_names=1
# 默认编码
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
# 指定时区
default-time-zone='+8:00'
# InnoDB
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_log_buffer_size=4M
#
join_buffer_size=4M
#
sort_buffer_size=4M
#
read_buffer_size=8M
#
read_rnd_buffer_size=16M
#
key_buffer_size=128M
#
max_allowed_packet=128M
#
tmp_table_size=128M
#
table_open_cache=256
# 最大连接数
max_connections=2500
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=D:\Tools\MySQL\Logs\Master\mysql-slow.log
# 修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqldump]
quick
max_allowed_packet=512M从库
bash
# 从库
[mysqld]
# ID
server-id=2
# 端口
port=3308
# 二进制日志
relay-log=mysql-slave-relay-bin
relay-log-index=mysql-slave-relay-bin.index
# 需要复制的数据库
replicate-do-db=lms
replicate-do-db=platform
replicate-do-db=test
# 无需复制的数据库
replicate-ignore-db=information_schema,performance_schema,mysql,sys
#
pid-file=D:\Tools\MySQL\Logs\Slave\mysql.pid
socket=D:\Tools\MySQL\Logs\Slave\mysql.sock
# 安装目录和数据目录
basedir=D:\Tools\MySQL\Server
datadir=D:\Tools\MySQL\Data\Slave
# 错误日志
log-error=D:\Tools\MySQL\Logs\Slave\mysql.log
# 使用旧版的认证，兼容传统客户端
# default-authentication-plugin=mysql_native_password
# 表名不区分大小写
lower_case_table_names=1
# 默认编码
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
# 指定时区
default-time-zone='+8:00'
# InnoDB
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_log_buffer_size=4M
#
join_buffer_size=4M
#
sort_buffer_size=4M
#
read_buffer_size=8M
#
read_rnd_buffer_size=16M
#
key_buffer_size=128M
#
max_allowed_packet=128M
#
tmp_table_size=128M
#
table_open_cache=256
# 最大连接数
max_connections=2500
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=D:\Tools\MySQL\Logs\Slave\mysql-slow.log
# 修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqldump]
quick
max_allowed_packet=512M