前言

本文主要介绍当前MySQL性能优化+原理+实战，包括以下方面：
已更新文章目录
MySQL遇到的的错误及解决方法
全局参数文件配置详解。

最近一次更新时间： 2023年5月9日

更新内容：

MySQL之盛放记录的大盒子【InnoDB 数据页结构】直通车

工作忙了，暂停更新几天，顺便复习复习前面的文章。各位小伙伴们，请谅解

一、已更新文章
- Linux安装MySQL 【重新认识MySQL上篇】
- MySQL的启动关闭原理和实战、及常见的错误排查
- MySQL字符集和校验规则(史上最全)
- 是不是史上最全的MySQL用户和权限原理实战你说了算
- 看了这一篇文章，你还不懂MySQL体系结构，你来找我
- MySQL之从一条记录说起【InnoDB 记录结构上篇】
- MySQL之从一条记录说起【InnoDB 记录结构下篇】
- MySQL之盛放记录的大盒子【InnoDB 数据页结构】
- 一文带你了解MySQL数据库InnoDB_Buffer_Pool
- MySQL之Change Buffer详解
- MySQL之Adaptive Hash Index详解
- MySQL之Doublewrite Buffer详解
- MySQL之Log Buffer详解
- MySQL之Server层的内存结构（了解）
- MySQL之后台线程详解
二、参数文件详解汇总
- user
- server_id
- innodb_fast_shutdown
- innodb_force_recovery
- innodb_data_file_path
- binlog_cache_size
- max_binlog_cache_size
- max_binlog_size
- skip-grant-tables
- skip-networking
- character_set_server
- default_character_set
- default_password_lifetime
- partial_revokes
- secure_file_priv
- default_password_lifetime
- authentication_policy
- default_authentication_plugin
- password_history
- password_reuse_interval
- password_require_current
- validate_password
- wait_timeout & interactive_timeout
- innodb_buffer_pool_size
- innodb_old_blocks_pc
- innodb_old_blocks_time
- innodb_buffer_pool_instances
- innodb_buffer_pool_chunk_size
- innodb_buffer_pool_dump_pct
- innodb_buffer_pool_dump_at_shutdown && innodb_buffer_pool_load_at_startup
- innodb_change_buffer_max_size
- innodb_change_buffering
- innodb_max_dirty_pages_pct
- innodb_io_capacity
- innodb_adaptive_flushing
- innodb_purge_batch_size
- innodb_read_io_threads & innodb_write_io_thread
- innodb_adaptive_hash_index
- innodb_adaptive_flushing
- innodb_adaptive_flushing_lwm
- innodb_adaptive_hash_index_parts
- innodb_doublewrite
- Innodb_dblwr_pages_written & Innodb_dblwr_writes
- innodb_doublewrite_batch_size
- innodb_doublewrite_dir
- innodb_doublewrite_files
- innodb_doublewrite_pages
- innodb_log_buffer_size
- innodb_log_checksums
- innodb_log_compressed_pages
- innodb_log_file_size & innodb_log_files_in_group
- innodb_flush_log_at_trx_commit
- innodb_default_row_format
三、错误和解决方法汇总
- ERROR 3118
- ERROR 1820 (HY000)
- [ERROR] Could not open file ‘/data/mysql/my3306/logs/error.log’ for error logging: Permission denied
- [ERROR] unknown variable ‘basedi=/usr/local/mysql’
- [ERROR] Do you already have another mysqld server running on port: 3306
- [ERROR] Failed to open log (file ‘/data/mysql/my3306/logs/mysql-bin.000012’, errno 2)
- [ERROR] InnoDB: The Auto-extending innodb_system data file ‘./ibdata1’ is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
- ERROR 1534
- ERROR 1133
- ERROR 1396
- ERROR 1141
- ERROR 1819 (HY000)
- ERROR 3892 (HY000)
- ERROR 1045 (28000)
- ERROR 3895 (HY000)
- ERROR 3955 (HY000)
- ERROR 4052 (HY000)
- ERROR 1118 (42000)

一、已更新文章

Linux安装MySQL 【重新认识MySQL上篇】

点击此处进行跳转

一、MySQL是怎么运行的二、MySQL的安装2.1 MySQL安装方式2.2 MySQL下载2.3 前期准备工作2.4 RPM包安装MySQL 2.5 二进制安装MySQL2.6 源码安装MySQL（了解）三、多实例部署3.1 多实例概念3.2 多实例安装（二进制）3.3 mysqld_multi（多实例第二种安装方式）3.4 使用service 控制实例的启停五、数据库远程连接六、数据库卸载6.1 通用二进制 OR 源码编译安装 OR 多实例卸载MySQL6.2 RPM安装卸载总结

MySQL的启动关闭原理和实战、及常见的错误排查

点击此处进行跳转

