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

[mysql] #prompt="\u@mysqldb \R:\m:\s [\d]> " #关闭自动补全sql命令功能 no-auto-rehash

##################################### 服务端参数配置 ##################################### [mysqld] port = 3306 datadir = /usr/local/mysql/data log-error = /usr/local/mysql/data/error.log pid-file = /usr/local/mysql/data/mysql.pid

只能用 IP 地址检查客户端的登录，不用主机名

skip_name_resolve = 1

指定时间存储默认时区

default_time_zone = "+8:00"

数据库默认字符集, 主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）

character-set-server = utf8mb4

数据库字符集对应一些排序等规则，注意要和 character-set-server 对应

collation-server = utf8mb4_general_ci

设置 client 连接 mysql 时的字符集, 防止乱码

init_connect='SET NAMES utf8mb4'

是否对 sql 语句大小写敏感，1: 不敏感

#lower_case_table_names = 1

执行 sql 的模式，规定了 sql 的安全等级, 暂时屏蔽，my.cnf 文件中配置报错

#sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

事务隔离级别，默认为可重复读，mysql 默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）

transaction_isolation = READ-COMMITTED

TIMESTAMP 如果没有显示声明 NOT NULL，允许NULL值

explicit_defaults_for_timestamp = true

控制 mysqld 进程能使用的最大文件描述(FD)符数量。

需要注意的是这个变量的值并不一定是你设定的值，mysqld 会在系统允许的情况下尽量获取更多的 FD 数量

open_files_limit = 65535

最大连接数

max_connections = 300

最大错误连接数

max_connect_errors = 600

MySQL 暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中

官方建议 back_log = 50 + (max_connections / 5), 封顶数为 65535, 默认值 = max_connections

back_log = 110

为所有线程打开的表的数量

例如，对于200个并发运行的连接，指定表缓存大小至少为 200 * N

其中N是您执行的任何查询中每个连接的最大表数

table_open_cache = 600

可以存储在定义缓存中的表定义数量, MIN(400 + table_open_cache / 2, 2000)

table_definition_cache = 700

为了减少会话之间的争用，可以将 opentables 缓存划分为 table_open_cache/table_open_cache_instances个小缓存

table_open_cache_instances = 64

每个线程的堆栈大小 如果线程堆栈太小，则会限制执行复杂 SQL 语句

thread_stack = 512K

禁止外部系统锁

external-locking = FALSE

SQL 数据包发送的大小，如果有 BLOB 对象建议修改成 1G

max_allowed_packet = 128M

order by / group by 时用到, 建议先调整为4M，后期观察调整

sort_buffer_size = 4M

inner left / right join 时用到, 建议先调整为4M，后期观察调整

join_buffer_size = 4M

如果您的服务器每秒达到数百个连接，则通常应将 thread_cache_size 设置得足够高，以便大多数新连接使用缓存线程

default value = 8 + ( max_connections / 100) 上限为 100

thread_cache_size = 20

MySQL 连接闲置超过一定时间后(单位：秒)将会被强行关闭

MySQL 默认的 wait_timeout 值为8个小时, interactive_timeout 参数需要同时配置才能生效

interactive_timeout = 1800 wait_timeout = 1800

Metadata Lock最大时长（秒），一般用于控制 alter 操作的最大时长 sine mysql5.6

执行 DML 操作时除了增加innodb事务锁外还增加 Metadata Lock，其他 alter（DDL）session 将阻塞

lock_wait_timeout = 3600

内部内存临时表的最大值。

比如大数据量的 group by / order by 时可能用到临时表，

超过了这个值将写入磁盘，系统IO压力增大

tmp_table_size = 64M max_heap_table_size = 64M

##################################### 慢 SQL 日志记录 #####################################

是否启用慢查询日志，1：启用，0：禁用

slow_query_log = 1

记录系统时区

log_timestamps = SYSTEM

指定慢查询日志文件的路径和名字

slow_query_log_file = /usr/local/mysql/data/slow.log

慢查询执行的秒数，必须达到此值可被记录

long_query_time = 5

将没有使用索引的语句记录到慢查询日志

log_queries_not_using_indexes = 0

设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间

log_throttle_queries_not_using_indexes = 60

对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中

min_examined_row_limit = 5000

记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。

log_slow_admin_statements = 0

作为从库时生效, 从库复制中如何有慢sql也将被记录

对于 ROW 格式 binlog，不管执行时间有没有超过阈值，都不会写入到从库的慢查询日志

