MySQL学习笔记（2）：配置和状态
本文更新于2020-05-30，使用MySQL 5.7，操作系统为Deepin 15.4。
配置文件
Windows下配置文件的读取顺序：
- %WINDIR%/my.ini
- C:/my.cnf
- INSTALL_DIR/my.ini，INSTALL_DIR为实际的安装目录
- --defaults-extra-file=xxx指定的文件
Linux下配置文件的读取顺序：
- /etc/my.cnf
- INSTALL_DIR/my.cnf，INSTALL_DIR为实际的安装目录
- --defaults-extra-file=xxx指定的文件
- ~/.my.cnf
配置文件内容分为多个组：
- [mysql]：配置mysql客户端工具。如default-character-set可配置默认客户端、连接、返回结果字符集。
- [mysqld]：配置mysqld服务器。
- [mysqldump]：配置mysqldump工具。
系统变量
关于系统变量的详情参阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html。
关于InnoDB系统变量的详情参阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html。
MySQL服务器可使用以下方式配置，各种方式的参数名相似：
- 系统变量：可使用
SHOW VARIABLES [LIKE "name"]或SELECT @@name查看，使用SET [GLOBAL|SESSION] [@@]name = value设置。参数名使用“_”分隔单词。 - 命令行参数：即mysqld的命令行参数。参数名加上“--”前缀，使用“-”分隔单词。可通过
mysqld --help --verbose可查看命令行参数的使用及从当前配置读取的系统变量值。 - 配置文件：配置文件的[mysqld]组，参数名使用“-”或“_”分隔单词。
参数值如为布尔类型，设置为0、OFF为假，1、ON为真。命令行参数和配置文件，不指定值时表示真。
系统变量 命令行参数 配置文件 说明
autocommit
是否自动提交事务
back_log
TCP监听端口新连接的等待队列大小
--binlog-do-db=
只有指定的库才记录二进制日志，可指定多次
binlog_format
--binlog-format=
二进制日志格式，，可为STATEMENT、ROW、MIXED
--binlog-ignore-db=
只有指定的库才不记录二进制日志，可指定多次
bulk_insert_buffer_size
每个线程MyISAM批量插入的缓存区大小
character_set_client
客户端字符集
character_set_connection
连接字符集，字符串常量的字符集也由此指定
character_set_database
默认库的字符集
character_set_results
返回给客户端的结果字符集
character_set_server
服务器字符集
collation_database
默认库的字符集校对规则
collation_server
服务器字符集校对规则
concurrent_insert
MyISAM表并发插入规则。NEVER或0不允许并发插入；AUTO或1当MyISAM表没有空洞时，允许一个线程读表的同时另一个线程从表尾插入；ALWAYS或2无论表有无空洞，都允许在表尾并发插入
datadir
数据目录。默认为/var/lib/mysql/
end_markers_in_json
设置trace分析优化器以JSON格式输出
event_scheduler
是否打开事件调度器
expire_logs_days
--expire-logs-days=
二进制日志过期天数
foreign_key_checks
是否进行外键检查
--general-log
是否启用查询日志
--general-log-file=
查询日志文件名，默认为“主机名.log”，默认保存在datadir指定的目录中
have_query_cache
服务器在安装时是否已经配置高速缓存
init_file
服务器启动后执行的SQL文件
innodb_buffer_pool_instances
InnoDB缓存池实例数，每个实例平分缓存池的大小
innodb_buffer_pool_size
InnoDB表数据和索引数据的最大缓存池大小
innodb_data_file_path
InnoDB共享表空间文件，格式为file_name:file_size[:autoextend[:max:max_file_size]]，可以“;”分隔
innodb_data_home_dir
InnoDB共享表空间目录
innodb_doublewrite
InnoDB是否开启双写策略
innodb_file_per_table
InnoDB是否使用多表空间的存储方式
innodb_flush_log_at_trx_commit
InnoDB事务提交时刷新日志的规则。0在事务提交时，不会立即将缓存中的redo日志写到磁盘文件，而是每秒触发一次，并调用操作系统fsync刷新IO缓存，若数据库崩溃，数据就会丢失；1在事务提交时，立即将缓存中的redo日志回写到磁盘文件，并调用操作系统fsync刷新IO缓存；2在事务提交时，立即将缓存中的redo日志回写到磁盘文件，但并不马上调用fsync刷新IO缓存，而是每秒触发一次，若数据库崩溃，只要操作系统没有崩溃，数据就不会丢失
innodb_io_capacity
InnoDB后台任务每秒的IO次数，包括刷新缓存池脏页等
innodb_lock_wait_timeout
InnoDB事务等待行锁的超时时间
innodb_locks_unsafe_for_binlog
InnoDB是否在执行INSERT INTO ... SELECT ...和CREATE TABLE ... SELECT ...时对源表记录加锁，如否则可能无法使用binlog正确恢复或复制数据
innodb_log_buffer_size
InnoDB日志缓存池大小
innodb_log_file_size
InnoDB日志文件大小
innodb_lru_scan_depth
InnoDB缓存池无可用数据页时扫描LRU list尾部数据页的个数。其后进行相关磁盘刷新操作，淘汰的页放入free list中
innodb_max_dirty_pages_pct
InnoDB缓存池中脏页的最大比例
innodb_old_blocks_pct
InnoDB缓存池LRU list中old sublist的比例
innodb_old_blocks_time
InnoDB缓存池LRU list中数据块从old sublist转移到new sublist的毫秒数
innodb_table_locks
InnoDB能否感知MySQL Server加的表级锁，同时MySQL Server能否感知InnoDB加的行级锁。能感知才能自动识别涉及表级锁的死锁
join_buffer_size
每个线程的表连接缓存区大小
key_cache_age_threshold
MyISAM索引缓存块由hot子表向warm子表降级的时间。对于有N个块的索引缓存来说，如果一个在hot子表头部的索引块在最后N*key_cache_age_threshold/100次缓存命中未被访问过，就会被降级到warm子表
key_cache_block_size
MyISAM索引缓存块大小
key_cache_division_limit
MyISAM索引缓存用做warm子表的比例。默认值为100，亦即不启用中点插入策略
key_buffer_size
MyISAM索引缓存区的大小。可使用
--local-infile
是否允许LOAD DATA LOCAL INFILE从客户端本地读取文件
log_bin
--log-bin[=]
是否开启二进制日志。命令行参数可指定文件名，，默认为“主机名-bin”，默认保存在datadir指定的目录中
log_bin_basename
二进制日志文件名（不带目录，实际文件名会追加形如.000001的编号）
log_bin_index
二进制日志文件的索引文件
log_error
--log-error[=]
错误日志文件名，默认为“主机名.err”，默认保存在datadir指定的目录中
--log-output=
查询日志和慢查询日志的保存方式。NONE不保存，其优先级最高；TABLE保存至表，查询日志保存至mysql库general_log表，慢查询日志保存至msql库slow_log表；FILE保存至文件，为默认值。可使用“,”分隔
--log-queries-not-using-indexes
是否将不使用索引的查询写入慢查询日志
--log-slave-updates
从库是否写二进制日志。如果从库同时也作为主库，则需打开这个选项，还需指定--log-bin
--log-slow-admin-statements
是否将执行慢的管理语句写入慢查询日志
long_query_time
执行时间（不计入获得锁的时间）超过该值的查询将写入慢查询日志
low_priority_updates
是否使MyISAM存储引擎的写请求比读请求优先级更低
lower_case_table_names
表名大小写规则。0磁盘存储的表名区分大小写，比较也区分大小写，Linux的默认值；1磁盘存储的表名为小写，比较不区分大小写，Windows的默认值；2磁盘存储的表名区分大小写，比较不区分大小写，Mac的默认值
--master-connect-retry=
从库在与主库的连接丢失时重试的时间间隔
max_connections
MySQL数据库的最大连接数
max_heap_table_size
Memory存储引擎最大的表大小
max_length_for_sort_data
对filesort，如查询到的字段总大小小于该值，则使用一次扫描算法，否则使用两次扫描算法
max_write_lock_count
表级锁获取写锁的最大次数，此后会允许获取一次读锁
min_examined_row_limit
扫描记录数不少于该值的查询将写入慢查询日志
open_files_limit
打开的文件描述符最大数量
optimizer_trace
优化器trace设置
optimizer_trace_max_size
化器trace使用的最大内存大小
profiling
是否打开profiling
query_cahce_size
查询缓存大小
query_cache_type
查询缓存使用规则。0或OFF关闭查询缓存；1或ON打开查询缓存（使用SQL_NO_CACHE除外）；2或DEMAND只缓存使用SQL_CACHE的查询
read_buffer_size
MyISAM表顺序读取的缓存大小，为每个线程独占
--read-only
从库是否只接受root的更新操作
read_rnd_buffer_size
MyISAM表随机读取的缓存大小，为每个线程独占
--replicate-do-db=
从库复制的库
--replicate-do-table=
从库复制的表
--replicate-ignore-db=
从库忽略复制的库
--replicate-ignore-table=
从库忽略复制的表
--replicate-wild-go-table=
以通配符方式指定从库复制的表
--replicate-wild-ignore-table=
以通配符方式指定从库忽略复制的表
--safe-user-create
是否不能用GRANT创建新用户，除非用户有mysql库user表的INSERT权限
--skip-grant-tables
是否令服务器跳过权限系统的使用
--skip-networking
是否不允许TCP/IP连接，而必须使用命名管道、共享内存、UNIX套接字
--skip-show-database
是否有SHOW DATABASES权限的用户才能执行且显示所有库名；否则所有用户都能执行，但只显示有权限的库名
--slave-skip-errors=
复制过程中从库可以自动跳过的错误号
slow_query_log
--slow-query-log
是否启用慢查询日志
slow_query_log_file
--slow-query-log-file[=]
慢查询日志文件名，默认为“主机名-slow.log”，默认保存在datadir指定的目录中
sort_buffer_size
内存排序缓存区大小，为每个线程独占
sql_log_bin
是否写入二进制日志。具有SUPER权限可禁止将自己的语句写入二进制日志
sql_log_off
是否关闭查询日志，只针对当前连接
sql_mode
SQL Mode
--ssl
是否支持SSL连接。如支持SSL，还需指定--ssl-ca、--ssl-cert、--ssl-key
sync_binlog
--sync-binlog=
二进制日志缓存刷新规则。0由文件系统控制二进制日志缓存的刷新，大于0表示每若干条二进制日志刷新文件系统缓存
table_open_cache
打开并缓存的所有表数量
thread_cache_size
缓存可重用的线程数量
tx_isolation
事务隔离级别
unique_checks
是否开启唯一性检查
状态变量
关于状态变量的概要参阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-status-variable-reference.html。
关于状态变量的详情参阅官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html。
状态变量 说明
Com_commit
事务提交的次数
Com_delete
执行DELETE的次数，不论提交还是回滚都会累加
Com_insert
执行INSERT的次数，不论提交还是回滚都会累加，对批量插入的操作只累加一
Com_rollback
事务回滚的次数
Com_select
执行SELECT的次数，不论提交还是回滚都会累加
Com_update
执行UPDATE的次数，不论提交还是回滚都会累加
Connection_errors_max_connections
因为超过数据库最大连接数而被拒绝的连接数
Connections
连接服务器的历史总次数
Handler_read_key
使用索引读取的行数，越高表示索引越经常使用
Handler_read_rnd_next
在数据文件读下一行的次数，越高表示进行越多表扫描
Innodb_buffer_pool_read_requests
从InnoDB缓存池读取的次数
Innodb_buffer_pool_reads
从磁盘读取到InnoDB缓存池的次数
Innodb_buffer_pool_wait_free
Innodb的IO线程从数据文件中读取了数据要写入缓存池的时候，需要等待空闲页的次数
InnoDB_row_lock_time_avg
InnoDB行锁平均等待时间
InnoDB_row_lock_waits
InnoDB航所等待次数
Innodb_rows_deleted
InnoDB执行DELETE删除的行数
Innodb_rows_inserted
InnoDB执行INSERT插入的行数
Innodb_rows_read
InnoDB执行SELECT返回的行数
Innodb_rows_updated
InnoDB执行UPDATE更新的行数
Key_blocks_unused
MyISAM未使用的索引缓存块数
Key_read_requests
从MyISAM索引缓存区读取的次数
Key_reads
从磁盘读取至MyISAM索引缓存区的次数
Key_write_requests
写入至MyISAM索引缓存区的次数
Key_writes
从MyISAM索引缓存区写入至磁盘的次数
Open_tables
打开并缓存的表数量
Qcache_free_blocks
查询缓存的空闲内存块数
Qcache_free_memory
查询缓存的空闲内存总数
Qcache_hits
查询缓存命中数
Qcache_inserts
被加入到查询缓存中的查询数目
Qcache_lowmem_prunes
因缺少内存而被从查询缓存中删除的查询数
Qcache_not_cached
没有被查询缓存缓存的查询数
Qcache_queries_in_cache
查询缓存中已注册的查询数目
Qcache_total_blocks
查询缓存的总内存块数
Slow_queries
慢查询次数
Sort_merge_passes
排序算法进行合并的次数
Table_locks_immediate
能够立即获得表级锁的次数
Table_locks_waited
不能立即获取表级锁而需要等待的次数，越高表示表级锁争用越严重
Threads_created
为连接创建的线程数量
Uptime
服务器工作时长
以上是 MySQL学习笔记（2）：配置和状态 的全部内容， 来源链接： utcz.com/z/534194.html