一、生产中mysqlq启动方式1.1 mysql的启动原理1.2 参数文件的优先级1.3 以server的方式启动mysql(实际启动mysql方式)1.4 mysqld_safe方式启动1.5 mysqld方式启动1.6 systemctl方式启动(CentOS 7)二、关库2.1 相关参数innodb_fast_shutdown （数据库关闭参数）2.2 相关参数innodb_force_recovery（恢复参数）2.2 关闭mysql多种方式三、常见的mysql启动失败案例3.1 目录权限3.2 参数问题3.3 配置文件3.4 端口占用3.5 误删二进制文件3.6 undo表空间异常3.7 binlog缓冲异常四、mysql启动失败的排查方法五、连接mysql的方式5.1 本地连接5.2 远程连接六、mysql数据库用户安全策略6.1 数据库的初始化6.2 修改密码6.3 删除无用用户6.4 mysql_secure_installation（推荐使用该方式修改密码）6.5 找回丢失的用户密码

MySQL字符集和校验规则(史上最全)

点击此处进行跳转

一、mysql 字符集和校验规则二、查看字符集方法2.1 查询字符集方法2.2 查看字符集的校对规则2.3 查询当前数据库设置的字符集2.4 查看当前数据库的校对规则三、mysql字符设置3.1 字符集设置的层级关系3.2 设置mysql服务器级别的字符集3.3 设置对象字符集四、字符集案例4.1 常用的字符集汉字占多少字节4.2 大小案例五、插入中文乱码解决六、数据库常见的字符集及如何选择字符集七、生产坏境中，如何避免乱码

是不是史上最全的MySQL用户和权限原理实战你说了算

点击此处跳转

一、访问控制1.1 连接验证（阶段1）1.2 允许的连接1.3 连接的优先级1.4 请求验证（阶段二）二、用户管理2.1 新增用户2.2 用户修改2.3 删除用户2.4 查看用户三、密码管理3.1 修改密码（最终版） 3.2 密码策略 3.4 密码插件四、用户权限管理4.1 权限粒度4.2 显示用户非权限属性4.3 全局级权限4.4 库级别权限4.5 表级权限4.6 列级权限4.7 权限回收4.8 MySQL权限（MySQL 8.0 新特性）五、角色管理（MySQL 8.0 新特性）5.1 创建角色并授予权限5.2 检查角色权限5.3 撤消角色或角色权限5.4 删除角色六、资源限制6.1 用户创建指定配额6.2 修改配额

看了这一篇文章，你还不懂MySQL体系结构，你来找我

点击此处跳转

一、网络连接层二、数据库服务层2.1 连接池2.2 SQL接口2.3 解析器2.4 查询优化器2.5 缓存2.6系统管理和控制工具三、存储引擎层四、系统文件层4.1 日志文件4.2 数据文件4.3 配置文件4.4 pid文件4.5 socket文件小结

MySQL之从一条记录说起【InnoDB 记录结构上篇】

点击此处跳转

一、InnoDB⻚简介二、InnoDB⾏格式三、指定行格式的语法四、Compact⾏格式4.1 记录的额外信息4.2 记录的真实数据4.3 CHAR(M)列的存储格式

MySQL之从一条记录说起【InnoDB 记录结构下篇】

点击此处跳转

一、Redundant行格式1.1 字段长度偏移列表1.2 记录头信息1.4 1byte_offs_flag的值是怎么选择的1.5 Redundant行格式中NULL值的处理1.6 CHAR(M)列的存储格式二、行数据溢出2.1 VARCHAR(M)最多能存储的数据2.2 记录中的数据太多产⽣的溢出三、行溢出的临界点四、DYNAMIC和COMPRESSED行格式总结

MySQL之盛放记录的大盒子【InnoDB 数据页结构】

点击此处跳转

一、数据页的结构预览二、用户真实记录在数据页中的存储（Free Space）三、记录头信息引出的数据页“记录”结构3.1 delete_mask3.2 min_rec_mask3.3 n_owned3.4 heap_no3.5 record_type3.7 next_record四、Page Directory（页目录）五、Page Header（页面头部）5.1 PAGE_DIRECTION5.2 PAGE_N_DIRECTION六、File Header（文件头部）6.1 FIL_PAGE_SPACE_OR_CHKSUM6.2 FIL_PAGE_OFFSET6.3 FIL_PAGE_TYPE6.4 FIL_PAGE_PREV和FIL_PAGE_NEXT七、File Trailer（文件尾部）总结

一文带你了解MySQL数据库InnoDB_Buffer_Pool

点击此处跳转

一、InnoDB架构二、Buffer Pool2.1 什么是缓冲池2.2 缓冲池大小的设置2.3 缓冲池的管理2.4 缓冲池的高并发2.5 缓冲池数据预热（了解）2.6 缓冲池的案例

MySQL之Change Buffer详解

点击此处跳转

一、如何减少因数据写入导致的IO？二、Change Buffer工作原理2.1 Change Buffer概念2.2 Change Buffer工作原理三、Change Buffer触发时机四、Change Buffer适用场景4.1 适合开启InnoDB的写缓冲机制4.2 不适合开启InnoDB的写缓冲机制五、Change Buffer的重要的参数 5.1 innodb_change_buffer_max_size5.2 innodb_change_buffering六、Change Buffer相关监控信息

MySQL之Adaptive Hash Index详解

点击此处跳转

