MySQL 配置基础

MySQL作为一种常见的数据库管理系统(DBMS)，其自身的各种配置项极大的影响了其性能。所以有必要进行了解和学习。

配置学习资源途径

我最近在看《高性能Mysql》，其中第8章讲解了关于配置的很多事项，都值得了解和学习。当然，官网也是最新信息查阅的重要渠道。

官网【可在5.1 TheMySQL Server找到配置解释。可在5.4找到BinLog相关内容。 】

MySQL配置

配置文件位置：

一般来说，MySQL服务端配置文件的默认位置是：/etc/my.cnf 或者 /etc/mysql/my.cnf
【也可以通过mysqld --verbose --help|grep -A 1 'Default option'确认配置文件的位置】

和nginx类似，也可以选择分目录进行include，便于管理整体配置结构。 （比如 !includedir /etc/my.cnf.d 然后在my.conf.d目录里对不同的配置项分文件。

配置修改基础：

1、mysql配置文件是分段的，要注意配置项放在了正确的段里（比如服务器主要用 [mysqld] 这一段

1、副作用：一些配置项会产生副作用，临时修改配置需要非常小心。（比如变更 query\_cache\_size 会立即删除所有查询的缓存，重新构建。
2、变量的值：变量不是越大越好，可能会导致内存交换或者超出地址空间。需要跟进情况设定。

2、配置项有不同的作用域，有的是全局，有的是会话。其次，还有动态变量，可在运行时修改。【也就是说，大部分非动态变量需要重启才能生效】
3、全局变量：修改后对当前会话及已存在的会话均不生效。可通过SHOW GLOBAL VARIABLES确认。
4、变量的单位：配置时要注意单位，命令行或者配置文件，可以使用后缀指定单位（比如1M等，但要注意，使用SQL的SET指令时就不能使用单位。
5、配置文件管理：最好使用git来进行版本管理，加上充足的注释。可以避免不少问题。

如何创建一个靠谱的MySQL配置？

1、一个好的配置，不是从学习配置项开始，也不是询问怎么设置或者怎么修改，更不是观察服务器行为和询问哪个配置可以提升性能。
【应该是从理解MySQL内核和行为开始】
2、保证基础配置都正确（比如日志路径，缓存配置，端口号，数据库存储位置等。如非必要，尽量使用默认配置。（默认配置经受过的测试是最多的。
3、优先进行语句优化等其他优化，最后考虑修改配置项。

一个最小配置示例

一般来说，选择尽量少的配置（或者说最小配置），如无必要，不用声明（不声明使用默认值）。

当然一些非常重要的配置项，即使使用默认配置，也最好声明出来。（比如default_storage_engine)

[mysqld]
#GENERAL
datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
pid-file                = /var/lib/mysql/mysqld.pid
user                    = mysql
port                    = 3306
default_storage_engine  = InnoDB
skip_name_resolve #关闭域名解析，避免dns导致的请求失败(关闭后要使用IP访问mysql)
#mysql 会做正向和反向dns查询。一旦失败就会拒绝连接。

#InnoDB
innodb_buffer_pool_size = 256M #核心配置，需要根据实际情况调整
innodb_log_file_size    = 50M #需要根据实际情况调整
innodb_file_per_table   = 1 #需要根据实际情况调整
innodb_flush_method     = O_DIRECT #这里是字母O，不是数字0
#LOGGING
log-error               = /var/log/mysql-error.log
slow_query_log          = /var/log/mysql-slow.log
#OTHER
tmp_table_size          = 32M #需要根据实际情况调整
max_heap_table_size     = 32M #需要根据实际情况调整
#query_cache_type       = 0 #mysql 8.0有变动，参数名不是这个
#query_cache_size       = 0 #mysql 8.0有变动，参数名不是这个
max_connections         = 100 #需要根据实际情况调整
thread_cache_size       = 50 #根据其余参数调整线程缓存
table_open_cache        = 10 #根据 opened_tables 调整
open_files_limit        = 65535 #尽量调大，配合内核参数
[client]
socket                  = /var/lib/mysql/mysql.sock
port                    = 3306

配置文件解释：
1、Innodb的配置项：

