【MySQL 8.0】标准配置文件详解(上)

将按照不同的模块介绍配置项：
文末附上参考配置文件

1.client

• socket
  MySQL 在本地可以通过 socket 方式连接，如果 my.cnf 配置文件中的 [client] 部分没有指定 socket 文件路径，mysql 默认会去寻找 /tmp/mysql.sock
  为了安全考虑，通常会设置特定的socket路径

[mysqld]
socket=/path/to/socket

[client]
socket=/path/to/socket

• port
  默认情况下，MySQL使用3306作为默认端口号，但是如果系统上有多个MySQL服务器实例运行，每个实例都需要使用不同的端口号来监听连接。可以使用port选项来指定不同的端口号

2.mysql

• prompt
prompt 选项用于设置 MySQL 命令行工具的提示符。
  MySQL 命令行工具的提示符默认为 mysql>，其中 mysql 是工具的名称，> 表示当前命令行输入的位置。通过设置 prompt 选项，可以自定义提示符的格式和内容。
  例如：

[mysql]
prompt = "\\u@\\d \\r:\\m:\\s>"

root@(none) 01:00:04>

• no_auto_rehash
  MySQL 命令行工具中的自动命令补全功能（也称为自动重建索引）会在用户输入命令时，根据已知的表和列名等数据库对象信息，自动补全命令的其他部分。例如，当用户输入 SEL 时，命令行工具会自动补全为 SELECT;
  虽然自动命令补全功能可以提高命令行工具的使用效率，但对于非常大型的数据库，自动补全会占用大量的系统资源和时间，导致命令行响应缓慢;
  设置 no auto rehash 选项，将禁用命令行工具中的自动命令补全功能

• loose skip binary as hex
  用于配置 MySQL 服务器在执行 LOAD DATA INFILE 语句时，是否将二进制数据作为十六进制字符串加载到数据库中。
  LOAD DATA INFILE 语句在导入过程中，如果文件中包含二进制数据，MySQL 默认会将其作为文本字符串加载到数据库中。但是，如果二进制数据中包含了某些特殊字符（例如 NULL 字符），这可能会导致数据加载不完整或格式错误。
  这个选项被设置为 true 时，MySQL 将二进制数据作为十六进制字符串加载到数据库中，而不是作为文本字符串。这样可以确保二进制数据在加载到数据库中时不会被解释或修改。

3.mysqld

• user
  当 MySQL 服务器启动时，会以一个操作系统用户的身份运行。这个用户通常是在 MySQL 安装过程中创建的，可以是一个专门的 MySQL 用户，也可以是系统上的普通用户。默认情况下，MySQL 服务器使用的用户身份是安装 MySQL 时创建的用户。但是，在某些情况下，例如需要控制 MySQL 服务器对系统资源的访问权限时，可能需要使用不同的用户身份来运行 MySQL 服务器。

• authentication_policy
authentication policy 选项可以用来指定 MySQL 服务器应该使用哪种身份验证方式和插件

• server-id
  在 MySQL 复制中，server-id 选项用于唯一标识每个 MySQL 实例，以便 MySQL 复制进程能够将更改从一个实例复制到另一个实例。在主从同步中，server-id 选项用于标识主服务器和从服务器，以便从服务器可以连接到主服务器并复制更改。因此，每个 MySQL 服务器应该有不同的 server-id 值，以避免出现冲突和错误。

• tmpdir
  在 MySQL 中，有些操作需要使用临时文件，例如排序、合并、临时表等操作，这些操作会生成大量的临时文件，因此需要为这些操作指定一个临时目录。如果没有指定临时目录，则 MySQL 服务器将使用系统默认的临时目录。
  临时目录应该是具有足够磁盘空间、权限和性能的目录，否则可能会导致 MySQL 服务器出现性能问题、故障或崩溃。

• datadir
  数据目录文件，需要保证它的权限和目录大小