一、MySQL InnoDB是否支持哈希索引？1.1 InnoDB不支持Hash Index1.2 InnoDB支持Hash Index二、Adaptive Hash Index的概念三、涉及Adaptive Hash Index的参数3.1 innodb_adaptive_hash_index3.2 innodb_adaptive_flushing3.3 innodb_adaptive_flushing_lwm3.4 innodb_adaptive_hash_index_parts小提示四、准备工作五、通过聚簇索引和普通索引访问记录的过 5.1 聚簇索引访问记录过程5.2 普通索引访问记录过程六、通过Adaptive Hash Index访问记录的过程七 、Adaptive Hash Index状态监控八、Adaptive Hash Index 注意事项8.1 使用场景8.2 注意事项8.3 Adaptive Hash Index 限制

MySQL之Doublewrite Buffer详解

点击此处跳转

一、不同程序的page1.1 Linux系统的Page OR MySQL的Page 大小对比1.2 不同大小Page的程序在交互过程中会出现什么问题二、Doublewrite Buffer概念三、Doublewrite Buffer工作流程四、Doublewrite Buffer的参数4.1 innodb_doublewrite4.2 Innodb_dblwr_pages_written & Innodb_dblwr_writes4.3 innodb_doublewrite_batch_size4.4 innodb_doublewrite_dir4.5 innodb_doublewrite_files4.6 innodb_doublewrite_pages

MySQL之Log Buffer详解

点击此处跳转

一、Redo Log是什么二、Redo Log三层架构2.1 Redo Log最终落盘的步骤2.2 MySQL对上述可能存在问题的折衷方案2.3 Redo Log刷盘策略最佳实践三 、Redo Log相关参数2.1 innodb_log_buffer_size2.2 innodb_log_checksums2.3 innodb_log_compressed_pages 2.4 innodb_log_file_size & innodb_log_files_in_group2.5 innodb_flush_log_at_trx_commit

MySQL之Server层的内存结构（了解）

点击此处跳转

一、Binlog Cache1.1 Binlog Cache工作原理1.2 Binlog的写入机制1.3 Binlog相关参数1.4 Binlog刷盘策略应用场景二、Query Cache三、Key Buffer3.1 Key Buffer是什么3.2 Key Buffer相关参数四、Read Buffer & Read Rnd Buffer4.1 Read Buffer是什么4.2 Read Buffer相关参数4.3 Read Rnd Buffer是什么4.4 Read Rnd Buffer相关参数五、Sort Buffer5.1 Sort Buffer是什么5.2 Sort Buffer相关参数5.3 常见的排序算法六、Join Buffer6.1 Join Buffer是什么6.2 Join Buffer相关参数

MySQL之后台线程详解

点击此处跳转

一、Master Thread二、IO Thread三、Purge Thread四、Page Cleaner Thread

二、参数文件详解汇总

在MySQL 8.0 之前的版本中，对于全局变量的修改，其只会影响其内存值，而不会持久化到配置文件中。数据库重启，又会恢复成修改前的值。从8开始，可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。set global命令类似，只不过不会将默认值持久化到配置文件中

在数据库启动时，会首先读取其它配置文件，最后才读取mysqld-auto.cnf文件。不建议手动修改该文件，其有可能导致数据库在启动过程中因解析错误而失败。如果出现这种情况，可手动删除mysqld-auto.cnf文件或将persisted_globals_load变量设置为off来避免该文件的加载。

持久化为默认值可以通过 set persist 全局变量=default进行还原

对于已经持久化了变量，可通过reset persist命令清除掉，注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的内容，对于已经修改了的变量的值，不会产生任何影响。

但是对于read only 的变量，修改参数后需要重启才能生效

mysql> set persist innodb_log_file_size=2073741824;ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variablemysql> set persist_only innodb_log_file_size=2073741824;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'innodb_log_file_size';+----------------------+-----------+| Variable_name| Value |+----------------------+-----------+| innodb_log_file_size | 104857600 |+----------------------+-----------+1 row in set (0.02 sec)mysql> restart ;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'innodb_log_file_size';No connection. Trying to reconnect...Connection id:8Current database: *** NONE ***+----------------------+------------+| Variable_name| Value|+----------------------+------------+| innodb_log_file_size | 2073034752 |+----------------------+------------+1 row in set (0.02 sec)

user

数据初始化后，所属的用户

[mysqld]user=mysql

server_id

给mysql服务指定一个id

set persistserver_id= 2;

innodb_fast_shutdown

数据库关闭参数，在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2，默认值为1。

set persist innodb_fast_shutdown= 1;

0表示在MySQL数据库关闭时，InnoDB需要完成所有的full purge(回收页)和merge insert buffer（合并插入缓冲区），并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几个小时来完成。如果在进行InnoDB升级时，必须将这个参数调为0，然后再关闭数据库。
1是参数innodb_fast_shutdown的默认值，表示不需要完成上述的full purge和merge insert buffer操作，但是在缓冲池中的一些数据脏页还是会刷新回磁盘。生产环境推荐的参数
2表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次mysql数据库启动时，会进行恢复操作（recovery）。如果写入日志文件出现异常，可能导致数据启动不了。

innodb_force_recovery

恢复参数，一个已读的持久变量，默认值0

set persist_only innodb_force_recovery=0;