log_slow_slave_statements = 1

##################################### BinLog 设置 ##################################### server-id = 110

开启 bin log 功能

log-bin=mysql-bin

binlog 记录内容的方式，记录被操作的每一行

binlog_format = ROW

对于 binlog_format = ROW 模式时，FULL 模式可以用于误操作后的 flashBack。

如果设置为 MINIMAL，则会减少记录日志的内容，只记录受影响的列，但对于部分 update 无法 flashBack

binlog_row_image = FULL

bin log 日志保存的天数

如果 binlog_expire_logs_seconds 选项也存在则 expire_logs_days 选项无效

expire_logs_days 已经被标注为过期参数

#expire_logs_days = 7 binlog_expire_logs_seconds = 1209600

master status and connection information 输出到表 mysql.slave_master_info 中

master_info_repository = TABLE

the slave's position in the relay logs 输出到表 mysql.slave_relay_log_info 中

relay_log_info_repository = TABLE

作为从库时生效, 想进行级联复制，则需要此参数

log_slave_updates

作为从库时生效, 中继日志 relay-log 可以自我修复

relay_log_recovery = 1

作为从库时生效, 主从复制时忽略的错误

如果在备份过程中执行ddl操作，从机需要从主机的备份恢复时可能会异常，从而导致从机同步数据失败

如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

slave_skip_errors = ddl_exist_errors

##################################### RedoLog 日志和 BinLog 日志的写磁盘频率设置 #####################################

RedoLog 日志（用于增删改事务操作） + binlog 日志（用于归档，主从复制）

为什么会有两份日志呢？

因为最开始 MySQL 没有 InnoDB 引擎,自带 MyISAM 引擎没有 crash-safe 能力，binlog 日志只用于归档

InnoDB 引擎是另一个公司以插件形式引入 MySQL 的，采用 RedoLog 日志来实现 crash-safe 能力

redo log 的写入（即事务操作）拆成两阶段提交（2PC）：prepare 阶段 和 commit 阶段

(事务步骤1) 执行 commit 命令，InnoDB redo log 写盘，然后告知 Mysql 执行器:[你可以写 binlog 了，且一并提交事务]，事务进入 prepare 状态

(事务步骤2) 如果前面 prepare 成功，Mysql 执行器生成 binlog 并且将 binlog 日志写盘

(事务步骤3) 如果 binlog 写盘成功，Mysql 执行器一并调用 InnoDB 引擎的提交事务接口，事务进入 commit 状态，操作完成，事务结束

参数设置成 1，每次事务都直接持久化到磁盘

参数设置成 0，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。

参数设置成 2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

即便都设置为1，服务崩溃或者服务器主机 crash，Mysql 也可能丢失但最多一个事务

控制 redolog 写磁盘频率，默认为1

innodb_flush_log_at_trx_commit = 1

控制 binlog 写磁盘频率

sync_binlog = 1

一般数据库中没什么大的事务，设成 1~2M，默认 32kb

binlog_cache_size = 4M

binlog 能够使用的最大 cache 内存大小

max_binlog_cache_size = 2G

单个 binlog 文件大小 默认值是 1GB

max_binlog_size = 1G

开启 GTID 复制模式

gtid_mode = on

强制 gtid 一致性，开启后对于 create table ... select ...或 CREATE TEMPORARY TABLE 将不被支持

enforce_gtid_consistency = 1

解决部分无主键表导致的从库复制延迟问题

其基本思路是对于在一个 ROWS EVENT 中的所有前镜像收集起来，

然后在一次扫描全表时，判断 HASH 中的每一条记录进行更新

该参数已经被标注为过期参数

#slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'

default value is CRC32

#binlog_checksum = 1

default value is ON

#relay-log-purge = 1

##################################### MyISAM 性能设置 #####################################

对 MyISAM 表起作用，但是内部的临时磁盘表是 MyISAM 表，也要使用该值。

可以使用检查状态值 created_tmp_disk_tables 得知详情

key_buffer_size = 15M

对 MyISAM 表起作用，但是内部的临时磁盘表是 MyISAM 表，也要使用该值，

例如大表 order by、缓存嵌套查询、大容量插入分区。

read_buffer_size = 8M

对 MyISAM 表起作用 读取优化

read_rnd_buffer_size = 4M

对 MyISAM 表起作用 插入优化

bulk_insert_buffer_size = 64M

##################################### innodb 性能设置 #####################################

Defines the maximum number of threads permitted inside of InnoDB.