• pid -file
  在 MySQL 中，每个 MySQL 服务器进程都有一个唯一的进程 ID (PID)，用于标识该进程。pid-file 选项用于指定将进程 ID 写入的文件的路径，以便其他程序或脚本可以轻松地访问该文件并使用 MySQL 服务器进程的 PID。

• character_set_server

可以使用 character_set_server 选项在 MySQL 配置文件中指定 MySQL 服务器使用的默认字符集。

• skip-name-resolve
  当 MySQL 服务器接受来自客户端的连接请求时，通常需要将主机名解析为 IP 地址。这个过程需要进行网络通信和 DNS 查询，如果客户端连接的主机名不正确或无法解析，则可能导致连接延迟或失败。但是，在某些情况下，如只使用 IP 地址进行连接或连接的主机名已经被解析为 IP 地址时，这个过程可能是不必要的。

可以使用 skip-name-resolve 选项在 MySQL 配置文件中控制 MySQL 服务器是否执行主机名解析

• secure-file-priv

当 MySQL 服务器执行 LOAD DATA INFILE 或 SELECT … INTO OUTFILE 等语句时，会从文件系统中读取或写入文件。如果没有适当的限制，可能会导致服务器面临安全风险，例如攻击者可以在服务器上读取或写入任意文件。因此，secure-file-priv 选项定义了 MySQL 服务器允许读取和写入的目录位置。

可以使用 secure-file-priv 选项在 MySQL 配置文件中指定允许读取和写入的目录位置。

• admin_address
  如果 MySQL 服务器遇到问题或发生错误，可能需要通知管理员或系统管理员。admin-address 选项定义了一个或多个管理员的联系信息，以便 MySQL 服务器可以向其发送通知或警报。这些联系信息通常包括电子邮件地址和名称，但也可以包括其他信息，如电话号码等。

• admin_port
  MySQL 服务器支持多个端口号，其中默认端口号为 3306。admin-port 选项允许管理员使用指定的端口号连接到 MySQL 服务器，以便执行管理任务和监控操作。这可以提供额外的安全性，连接数爆满时，可以通过该端口登录mysql

• lower_case_table_names
  具体来说，lower_case_table_names 的取值有以下几种：

0：表名使用大小写敏感的方式处理。
1：表名在存储时将被转换为小写字母，但在检索时保持原样，此时如果存在大小写相同但是内容不同的表名，将会出现错误。
2：表名在存储和检索时都将被转换为小写字母，这样可以避免大小写不匹配的问题，但可能导致表名冲突。
需要注意的是，lower_case_table_names 选项对已经存在的表名不会产生影响，只会影响新创建的表名。如果在 MySQL 数据库中已经存在表名，应该在设置 lower_case_table_names 选项之前备份数据，并进行相应的调整。

• local-infile
  MySQL 数据库支持使用 LOAD DATA INFILE 语句将数据从文件加载到表中。local-infile 选项控制是否允许在客户端本地加载数据文件，即将数据文件从客户端传输到服务器，然后使用LOAD DATA INFILE语句将数据加载到 MySQL 表中。

如果 local-infile 选项被设置为 ON，则允许在客户端本地加载数据文件。如果该选项被设置为 OFF，则不允许在客户端本地加载数据文件。

4.performance

- lock wait timeout
事务锁等待超时时间

• open files limit
  操作系统对mysqld可用的文件描述符的限制
  MySQL 8.0.19 及更高版本：操作系统限制

• back_log
  表示在 MySQL 暂时停止响应新请求之前的这段短时间内可以堆叠多少请求

• max_connections
  mysql接受的最大连接数

• max connnect error
  来自主机的连续连接请求在没有成功连接的情况下被中断后 服务器阻止该主机进一步连接

• table open cache
  所有线程打开表的数量

MAX(
    (open_files_limit - 10 - max_connections) / 2,
    400
   )

• table definition cache
  增加这个参数加速打开表的速度