当正常关闭mysql数据库时，下次的启动应该是非常“正常”。但是如果没有正常地关闭数据库，如用kill命令关闭数据库，在mysql数据库运行中重启了服务器，或者在关闭数据库时，将参数innodb_fast_shutdown设为2，下次mysql数据库启动时都会对InnoDB存储引擎的表进行恢复操作.

参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。该参数值默认为0，代表当发生需要恢复时，进行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption(坏块)，mysqL数据库可能发生宕机（crash），并把错误写入错误日志中去。但是，在某些情况下，可能并不需要进行完整的恢复操作，比如在对一个表进行alter table操作时发生意外了，数据库重启时会对InnoDB表进行回滚操作，对于一个大表来说这需要很长时间，可能是几个小时。这时用户可以自行进行恢复，例如把表删除，从备份中重新导入数据到表，可能这些操作的速度要远远快于回滚操作。

参数innodb_force_recovery还可以设置为6个非零值：1到6。大的数字表示包含了前面所有小数字表示的影响。具体情况如下：

1（SRV_FORCE_IGNORE_CORRUPT）：忽略检查到的corrupt页。
2（SRV_FORCE_NO_BACKGROUND）：阻止Master Thread线程的运行，如MasterThread线程需要进行full purge（完整清理）操作，而这会导致crash。
3（SRV_FORCE_NO_TRX_UNDO）：不进行事务的回滚操作。
4（SRV_FORCE_NO_IBUF_MERGE）：不进行插入缓冲的合并操作。
5（SRV_FORCE_NO_UNDO_LOG_SCAN）：不查看撤销日志（Undo Log），InnoDB存储引擎会将未提交的事务视为已提交。
6（SRV_FORCE_NO_LOG_REDO）：不进行前滚的操作。

建议：生产环境只能设置为0。即遇到故障，系统就要自动进行所有恢复操作。如果启动不了，就调整该参数进行数据库的调整。

innodb_data_file_path

共享表空间ibdata1里保存元数据信息、insert buffer、double write、undo、回滚指针，在初始化数据库之前设置innodb_data_file_path的大小。重新修改配置文件中innodb_data_file_path的大小，必须是小于默认的大小（12M）或者等于默认的大小,是一个非持久已读变量

[mysqld]innodb_data_file_path=ibdata1:12M:autoextend

binlog_cache_size

binlog缓存根据实际情况调大

[mysqld] binlog_cache_size = 128M

max_binlog_cache_size

最大binlog缓存根据实际情况调大

[mysqld]max_binlog_cache_size = 512M

max_binlog_size

最大max_binlog日志根据实际情况调大

[mysqld]max_binlog_size=512M

skip-grant-tables

跳过密码验证，忘记root密码，可使用该参数，一般配合skip-networking参数一起使用

[mysqld]skip-grant-tables

skip-networking

禁止远程连接

[mysqld]skip-networking

character_set_server

服务端字符集设置，需要重启才能生效，影响的是character_set_server character_set_database 以及表的字符集

[mysqld]character_set_server=utf8

default_character_set

客户端字符集设置，不需要重启，就能影响。它影响的参数是：character_set_client character_set_connection character_set_results ，如果有别的客户端没有配置，也是根据别的客服端的设置

[client]default_character_set=utf8

default_password_lifetime

设置密码过期的全局参数，最大值65535天，当创建用户或者修改用户密码策略为非默认过期时间，该参数失效

[mysql]default_password_lifetime=180

partial_revokes

从MySQL 8.0.16开始，MySQL推出了一种部分权限回收（Partial Revokes）的功能，可以将粗粒度赋予的权限在细粒度上回收。要使用这个功能需要将系统参数 partial_revokes 设置成 on，这个参数默认是 off，即默认不允许使用部分权限回收功能，在使用时会遇到下面的错误

set persist partial_revokes = ON;

secure_file_priv

MySQL中的一个非持久已读变量，用于限制LOAD DATA INFILE和SELECT … INTO OUTFILE语句的文件读写权限。它指定了允许从哪个目录中读取或写入文件，

[mysqld]secure_file_priv = /tmp/;

default_password_lifetime

密码过期时间，是一个全局变量，可以通过set global设置，意味着服务器重启，该变量失效，可以在my.cnf文件中指定实现持久化

[mysqld]default_password_lifetime=180

default_password_lifetime=180 密码有效期为6个月，最大值65535
default_password_lifetime=0 默认是0，密码永不过期

authentication_policy

管理用户的身份认证，多因素身份认证中使用

set persist authentication_policy='sha256_password,,';

default_authentication_plugin

该参数文件是指定密码插件，一个非持久已读变量，mysql 8版本默认caching_sha2_password，mysql 5.7默认mysql_native_password

[mysqld]default_authentication_plugin=mysql_native_password

此参数也可以不设置，在创建用户时需要手动指定

CREATE USER 'user'@'host' IDENTIFIED by 'password' WITH 'mysql_native_password'

password_history

password_reuse_interval

最近使用的密码保留天数

set persist password_reuse_interval=30;

password_require_current

密码必要验证策略

set persist password_require_current=on;

validate_password

密码复杂度

