配置文件
[mysqld]
datadir=/deploy/mysql/mysql_data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
plugin-load = validate_password.so
validate-password = ON
character_set_client_handshake = FALSE
character_set_server = utf8mb4
collation_server = utf8mb4_bin
init_connect='SET NAMES utf8mb4 COLLATE UTF8MB4_BIN'
max_connections = 3000
max_connect_errors=500
wait_timeout = 3600
transaction_isolation = READ-COMMITTED
innodb_log_buffer_size = 167772160
innodb_log_file_size = 1024M
innodb_buffer_pool_size = 10G
innodb_strict_mode = 0
innodb_flush_log_at_trx_commit = 1
binlog_format = row
sync_binlog = 1
max_heap_table_size = 512M
max_allowed_packet = 512M
lower_case_table_names = 0
skip_name_resolve
net_read_timeout=120
net_write_timeout=300
解释
参数名 | 参数值 | 说明 |
plugin-load | validate_password.so | 密码强度审计插件 |
validate_password | ON | 密码验证，默认开启。生产环境建议开启 |
character_set_client_handshake | FALSE | 使用服务端字符集 |
character_set_server | utf8mb4 | 服务器字符集 |
collation-server | utf8mb4_bin | 数据库字段排序规则 |
max_connections | 3000 | 客户端的最大连接数 |
max_connect_errors | 500 | 允许的最大错误连接数 |
wait_timeout | 3600 | 连接超时时间，默认为28800s，即8小时 |
transaction_isolation | READ-COMMITTED | 数据库隔离级别 |
innodb_buffer_pool_size | 10G | 用于缓存 索引 和 数据的内存大小,当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。建议为机器内存的70% |
sync_binlog | 1 | 每次事务提交，MySQL都会把binlog同步到磁盘 |
max_allowed_packet | 512M | 接受的数据包大小 |
lower_case_table_names | 0 | 对大小写敏感 |
skip_name_resolve | N/A | 禁止域名解析 |
net_read_timeout | 120 | 在中止读取之前等待来自连接的更多数据的秒数。默认30秒 |
net_write_timeout | 300 | 在中止写入之前等待块写入连接的秒数。默认60秒 |
相关命令
查看innodb_buffer_pool_size的大小
SELECT @@innodb_buffer_pool_size/1024/1024/1024; #单位为G
