MySQL8.0大师之路：第11章：MySQL服务器管理-11.1 MySQL服务器（mysqld）

JeeStudy

    MySQL服务器（mysqld）是完成MySQL安装中大部分工作的主程序。


以下讨论涵盖了这些MySQL服务器配置主题：
    •服务器支持的启动选项。您可以在命令行上，通过配置文件或同时在这两者上指定这些选项。
    •服务器系统变量。这些变量反映了启动选项的当前状态和值，其中一些可以在服务器运行时进行修改。
    •服务器状态变量。这些变量包含有关运行时操作的计数器和统计信息。
    •如何设置服务器SQL模式。此设置修改了SQL语法和语义的某些方面，例如为了与其他数据库系统的代码兼容，或控制特定情况下的错误处理。
    •配置和使用IPv6支持。
    •配置和使用时区支持。
    •服务器端帮助功能。
    •服务器关闭过程。根据表的类型（事务性或非事务性）以及是否使用复制，需要考虑性能和可靠性。





配置服务器
MySQL服务器mysqld具有许多命令选项和系统变量，可以在启动时进行设置以配置其操作。要确定服务器使用的默认命令选项和系统变量值，请执行以下命令：

[root@CentOS8 ~]# mysqld -verbose --help


要在服务器运行时查看服务器实际使用的当前系统变量值，请连接至该服务器并执行以下语句：
mysql> SHOW VARIABLES;


要查看正在运行的服务器的一些统计信息和状态指示器，请执行以下语句：
mysql> SHOW STATUS;


系统变量和状态信息也可以使用mysqladmin命令获得：
[root@CentOS8 ~]# mysqladmin -p variables
[root@CentOS8 ~]# mysqladmin -p extended-status


服务器配置验证
从MySQL 8.0.16开始，MySQL Server支持--validate-config选项，该选项使您无需运行服务器即可检查启动配置是否存在问题：
mysqld --validate-config
如果未发现错误，则服务器以退出代码0终止。如果发现错误，则服务器显示诊断消息，并以退出代码1终止。
如：
[root@CentOS8 ~]# mysqld --validate-config --no-such-option
2020-04-14T13:37:28.109075Z 0 [ERROR] [MY-000068] [Server] unknown option '--no-such-option'.
2020-04-14T13:37:28.109176Z 0 [ERROR] [MY-010119] [Server] Aborting



一旦发现任何错误，服务器将终止。要进行其他检查，请更正最初的问题，然后使用--validate-config再次运行服务器。



对于前面的示例，在使用--validate-config导致显示错误消息的情况下，服务器退出代码为1。根据log_error_verbosity值，也可能会显示警告消息和信息消息，但不会立即终止验证或退出代码为1。例如，此命令将产生多个警告，并同时显示这两个警告。但是没有错误发生，因此退出代码为0：

例如：[root@CentOS8 ~]# mysqld --validate-config --log_error_verbosity=2 --read-only=s --transaction_read_only=s




此命令会产生相同的警告，但也会产生错误，因此错误消息将与警告一起显示，并且退出代码为1：
例如：mysqld --validate-config --log_error_verbosity=2 --no-such-option --read-only=s --transaction_read_only=s




--validate-config选项的范围仅限于配置检查，以确保服务器可以在不启动过程的情况下执行该检查。这样，配置检查不会初始化存储引擎和其他插件，组件等，也不会验证与那些未初始化的子系统关联的选项。
--validate-config可以随时使用，但在升级后特别有用，它可以检查升级的服务器是否认为旧的服务器先前使用的任何选项是否已弃用或已作废。例如，tx_read_only系统变量在MySQL 5.7中已弃用，在8.0中已删除。假设使用my.cnf文件中的系统变量运行MySQL 5.7服务器，然后将其升级到MySQL 8.0。使用--validate-config运行升级的服务器以检查配置会产生以下结果：

shell> mysqld --validate-config
2018-11-05T10:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable 'tx_read_only=ON'.
2018-11-05T10:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting





--validate-config可以与--defaults-file选项一起使用，以仅验证特定文件中的选项：
shell> mysqld --defaults-file=./my.cnf-test --validate-config
2018-11-05T10:40:02.712141Z 0 [ERROR] [MY-000067] [Server] unknown variable 'tx_read_only=ON'.
2018-11-05T10:40:02.712178Z 0 [ERROR] [MY-010119] [Server] Aborting



请记住，-defaults-file（如果已指定）必须是命令行上的第一个选项。 （以相反的顺序执行前面的示例会产生一条消息，即--defaults-file本身是未知的。）





SQL模式
MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值将这些模式不同地应用于不同的客户端。 DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序都可以将其会话SQL模式设置为自己的要求。模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用更加容易。



