cbear小站
large picture of the cover

3-2-服务器配置和状态

1. MySQL中的系统数据库

  • mysql数据库

    是mysql的核心数据库 , 类似于Sql Server中的master库 , 主要负责存储数据库的用户、权限设 置、关键字等mysql自己需要使用的控制和管理信息

  • performance_schema数据库

    MySQL 5.5开始新增的数据库 , 主要用于收集数据库服务器性能参数,库里表的存储引擎均为 PERFORMANCE_SCHEMA , 用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

  • information_schema数据库

    MySQL 5.0之后产生的 , 一个虚拟数据库 , 物理上并不存在information_schema数据库类似与"数据字典" , 提供了访问数据库元数据的方式, 即数据的数据。比如数据库名或表名、列类型、访问权限(更加细化的访问方式)

  • sys数据库

    MySQL5.7之后新增加的数据库 , 库中所有数据源来自performance_schema。目标是把 performance_schema的把复杂度降低 , 让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况

2. 服务器配置和状态

mysqld是一个可执行文件 , 代表着Mysql服务器程序 , 执行这个文件可以直接启动一个服务器进程。可以通过mysqld的命令行选项 , 服务器系统变量和服务器状态变量进行MySQL的配置和查看状态。

对于系统变量和状态变量的变量的作用域(Var Scope)是全局的、会话的或两者都有。

官方帮助链接:

MySQL :: MySQL 5.7 Reference Manual :: 5.1.3 Server Option, System Variable, and Status Variable Reference

Full List of MariaDB Options, System and Status Variables - MariaDB Knowledge Base

image-20230330114544865

dynamic : 表示在数据库运行过程中动态调整对应参数

注意 :

  • 其中有些参数支持运行时修改 , 会立即生效

  • 有些参数不支持动态修改 , 且只能通过修改配置文件 , 并重启服务器程序生效

  • 有些参数作用域是全局的 , 为所有会话设置

  • 有些可以为每个用户提供单独(会话)的设置

获取mysqld的可用选项列表

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults  

范例 :

#查看可用选项列表和当前值
[root@centos8 ~]#/usr/libexec/mysqld --verbose --help
#查看mysqld的当前启动选项
[root@centos8 ~]#/usr/libexec/mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--plugin-load-add=auth_gssapi.so --datadir=/var/lib/mysql --
socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mariadb/mariadb.log --pidfile=/run/mariadb/mariadb.pid

设置服务器选项方法:

  1. 在命令行中设置

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

  2. 在配置文件my.cnf中设置

    范例:

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

    范例: skip-grant-tables是服务器选项,但不是系统变量

    [root@centos8 ~]#mysqladmin variables |grep skip_grant_tables
[root@centos8 ~]#mysql 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-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 'skip_grant_tables';
Empty set (0.001 sec)

2.1 服务器选项

MySQL的配置信息可以通过两种方式实现 : 一种是命令行形式 ,在启动MySQL服务时后边带上相关配置参数 , 此种方式会在MySQL重启后失效 ; 另外一种是通过写入配置文件 , 如my.cnf , 启动或者重启MySQL服务都会生效 , 此种方式是永久生效。

mysqld是MySQL的守护进程 , 可以通过手动调用mysqld来启动MySQL服务 , 这种方式只有一个mysqld进程 , 没有守护进程 , 如果MySQL服务挂了 , 没有检查重启的机制 , 生产环境不会使用这种方式启动MySQL服务。

执行mysqld初始化数据库时 , 可以指定服务器启动选项(server options)。MySQL 5.7中 , 启动项可以分为与安全相关的启动项、SSL相关的启动项、Binlog相关的启动项、复制相关的启动项、插件加载相关的启动项、以及存储引擎相关的启动项。

2.1.1 选项说明

2.1.1.1 Cmd-Line

命令行使用格式如下

shell> mysqld [options]

mysqld是MySQL服务器二进制文件的名称 , 而[options]是指定的选项列表。MySQL支持许多选项 , 这些选项可以在启动MySQL服务器时使用。这些选项可以控制服务器的行为和性能 , 以及其与其他应用程序的交互方式。在命令行中 , 选项的名称可以用短划线(-)替换配置文件选项中的下划线(_)。

