Mysql数据库配置详解（基于5.7版本）【持续更新】

Mysql数据库配置详解（基于5.7版本）

首先说一下mysql的session和global

global：全局，不包含当前连接，之后新获取的连接都会生效。
session：当前会话，也就是当前连接。

• 在MySQL中要修改全局（global）变量，有两种方法：

1. 修改my.ini配置文件，如果要设置全局变量最简单的方式是在my.ini文件中直接写入变量配置，
   此方法需要重启数据库服务，重启后全局变量永久生效。

2. 使用关键字global设置全局变量，如：
   SET GLOBAL autocommit=1; // 开启自动提交
   需要注意的是，使用此方法对global全局变量的设置仅对于新开启的会话才有效，对已经开启的会话不生效。并且数据库重启后该设置失效。

• 在MySQL中要修改会话（session）变量，可以使用session关键字，如：
  SET SESSION autocommit=1;
  修改session变量配置，仅仅是对本session的变量配置有效，对于其他session无效。
  mysql 直接set不加global或者session时，mysql默认的时session级别，如：
  SET autocommit=1;此时和的SET SESSION autocommit=1;效果一致。

• 需要注意的是虽然设置了global变量、session变量，但是在mysql服务重启之后，数据库的配置又会重新初始化，一切按照my.ini的配置进行初始化。global和session的配置都会失效了。

mysql服务端的一些配置项（性能相关）

基础配置

• max_connections

  MySQL的最大连接数,MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。
  如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，
  因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
  数值过小会经常出现ERROR 1040: Too many connections错误，这里可以主要查看以下两个数值，以分析设置合适的数值。

  1. 通过show variables like 'max_connections'; 查看 max_connections 当前设置的值。
  2. 通过show status like 'max%connections'; 查看 Max_used_connections 数值。

  这两个数值大致关系：max_used_connections / max_connections * 100% （理想值≈ 85%）
  如果 max_used_connections 跟 max_connections 相同，那么就是 max_connections 设置过低或者超过服务器负载上限了，低于10%则设置过大。
  MySQL的 max_connections 参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接，max_connections 的默认值为100。

  由于MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了 max_connections 。因此MySQL的实际最大可连接数为max_connections+1；

  max_connections 参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；
  增加 max_connections 参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；
  该参数设置过小的最明显特征是出现**”Too many connections”错误；一般来说，该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则容易出现类似”Too many connections”**的错误。

• max_allowed_packet

  有时候包含大量数据的插入和更新会被 max_allowed_packet 参数限制掉，导致失败。
  比如说：将本地数据库迁移到远程数据库时运行sql错误。可能会报 max_allowed_packet 相关的错误。

  再比如：插入数据时某个字段数据过于庞大(比如"text"、"blob"等字段类型)，会报
  Packet for query is too large (20682943>1048576). You can change this value on the server by setting the max_allowed_packet’ variable.

  这个参数是指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小。

  5.7版本的默认值为4M。据官网描述, 数据包的值范围为1k~1G, 只能是1024的倍数，不能整除1024的，会向下取整。 若设置成1025，最终结果为1024。

• interactive_timeout 和 wait_timeout

  这两个参数放在一起说，是因为它们两个通常是成对设置的。比如说，有时候你在配置文件中写入 wait_timeout = 60，然后重启MySQL服务器发现并未生效。
  此时要同时设置 interactive_timeout 和 wait_timeout 才会生效。

  下面来解释一下这两个参数的功能。

  • interactive_timeout:

  参数含义：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在 mysql_real_connect()中使用 CLIENT_INTERACTIVE 选项的客户端。
  参数默认值：28800秒（8小时）

  • wait_timeout:

  参数含义：服务器关闭非交互连接之前等待活动的秒数。
  在线程启动时，根据全局 wait_timeout 值或全局 interactive_timeout 值初始化会话 wait_timeout值，取决于客户端类型(由 mysql_real_connect()的连接选项 CLIENT_INTERACTIVE 定义)。。
  参数默认值：28800秒（8小时）

  上边的参数含义可能不是很好理解，再简单的说一下。
  首先要说明一下交互式和非交互式。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。
  举个好接受的例子，就是通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。
  interactive_time — 其取值范围随 wait_timeout 变动，默认值28800。比如我们在终端上进入mysql管理，使用的就是交互式连接。这时候，如果长时间未操作，超过了 interactive_time 设置的时间就会自动断开。

  wait_timeout — 当一个客户端连接到MySQL数据库后，如果客户端不自己断开，也不做任何操作，MySQL数据库会将这个连接保留 wait_timeout 这么长时间（单位是s，默认是28800s，也就是8小时），
  超过这个时间之后，MySQL数据库为了节省资源，就会在数据库端断开这个连接；当然，在此过程中，如果客户端在这个连接上有任意的操作，MySQL数据库都会重新开始计算这个时间。

  MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，
  他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报’too many connections’的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。
  在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。

