mysql 5.7.33 优化配置

D:\mysql-5.7.41-winx64\bin>mysqld --initialize --console
D:\mysql-5.7.41-winx64\bin>mysqld install mysql5741
D:\mysql-5.7.41-winx64\bin>net start mysql5741
D:\mysql-5.7.41-winx64\bin>mysql -uroot -p -P5741
mysql> alter user 'root'@'localhost' identified by 'root5741';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 5.7.33 推荐配置
# v1.0.1 20220314 by xiet

[mysql]
# 设置mysql客户端默认字符集
default-character-set = utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir = D:/mysql-5.7.33-winx64
# 设置mysql的数据文件目录
datadir = D:/mysql-5.7.33-winx64/data
# 允许最大连接数
max_connections = 200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server = utf8
collation-server = utf8_general_ci

# 禁用DNS解析，请注意在增加该配置参数后，mysql的授权表中的host字段就不能够使用域名而只能够使用ip地址了，因为这是禁止了域名解析的结果
# 可能导致mysql ERROR 1130问题，初始化后localhost无法连接服务器
# skip-name-resolve
# 跳过数据库权限验证 
# skip-grant-tables

# 创建新表时将使用的默认存储引擎
default-storage-engine = INNODB
# 内存的二分之一，理论上越大越好
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
# InnoDB是否需要将所有表的数据和索引存放在共享表空间里
innodb_file_per_table = 1
innodb_doublewrite = 0
innodb_max_dirty_pages_pct = 80
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
# redo日志被用于确保写操作快速而可靠并且在崩溃时恢复
innodb_log_file_size = 256M
innodb_thread_concurrency = 0
# linux下启用此配置，分别为fdatasync（默认），O_DSYNC和O_DIRECT
# innodb_flush_method = O_DIRECT 
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 600
innodb_use_native_aio


# 超时时间设置，interactive_timeout针对交互式连接,wait_timeout针对非交互式连接
wait_timeout = 2147483
interactive_timeout = 2147483
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 连接数相关配置
back_log    = 256
max_connections = 5000
max_connect_errors = 6000
# skip-external-locking,external-locking用于多进程条件下为MyISAM数据表进行锁定
external-locking = FALSE

# 尺寸相关参数
tmp_table_size = 246M
max_heap_table_size = 246M
key_buffer_size = 400M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 8M
join_buffer_size = 1M
net_buffer_length = 1M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
table_open_cache = 4096

# 事件调度器，默认关闭
event_scheduler = OFF

# 请求缓存，如果每次数据都不一样，设置意义不大
query_cache_size = 0
query_cache_type = 0


# 日志设置
# binlog日志，主从库设置需要
# log-bin = mysql-bin
# master
server-id = 1
binlog-ignore-db = mysql
auto-increment-increment = 1
auto-increment-offset = 1
log-bin-trust-function-creators = 1
#binlog_format = mixed
# binlog过期清理
expire_logs_days = 7
relay_log_purge = 0
# 错误日志
log-error = D:/mysql-5.7.33-winx64/logs/mysqld.log
# mysql慢日志开启
slow_query_log = ON
slow_query_log_file = D:/mysql-5.7.33-winx64/logs/mysql-slow.log
long_query_time = 1
# 将所有到达MySQL Server的SQL语句记录下来，一般不会开启，排查问题时可短时间开启
#general_log = ON
#general_log_file = /data/mysql/log/general.log