以下是一些常用的选项及其用法：

  • --help : 显示MySQL服务器支持的所有选项。

  • --port=<port_num> : 指定MySQL服务器监听的端口号。例如 , --port=3306将使MySQL服务器在端口3306上监听连接请求。

  • --bind-address=<ip_address> : 指定MySQL服务器监听的IP地址。例如 , --bind-address=127.0.0.1将使MySQL服务器仅接受来自本地主机的连接请求。

  • --user=<user_name> : 指定MySQL服务器运行的用户名。例如 , --user=mysql将使MySQL服务器以mysql用户的身份运行。

  • --datadir=<dir_path> : 指定MySQL服务器数据目录的路径。例如 , --datadir=/var/lib/mysql将使MySQL服务器使用/var/lib/mysql作为其数据目录。

  • --log-error=<file_path> : 指定MySQL服务器错误日志文件的路径。例如 , --log-error=/var/log/mysql/error.log将使MySQL服务器将其错误日志写入/var/log/mysql/error.log文件中。

2.1.1.2 Option File

MySQL还支持使用选项文件来指定选项。选项文件是一种文本文件 , 其中包含MySQL服务器选项及其值。MySQL服务器在启动时可以读取选项文件 , 并使用其中的选项值。选项文件的名称和位置取决于操作系统和MySQL安装方式。

在Windows下 , MySQL的配置文件是 my.ini , 存储在MySQL的安装目录中。而在Linux下 , MySQL的配置文件是 /etc/my.cnf 或者 /etc/mysql/my.cnf。以下是一些常用的选项文件及其默认位置：

  • /etc/my.cnf：适用于整个系统的选项文件。

  • ~/.my.cnf：适用于当前用户的选项文件。

  • /usr/local/mysql/etc/my.cnf：适用于通过源代码安装的MySQL的选项文件。

my.cnf中的参数设置被分为四个组别: [client]、[mysql]、[mysqld]、[mysqld_safe]。它的配置文件中包含了许多参数组别，如下所示:

  • [mysqld] : 这是mysqld服务的主要参数组别 , 其中包含了大量控制mysqld服务行为的参数 , 如端口号、数据存储路径、字符集等。

  • [mysqld_safe] : 这个参数组别包含了mysqld_safe服务的参数 , 通常情况下不需要修改。

  • [mysql] : 这个参数组别包含了MySQL命令行客户端的参数。

  • [client] : 这个参数组别包含了与MySQL客户端有关的参数以及配置选项 , 如默认字符集、socket文件路径等、连接到服务器的用户名、密码和主机名等。

  • [mysqldump] : 这个参数组别包含了mysqldump命令的参数。

  • [mysqld_multi] : 这个参数组别包含了mysqld_multi服务的参数 , 用于启动和管理多个MySQL实例。

  • [mysqlhotcopy] : 这个参数组别包含了mysqlhotcopy命令的参数。

选项文件的格式如下：

[mysqld]
option1=value1
option2=value2
...

在这里，[mysqld]部分表示这些选项仅适用于MySQL服务器。如果要指定适用于客户端的选项，则可以使用[mysql]部分。

MySQL的配置文件中包含了许多变量 , 可以通过修改这些变量来优化MySQL数据库的性能。以下是一些常见的 MySQL配置文件变量:

  • max_connections : 指定MySQL数据库的最大连接数。

  • innodb_buffer_pool_size : 指定InnoDB存储引擎使用的缓冲池大小。

  • query_cache_size : 指定查询缓存的大小。

  • tmp_table_size : 指定允许使用的临时表的最大大小。

  • log_slow_queries : 指定是否记录慢查询日志。

可以通过修改MySQL的配置文件来调整这些变量的值 , 从而优化MySQL数据库的性能。在修改配置文件之前 , 需要先备份原来的配置文件 , 以免修改错误导致MySQL无法启动。

2.1.2 启动选项

2.1.2.1 安全相关启动项

与安全相关的启动项参见链接: https://dev.mysql.com/doc/refman/5.7/en/security-options.html

image-20230328095044826