• innodb\_buffer\_pool\_size 【最核心配置，innodb严重依赖缓冲池。

  索引、行数据缓存、哈希索引，插入缓存、锁等。必须为innodb配置足够的缓冲池。 一般为内存的75%-80%，建议配置的稍低，根据情况增加比例。[ 《高性能MySQL》P343

• Innodb任意时刻可以保持打开.idb文件的数量是有限的，由如下两个参数控制：
  innodb_file_per_table：When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default.
  innodb_open_files：It specifies the maximum number of .ibd files that MySQL can keep open at one time.

  也可参考官网：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

2、MySQL配置项：

• 线程缓存(thread_cache_size)：
  [参数]thread_cache_size：可以保持在缓存中的线程数。
  当关闭一个连接时，会将这个连接的线程放入缓存（如果缓存有空间）。下次可以直接读缓存，而非重新建立连接。
  [状态]thread_cached：当前已经缓存了的线程数。
  [状态]threads_created：每秒创建的新线程数，（如果每秒要创建很多线程，那需要考虑增加缓存大小
  [状态]threads_connectd：当前正在连接中的线程数。
  线程缓存的配置和上述3个状态高度相关。根据实际情况判断缓存应该配置多少。

  PS：灵活利用SHOW STATUS和SHOW VARIABLES 查看配置的值，查看当前状态。

• 表缓存：
  [参数]table_open_cache：如果[状态]opened_tables很大，或者一直在增长。考虑增加这个参数。
  [参数]table_definition_cache：通常都可以配置的比较高，最好可以缓存下所有表的定义。【除非有上万张表】

配置修改后的生效与确认

了解了基础配置，修改了配置文件。还需要关注配置的生效问题。
有的动态配置修改之后就马上生效。有的全局配置需要重启才能生效。systemctl restart mysqld
重启后，还需要获取变量值进行确认,是否配置变量符合预期。

如何确认MySQL的配置/状态？

1、SHOW GLOBAL STATUS 及 SHOW STATUS
查看数据库当前的状态参数。（确认当前运行情况）
2、SHOW VARIABLES
查看数据库的配置参数。（也可直接进行过滤搜索，确认默认配置值）
【PS：1和2都可通过WHERE等语句进行筛选过滤。比如：show variables where variable_name like 'inno%pool%size%';
【或者说SHOW语句本质也是在查数据库。只不过是描述数据库本身的数据

3、[shell]mysqladmin extended-status -ri60
每60秒查看状态变量的增值
具体可参考:https://blog.csdn.net/demonson/article/details/80103204
4、STATUS
查看MySQL基础信息，包括版本，字符集，socket，线程数等。

自己在学习过程中，遇到的其他问题

0、yum安装mysql的步骤
首先是获取rpm包，通过如下两个连接均可。文件名类似这样mysql57-community-release-el7-3.noarch.rpm。
https://dev.mysql.com/downloads/repo/yum/
https://repo.mysql.com/
随后rpm -ivh xxx.rpm导入包，yum makecache生成缓存。最后yum install -y mysql-community-{client,libs,common,server}.x86_64即可完成安装。（一般来说，这4个组件就足够了。）

1、yum安装mysql后，初次登录和密码问题
首先确认/etc/my.cnf配置，确认err-log的配置。（一般默认是/var/log/mysqld.log）然后查看错误日志，即可发现临时密码。再使用sudo mysql -uroot -p'tmp_passwd'即可登录。
参考：https://www.cnblogs.com/array...
其次，临时密码不可用于长期登录，必须修改后才可进行更多操作。如果尝试使用临时密码登陆后操作，会出现如下提示：You must reset your password using ALTER USER statement before executing this statement. 可以根据mysql版本采取对应的语句进行密码修改。

• 5.7.6之前：SET PASSWORD = PASSWORD('Newpasswd');
• 5.7.6之后：ALTER USER USER() IDENTIFIED BY 'Newpasswd';

参考:https://blog.csdn.net/muziljx/article/details/81541896

2、[小结]如何修改配置？修改之后何时生效？如何确认修改后的值是否正确？

• 修改配置：通过SHOW VARIABLES WHERE xxx 确认变量当前数值，确认修改变量是否是动态生效。修改MySQL配置文件并保存（一般是/etc/my.cnf）
• 何时生效？：动态变量即时生效，全局变量重启生效[对之前的会话和当前会话都不生效]，会话变量不确定（？待确认）
• 如何确认？：也可通过SHOW VARIABLES和SHOW STATUS加上WHERE等语句进行筛选(前者为配置变量，后者为当前的状态)，确认配置变量是否符合预期。（比如SHOW variables WHERE Variable_name = 'innodb_buffer_pool_size';）

参考：https://blog.csdn.net/demonson/article/details/80103204

3、MySQL内部的help
连接上MySQL命令行之后，可以方便的使用help查找不确定的命令。比如help create查看可以创建什么，help create database确认怎么创建新库的语法。
创建数据库表的方法也不难：

CREATE TABLE test (
列名  数据类型  限制,
id INTEGER PRIMARY KEY,
);

help相关参考：https://blog.csdn.net/woqutechteam/article/details/81115892

4、其他小问题

• 可以通过SELECT database();和STATUS;来确认，自己当前在哪个数据库。
• SCHEMA和DATBASE 在MySQL里是同义词。见https://stackoverflow.com/questions/1219711/mysql-create-schema-and-create-database-is-there-any-difference
• innodb_flush_method = O_DIRECT这里是字母O不是数字0

小结

本文主要讲述了MySQL配置的基础知识，包括配置文件的位置、配置项的分段（[mysqld]）、作用域、还有配置变量的查询及当前状态变量确认。
而且对少量配置变量进行了解释。
更多配置项的解释都可通过官网确认。https://dev.mysql.com/doc/refman/5.7/en/