使用配置文件

MySQL有两种类型的参数。

  • 静态参数：重启MySQL服务器后才能生效
  • 动态参数：可以在不重启MySQL服务器情况下即时更改它

变量可以通过以下方式设置。

  • 配置文件：MySQL有一个配置文件，可以在其中指定数据的位置、MySQL可以使用的内存以及其它各种参数
  • 启动脚本：可以直接将参数传递给mysqld进程。启动脚本仅在调用服务器时才有效。
  • 使用SET命令（仅限动态变量）：这将持续到服务器重新启动时，需要在配置文件中设置变量，才能使更改持久化；另一种使更改持久化的方法是在PERSIST关键字或@@persist之前加上变量名称。

默认配置文件是/etc/my.cnf(RedHat和CentOS)和/etc/mysql/my.cnf(在Debian系统上)。

如何操作

配置文件包含由section_name指定的部分。所有与section相关的参数都可以放在section_name下面，例如：

[mysqld] <---section name
<parameter_name> = <value> <---- parameter values
[client]
<parameter_name> = <value>
[mysqldump]
<parameter_name> = <value>
[mysqld_safe]
<parameter_name> = <value>
[server]
<parameter_name> = <value>
  • mysql：该部分由mysql命令行客户端读取
  • client：该部分由所有连接的客户端读取（包括mysql cli）
  • mysqld：该部分由mysql服务器读取
  • mysqldump：该部分由名为mysqldump的备份工具读取
  • mysqld_safe：该部分由mysqld_safe进程读取（MySQL服务器启动脚本）

除此之外，mysqld_safe进程会从选项文件中的[mysqld]和[server]部分读取所有选项。

例如：mysqld_safe进程从mysqld部分读取pid-file选项。

[root@www ~]# vim /etc/my.cnf
pid-file=/var/run/mysqld/mysqld.pid

在使用systemd的系统中，mysqld_safe将不会被安装。要配置启动脚本，需要修改mysqld.service单元unit文件。

使用全局变量和会话变量

可以通过连接到MySQL并执行SET命令来设置参数。

根据变量的作用域可以将变量分为两种：

  • 全局变量：适用于所有新连接
  • 会话变量：仅适用于当前连接（会话）

如何操作

如果想记录所有执行时间超过1秒的查询，则可以执行：

mysql> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

要使更改在重新启动时保持持久化，使用：

mysql> SET PERSIST long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

或者使用：

mysql> SET @@persist.long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

持久化的全局系统变量设置存储在数据目录中的mysqld-auto.cnf中。

如果只想为此会话记录查询，而不是为所有连接记录查询，可以使用以下命令：

mysql> SET SESSION long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

配置参数

本节将介绍安装完成后需要配置的基本内容。除了这些需要配置的内容，剩下的全部可以保留默认值或稍后根据负载进行调整。

如何操作

数据目录

由MySQL服务器管理的数据存储在名为数据目录的目录下。数据目录的每个子目录都是一个数据库目录，并对应服务器管理的数据库。默认情况下，数据目录有三个子目录：

  • mysql：MySQL系统数据库
  • performance_schema：提供用于在运行时检查服务器的内部执行情况的信息
  • sys：提供一组对象，帮助更轻松地解释performance schema信息

除此之外，数据目录包含日志文件、InnoDB表空间和InnoDB日志文件、SSL和RSA秘钥文件、mysqld的pid，以及存储持久化全局系统变量设置的mysqld-auto.cnf文件。

要设置数据目录，需要更改datadir值，或将datadir的值添加到配置文件中。datadir的值默认是/var/lib/mysql：

[root@www mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql

可以将其设置为你要存储数据的任何位置，但应该将数据目录的所有权更改为mysql。还要确保带有数据目录的磁盘卷有足够的空间来容纳所有数据。

innodb_buffer_pool_size

这是InnoDB最重要的调优参数，它决定InnoDB存储引擎可以使用多少内存空间来缓存内存中的数据和索引。将其值设置太低可能会降低MySQL服务器的性能，而将其设置太高会增加MySQL进程的内存消耗。MySQL 8中的innodb_buffer_pool_size是动态的，这意味着可以改变innodb_buffer_pool_size，而不用重启服务器。

调试innodb_buffer_pool_size的简单指南。

  1. 找出数据集的大小。不要将innodb_buffer_pool_size的值设置的高于数据集的值。假设有一个12GB RAM的机器，数据集是3GB,那么你可以设置innodb_buffer_pool_size的值为3GB。如果数据集的大小增长了，可以在需要时增加它，而无须重新启动MySQL。
  2. 通常，数据集的大小比可用RAM大的多。对于整个RAM，你可以为操作系统分配一些，为其他进程分配一些，为MySQL内的per-thread缓冲区分配一些，为InnoDB之外的MySQL服务器分配一些，其余的可以分配给InnoDB缓冲池。这是一个通用性很强的表，假设它是一个专用的MySQL服务器，所有表都是InnoDB的，并且per-thread缓冲区都保留默认值，那么你就可以开始使用它了。如果系统内存不足，可以动态减少缓冲池的大小。下表列出了RAM与对应的缓冲池大小。
    RAM 缓冲池大小（范围）
    4GB 1GB~2GB
    8GB 4GB~6GB
    12GB 6GB~10GB
    16GB 10GB~12GB
    32GB 24GB~28GB
    64GB 45GB~56GB
    128GB 108GB~116GB
    256GB 220GB~245GB

innodb_buffer_pool_instances

可以将InnoDB缓冲池划分为不同的区域，以便在不同线程读取和写入缓存页面时减少争用，从而提高并发性。例如，如果缓冲池大小为64G，innodb_buffer_pool_instances的值为32，则缓冲区将被分为32个区域，每个区域的大小为2GB。

如果缓冲池大小超过16GB，则可以设置实例，以便每个区域至少获得1GB的空间。

innodb_log_file_size

innodb_log_file_size是重做日志空间的大小，用于数据库崩溃时重放已提交的事务。innodb_log_file_size的默认值是48MB，这不足以满足生产工作负载的需求。开始的时候，可以将其设置为1GB或2GB。此更改需要重新启动服务器才能生效。停止MySQL服务器的运行并确保它没有被错误地关闭。在my.cnf中进行更改并启动服务器。在早期版本中，需要停止服务器的运行，删除日志文件，然后启动服务器。在MySQL 8中，这一系列操作是自动进行。

更改数据目录

数据量会随着时间的推移而增长，当它超出文件系统的容量时，需要添加磁盘或将数据目录移动到更大的卷中。

如何操作

  1. 检查当前的数据目录。
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
  1. 停止mysql，并确保它成功停止了：
[root@www ~]# systemctl stop mysqld.service
  1. 检查状态
[root@www ~]# systemctl status mysqld.service
  1. 在新位置创建目录并将所有权更改到mysql下：
[root@www ~]# mkdir -pv /data
mkdir: created directory ‘/data’
[root@www ~]# chown -R mysql:mysql /data
  1. 将文件移动到新的数据目录中:
[root@www ~]# rsync -av /var/lib/mysql /data/
  1. 修改配置文件
[root@www ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
  1. 启动MySQL服务器并确认数据目录已更改：
[root@www ~]# systemctl start mysqld.service 
mysql> show variables like '%datadir%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.00 sec)
  1. 验证数据是否完整并删除旧的数据目录。