[mysqld]plugin-load=validate_password.so ##开启validate-password=off ##关闭validate_password_check_user_name = on #校验验用户名validate_password_dictionary_file =/tmp/file # 字典文件，字典文件中存在的密码不得使用validate_password_length=8 # 密码的最小长度，此处为8validate_password_mixed_case_count=1 # 至少要包含小写或大写字母的个数，此处为1validate_password_number_count=1 # 至少要包含的数字的个数，此处为1validate_password_policy=0 # 密码安全策略 0~2,默认MEDIUM（2）

也可以使用如下安装

install plugin validate_password soname 'validate_password.so'

而关闭密码复杂度使用该命令

uninstall plugin validate_password

wait_timeout & interactive_timeout

关闭连接等待的时间

对于非交互式连接，类似于jdbc连接wait_timeout的值继承自服务器端全局变量wait_timeout。
对于交互式连接，类似于mysql客户端连接wait_timeout的值继承自服务器端全局变量interactive_timeout。

set persist interactive_timeout=28800;set persist wait_timeout=28800;

innodb_buffer_pool_size

缓冲池的配置通过变量innodb_buffer_pool_size来设置，通常它的大小占用内存60%-80%，MySQL默认是134217728字节，也就是128M。

set persist innodb_buffer_pool_size=11274289152;

innodb_old_blocks_pc

代表整个 LRU 链表中 young 区域与 old 区域比例，默认是 63:37

set persist innodb_old_blocks_pct = 40;

innodb_old_blocks_time

old 区域停留时间

set persist innodb_old_blocks_time= 2000;

也就说，只有同时满足被访问与在 old 区域停留时间超过 1 秒两个条件，才会被插入到 young 区域头部

innodb_buffer_pool_instances

给 MySQL 设置多个 Buffer Pool 来提升 MySQL 的并发能力,默认值1

set persist_only innodb_buffer_pool_instances=4;

innodb_buffer_pool_chunk_size

每个 chunk 的大小。默认128M

set persist_only innodb_buffer_pool_chunk_size= 132417728;

innodb_buffer_pool_dump_pct

指定每个缓冲池最近使用的页面读取和转储的百分比。范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。

set persist innodb_buffer_pool_dump_pct=40;

innodb_buffer_pool_dump_at_shutdown && innodb_buffer_pool_load_at_startup

innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。通常与innodb_buffer_pool_dump_at_shutdown结合使用

set persist innodb_buffer_pool_dump_at_shutdown = off

innodb_change_buffer_max_size

配置写缓冲的大小，占整个缓冲池的比例，默认25表示change buffer最大可以占用innodb buffer的25%，最大可设置的值为50%

 set persist innodb_change_buffer_max_size = 20

innodb_change_buffering

默认all表示所有的非唯一普通索引页写入都使用change buffer

all：所有的非唯一普通索引页写入
none：关闭change buffer
inserts：缓冲插入操作
deletes：缓冲删除操作
changes：缓冲插入和删除操作
purges：缓冲在后台发生的物理删除操作

set persist innodb_change_buffering=all;

innodb_max_dirty_pages_pct

InnoDB通过判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中的最大脏页的百分数innodb_max_dirty_pages_pct这个动态参数，如果超过了这个阈值，InnoDB认为需要做磁盘同步的操作，将100个脏页写入磁盘中，这个参数不建议调整。

 set persist innodb_max_dirty_pages_pct=90;

innodb_io_capacity

因此从InnoDB1.0.x开始，提供了动态变量innodb_io_capacity，来表示磁盘IO的吞吐量，默认为200。
当用户采用SSD或其它提高IO性能的措施后，可调整innodb_io_capacity直到符合磁盘IO的吞吐量(IPOS),但是不建议超过20000。

set persist innodb_io_capacity=300

innodb_adaptive_flushing

自适应刷新。默认开启，该值影响每秒刷新脏页的数量。具体规则：InnoDB会通过判断产生重做日志（redo log）的速度来决定最合适的刷新脏页数量。因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页，如果自己配置了innodb_io_capacity，也可以关闭，建议开启

set persist innodb_adaptive_flushing = off;

innodb_purge_batch_size

在InnoDB1.0.x版本开始引入innodb_purge_batch_size（默认值300），用于控制每次full purge操作时回收Undo页的数量

 set persist innodb_purge_batch_size=300;

innodb_read_io_threads & innodb_write_io_thread

从InnoDB1.0.x版本开始，read thread和write thread分别增大到了4个，分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置，并且读线程ID总是小于写线程。

 set persist_only innodb_write_io_threads=5;

innodb_adaptive_hash_index

该参数影响自适应哈希索引是否启用。默认情况下启用此变量。当我们禁用自适应哈希索引会立即清空哈希表。当哈希表被清空时，正常操作可以继续，并且执行使用哈希表的查询直接访问索引 B 树。当重新启用自适应散列索引时，在正常操作期间会再次填充散列表。

set persist innodb_adaptive_hash_index = on;

innodb_adaptive_flushing

该参数影响每秒刷新脏页的操作，默认情况下是启用此变量，刷新脏页会通过判断产生重做日志的速度来判断最合适的刷新脏页的数量，如果关闭该参数会导致你的MySQL的服务器的tps有明显的波动。每当重做日志写满了，MySQL就会停下手头的任务，先把脏页刷到磁盘里，才能继续干活