其中 , secure_file_priv、skip-grant-tables、skip_networking、skip_name_resolve四个启动项着重介绍一下。

  • secure_file_priv : 该启动项会影响LOAD DATA、SELECT ... INTO OUTFILE语句以及LOAD_FILE()函数的使用。

    • 该启动项的值为空时 , 无任何影响;

    • 该启动项的值为路径名时, 文件的导入和导出操作只能在该路径下进行;

    • 该启动项的值为NULL时 , MySQL Server会禁止文件的导入和导出操作。

  • skip-grant-tables

    该启动项默认关闭(OFF) , 如果开启，会有如下后果：

    • MySQL服务在启动时不会读取系统数据库中的权限表(grant tables) , 也就会导致任何用户对所有数据库都有无限制的访问权限（因此，该启动项可以在我们忘记数据库管理员账号密码时用来修改密码）；

    • MySQL服务在启动时不会加载系统数据库中注册的某些对象，比如通过INSTALL PLUGIN安装的插件、通过CREATE EVENT创建的定时事件、通过CREATE FUNCTION创建的函数；

    • 系统变量disabled_storage_engines不会生效。

  • skip_networking 该启动项控制数据库是否允许TCP/IP连接 , 默认是关闭的(即允许TCP连接)。在我们忘记数据库管理员账号密码时,如果想要通过开启skip-grant-tables来修改密码 , 出于安全考虑 , 最好一并开启skip_networking。

  • skip_name_resolve

    检查客户端连接时是否解析主机名。如果这个变量是OFF, mysqld在检查客户端连接时解析主机名。如果是ON, mysqld只使用IP号;在这种情况下 , 授权表中的所有Host列值必须是IP地址。

    • MySQL服务端会在内存中维护一份host信息 , 包括IP、主机名和错误信息。当客户端建立连接时 , MySQL服务端会检查hostname是否在host cache中。如果在 , 服务端会决定是否继续提供请求 , 看该主机是否被锁住。如果不在 , 服务端会试图去解析hostname , 尝试IP-to-hostname-to-IP DNS这样的解决方式。因此 , 禁用DNS解析可以减少MySQL服务端进行DNS解析的时间 , 提高MySQL的性能。

    • 在MySQL的授权表中 , 可以使用主机名或者IP地址来授权。使用skip_name_resolve选项禁用DNS解析后 , 只能使用IP地址进行授权 , 不能使用主机名。

    • 在MySQL的配置文件my.cnf中 , 可以使用skip_name_resolve选项来禁用DNS解析。

    • 开启skip_name_resolve选项的情况 :

      • 在第一次建立连接时缓存IP和hostname的映射关系 , 同一主机的后续连接将直接查看host cache , 而不用在和DNS解析。

      • host cache中同样包含IP和登录错误的错误信息 , 可根据错误信息快速判断是否是同一主机。

    • 不能开启skip_name_resolve选项的情况 :

      • 需要使用主机名进行授权的情况。

      • 主机的IP地址经常变化的情况。

2.1.2.2 SSL相关的启动项

与SSL相关的启动项链接 : https://dev.mysql.com/doc/refman/5.7/en/connection-options.html#encrypted-connection-options

image-20230328105900963

2.1.2.3 Binlog相关的启动项

在MySQL中 ,binlog是二进制日志 , 是MySQL中最重要的日志之一。它记录了对MySQL数据库执行更改的所有操作 , 包括对数据库表结构的变更 、 对数据的变更 , 例如CREATE、ALTER TABLE、INSERT、UPDATE、DELETE等 ,但不会记录查询语句select。二进制日志有三个重要的功能：用于复制、用于恢复、用于审计。

与Binlog相关的启动项 : https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html

在MySQL的配置文件中 , binlog的配置一般都写在my.cnf文件中 , 以方便启动MySQL时直接让这些配置生效。以下是一些常用的binlog相关的启动项:

  • log-bin : 指定二进制日志文件的文件名前缀 , 例如log-bin=mysql-bin。

  • binlog_format : 指定二进制日志的格式 , 有三种可选格式: STATEMENT、ROW、MIXED。其中STATEMENT格式记录的是SQL语句 , ROW格式记录的是行的变化 , MIXED格式则是根据具体情况自动选择STATEMENT或ROW格式。

  • binlog_row_image : 用于ROW格式的二进制日志 , 指定记录行变化时记录的是完整的行数据还是只记录变化的部分数据。有两个可选值 : FULL和MINIMAL。FULL表示记录完整的行数据 , MINIMAL表示只记录变化的部分数据 , 可以减少日志量。

  • log-bin-index : 二进制文件的index文件的名字。binlog index文件中包含了所有binlog日志文件的文件名。

当MySQL启动时 , 可以使用服务器启动选项(server options) 来指定一些参数。其中 , 与binlog相关的启动项有:

  • --log-bin : 指定二进制日志文件的文件名前缀 , 例如--log-bin=mysql-bin。

  • --binlog-format : 指定二进制日志的格式 , 有三种可选格式 : STATEMENT、ROW、MIXED。

  • --binlog-row-image : 用于ROW格式的二进制日志 , 指定记录行变化时记录的是完整的行数据还是只记录变化的部分数据。有两个可选值 : FULL和MINIMAL。

2.1.2.4 复制相关的启动项

MySQL支持复制 , 复制是将一个MySQL服务器(称为主服务器)上的更改自动复制到另一个MySQL服务器(称为从服务器)上的过程。MySQL复制有许多选项 , 可以通过在主服务器和从服务器上设置选项来控制它的行为。