MySQL 8.0中的默认SQL模式包括以下模式：
ONLY_FULL_GROUP_BY，
STRICT_TRANS_TABLES，
NO_ZERO_IN_DATE，
NO_ZERO_DATE，
ERROR_FOR_DIVISION_BY_ZERO，
NO_ENGINE_SUBSTITUTION。


要在服务器启动时设置SQL模式，请在命令行上使用--sql-mode =“ modes”选项，或者在选项文件中使用sql-mode =“ modes”，例如my.cnf（Unix操作系统）或my .ini（Windows）。模式是由逗号分隔的不同模式的列表。要显式清除SQL模式，请在命令行上使用--sql-mode =“”或在选项文件中使用sql-mode =“”将其设置为空字符串。

注意：MySQL安装程序可能会在安装过程中配置SQL模式。如果SQL模式不同于默认模式或期望的模式，请检查服务器在启动时读取的选项文件中的设置。


要在运行时更改SQL模式，请使用SET语句设置全局或会话sql_mode系统变量：
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';



设置GLOBAL变量需要SYSTEM_VARIABLES_ADMIN或SUPER特权，并且会影响此后连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。每个客户端可以随时更改其会话sql_mode值。



要确定当前的全局或会话sql_mode设置，请使用：
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;



重要提示：
SQL模式和用户定义的分区。在将数据创建并插入分区表后更改服务器SQL模式可能会导致此类表的行为发生重大变化，并可能导致数据丢失或损坏。强烈建议您一旦使用用户定义的分区创建表，就不要更改SQL模式。复制分区表时，主服务器和从服务器上不同的SQL模式也会导致问题。为了获得最佳结果，您应该始终在主服务器和从服务器上使用相同的服务器SQL模式。



最重要的SQL模式
最重要的sql_mode值可能是这些


•ANSI
此模式更改语法和行为，使其更符合标准SQL。
•STRICT_TRANS_TABLES
如果无法按给定值插入事务表中的值，请中止该语句。对于非事务表，如果该值出现在单行语句或多行语句的第一行中，则中止该语句。
•TRADITIONAL
使MySQL表现得像“传统” SQL数据库系统。当在列中插入不正确的值时，此模式的简单描述是“给出错误而不是警告”。

注意：启用TRADITIONAL模式后，一旦发生错误，INSERT或UPDATE将中止。如果您使用的是非事务性存储引擎，则可能不是您想要的，因为在错误之前进行的数据更改可能不会回滚，从而导致“部分完成”更新。
当提及“严格模式（strict mode,”）”时，表示已启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES之一或两者都启用的模式。


验证系统对IPv6的支持
以下列表描述了所有受支持的SQL模式：
在MySQL Server可以接受IPv6连接之前，服务器主机上的操作系统必须支持IPv6。作为确定是否正确的简单测试，请尝试以下命令：

[root@CentOS8 init.d]# ping6 ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.741 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.055 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.052 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.100 ms
64 bytes from ::1: icmp_seq=5 ttl=64 time=0.074 m



要生成系统网络接口的描述，请调用ifconfig -a并在输出中查找IPv6地址。


配置MySQL服务器以允许IPv6连接
MySQL服务器在一个或多个网络套接字上侦听TCP / IP连接。每个套接字都绑定到一个地址，但是一个地址可以映射到多个网络接口。在服务器启动时设置bind_address系统变量，以指定服务器实例接受的TCP / IP连接。从MySQL 8.0.13开始，您可以为此选项指定多个值，包括IPv6地址，IPv4地址和解析为IPv6或IPv4地址的主机名的任意组合。或者，您可以指定允许在多个网络接口上侦听的通配符地址格式之一。缺省值*或::允许在所有服务器主机IPv4和IPv6接口上同时进行IPv4和IPv6连接。



使用IPv6本地主机地址进行连接
以下过程显示了如何配置MySQL，以允许使用:: 1本地主机地址连接到本地服务器的客户端进行IPv6连接。此处给出的说明假定您的系统支持IPv6。





1.使用适当的bind_address设置启动MySQL服务器，以使其接受IPv6连接。
例如，将以下行放入服务器选项文件中，然后重新启动服务器：

[mysqld]
bind_address = *



将*（或：:）指定为bind_address的值将允许所有服务器主机IPv4和IPv6接口上的IPv4和IPv6连接。如果要将服务器绑定到特定的地址列表，则可以从MySQL 8.0.13开始，通过为bind_address指定一个逗号分隔的值列表来实现。此示例指定IPv4和IPv6的本地主机地址：

[mysqld]
bind_address = 127.0.0.1,::1