set persist innodb_adaptive_flushing = on;

innodb_adaptive_flushing_lwm

该参数可以设置redo log flush低水位线，当需要flush的redo log超过这个低水位时，innodb会立即启用adaptive flushing，默认值10，最小值0，最大值70

set persist innodb_adaptive_flushing_lwm= 10;

innodb_adaptive_hash_index_parts

该参数是5.7后InnoDB将自适应哈希索引进行了分区处理，每个区对应一个锁，如果大量地访问，那么可能会对性能产生影响(抢锁)，InnoDB将这个值默认设为8，最小值1，最大值512

set persist_only innodb_adaptive_hash_index_parts= 10;

innodb_doublewrite

Doublewrite Buffer是否启用开关，默认是开启状态，InnoDB将所有数据存储两次，首先到双写缓冲区，然后到实际数据文件

如果关闭，需要在my.cnf文件中指定

[mysqld]innodb_doublewrite = off

Innodb_dblwr_pages_written & Innodb_dblwr_writes

Innodb_dblwr_pages_written 是doublewrite写的总的页数，即一共写入了489页
innodb_dblwr_writes是实际写入的次数为176

如果两个计数的比例远远小于64:1，则说明系统写入压力并不高。

innodb_doublewrite_batch_size

控制要批量写入的双写页数。此变量用于高级性能调整。默认值应该适合大多数用户（默认值是0，最大值是256）

innodb_doublewrite_dir

定义了目录InnoDB创建双写文件。如果未指定innodb_data_home_dir目录，则在目录中创建双写文件，如果未指定，则默认为数据目录。

innodb_doublewrite_files

定义了双写文件的数量。默认情况下，为每个缓冲池实例创建两个双写文件。双写文件的最大数量是缓冲池实例数量的两倍。（缓冲池实例的数量由innodb_buffer_pool_instances变量控制）

Doublewrite文件名具有以下格式：
例如，以下双写文件是为页面大小为16KB和单个缓冲池的MySQL实例创建的：#ib_丨page_size_file_丨number.dblwr

所以我们的DWB文件就被创建成下面的模样：

[root@mysql2 data]# ll \#ib_16384_*-rw-r----- 1 mysql mysql327680 5月 4 12:40 #ib_16384_0.dblwr-rw-r----- 1 mysql mysql 8716288 4月26 16:54 #ib_16384_1.dblwr

innodb_doublewrite_pages

控制每个线程双写页的最大数目。如果未指定值，innodb_doublewrite_pages则设置为该innodb_write_io_threads值。此变量用于高级性能调整。默认值应该适合大多数用户。（最小值和默认值一样，都是innodb_write_io_threads的值，最大值是256）

innodb_log_buffer_size

InnoDB用于写入磁盘上的日志文件的缓冲区的大小（以字节为单位）。这就是定义我们文章标题的Log Buffer大小的参数。随着32KB和64KBinnodb_page_size值的引入，默认值从8MB更改为16MB。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将日志写入磁盘。因此，如果您有更新、插入或删除许多行的事务，则增大日志缓冲区可以节省磁盘 I/O。

set persist innodb_log_buffer_size =33554432;

innodb_log_checksums

启用或禁用重做日志页面的校验和，当innodb_log_checksums被禁用时，重做日志页面校验字段的内容被忽略，默认是开启

set persist innodb_log_checksums = on;

innodb_log_compressed_pages

日志文件页存储压缩 , 将减少redo log的写入量。默认是开启

set persist innodb_log_compressed_pages= on;

innodb_log_file_size & innodb_log_files_in_group

innodb_log_file_size是日志组每个Redo Log文件的大小，单位字节
innodb_log_files_in_group是定义日志组文件的数量

innodb_log_file_size * innodb_log_files_in_group 组合大小不能超过略小于 512GB 的最大值。从文章开头的架构图可以看出，Log Buffer也是内存+磁盘的结构，这两个参数就是定义Log Buffer磁盘结构日志文件组的，同时这两个参数也很重要。

write pos是当前记录的位置，一边写一边后移，写到第2号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的“Free”部分还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示Redo Log满了，这时候不能再执行新的更新，得停下来把checkpoint推进一下

innodb_flush_log_at_trx_commit

控制提交操作的严格ACID合规性与当与提交相关的I/O操作重新排列并批量完成时可能实现的更高性能之间的平衡。这个参数非常重要，后面的Log Buffer原理主要是关于这个参数的。设置的值不同会产生不同的效果，可设置的值可以是0、1或2。

1：表示每次事务提交都会将redo log buffer刷写到redo log
0：表示每次事务提交不会刷写到redo log，而是一秒后再刷写到redo log
2：表示每次事务提交不会刷写到redo log，而是存放到os cache，等一秒后再刷写到redo log

innodb_default_row_format

创建表是默认的行格式，默认值Dynamic

mysql> show variables like '%innodb_default_row_format%';+---------------------------+---------+| Variable_name | Value |+---------------------------+---------+| innodb_default_row_format | dynamic |+---------------------------+---------+1 row in set (0.04 sec)mysql> set persist innodb_default_row_format = 'dynamic';Query OK, 0 rows affected (0.01 sec)