• thread stack
  每个线程的堆栈大小
  如果线程堆栈大小太小，它会限制服务器可以处理的 SQL 语句的复杂性、存储过程的递归深度以及其他消耗内存的操作。

默认值（64 位平台，≥ 8.0.27） 1048576
默认值（64 位平台，≤ 8.0.26） 286720

• sort buffer size
  每个必须执行排序的会话都会分配一个此大小的缓冲区 增加该值加速order by 或group by 的速度

• thread cache size
  服务区缓存线程的数量 当客户端断开连接时，如果客户端的线程少于 thread_cache_size线程，则将其放入缓存中。如果可能，通过重用从缓存中获取的线程来满足对线程的请求，并且只有当缓存为空时才会创建新线程
  默认值公式 上限为 100：

8 + (max_connections / 100)

• tmp table size
  存储引擎创建的内部内存临时表的最大大小MEMORY

5.log setting

• log_timestamps
  log中日志文件记录时间戳的时区 Default Value UTC
• error log
  错误日志路径
• log_error_verbosity
  日志输出等级

log_error_verbosity Value Permitted Message Priorities
1 ERROR
2 ERROR, WARNING
3 ERROR, WARNING, INFORMATION

• slow query log
  慢日志开启

• log slow extra
  输出额外信息到slow log中
  Thread_id: ID
  Errno: error_number
  Bytes_received: N
  Bytes_sent: N

• long query time
  慢查询阈值

• log_slow_admin_statements
  记录以下语句慢查询
  ALTER TABLE、 ANALYZE TABLE、 CHECK TABLE、 CREATE INDEX、 DROP INDEX、 OPTIMIZE TABLE和 REPAIR TABLE。

• log_bin
  开启binlog

• binlog_format
  binlog 格式

• sync binlog

sync_binlog=0：禁用MySQL服务器将二进制日志同步到磁盘。相反，MySQL 服务器依靠操作系统不时将二进制日志刷新到磁盘，就像它对任何其他文件所做的那样。此设置提供最佳性能，但在电源故障或操作系统崩溃的情况下，服务器可能已提交尚未同步到二进制日志的事务。

sync_binlog=1：在提交事务之前启用二进制日志到磁盘的同步。这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下，二进制日志中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证二进制日志中不会丢失任何事务。

sync_binlog=N，其中N是 0 或 1 以外的值：二进制日志在 N收集二进制日志提交组后同步到磁盘。在电源故障或操作系统崩溃的情况下，服务器可能已提交尚未刷新到二进制日志的事务。由于磁盘写入次数增加，此设置可能会对性能产生负面影响。较高的值可提高性能，但会增加数据丢失的风险。

• binlog_cache_size
  事务期间用于保存二进制日志更改的内存缓冲区的大小。

• max_binlog_cache_size
  如果一个事务需要超过这个字节数的内存，服务器会生成一个Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage错误
  最大推荐值为4GB；这是因为 MySQL 当前无法处理大于 4GB 的二进制日志位置

• max_binlog_size
  如果写入二进制日志导致当前日志文件大小超过此变量的值，则服务器轮换二进制日志（关闭当前文件并打开下一个文件）

• binlog_rows_query_log_events
  把sql语句打印到binlog日志里面

• binlog_expire_logs_seconds
  设置binlog过期时间 单位 秒

参考配置文件

#
## my.cnf for 8.0版本


[client]
port	= 3306
socket	= /data/mysql/mysql.sock

[mysql]
prompt = "\\u@\\d \\r:\\m:\\s>"
no_auto_rehash
loose-skip-binary-as-hex