2.以管理员身份，连接到服务器并为本地用户创建一个帐户，该帐户将通过:: 1本地IPv6主机地址进行连接：
mysql> CREATE USER 'ipv6user'@'::1' IDENTIFIED BY 'ipv6pass';


3.调用mysql客户端以使用新帐户连接到服务器：
shell> mysql -h ::1 -u ipv6user -pipv6pass


4.尝试一些显示连接信息的简单语句：
mysql> status
--------------
mysql  Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)


Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.19 MySQL Community Server - GPL
Protocol version:       10
Connection:             ::1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 47 min 27 sec


Threads: 3  Questions: 11  Slow queries: 0  Opens: 132  Flush tables: 3  Open tables: 52  Queries per second avg: 0.003



上面的信息可以与socket登录的做比较：
mysql> status
--------------
mysql  Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)


Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.19 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /tmp/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 46 min 48 sec


Threads: 2  Questions: 7  Slow queries: 0  Opens: 132  Flush tables: 3  Open tables: 52  Queries per second avg: 0.002



查看当前MySQL服务器绑定了哪些地址：
mysql> SELECT CURRENT_USER(), @@bind_address;


使用IPv6非本地主机地址进行连接
以下过程显示了如何配置MySQL以允许远程客户端进行IPv6连接。与本地客户端的上述过程类似，但是服务器和客户端主机是不同的，并且每个主机都有自己的非本地IPv6地址。该示例使用以下地址：

Server host: 2001:db8:0:f101::1
Client  host: 2001:db8:0:f101::2



这些地址是从IANA建议的非路由地址范围中选择的，出于文档目的，并且足以在本地网络上进行测试。要接受来自本地网络外部客户端的IPv6连接，服务器主机必须具有公共地址。如果您的网络提供商为您分配了一个IPv6地址，则可以使用该地址。否则，获取地址的另一种方法是使用IPv6代理



1.使用适当的bind_address设置启动MySQL服务器，以使其接受IPv6连接。例如，将以下行放入服务器选项文件中，然后重新启动服务器：

[mysqld]
bind_address = *



将*（或：:）指定为bind_address的值将允许所有服务器主机IPv4和IPv6接口上的IPv4和IPv6连接。
如果要将服务器绑定到特定的地址列表，则可以从MySQL 8.0.13开始，通过为bind_address指定一个逗号分隔的值列表来实现。
本示例指定一个IPv4地址以及所需的服务器主机IPv6地址：



[mysqld]
bind_address = 198.51.100.20,2001:db8:0:f101::1





2.在服务器主机（2001：db8：0：f101 :: 1）上，为将从客户端主机（2001：db8：0：f101 :: 2）连接的用户创建一个帐户：
mysql> CREATE USER 'remoteipv6user'@'2001:db8:0:f101::2' IDENTIFIED BY 'remoteipv6pass';


3.在客户端主机（2001：db8：0：f101 :: 2）上，调用mysql客户端以使用新帐户连接到服务器：
shell> mysql -h 2001:db8:0:f101::1 -u remoteipv6user -premoteipv6pass


4.尝试一些显示连接信息的简单语句：mysql> STATUS
...
Connection: 2001:db8:0:f101::1 via TCP/IP
...
mysql> SELECT CURRENT_USER(), @@bind_address;
+-----------------------------------+----------------+
| CURRENT_USER() | @@bind_address |
+-----------------------------------+----------------+
| remoteipv6user@2001:db8:0:f101::2 | :: |
+-----------------------------------+----------------+







MySQL服务器时区支持
MySQL Server维护几个时区设置：
•系统时区。服务器启动时，它将尝试自动确定主机的时区，并使用它来设置system_time_zone系统变量。此后该值不变。要在启动时为MySQL Server明确指定系统时区，请在启动mysqld之前设置TZ环境变量。
如果使用mysqld_safe启动服务器，则其--timezone选项提供了另一种设置系统时区的方法。 TZ和--timezone的允许值取决于系统。


•服务器当前时区。
全局time_zone系统变量指示服务器当前正在运行的时区。time_zone的初始值为'SYSTEM'，表示服务器时区与系统时区相同。



注意：如果设置为SYSTEM，则每个需要时区计算的MySQL函数调用都会进行系统库调用以确定当前系统时区。此调用可能受到全局互斥锁的保护，从而导致争用。




初始全局服务器时区值可以在启动时使用命令行上的--defaulttime-zone选项显式指定，也可以在选项文件中使用以下行：
default-time-zone='timezone'


如果您具有SYSTEM_VARIABLES_ADMIN或SUPER特权，则可以使用以下语句在运行时设置全局服务器时区值：
SET GLOBAL time_zone = timezone;