三、错误和解决方法汇总

ERROR 3118

ERROR 3118 (HY000): Access denied for user ‘test’@‘localhost’. Account is locked.

错误原因：用户账号状态为锁定，登陆该账号，显示账户锁定
解决方法：修改账户状态为解锁

mysql> select user,host,account_locked from mysql.user;+---------+-----------+----------------+| user| host| account_locked |+---------+-----------+----------------+| root| localhost | N|| jeffrey | localhost | N|| test| localhost | Y|+---------+-----------+----------------+3 rows in set (0.00 sec)mysql> alter user 'test'@'localhost' account unlock;Query OK, 0 rows affected (0.00 sec)mysql> select user,host,account_locked from mysql.user;+---------+-----------+----------------+| user| host| account_locked |+---------+-----------+----------------+| root| localhost | N|| jeffrey | localhost | N|| test| localhost | N|+---------+-----------+----------------+3 rows in set (0.00 sec)

ERROR 1820 (HY000)

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

错误原因：用户密码到期
解决方法：修改密码，重新登陆即可

mysql> show create user 'test'@'localhost' \g;+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| CREATE USER for test@localhost|+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)ERROR: No query specifiedmysql> alter user 'test'@'localhost' identified by '123456';Query OK, 0 rows affected (0.01 sec)

[ERROR] Could not open file ‘/data/mysql/my3306/logs/error.log’ for error logging: Permission denied

[ERROR] Could not open file ‘/data/mysql/my3306/logs/error.log’ for error logging: Permission denied
原因：数据目录的用户和用户组不是我们设置的用户
解决：

[root@mysql2 ~]# chown -R mysql:mysql /data/*[root@mysql2 ~]# chmod -R 750 /data/mysql/

[ERROR] unknown variable ‘basedi=/usr/local/mysql’

[ERROR] unknown variable ‘basedi=/usr/local/mysql’
在修改配置文件参数时，可能因为参数拼写错误或者参数因版本不再支持，在错误日志会记录内容，在mysql启动关闭及在运行时的错误
解决：在配置文件将错误的参数修改

[ERROR] Do you already have another mysqld server running on port: 3306

[ERROR] Do you already have another mysqld server running on port: 3306
端口被占用导致MySQL启动失败
在多实例的环境下，因为某几个实例的端口是一样的，导致启动时造成端口的冲突，而无法启动，我们需要在my.cnf参数文件中修改端口

[ERROR] Failed to open log (file ‘/data/mysql/my3306/logs/mysql-bin.000012’, errno 2)

[ERROR] Failed to open log (file ‘/data/mysql/my3306/logs/mysql-bin.000012’, errno 2)
二进制日志文件保的记录已提交的dml、ddl、dcl，主要用来主从复制、增量备份，而我们手工删除二进制文件导致mysql启动失败
解决：vim mysql-bin.index 将对应的二进制日志文件记录删除即可

[ERROR] InnoDB: The Auto-extending innodb_system data file ‘./ibdata1’ is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!

[ERROR] InnoDB: The Auto-extending innodb_system data file ‘./ibdata1’ is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
修改共享表空间参数大小导致MySQL启动失败,而共享表空间ibdata1里保存元数据信息、insert buffer、double write、undo、回滚指针

innodb_data_file_path=ibdata1:12M:autoextend

解决：在初始化数据库之前设置innodb_data_file_path的大小。重新修改配置文件中innodb_data_file_path的大小，必须是小于默认的大小（12M）或者等于默认的大小

ERROR 1534

ERROR 1534 (HY000): Writing one row to the row-based binary log failed
报错

mysql> call insert_emp(1,4000000);ERROR 1534 (HY000): Writing one row to the row-based binary log failed+---------------------+| @@binlog_cache_size |+---------------------+| 2097152 |+---------------------+1 row in set (0.00 sec)

因为内存不足导致的binlog cache size不够不能写入binlog，导致语句无法执行在配置文件中调整binlog_cache_size和max_binlog_cache_size参数的值，改大一点

binlog_cache_size = 128M(binlog缓存根据实际情况调大)
max_binlog_cache_size = 512M (binlog日志根据实际情况调大)

ERROR 1133

ERROR 1133 (42000): Can’t find any matching row in the user table
原因：没有该用户
解决方法：确认该用户是否存在

ERROR 1396

ERROR 1396 (HY000): Operation CREATE USER failed for ‘test’@‘localhost’
原因：创建用户时，用户存在
解决方法：删除用户重新创建或者创建用户名不存在的用户

ERROR 1141

原因：从MySQL 8.0.16开始，MySQL推出了一种部分权限回收（Partial Revokes）的功能，可以将粗粒度赋予的权限在细粒度上回收。要使用这个功能需要将系统参数 partial_revokes 设置成 on，这个参数默认是 off，即默认不允许使用部分权限回收功能，在使用时会遇到下面的错误：

mysql> revoke select on mysql.* from 'grant'%'localhost';ERROR 1141 (42000): There is no such grant defined for user 'grant' on host localhost

解决方法：要允许 revoke的操作，请启用 partial_revokes：

mysql> set persist partial_revokes = ON;Query OK, 0 rows affected (0.00 sec)