• tmp_table_size 和 max_heap_table_size

  这两个值通常也是成对出现，是应为对于某些query，MySQL会创建临时表来进行处理，临时表有两种：基于MEMORY存储引擎的临时内存表以及基于MyISAM存储引擎的临时磁盘表。当临时内存表的大小达到一定限制的时候，
  MySQL就会将临时内存表写入到磁盘，变为临时磁盘表。这个限制由 tmp_table_size 和 max_heap_table_size 这两个变量中的最小值确定。

  • tmp_table_size

  内存中内部临时表的最大大小。此变量不适用于用户创建的表，默认值为16M。
  其实在优化sql的时候就因该尽量避免临时表的产生。如果你需要执行很多高级查询，同时你的服务器上有足够的内存，那就适当的调高 tmp_table_size值。因为，
  如果生成的临时表数据量大于 tmp_table_size 则会将临时表存储到磁盘而不是内存，这时会加大系统I/O压力，同时严重影响读写速度。

  • max_heap_table_size

  此变量设置允许用户创建的内存表的最大值。因为临时表也是属于内存表所以也会受此参数的限制。所以如果要增加 tmp_table_size 的大小也需要考虑 max_heap_table_size 的大小。

  • 可以通过 Created_tmp_disk_tables 和 Created_tmp_tables 状态来分析是否需要增加 tmp_table_size。

• table_open_cache

  所有线程所打开的表个数。增加此值会增加mysqld进程所需的文件描述符(FD)的数量(和 open_files_limit 参数有关)。

  MySQL是多线程的，可能在同一时刻有多个客户端访问同一张表。为了能最小化多个客户端在相同表上的不同状态问题，并发会话中访问的每张表都会单独打开。虽然这可能消耗过多的内存，但是通常会提高系统的性能。

  调大 table_open_cache 参数值，可以减少业务表频繁打开和关闭。至于调整到多少合适，可以查看 MySQL 状态参数 Open_tables 和 Opened_tables，当 Open_tables 接近 table_open_cache，
  并且 Opened_tables 不会快速增加时，那么此时的 table_open_cache 值就是一个比较合适的值。

• table_open_cache_instances

  打开的表缓存实例数。此参数一般是搭配 table_open_cache 使用。为了通过减少会话之间的争用来提高可伸缩性，可以将打开的表缓存分区为几个大小为 table_open_cache / table_open_cache_instances 的较小缓存实例。
  默认值为16。就可以简单的理解为，若此值为1时，那么所有会话访问同一个缓存区，可能会有资源争用的情况。为16时，将用来缓存打开表的缓存区切分成16个实例。
  会话只需锁定一个实例即可访问 DML 语句。这会在实例之间分段缓存访问，从而允许在有许多会话访问表时使用缓存的操作具有更高的性能。（DDL 语句仍然需要对整个缓存进行锁定，但此类语句的频率远低于 DML 语句。）

  若服务器为16个或更多内核的系统，建议值为8或16。

  注意：如果表上有许多大型触发器导致内存负载过高，table_open_cache_instances的默认设置可能会导致内存使用过度。在这种情况下，为了限制内存使用，可能要考虑将table_open_cache_instances设置为1。

• table_definition_cache

  可以存储在表定义缓存中的表定义数。当MySQL中存在大量的表，此缓存会提高对表定义信息的访问效率。

  如果数据字典缓存中的表实例数超过 table_definition_cache 限制，LRU 机制将开始标记要逐出的表实例，并最终将其从 InnoDB 数据字典缓存中删除。由于具有外键关系的表实例未放在 LRU 列表中，
  因此具有缓存元数据的打开表的数量可能会高于 table_definition_cache限制。

  此参数和 innodb_open_files 有关系，因为，一次可以打开的每个表空间的文件数受 table_definition_cache 和 innodb_open_files 设置的限制。同时设置时，取更高者。

  Tip：LRU是Least Recently Used的缩写，即最近最少使用，是一种常用的置换算法，选择最近最久未使用的予以淘汰。

innodb相关配置

抽空更新，之后补充上。。。。。。