每会话时区。每个连接的客户端都有自己的会话时区设置，由session time_zone变量指定。最初，会话变量从全局time_zone变量获取其值，但是客户端可以使用以下语句更改其自己的时区：

SET time_zone = timezone;
会话时区设置会影响对时区敏感的时间值的显示和存储。这包括由NOW（）或CURTIME（）之类的函数显示的值，以及在TIMESTAMP列中存储和检索的值。 TIMESTAMP列的值从会话时区转换为UTC以进行存储，并从UTC转换为会话时区以进行检索。
会话时区设置不影响UTC_TIMESTAMP（）之类的函数显示的值或DATE，TIME或DATETIME列中的值。这些数据类型中的值也不会存储在UTC中；仅当从TIMESTAMP值转换时，时区才适用。如果要使用DATE，TIME或DATETIME值的特定于语言环境的算术，请将它们转换为UTC，执行算术，然后再转换回去。
可以像这样查看当前的全局和会话时区值：

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;


时区值可以以多种格式给出，都不区分大小写：
•作为值'SYSTEM'，表示服务器时区与系统时区相同。
•作为字符串，以[H] H：MM形式表示与UTC的偏移量，并带有+或-前缀，例如'+10：00'，'-6:00'或'+05：30'。前导零可用于小于10的小时值；在这种情况下，MySQL在存储和检索该值时会加上前导零。 MySQL将'-00：00'或'-0：00'转换为'+00：00'。在MySQL 8.0.19之前，此值必须在'-12：59'到'+13：00'的范围内（包括端点）；从MySQL 8.0.19开始，允许的范围是“ -14：00”到“ +14：00”（包括首尾）。
•作为命名时区，例如“欧洲/赫尔辛基”，“美国/东部”或“ MET”。仅当已创建并填充mysql数据库中的时区信息表时，才能使用命名时区。





填充时区表
mysql系统模式中存在几个表来存储时区信息。 MySQL安装过程会创建时区表，但不会加载它们。为此，请按照以下说明进行操作。

加载时区信息不一定是一次性操作，因为该信息会偶尔更改。当发生此类更改时，使用旧规则的应用程序将过时，您可能会发现有必要重新加载时区表，以使MySQL服务器使用的信息保持最新。



如果您的系统有自己的zoneinfo数据库（描述时区的文件集），请使用mysql_tzinfo_to_sql程序加载时区表。此类系统的示例是Linux，macOS，FreeBSD和Solaris。这些文件的一个可能位置是/ usr / share / zoneinfo目录。如果您的系统没有zoneinfo数据库，则可以使用可下载的程序包，如本节后面所述。要从命令行加载时区表，请将zoneinfo目录路径名传递给mysql_tzinfo_to_sql并将输出发送到mysql程序。
例如：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql


此处显示的mysql命令假定您使用诸如root之类的帐户连接到服务器，该帐户具有修改mysql系统模式中表的权限。根据需要调整连接参数。
mysql_tzinfo_to_sql读取系统的时区文件并从中生成SQL语句。 mysql处理这些语句以加载时区表。
mysql_tzinfo_to_sql也可以用于加载单个时区文件或生成leap秒信息：

•要加载与时区名称tz_name对应的单个时区文件tz_file，请像这样调用mysql_tzinfo_to_sql：
mysql_tzinfo_to_sql tz_file tz_name | mysql -u root -p mysql
使用这种方法，您必须执行一个单独的命令来为服务器需要知道的每个命名区域加载时区文件。
•如果您的时区必须占leap秒，请像这样初始化leap秒信息，其中tz_file是您的时区文件的名称

mysql_tzinfo_to_sql --leap tz_file | mysql -u root -p mysql
运行mysql_tzinfo_to_sql后，请重新启动服务器，以使其不再继续使用任何以前缓存的时区数据。
如果您的系统没有zoneinfo数据库（例如Windows），则可以使用包含SQL语句的软件包，该软件包可从MySQL开发人员区域下载：

https://dev.mysql.com/downloads/timezones.html


如果您的系统具有zoneinfo数据库，请不要使用可下载的时区软件包。请改用mysql_tzinfo_to_sql实用程序。否则，可能会导致MySQL和系统上其他应用程序之间在日期时间处理上有所不同。



要使用已下载的SQL语句时区包，请对其进行解压缩，然后将解压缩的文件内容加载到时区表中：
mysql -u root -p mysql < file_name
然后重启服务。


重要提示：不要使用包含MyISAM表的可下载的时区包。这适用于较旧的MySQL版本。 MySQL现在将InnoDB用于时区表。尝试用MyISAM表替换它们会引起问题。