配置正确后还错误，请检查revoke的权限是否在用户权限里(粒度)

ERROR 1819 (HY000)

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
原因：不满足密码复杂度
解决方法：更改密码符合密码复杂度策略

ERROR 3892 (HY000)

ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it
原因：密码必要验证策略，修改密码时当前密码需要在REPLACE子句中指定
解决方法：修改密码时当前密码需要在REPLACE子句中指定

mysql> alter user user() identified by '123456' replace '123456';Query OK, 0 rows affected (0.01 sec)

ERROR 1045 (28000)

ERROR 1045 (28000): Access denied for user ‘test1’@‘localhost’ (using password: YES)
原因：密码错误
解决方法：输入正确的密码

ERROR 3895 (HY000)

ERROR 3895 (HY000): Current password can not be retained for user ‘test1’@‘localhost’ because new password is empty.
原因：双密码策略，当前密码为空，旧密码无法使用
解决方法：修改密码时，当前密码不为空

ERROR 3955 (HY000)

ERROR 3955 (HY000): Access denied for user ‘test3’@‘localhost’. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
原因：失败登陆追踪，输入错误密码次数超过指定设置
解决方法：联系管理员修改密码或者解锁

ERROR 4052 (HY000)

ERROR 4052 (HY000): Invalid plugin “mysql_native_password” specified as 2 factor during “CREATE USER”.
原因：多因素身份认证，同时用2种内部存储插件
解决方法：装一个可插拔插件 Socket Peer-Credential等

mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';Query OK, 0 rows affected (0.00 sec)mysql> create user 'test7'@'localhost' identified by '123456' and identified with auth_socket as 'root';Query OK, 0 rows affected (0.05 sec)

ERROR 1118 (42000)

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

原因：行数据溢出
解决方法：更换字段类型

很不错的一篇文章，值得点赞收藏，带你全面了解MySQL性能调优、错误代码总结和全局参数配置（持续更新中ing）

目录

一、已更新文章

Linux安装MySQL 【重新认识MySQL上篇】

MySQL的启动关闭原理和实战、及常见的错误排查

MySQL字符集和校验规则(史上最全)

是不是史上最全的MySQL用户和权限原理实战你说了算

看了这一篇文章，你还不懂MySQL体系结构，你来找我

MySQL之从一条记录说起 【InnoDB 记录结构上篇】

MySQL之从一条记录说起 【InnoDB 记录结构下篇】

MySQL之盛放记录的大盒子 【InnoDB 数据页结构】

一文带你了解MySQL数据库InnoDB_Buffer_Pool

MySQL之Change Buffer详解

MySQL之Adaptive Hash Index详解

MySQL之Doublewrite Buffer详解

MySQL之Log Buffer详解

MySQL之Server层的内存结构（了解）

MySQL之后台线程详解

二、参数文件详解汇总

user

server_id

innodb_fast_shutdown

innodb_force_recovery

innodb_data_file_path

binlog_cache_size

max_binlog_cache_size

max_binlog_size

skip-grant-tables

skip-networking

character_set_server

default_character_set

default_password_lifetime

partial_revokes

secure_file_priv

default_password_lifetime

authentication_policy

default_authentication_plugin

password_history

password_reuse_interval

password_require_current

validate_password

wait_timeout & interactive_timeout

innodb_buffer_pool_size

innodb_old_blocks_pc

innodb_old_blocks_time

innodb_buffer_pool_instances

innodb_buffer_pool_chunk_size

innodb_buffer_pool_dump_pct

innodb_buffer_pool_dump_at_shutdown && innodb_buffer_pool_load_at_startup

innodb_change_buffer_max_size

innodb_change_buffering

innodb_max_dirty_pages_pct

innodb_io_capacity

innodb_adaptive_flushing

innodb_purge_batch_size

innodb_read_io_threads & innodb_write_io_thread

innodb_adaptive_hash_index

innodb_adaptive_flushing

innodb_adaptive_flushing_lwm

innodb_adaptive_hash_index_parts

innodb_doublewrite

Innodb_dblwr_pages_written & Innodb_dblwr_writes

innodb_doublewrite_batch_size

innodb_doublewrite_dir

innodb_doublewrite_files

innodb_doublewrite_pages

innodb_log_buffer_size

innodb_log_checksums

innodb_log_compressed_pages

innodb_log_file_size & innodb_log_files_in_group

innodb_flush_log_at_trx_commit

innodb_default_row_format

三、错误和解决方法汇总

ERROR 3118

ERROR 1820 (HY000)

[ERROR] Could not open file ‘/data/mysql/my3306/logs/error.log’ for error logging: Permission denied

[ERROR] unknown variable ‘basedi=/usr/local/mysql’

[ERROR] Do you already have another mysqld server running on port: 3306

[ERROR] Failed to open log (file ‘/data/mysql/my3306/logs/mysql-bin.000012’, errno 2)

[ERROR] InnoDB: The Auto-extending innodb_system data file ‘./ibdata1’ is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!

MySQL之从一条记录说起【InnoDB 记录结构上篇】

MySQL之从一条记录说起【InnoDB 记录结构下篇】

MySQL之盛放记录的大盒子【InnoDB 数据页结构】