[mysqld]
user	= mysql
port	= 3306
authentication_policy=mysql_native_password
#主从复制或MGR集群中，server_id记得要不同
#另外，实例启动时会生成 auto.cnf，里面的 server_uuid 值也要不同
#server_uuid的值还可以自己手动指定，只要符合uuid的格式标准就可以
server_id = 3306
#basedir	= /usr/local/mysql
tmpdir=/data/tmp01:/data/tmp02:/data/tmp03
datadir	= /data/mysql
socket	= /data/mysql/mysql.sock
pid-file=/data/mysql/mysql.pid
character_set_server = UTF8MB4
skip_name_resolve = 1
secure_file_priv=""
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
#default_time_zone = "+8:00"
#启用admin_port，连接数爆满等紧急情况下给管理员留个后门
admin_address = '127.0.0.1'
admin_port = 33062
lower_case_table_names = 1
local_infile = 1

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 10240
max_connect_errors = 1000000
table_open_cache = 20000
table_definition_cache = 2000
thread_stack = 512K
sort_buffer_size = 2M
#join_buffer_size = 4M
#read_buffer_size = 8M
#read_rnd_buffer_size = 4M
#bulk_insert_buffer_size = 64M
thread_cache_size = 256
#interactive_timeout = 600
#wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = /data/logs01/mysql-error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
slow_query_log_file = /data/logs01/mysql-slow.log
long_query_time = 0.1
#log_queries_not_using_indexes = 1
#log_throttle_queries_not_using_indexes = 60
#min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_bin = /data/logs01/mysql_bin
binlog_format = ROW
sync_binlog = 1000 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#MySQL 8.0.22前，想启用MGR的话，需要设置binlog_checksum=NONE才行
binlog_checksum = CRC32


#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
relay_log = /data/logs01/relaylog
relay_log_index = /data/logs01/mysqld_relay_bin.index
relay_log_recovery = 1
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16 #可以设置为逻辑CPU数量的2倍
binlog_transaction_dependency_tracking = WRITESET
replica_preserve_commit_order = 1
replica_checkpoint_period  = 2
skip_replica_start = ON
read_only = 0 
gtid_mode = ON
enforce_gtid_consistency = ON
replica_transaction_retries    = 128


#mgr settings
#loose-plugin_load_add = 'mysql_clone.so'
#loose-plugin_load_add = 'group_replication.so'
#loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#MGR本地节点IP:PORT，请自行替换
#loose-group_replication_local_address = "172.16.16.10:33061"
#MGR集群所有节点IP:PORT，请自行替换
#loose-group_replication_group_seeds = "172.16.16.10:33061,172.16.16.11:33061,172.16.16.12:33061"
#loose-group_replication_start_on_boot = OFF
#loose-group_replication_bootstrap_group = OFF
#loose-group_replication_exit_state_action = READ_ONLY
#loose-group_replication_flow_control_mode = "DISABLED"
#loose-group_replication_single_primary_mode = ON
#loose-group_replication_communication_max_message_size = 10M
#loose-group_replication_unreachable_majority_timeout = 30
#loose-group_replication_member_expel_timeout = 5
#loose-group_replication_autorejoin_tries = 288

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:16M:autoextend
innodb_flush_log_at_trx_commit = 2 #MGR环境中由其他节点提供容错性，可不设置双1以提高本地节点性能
innodb_log_buffer_size = 500M
innodb_log_file_size = 2G #如果线上环境的TPS较高，建议加大至1G以上，如果压力不大可以调小
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_thread_concurrency = 0
innodb_read_io_threads = 48
innodb_write_io_threads = 48
innodb_commit_concurrency = 48
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 20000
innodb_io_capacity_max = 50000
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_buffer_pool_dump_pct = 75
#Innodb_io_capacity/innodb_buffer_pool_instances Innodb_io_capacity>innodb_lru_scan_depth*innodb_buffer_pool_instance
innodb_lru_scan_depth = 4000
#innodb_lock_wait_timeout = 10
#innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 1G
innodb_print_ddl_logs = 1
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log_error文件增长较快
#innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
innodb_adaptive_hash_index = OFF
#提高索引统计信息精确度
innodb_stats_persistent_sample_pages = 500

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
#innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

[mysqldump]
quick