与复制相关的启动项参见 : https://dev.mysql.com/doc/refman/5.7/en/replication-options-reference.html

  • server_id : MySQL服务器的唯一标识符。必须在主服务器和从服务器上设置不同的ID。server-id的值在1到 2^{32}

  • relay_log : 启用中继日志。中继日志是从服务器上的二进制日志副本 , 用于将更改从主服务器复制到从服务器 。Relay日志的文件名前缀，例如relay-log = /mysql/log/relay-log。

  • rpl_semi_sync_master_enabled : 主库复制架构中 , 在主库开启配置:rpl_semi_sync_master_enabled=1 。需要先在主库安装半同步复制插件才能开启生效。

    mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

  • rpl_semi_sync_slave_enabled : 主库复制架构中 , 在备库开启配置: rpl_semi_sync_slave_enabled=1。需要先在备库安装半同步复制插件才能开启生效。

    mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

  • expire_logs_days : 单位为天 , 自动清除(purge)设定天数前生成的binlog日志文件。可设定的值为0到99天。设置expire_logs_days=0表示不自动清除binlog日志文件。

    PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

2.1.2.5 插件相关的启动项

MySQL服务器插件可以通过多种方式加载和启用。在MySQL 5.7.11之后 , 不推荐使用mysql_plugin , 并且在MySQL 8.0中已删除。替代方法包括在服务器启动时使用--plugin-load或--plugin-load-add选项或在运行时使用INSTALL PLUGIN语句加载插件。

在MySQL服务器重启后，如果要使用指定的插件，可以将其写入选项文件中。例如，将plugin-load=myplugin=somepluglib.so写入选项文件中。

如果设计的插件必须在服务器启动期间对其进行加载和初始化 , 则尝试在运行时使用INSTALL PLUGIN语句加载该插件会产生错误。在这种情况下 , 必须使用--plugin-load、--plugin-load-add或--early-plugin-load。如果使用这些选项同时命名了一个插件 , 并且在mysql.plugintable中同时命名 , 则服务器将启动 , 但会将这些消息写入错误日志。

与插件加载相关的内容参见: MySQL :: MySQL 5.7 Reference Manual :: 5.5.1 Installing and Uninstalling Plugins

  • --plugin-dir : 指定MySQL服务器插件目录。

  • --plugin-load : 覆盖默认的插件列表。可以指定多个插件 , 以逗号分隔。

  • --plugin-load-add : 添加插件到默认插件列表中。

  • --early-plugin-load : 在服务器启动时加载插件 , 即使插件在安装过程中还没有完成初始化。需要特别小心 , 因为插件可能会在服务器启动时产生错误 , 导致服务器无法启动。

  • --plugin-load-options : 在加载插件时传递给插件的选项。

2.1.2.6 存储引擎相关的启动项