A value of 0 (the default) is interpreted as infinite concurrency (no limit)

innodb_thread_concurrency = 0

一般设置物理存储的 60% ~ 70%

innodb_buffer_pool_size = 8G

当缓冲池大小大于 1GB 时，将 innodb_buffer_pool_instances 设置为大于 1 的值，可以提高繁忙服务器的可伸缩性

innodb_buffer_pool_instances = 4

默认启用。指定在 MySQL 服务器启动时，InnoDB 缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown 结合使用

innodb_buffer_pool_load_at_startup = 1

默认启用。指定在 MySQL 服务器关闭时是否记录在 InnoDB 缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程

innodb_buffer_pool_dump_at_shutdown = 1

定义 InnoDB 系统表空间数据文件的名称、大小和属性

#innodb_data_file_path = ibdata1:1G:autoextend

InnoDB 用于写入磁盘日志文件的缓冲区大小（以字节为单位）。默认值为 16MB

innodb_log_buffer_size = 32M

InnoDB 日志文件组数量

innodb_log_files_in_group = 3

InnoDB 日志文件组中每一个文件的大小

innodb_log_file_size = 2G

是否开启在线回收（收缩）undo log 日志文件，支持动态设置，默认开启

innodb_undo_log_truncate = 1

当超过这个阀值（默认是1G），会触发 truncate 回收（收缩）动作，truncate 后空间缩小到 10M

innodb_max_undo_log_size = 4G

The path where InnoDB creates undo tablespaces

没有配置则在数据文件目录下

innodb_undo_directory = /usr/mysql-8.0.32/undolog

用于设定创建的 undo 表空间的个数

已经弃用了，只能手动添加 undo 表空间

innodb_undo_tablespaces 变量已弃用，从 MySQL 8.0.14 开始不再可配置

innodb_undo_tablespaces = 95

提高刷新脏页数量和合并插入数量，改善磁盘 I/O 处理能力

根据您的服务器 IOPS 能力适当调整

一般配普通 SSD 盘的话，可以调整到 10000 - 20000

配置高端 PCIe SSD 卡的话，则可以调整的更高，比如 50000 - 80000

innodb_io_capacity = 4000 innodb_io_capacity_max = 8000

如果打开参数 innodb_flush_sync, checkpoint 时，flush 操作将由 page cleaner 线程来完成，此时 page cleaner 会忽略 io capacity 的限制，进入激烈刷脏

innodb_flush_sync = 0 innodb_flush_neighbors = 0

CPU 多核处理能力设置，假设 CPU 是 4 颗 8 核的，设置如下

读多，写少可以设成 2:6 的比例

innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50

该参数针对 unix、linux，window 上直接注释该参数.默认值为 NULL

O_DIRECT 减少操作系统级别 VFS 的缓存和 Innodb 本身的 buffer 缓存之间的冲突

innodb_flush_method = O_DIRECT

innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32

为了获取被锁定的资源最大等待时间，默认 50 秒，超过该时间会报如下错误:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

innodb_lock_wait_timeout = 20

默认 OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作。如果设置 ON，则整个事务都会回滚

innodb_rollback_on_timeout = 1

强所有发生的死锁错误信息记录到 error.log 中，之前通过命令行只能查看最近一次死锁信息

innodb_print_all_deadlocks = 1

在创建 InnoDB 索引时用于指定对数据排序的排序缓冲区的大小

innodb_sort_buffer_size = 67108864

控制着在向有 auto_increment 列的表插入数据时，相关锁的行为，默认为 2

0：traditonal（每次都会产生表锁）

1：consecutive（ mysql 的默认模式，会产生一个轻量锁，simple insert 会获得批量的锁，保证连续插入）

2：interleaved（不会锁表，来一个处理一个，并发最高）

innodb_autoinc_lock_mode = 1

表示每个表都有自已独立的表空间

innodb_file_per_table = 1

指定 Online DDL 执行期间产生临时日志文件的最大大小，单位字节，默认大小为 128MB。

日志文件记录的是表在 DDL 期间的数据插入、更新和删除信息(DML操作)，一旦日志文件超过该参数指定值时，

DDL 执行就会失败并回滚所有未提交的当前 DML 操作，所以，当执行 DDL 期间有大量 DML 操作时可以提高该参数值，

但同时也会增加 DDL 执行完成时应用日志时锁定表的时间

innodb_online_alter_log_max_size = 4G

[mysqldump] quick max_allowed_packet = 128M