12、MySQL数据库配置

MySQL系统数据库

MySQL 系统数据库主要包括以下几个：

mysql 数据库：用于存储 MySQL 的权限信息和系统变量等，包括 user 表、db 表、host 表等，这些表是管理 MySQL 权限的重要基础。

information_schema 数据库：用于存储关于 MySQL 数据库对象的元数据信息，包括数据库、表、列、索引、用户权限等信息。

performance_schema 数据库：用于存储 MySQL 的性能统计数据，包括 CPU、I/O、内存等各种资源的使用情况，以及锁等方面的信息。

sys 数据库：提供了一系列的视图，用于方便管理者查询 MySQL 系统状态和监控 MySQL 系统。

服务器配置及状态

可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态

官方手册：​​https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html​​

注意：
其中有些参数支持运行时修改，会立即生效
有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
有些参数作用域是全局的，为所有会话设置
有些可以为每个用户提供单独（会话）的设置

服务器选项

#获取mysqld当前启动选项
mysqld --print-defaults

设置服务器选项方法：

设置 MySQL 服务器选项可以通过多种方式完成，包括：

在 MySQL 配置文件中设置选项值，通常是 my.cnf 或 my.ini 文件。

vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

在 MySQL 启动命令中设置选项值，使用 --option_name=value 的格式。

shell> /usr/bin/mysqld_safe --skip-name-resolve=1
shell> /usr/libexec/mysqld --basedir=/usr

服务器系统变量

服务器系统变量是MySQL服务器的重要配置参数，可以影响MySQL服务器的性能和行为。MySQL提供了许多服务器系统变量，用于控制各种功能，如内存使用、查询执行、复制和安全等。这些变量可以在MySQL配置文件中设置，也可以在运行时使用SET语句进行更改。

下面是一些常用的服务器系统变量：

innodb_buffer_pool_size：InnoDB存储引擎使用的缓冲池大小，以字节为单位。这个变量决定了InnoDB能够缓存多少数据和索引，可以影响查询性能。

max_connections：MySQL服务器允许的最大连接数。这个变量可以影响服务器的并发性能，但也要考虑系统的内存和CPU资源。

query_cache_size：MySQL查询缓存的大小，以字节为单位。查询缓存可以提高查询性能，但也可能降低性能，因为缓存需要占用内存和CPU资源。

tmp_table_size：临时表使用的最大内存量，以字节为单位。临时表通常用于处理大量数据或排序和分组操作，可以提高查询性能。

join_buffer_size：连接操作使用的缓冲区大小，以字节为单位。连接操作通常是比较消耗CPU和内存资源的操作，通过调整这个变量可以提高查询性能。

需要注意的是，改变某些服务器系统变量的值可能会影响到MySQL服务器的稳定性和安全性，因此在更改服务器系统变量之前应该进行详细的测试和评估。

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)
#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;
#查看选项和部分变量

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量

SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
character_set_results=utf8mb4
#character_set_results不是服务器选项,写入配置文件将导致无法启动
[root@centos8 ~]#systemctl restart mariadb
Job for mariadb.service failed because the control process exited with error
code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

修改最大连接数

修改配置文件
[mysqld]
max_connections = 8000
或者修改全局变量
set global max_connections=8000;
修改后可能无法增大，需要修改限制
#方法1
[root@centos8 ~]#vim /usr/lib/systemd/system/mariadb.service
[Service]
#加下面一行
LimitNOFILE=65535
#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@node3 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535

修改页大小

说明：初始化数据目录后，不能更改此系统变量的值。 在MariaDB实例启动时设置InnoDB的页面大小，此后保持不变。

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
innodb_page_size=64k
[root@centos8 ~]#rm -rf /var/lib/mysql/*  #需要进行重新初始化
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 65536 |
+------------------+-------+

服务器变量 SQL_MODE

SQL_MODE 是 MySQL 服务器的一个系统变量，用于控制 MySQL 数据库的语法和行为。它可以设置为一个或多个值的列表，不同的值之间用逗号隔开。SQL_MODE 可以在启动 MySQL 服务器时设置，也可以在运行时使用 SET 命令进行设置。

SQL_MODE 可以影响以下内容：

语法检查：SQL_MODE 可以控制 MySQL 是否允许某些特定的 SQL 语句，或者是否执行某些特定的语句。

数据校验：SQL_MODE 可以控制 MySQL 是否允许插入或更新某些特定的数据类型或值。

严格模式：SQL_MODE 可以控制 MySQL 是否启用严格模式，以强制执行更严格的数据验证和类型检查。

SQL_MODE 可以设置的选项非常多，包括 ANSI、MySQL、Oracle 等多种不同的模式。在设置 SQL_MODE 时，需要考虑数据库应用程序的需求，以及是否需要兼容不同的数据库平台。

以下是一些常用的 SQL_MODE 值：

ANSI：启用 ANSI SQL 模式。

STRICT_TRANS_TABLES：在插入或更新数据时，禁止插入空值或零值，以及数据类型不匹配的值。

NO_AUTO_CREATE_USER：禁止自动创建用户。

ONLY_FULL_GROUP_BY：禁止 GROUP BY 语句中使用非聚合列。

ERROR_FOR_DIVISION_BY_ZERO：在发生除以零错误时，抛出错误。

NO_ZERO_IN_DATE：不允许日期中出现 '0000-00-00' 的情况；

NO_ENGINE_SUBSTITUTION：如果使用的存储引擎不可用，不要使用另一个存储引擎替代它。

NO_BACKSLASH_ESCAPES：禁用反斜杠的转义功能。如果启用此模式，则在字符串中使用反斜杠时，将会将其视为普通字符，而不是转义字符。

PIPES_AS_CONCAT：将“||”解释为字符串连接运算符，而不是按位或运算符

可以使用以下命令查看当前 MySQL 服务器的 SQL_MODE 变量值：

SHOW VARIABLES LIKE 'sql_mode';

可以使用以下命令修改 SQL_MODE 变量的值：

SET sql_mode='value1,value2,...';

注意：如果需要永久更改 SQL_MODE 变量的值，可以将其添加到 MySQL 配置文件中（如 my.cnf 或 my.ini），并在 MySQL 服务器启动时加载该文件。