下面仅介绍InnoDB相关的几个启动项 , 其余内容参见:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables

  • innodb_flush_log_at_trx_commit 决定了InnoDB日志(即redo log和undo log , 记录在ib_logfile中) 写入文件系统和持久到磁盘的方式。可取值为0、1、2 , 默认为1。

    • innodb_flush_log_at_trx_commit=0 : 每隔1秒钟将日志写入文件系统缓存并刷到磁盘。也就是说 , 服务异常的情况下可能丢失1秒以内的事务数据 ;

    • innodb_flush_log_at_trx_commit=1 : 在每一次事务提交时 , 都把日志写入文件系统缓存并刷到磁盘。对IO性能的要求非常高 ;

    • innodb_flush_log_at_trx_commit=2 : 在每一次事务提交时 , 写入日志到文件系统缓存 , 同时每隔1秒钟刷到磁盘。此时如果只是数据库服务挂了 , 文件系统没有问题 , 就不会丢失数据 ; 如果服务器宕机或者掉电了 , 还是可能损失1秒以内的数据。

  • innodb_file_per_table : 设置为ON时 , 数据库中的表默认会在file-per-table表空间中创建 ; 设置为OFF时 , 数据库中的表默认会在系统表空间中创建。 默认值为ON。假设我们在product数据库下创建了一个fruit表 , 该表的数据和索引都将存储在 /mysql/data/product/fruit.ibd数据文件中( /mysql/data为数据目录)。

  • innodb_lock_wait_timeout : InnoDB事务等待行锁的超时时间 , 默认为50秒。如果等待时间达到了该值 , 就会报下面的错误 , 并回滚要执行的语句。

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • innodb_log_buffer_size : InnoDB写日志文件用到的缓存区大小 , 单位为bytes。innodb_page_size为32K或64K时 , 默认值为16M。如果日志缓存区设置得太小 , 在进行大事务时 , log buffer被占满就会导致刷盘 , 从而增加了磁盘IO开销。

  • innodb_log_group_home_dir : redo日志的路径位置

  • innodb_dedicated_server : 在MySQL每次启动时自动探测服务器(包括虚拟机和容器的内存) 配置并自动生效。MySQL 8中开启innodb_dedicated_server后让MySQL自动探测服务器的内存大小 , 根据内存大小可以自动设置以下参数:

    • innodb_buffer_pool_size

    • innodb_log_file_size

    • innodb_flush_method

    • innodb_log_files_in_group(MySQL 8.0.14)

    • innodb_redo_log_capacity , (MySQL 8.0.30取代了innodb_log_file_size , innodb_log_files_in_group选项)

  • innodb_buffer_pool_size : InnoDB在内存中缓存表数据和索引的缓冲池大小 , 单位为bytes。默认值为134217728 bytes , 即128MB。

    • 当innodb_dedicated_server为OFF时 , 缓冲池大小的默认值为128M

    • 当innodb_dedicated_server为ON时 , 根据内存大小自动设置缓冲池大小 , 具体策略如下：

      • 内存<1G: 缓冲池大小设置为 128M

      • 内存<=4G: 缓冲池大小设置为 探测到的物理内存 * 0.5

      • 内存>4G : 缓冲池大小设置为探测到的物理内存 * 0.75

  • innodb_redo_log_capacity : 定义redo log日志文件占用的磁盘空间大小。MySQL会生成32个 redo log文件,每个文件的大小等于1/32 * innodb_redo_log_capacity。该选项已经替代了innodb_log_files_in_group 和 innodb_log_file_size这两个变量。所以当这个选项innodb_redo_log_capacity设置的时候innodb_log_files_in_group 和 innodb_log_file_size 这两个变量的设置将会被忽略。innodb_redo_log_capacity默认大小为104857600字节(100MB) , 最大redo日志容量为128GB。

    在MySQL 8.0.30之前 , InnoDB默认在数据目录中创建两个重做日志文件 , 分别名为ib_logfile0和ib_logfile1 , 并以循环方式写入这些文件。

    在MySQL 8.0.30版本中，MySQL会生成32个 redo log文件,每个文件的大小等于1/32 * innodb_redo_log_capacity。 修改redo log容量大小后每个文件大小可能会有所不同。Redo log有两种 : 正在使用的(#ib_redoNN)和未被使用的(#ib_redoNN_tmp) , 其中NN是Redo日志文件编号。活跃的redo日志共有6个, 备用Redo日志文件共有26个

  • innodb_log_file_size: InnoDB事务日志文件的大小。可以根据系统的事务负载和可用磁盘空间设置它。如果将其设置得太小 , 可能会导致频繁的日志文件切换 , 影响性能 ; 如果将其设置得太大 ,可能会影响系统的崩溃恢复时间。MySQL 8.0.30已经弃用该选项

    • 当innodb_dedicated_server为OFF时 , 日志文件大小的默认值为48M。

    • 当innodb_dedicated_server为ON时 , 根据内存大小自动设置日志文件大小 。在MySQL 8.0.13之前的版本(MySQL 8.*) 中 , 根据服务器内存来动态设置innodb_log_file_size大小。规则如下具体策略如下:

      • 内存<1G : 日志文件大小设置为48M

      • 内存<=4G : 日志文件大小设置为128M

      • 内存<=8G : 日志文件大小设置为 512M

      • 内存<=16G: 日志文件大小设置为1024M

      • 内存>16G: 日志文件大小设置为2G

    • 从MySQL 8.0.14开始 , 根据buffer pool size的大小进行配置innodb_log_file_size参数

      • 内存<=8G : 日志文件大小设置为 512M

      • 内存<=128G: 日志文件大小设置为1024M

      • 内存>128G: 日志文件大小设置为2G

  • innodb_log_files_in_group : 是MySQL InnoDB存储引擎中控制Redo log文件数量的一个参数。其默认值和推荐值都是2。如果设置了innodb_dedicated_server , 日志文件数量会自动根据缓冲池大小进行配置。数值范围为2-100。MySQL 8.0.30已经弃用该选项

    • 当innodb_dedicated_server为OFF时 , 默认为设置为2个文件数量。

    • 当innodb_dedicated_server为ON时 。buffer存储池大小值为NG。

      • 如果round(N)小于2, 则innodb_log_files_in_group默认为2。

      • 如果N小于等于8 , 则innodb_log_files_in_group的值为round(N)

      • 如果N小于等于128G , 则innodb_log_files_in_group的值为round(N*0.75)

      • 如果N大于128G ,则innodb_log_files_in_group默认为64

      注 : round()表示指定的小数位数进行四舍五入运算

    Redo log是一个环形的数据结构，它记录了InnoDB存储引擎中的所有修改操作 , 以便在数据库崩溃后进行恢复。Redo log的空间大小由innodb_log_file_size参数控制 , 它指定了每个Redo log文件的大小。innodb_log_files_in_group和innodb_log_file_size两个参数相乘可以得到总的可用Redo log空间。

  • innodb_flush_method : 是一个用来控制InnoDB数据文件和redo log打开、刷写模式的参数。是一个global级别的只读参数，不能动态修改。它有三个可选值 :

    • fdatasync(默认值) : 调用系统函数fsync()来刷新操作系统缓冲区，将数据文件和redo log中的buffer写入磁盘。

    • O_DSYNC : 使用O_SYNC方式打开和刷写redo log , 使用fsync()刷新数据文件。这种方式会降低写入磁盘的速度，但不会明显降低整体SQL请求量。

    • O_DIRECT : 使用O_DIRECT方式打开数据文件 , 使用fsync()刷新数据文件和redo log。这种方式会最小化缓冲对IO的影响，减少CPU处理压力和物理内存占用，但写入磁盘的速度会明显降低。 使用O_DIRECT会绕过操作系统的缓存系统, 消除本地缓存影响, 强制InnoDB每次都直接访问存储系统 , 这可能会对性能有所提高。如果任何查询都需要进行表扫描 , 则需要访问磁盘。使用O_DIRECT时 , 内存中没有磁盘缓存可用, 因此必须直接访问存储介质。

    • O_DIRECT和O_DIRECT_NO_FSYNC 。这两种I/O模式的区别如下：

      • O_DIRECT : 对于读/写操作 , 都会跳过OS cache , 直接在device(disk)上读/写 , 然后使用fsync()刷元数据。

      • O_DIRECT_NO_FSYNC : 在InnoDB刷新I/O期间使用O_DIRECT , 但在每次写操作后跳过fsync()系统调用。在MySQL 8.0.14之前 , 这种设置对于需要fsync()系统调用才能同步文件系统元数据更改的文件系统(例如XFS和EXT4)不适用。但是从MySQL 8.0.14开始 , 为了确保文件系统元数据更改的同步 , 调用了fsync()系统调用。但是每次写操作后仍然跳过fsync()系统调用。

    与innodb_dedicated_server相关设置

    • 当innodb_dedicated_server=ON时 , 该参数会根据官方文档的说明被设置为O_DIRECT_NO_FSYNC。如果系统不允许该设置 , 则使用默认的Flush method。

    • 当innodb_dedicated_server=OFF时 , 该参数默认取值为fsync。

  • innodb_io_capacity : 定义了InnoDB后台任务每秒可用的I/O操作数(IOPS) , 例如用于从buffer pool中刷新脏页和从change buffer中合并数据。该参数是innodb后台进程最大的I/O性能指标 , 影响刷新脏页和插入缓冲的数量。默认值为200。

    • 该参数对读无效 , 只对写有决定意义。因此只有在频繁写操作的时候才有意义 , 不适用于读操作。

    • 该参数可以提高TPS(吞吐性能)。在高转速磁盘下 , 尤其是现在SSD盘得到普及 , 可以根据需要适当提高该参数的值。

    • 具体的配置建议是 : innodb_io_capacity的值为innodb_io_capacity_max的一半 , 默认innodb_io_capacity=100。

    • innodb_io_capacity推荐值如下:

      • SATA/SAS硬盘 : 200

      • SAS RAID10 : 2000

      • SSD硬盘 : 8000

      • Fusion-io(闪存卡): 25000-50000

获取mysqld的可用选项列表

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults  

范例 :

#查看可用选项列表和当前值
[root@centos8 ~]#/usr/libexec/mysqld --verbose --help
#查看mysqld的当前启动选项
[root@centos8 ~]#/usr/libexec/mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--plugin-load-add=auth_gssapi.so --datadir=/var/lib/mysql --
socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mariadb/mariadb.log --pidfile=/run/mariadb/mariadb.pid

设置服务器选项方法:

  1. 在命令行中设置

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

  2. 在配置文件my.cnf中设置

    范例:

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

    范例: skip-grant-tables是服务器选项,但不是系统变量

    [root@centos8 ~]#mysqladmin variables |grep skip_grant_tables
[root@centos8 ~]#mysql 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-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 'skip_grant_tables';
Empty set (0.001 sec)

2.2 服务器系统变量

MySQL中的系统变量是MySQL自带的变量参数 , 用于对MySQL服务器的配置 , 其对所有客户端生效。系统变量根据其作用范围可分为全局级(Global)和会话级(Session) , 顾名思义 , 前者是对整个MySQL服务器生效 , 后者只对当前连接的客户端会话生效。有些系统变量会同时拥有全局级和会话级。客户端连接之后 , 会使用全局级的系统变量来对当前会话的会话级系统变量进行初始化工作。需要注意的是 , 客户端连接建立之后 , 全局级系统变量的修改将不会影响到该客户端的会话级系统变量中。

在MySQL中 , 有两种变量类型 : 局部变量以"@"开头 , 形式为"@variable_name" ;全局变量以"@@"开头 , 形式为"@@variable_name"。两种变量不区分大小写，但它们的用法略有不同。全局变量是系统中只读的，可以在配置文件中进行修改。

局部变量需要在存储过程或函数中定义。局部变量的作用域仅限于定义它的BEGIN END块中 , 而且必须是BEGIN END块中的第一条语句。

系统变量的设置有以下几种方式:

  • 修改MySQL源代码 , 重新编译MySQL(仅适用于高级用户)。

  • 在MySQL配置文件中修改MySQL系统变量的值(需要重启MySQL服务才会生效)。

  • 在MySQL服务运行期间 , 使用SET命令重新设置系统变量的值。

MySQL提供了许多系统变量，其中包括全局变量和会话变量。全局变量是针对整个服务器的 , 即所有的连接都起作用。若服务器重启 , 则会恢复原始状态 , 之前所做的更改都会失效。会话变量(局部变量)只针对当前客户端当次连接有效 , 不影响其他连接。

  • 查看系统变量

    • 查看所有的全局变量

      SHOW GLOBAL VARIABLES;

    • 查看所有变量(包括global和session)

      SHOW [SESSION] VARIABLES;
​
#等同于
[root@centos8 ~]#mysqladmin variables

    • 查看指定的系统变量

      SHOW [SESSION]VARIABLES LIKE 'VAR_NAME';
SELECT @@session.var_name

      后面加上like语句进行变量名匹配，其pattern中同样支持 %进行模糊匹配。例如 , 可以使用show variables like 'log%'来查看所有以“log”开头的系统变量

    • 查看指定的全局系统变量

      SHOW GLOBAL VARIABLES LIKE 'VAR_NAME';
SELECT @@global.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;

    使用命令HELP SET查看对应帮助语法。

常用系统变量

  • character_set_results : 用于向客户端返回查询结果的字符集。这包括结果数据(如列值)、结果元数据(如列名)和错误消息。

  • max_connections : 允许的客户端同时最大连接数量。

  • open_files_limit : 操作系统中mysqld可用的文件描述符的数目。mysql实际的取值会从下面四个值当中获取最大的

    • 10 + maxconnections + (tableopen_cache * 2)

    • max_connections * 5

    • 通过limit直接设置。

    • 如果系统中limit设置为infinity ,通过open_files_limit直接设置对应参数,否则为5000。

    另外可以通过systemd服务文件,对应的service文件添加LimitNOFILE=655350 ,实现配置文件描述符。

  • innodb_page_size : InnoDB表空间的页面大小。作为innodb和OS交互单位。文件系统对文件的buffer IO , 也是page为单位进行处理的。Linux的默认page的大小4096字节 , 当要将数据写入到文件的时候 , 会先在内存里 , 然后将对应的 page cache , 整个的从内存刷到磁盘上。但是如果要写入的文件区域 , 因为还没有被缓存或者被置换出去了等原因 , 在内存里不存在对应的page cache, 则需要先将对应page的内容从磁盘上读到内存里 , 修改要写入的数据 , 然后在将整个page 写回到磁盘 ; 在这种情况下 , 会有一次额外的读IO开销 , IO的性能会有一定的损失。假如mysql的16K的页表数据支持起 , 那就是一次内存IO光是虚拟地址到物理地址的转换就要去内存查 4 次页表 , 再算上真正的内存访问 , 需要5次内存IO才能获取一个内存数据。

范例: character_set_results是系统变量并非服务器选项

[root@centos8 ~]#mysql 
MariaDB [(none)]> show variables like 'character_set_results';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| character_set_results | utf8 |
+-----------------------+-------+
1 row in set (0.001 sec)
​
MariaDB [(none)]> set character_set_results="utf8mb4";
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.001 sec)
​
[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.

范例: 修改mysql的最大并发连接数

#默认值为151
[root@centos8 ~]#mysqladmin variables |grep '\<max_connections'
| max_connections                                       | 151
[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.002 sec)
​
MariaDB [(none)]> set global max_connections=2000;
Query OK, 0 rows affected (0.001 sec)
​
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.001 sec)
​
​
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
max_connections = 8000
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql 
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 8000  |
+-----------------+-------+
1 row in set (0.001 sec)
​
MariaDB [(none)]> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              8000 |
+-------------------+
1 row in set (0.000 sec)
​
​
#方法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
[root@centos8 ~]#systemctl daemon-reload
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p -e "select @@max_connections"
Enter password: 
+-------------------+
| @@max_connections |
+-------------------+
|              8000 |
+-------------------+

