MySQL 8.0 需要调整哪些参数【转】

这一节内容来聊聊新部署的 MySQL 8.0，需要调整哪些参数。包括一个 my.cnf 的示例和重要参数的解释及建议。

1 my.cnf 示例

首先列出一个 8.0 my.cnf 的实例，这个只针对普通的单实例或者主从环境，对于其他环境，比如 MGR，可能有更多的一些参数需要调整。并且也不是最完整，最终要根据自己实际环境进行调整。

复制代码
[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port    = 3306
socket  = /tmp/mysql.sock

##  dir set
datadir           = /data/mysql/data
innodb_data_home_dir   = /data/mysql/data
innodb_log_group_home_dir = /data/mysql/data
log-bin           = /data/mysql/binlog/mysql-bin
log_bin_index             = /data/mysql/binlog/mysql-bin.index  
relay-log         = /data/mysql/binlog/mysql-relay-bin
tmpdir            = /data/mysql/tmpdir
slow_query_log_file   = /data/mysql/log/mysql-slow.log
general_log_file   = /data/mysql/log/mysql-general.log
log-error         = /data/mysql/log/mysql.err

## slave and binlog
server-id = 6666             
skip-slave-start = 0       
read_only = 0          
binlog_format = row             
log-slave-updates = 1
master_info_repository = table
relay_log_info_repository = table
relay_log_purge = 1
relay_log_recovery = 1
sync_binlog = 100                 

binlog_cache_size = 1M
binlog_expire_logs_seconds = 2592000
log_bin_trust_function_creators = 1         
slave_net_timeout=60                
#binlog_error_action="IGNORE_ERROR"     

innodb_autoinc_lock_mode=1          

## 
back_log = 200
bulk_insert_buffer_size = 8M            
#character-set-server = utf8
lower_case_table_names = 1              

## 基线
local-infile = off
skip-networking = off
skip-name-resolve = on 

## connect
max_allowed_packet = 32M
max_connect_errors = 1000
max_connections = 3000
wait_timeout = 3600            
interactive_timeout = 3600          

table_open_cache = 4096
thread_cache_size =  64
thread_stack = 192K
transaction-isolation = REPEATABLE-READ     # 
pid-file = mysql.pid

## slow
slow_query_log = 1              
long_query_time = 1
log-slow-admin-statements
log_queries_not_using_indexes = 0
slow_launch_time = 1
read_buffer_size = 4M               
read_rnd_buffer_size = 8M           
sort_buffer_size = 8M
join_buffer_size = 32M
tmp_table_size = 128M
max_heap_table_size = 128M

default-storage-engine = innodb
explicit_defaults_for_timestamp = on           

## innodb
innodb_buffer_pool_size = 1G                   
innodb_max_dirty_pages_pct = 80         
innodb_thread_concurrency = 8           
innodb_buffer_pool_instances = 2        
innodb_flush_log_at_trx_commit = 2      
innodb_read_io_threads = 8          
innodb_write_io_threads = 4         
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_lru_scan_depth = 1024
innodb_use_native_aio = 1
innodb_flush_neighbors = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

innodb_data_file_path=ibdata:1G:autoextend
innodb_log_files_in_group = 3
innodb_log_file_size = 2G
innodb_file_per_table = 1 

innodb_flush_method = O_DIRECT
innodb_strict_mode = 1
innodb_lock_wait_timeout = 30
innodb_log_buffer_size = 16M
innodb_adaptive_flushing = 1
innodb_change_buffering = all
innodb_purge_threads = 4            
innodb_purge_batch_size = 300           

innodb_old_blocks_time = 1
innodb_fast_shutdown = 0
performance_schema = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 4M 

innodb_page_size = 16k
gtid_mode=on
enforce_gtid_consistency=on     

table_open_cache_instances=16
binlog_rows_query_log_events=1      


## pasword
default_password_lifetime=0 

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
prompt=\p@\d>\_

[mysqld_safe]
open-files-limit = 28192

[mysqlhotcopy]
interactive-timeout
复制代码
 

2 重要参数解释

port

端口

 

socket

指定 socket 文件

 

datadir

数据目录的路径

 

log-bin

控制是否开启 Binlog，建议所有实例都开启 Binlog。在 MySQL 8.0 之前的版本，默认情况下禁用二进制日志，如果增加了该参数，则会开启 Binlog。从 MySQL 8.0 开始，无论是否设置了该参数，默认都开启 Binlog。后面可不带字符串，则使用主机名来定义 Binlog 文件，如果后面字符串是类似例子中的 /data/mysql/binlog/mysql-bin，则表示把 Binlog 存放在 /data/mysql/binlog/ 文件下，并以 mysql-bin 前缀命名。

 

log_bin_index

Binlog 文件索引文件的名称

 

binlog_format

二进制日志格式，可选择的日志格式有三种：statement、row、mix，具体介绍看查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-formats.html。生产环境通常建议设置为 row，安全性最高。

 

relay-log

中继日志的名称。

 

slow_query_log

8.0 版本下，默认也是关闭 Slow Log 的，如果要开启，需要将 slow_query_log 设置为 1。

 

long_query_time

查询超过多少秒，则会记录到慢查询日志中，通常建议设置为 1，如果业务要求 MySQL 有更高的 QPS，则可以设置为 0.1 秒，发现慢查询及时优化。

 

slow_query_log_file

慢查询日志路径和名称。

 

server-id

主从或者 MGR 架构，需要将所有成员的 server id 设置的不一样。

 

read_only

主库关闭 read_only；从库视架构而定，通常从库开启 read_only。

 

log-slave-updates

表示从库从主库同步数据时，是否也写入从库自己的 Binlog。级联（A->B->C）情况下，中间的机器（B）必须要开启。通常建议都开启。这里要注意的是，从 MySQL 8.0.26，该参数改为了 log_replica_updates。

 

sync_binlog

控制 MySQL 服务器将二进制日志同步到磁盘的频率。对安全性要求较高的情况，建议设置成 1。各种值对应的效果，可查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_slave_updates。

 

binlog_expire_logs_seconds

Binlog 过期时间，单位秒。在以往的版本一般使用的是 expire_logs_days，该参数的单位为天。

 

lower_case_table_names

是否区分大小写，建议设置为1，各种值表示的含义可参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names。

 

max_connections

最大连接数，默认为 151，通常建议设置 1000 以上，当然也看具体配置。

 

wait_timeout

关闭非交互连接之前等待活动的秒数，默认 28800 秒，也就是 8 小时，建议设置小一点，防止空闲连接过多，比如设置为：3600。

 

interactive_timeout

关闭交互式连接之前等待活动的秒数，默认也是 28800 秒。也建议设置的小一点，比如 3600。

 

transaction-isolation

事务隔离级别，默认为 REPEATABLE READ，四种事务隔离级别的描述可参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html。

通常也是 REPEATABLE READ 和 READ COMMITTED 二选一，如果能接受幻读，业务可能是高并发的场景，可以配置成 RC；如果不能接受幻读，就设置成 RR 隔离级别。

 

innodb_buffer_pool_size

缓冲池的大小，对性能影响非常大，通常设置为机器内存的 50%-80%。

 

innodb_buffer_pool_instances

InnoDB 缓冲池的区域数，如果配置大于 1 的值，要求 innodb_buffer_pool_size 的值大于或等于 1G。将缓冲池划分多个区域，可以减少不同线程读取和写入缓存页时的争用，可提高并发性。

 

innodb_flush_log_at_trx_commit

控制 redo log 的写入策略，可设置为 0，1，2。

0：事务提交时，在事务提交时，每秒触发一次 redo log buffer 写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。

1：事务提交时，InnoDB 立即将缓存中的 redo 日志写到日志文件中，并调用操作系统 fsync 刷新 IO 缓存；

2：事务提交时，InnoDB 立即将缓存中的 redo 日志写到日志文件中，但不是马上调用 fsync 刷新 IO 缓存，而是每秒只做一次磁盘 IO 缓存刷新操作。

如果想要数据最为安全，可设置为 1，只是性能会下降一部分；

将参数设置为 0 时，如果数据库崩溃，最后 1秒钟的 redo log 可能会由于未及时写入磁盘文件而丢失，这种方式尽管效率最高，但是最不安全。

将参数设置为 2 时，如果数据库崩溃，由于已经执行了重做日志写入磁盘的操作，只是没有做磁盘 IO 刷新操作，因此，只要不发生操作系统崩溃，数据就不会丢失，这种方式是对性能和安全的一种折中处理。

 

innodb_io_capacity

控制每秒可用于 InnoDB 后台任务的 I/O 数，也就是 IOPS。如果是 SSD，可设置 5000 以上。

 

innodb_data_file_path

系统表空间数据文件的名称、大小和属性。默认为 12M，很多场景不适合，建议设置为 1G。

 

innodb_log_files_in_group

Redo Log 的个数，MySQL 以循环的方式写入 Redo Log 到这些文件。建议是指 2 或者大于 2 的值。

 

innodb_log_file_size

Redo Log 的大小，默认 48M，建议设置为 1 或者 2G，因为过小可能会导致 Redo Log 频繁切换。

 

innodb_file_per_table

是否开启独立表空间，建议设置为 1，即开启独立表空间。独立表空间，每一张表都会有独立的表空间文件，碎片率低，方便维护。

 

innodb_lock_wait_timeout

InnoDB 行锁等待时间，默认 50 秒，发生锁等待超时时，当前语句会回滚

 

gtid_mode=on

是否启用基于 GTID 的日志记录，以及日志可以包含的事务类型。可设置的值如下：

  • OFF: 新的事务和复制的事务都必须是匿名的。

  • OFF_PERMISSIVE: 新事务是匿名的。复制的事务可以是匿名事务或 GTID 事务。

  • ON_PERMISSIVE: 新事务是 GTID 事务。复制的事务可以是匿名事务或 GTID 事务。

  • ON：新事务和复制事务都必须是 GTID 事务。

建议设置为 ON。

 

enforce_gtid_consistency=on

是否允许违反 GTID 一致性，有以下值可以设置：

  • OFF：允许所有事务违反 GTID 一致性。

  • ON: 不允许任何事务违反 GTID 一致性。

  • WARN：允许所有事务违反 GTID 一致性，但在这种情况下会生成警告。

建议设置成 ON，保证基于 GTID 的复制时，主从数据一致。

 

 

这些就是 MySQL 8.0 的一些重要参数，当然，可能并不完整，朋友们可以留言进行补充或者参与讨论。

转自

MySQL 8.0 需要调整哪些参数
https://mp.weixin.qq.com/s?__biz=MzIyOTUzNjgwNg==&mid=2247484596&idx=1&sn=7c050e483419becdc4e3454c49599140&chksm=e84061fddf37e8ebcf2c299e32fad6ea18ec4ea5ccba5189e691ee86e6fc1548aa5134a3ba83&scene=21#wechat_redirect

posted @   paul_hch  阅读(289)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL核心揭秘：从查询到修改，彻底理解 Undo Log、Redo Log、Binlog 与 ACID 的关系【转】
· MySql三大日志redolog、undolog、binlog详解【转】
· Mysql 8.x参数优化
· MySQL配置参数
· mysql 初始化配置
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 记一次.NET内存居高不下排查解决与启示
历史上的今天：
2015-12-30 TCP/IP模型各个层次的功能和协议
点击右上角即可分享
微信分享提示