MySQL8 配置 MySQL详解

在本章中，我们将介绍以下配方：

MySQL 有两种类型的参数：

• 静态，重启 MySQL 服务器后生效
• 动态，无需重启 MySQL 服务器即可动态更改

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

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

默认配置文件是/etc/my.cnf（在 Red Hat 和 CentOS 系统上）和/etc/mysql/my.cnf（Debian 系统）。在您喜爱的编辑器中打开该文件，并根据需要修改参数。本章讨论了主要参数。

配置文件包含由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选项。

shell> sudo vi /etc/my.cnf
[mysqld]
pid-file = /var/lib/mysql/mysqld.pid

在使用systemd的系统中，不会安装mysqld_safe。要配置启动脚本，需要在/etc/systemd/system/mysqld.service.d/override.conf中设置值。

例如：

[Service]
LimitNOFILE=max_open_files
PIDFile=/path/to/pid/file
LimitCore=core_file_limit
Environment="LD_PRELOAD=/path/to/malloc/library"
Environment="TZ=time_zone_setting"

如前几章所述，您可以通过连接 MySQL 并执行SET命令来设置参数。

根据变量的范围，有两种类型的变量：

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

例如，如果要记录所有小于 1 秒的查询，可以执行：

mysql> SET GLOBAL long_query_time = 1;

要使更改在重新启动期间保持不变，请使用：

mysql> SET PERSIST long_query_time = 1;
Query OK, 0 rows affected (0.01 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;

假设您希望使用启动脚本而不是通过systemd启动 MySQL，特别是为了测试或一些临时更改。您可以将变量传递给脚本，而不是在配置文件中对其进行更改。

shell> /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/centos7.err --pid-file=/usr/local/mysql/data/centos7.pid --init-file=/tmp/mysql-init &

您可以看到，--init-file参数被传递给了服务器。服务器在启动之前执行该文件中的 SQL 语句。

安装后，本节将介绍需要配置的基本内容。其余的都可以保留为默认值，或者稍后根据负载进行调整。

让我们进入细节。

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

data directory有三个子目录：

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

除此之外，data directory还包含日志文件、InnoDB表空间和InnoDB日志文件、SSL 和 RSA 密钥文件、mysqld的pid和mysqld-auto.cnf，它们存储持久化的全局系统变量设置。

设置data directory更改/将datadir的值添加到配置文件中。默认为/var/lib/mysql：

shell> sudo vi /etc/my.cnf
[mysqld]
datadir = /data/mysql

您可以将其设置为您想要存储数据的任何位置，但是您应该将data directory的所有权更改为mysql。

Make sure that the disk volume bearing the data directory has sufficient space to hold all your data.

这是最重要的调整参数，它决定了InnoDB存储引擎可以使用多少内存在内存中缓存数据和索引。设置过低会降低 MySQL 服务器的性能，设置过高会增加 MySQL 进程的内存消耗。MySQL 8 最好的地方在于innodb_buffer_pool_size是动态的，这意味着您可以在不重新启动服务器的情况下改变innodb_buffer_pool_size。

以下是有关如何调整它的简单指南：

1. 找出数据集的大小。不要将innodb_buffer_pool_size的值设置为高于数据集的值。假设您有一台 12GB 的 RAM 机器，并且您的数据集是 3GB；然后您可以将innodb_buffer_pool_size设置为 3 GB。如果您希望数据增长，您可以根据需要增加数据，而无需重新启动 MySQL。

2. 通常，数据集的大小远远大于可用的 RAM。在总 RAM 中，您可以为操作系统设置一些，为其他进程设置一些，为 MySQL 内部的每线程缓冲区设置一些，为 MySQL 服务器设置一些，除了InnoDB之外。其余的可分配给InnoDB缓冲池大小。 这是一个非常通用的表，为您提供了一个很好的开始值，假设它是一个专用的 MySQL 服务器，所有的表都是InnoDB，每个线程的缓冲区都保留为默认值。如果系统内存不足，可以动态减少缓冲池。 T28】【T30 12GB【T32-10GB】【T54 128 GB】【T56 108 GB-116 GB】256 GB【T68】

   | RAM | 缓冲池大小（范围） | | 4GB | 1GB-2GB | | 8GB | 4GB-6GB | | 10GB | 10GB | | | | | 10GB】 | | | | 10GB】 | | 32 GB | 24 GB-28 GB | | 64 GB | 45 GB-56 GB | | | 【T6220】 | | | 【T56 108 GB】 | | | | 【T6220】GB】 | 【T6220】 |

您可以将InnoDB缓冲池划分为不同的区域，通过减少不同线程对缓存页的读写时的争用，来提高并发性。例如，如果缓冲池大小为 64 GB，innodb_buffer_pool_instances为 32，则将缓冲区分成 32 个区域，每个区域 2 GB。

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

这是用于在数据库崩溃时重播已提交事务的重做日志空间的大小。默认值为 48 MB，这可能不足以满足生产工作负载。首先，可以设置 1 GB 或 2 GB。此更改需要重新启动。停止 MySQL 服务器并确保它在没有错误的情况下关闭。在my.cnf中进行更改并启动服务器。在早期版本中，您需要停止服务器，删除日志文件，然后启动服务器。在 MySQL 8 中，它是自动的。修改重做日志文件的说明见第 11 章、管理表空间、更改 InnoDB 重做日志文件的数量或大小部分。

您的数据可能会随着时间的推移而增长，当它超出文件系统时，您需要添加一个磁盘或将data directory移动到更大的卷中。

1. 检查电流data directory。默认情况下，data directory为/var/lib/mysql：

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.04 sec)

2. 停止mysql并确保其已成功停止：

shell> sudo systemctl stop mysql

3. 检查状态：

shell> sudo systemctl status mysql

它应该显示Stopped MySQL Community Server。

4. 在新位置创建目录，并将所有权更改为mysql：

shell> sudo mkdir -pv /data
shell> sudo chown -R mysql:mysql /data/

5. 将文件移动到新的data directory：

shell> sudo rsync -av /var/lib/mysql /data

6. 在 Ubuntu 中，如果启用了 AppArmor，则需要配置访问控制：

shell> vi /etc/apparmor.d/tunables/alias
alias /var/lib/mysql/ -> /data/mysql/,
shell> sudo systemctl restart apparmor

7. 启动 MySQL 服务器并验证data目录已更改：

shell> sudo systemctl start mysql
mysql> show variables like '%datadir%'; 
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.00 sec)

8. 验证数据是否完整，并移除旧的data directory：

shell> sudo rm -rf /var/lib/mysql

如果 MySQL 启动失败，出现错误-MySQL data dir not found at /var/lib/mysql, please create one：

执行，sudo mkdir /var/lib/mysql/mysql -p

如果显示为MySQL system database not found，则运行；mysql_install_db工具，用于创建所需的目录。