范例 : 修改页大小

参看：https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_size

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

[root@centos8 ~]#mysqladmin variables |grep innodb_page_size
| innodb_page_size                                       | 16384
[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.001 sec)
​
[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
​
MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 65536 |
+------------------+-------+
1 row in set (0.001 sec)
​
​
​

2.3 服务器状态变量

MySQL系统状态变量是反映MySQL数据库服务器自上次启动以来的累计相关状态信息的变量 , 分为会话级别与全局级别状态信息。状态变量，用于保存mysqld运行中的统计数据的变量 不可更改 , 为只读属性,由系统更新。

MySQL服务器启动后 , 会将所有的状态变量初始化为默认值。状态变量的值是从服务器启动以来的累计值 , 而不是当前运行状态的值。

MySQL的状态变量有很多，比如Aborted_clients、Bytes_received、Bytes_sent、Com_select、Connections等等。可以使用SHOW STATUS命令查看所有的状态变量,或者在命令行运行mysqladmin extended-status来查看这些状态变量。

范例:

MariaDB [(none)]> show status like "innodb_page_size";
+------------------+-------+
| Variable_name   | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> SHOW GLOBAL STATUS like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select   | 5     |
+---------------+-------+
1 row in set (0.001 sec)  
​
MariaDB [(none)]> show status like "connections";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 12    |
+---------------+-------+
1 row in set (0.001 sec)
​
​

2.4 服务器变量SQL_MODE

SQL_MODE , 对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置。SQL_MODE 对SQL语句的执行产生影响。SQL_MODE可以改变MySQL服务器的某些行为 , 使其更符合SQL语言标准或是与其他数据库服务器或老版本的MySQL服务器保持兼容。SQL_MODE的设置可以允许一些非法操作 , 因此在生产环境中 , 必须将其设置为严格模式 , 以便检测和避免潜在的问题。

参考:

https://mariadb.com/kb/en/library/sql-mode/

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode

常见MODE:

  • NO_AUTO_CREATE_USER : 禁止GRANT创建密码为空的用户

  • NO_ZERO_DATE : 在严格模式 , 不允许使用0000-00-00的时间

  • ONLY_FULL_GROUP_BY : 对于GROUP BY聚合操作 , 如果在SELECT中的列 , 没有在GROUP BY中出现 , 那么将认为这个SQL是不合法的

  • NO_BACKSLASH_ESCAPES : 反斜杠\作为普通字符而非转义字符

  • PIPES_AS_CONCAT : 将||视为连接操作符而非或运算符

  • ANSI : 使MySQL的行为更符号ANSI SQL标准

  • TRADITIONAL : 启用所有严格模式和更多严格检查

  • STRICT_TRANS_TABLES : 在插入或更新数据时,如果数据类型不匹配,则产生错误

  • STRICT_ALL_TABLES : STRICT_TRANS_TABLES的加强版,要求所有表都遵循严格模式

  • NO_ZERO_DATE : 禁止在日期和时间中使用0000-00-00 00:00:00。

  • ERROR_FOR_DIVISION_BY_ZERO : 在除以零时产生错误，而不是警告。

范例: CentOS 8修改SQL_MODE变量实现分组语句控制

MariaDB [(none)]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [(none)]> select @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
​
​
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|    NULL |        2 |
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
8 rows in set (0.000 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
+-------+---------+----------+
| stuid | classid | count(*) |
+-------+---------+----------+
|    24 |    NULL |        2 |
|     2 |       1 |        4 |
|     1 |       2 |        3 |
|     5 |       3 |        4 |
|     4 |       4 |        4 |
|     6 |       5 |        1 |
|     9 |       6 |        4 |
|     8 |       7 |        3 |
+-------+---------+----------+
8 rows in set (0.001 sec)
#修改SQL_MODE
MariaDB [hellodb]> set sql_mode="ONLY_FULL_GROUP_BY";
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value             |
+---------------+--------------------+
| sql_mode     | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|    NULL |        2 |
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
ERROR 1055 (42000): 'hellodb.students.StuID' isn't in GROUP BY
​
​

范例：CentOS 7 修改SQL_MODE变量

MariaDB [hellodb]> create table test (id int ,name varchar(3));
Query OK, 0 rows affected (0.04 sec)
MariaDB [hellodb]> insert test values(1,'abcde');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [hellodb]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
​
​
MariaDB [hellodb]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | abc |
+------+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode     |       |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| Variable_name | Value                                                         
                                                                                
      |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
| sql_mode     | 
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV
ISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------------------
--------------------------------------------------------------------------------
-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> insert test values(2,'magedu');
ERROR 1406 (22001): Data too long for column 'name' at row 1
​


熊熊

site logo

Hi,Friend

© 2025 cbear小站