MySQL 之服务器配置和状态

1 MySQL 架构和性能优化

1.3 服务器配置和状态

设置 MySQL 服务的特性，可以通过 mysqld 服务选项，服务器系统变量和服务器状态变量这三个方面来进行设置和查看。

官方文档

https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.htmlhttps://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.htmlhttps://mariadb.com/kb/en/variables-and-modes/

文档说明

Name	Cmd-Line	OptionFile	SystemVar	StatusVar	VarScope	Dynamic
名称	是否能在命令行下设置	是否能写配置文件	是否是系统变量	是否是状态变量	作用范围	是否能动态修改

• Cmd-Line 和 Opton File 列的值如果是 Yes，则表示该项是服务器选项

• System Var 列的值如果是 Yes，则表示该项是系统变量

• Status Var 列的值如果是 Yes，则表示该项是状态变量

• Option File 指配置文件

• 服务器选项通常在命令行后面添加或在配置文件中设置

• 状态变量表示的是当前的一个状态值

• 变量生效范围有三种，分别是全局，会话，全局和会话，Var Scope 列对应的值分别是 Global，Session，Both

• Dynamic 列表示是否可以动态修改，如果该列值为 No，则表示不可修改，状态变量都不可修改，部分系统变量也不可修改

• 一个配置项可以同时是服务器选项，系统变量，状态变量这三种中的两种，但不会同时是三种角色

  1.3.1 服务器选项

查看所有可用选项列表

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

查看服务启动时在命令行下添加的选项

[root@localhost ~]# ps aux | grep mysqldmysql 2423 0.6 23.7 1836108 433416 ? Ssl 13:01 3:27 /usr/libexec/mysqld --basedir=/usr
#这个选项是配置在服务脚本中的[root@localhost ~]# systemctl cat mysqld.service | grep basedir# Note: we set --basedir to prevent probes that might trigger SELinux alarms,ExecStart=/usr/libexec/mysqld --basedir=/usr

查看当前服务启动选项

[root@localhost ~]# mysqld --print-defaults/usr/libexec/mysqld would have been started with the following arguments:--default_authentication_plugin=mysql_native_password --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysql/mysqld.log --pid-file=/run/mysqld/mysqld.pid
#这些选项都是写在配置文件中的[root@localhost ~]# cat /etc/my.cnf.d/mysql-server.cnf......[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pid
[root@localhost ~]# cat /etc/my.cnf.d/mysql-default-authentication-plugin.cnf......[mysqld]default_authentication_plugin=mysql_native_password

在命令行中设置服务器选项

[root@localhost ~]# /usr/libexec/mysqld --basedir=/usr --max_connections=202 --user=mysql &[1] 9358
#查看mysql> show variables like 'max_connections%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 202   |+-----------------+-------+1 row in set (0.01 sec)

在配置文件中设置服务器选项​​​​​​​​​​​​​​

[root@localhost ~]# vim /etc/my.cnf[mysqld]max_connections=200
#查看，此时配置在在配置文件中可见，但并没有生效，需要重启[root@localhost ~]# mysqld --print-defaults/usr/libexec/mysqld would have been started with the following arguments:--default_authentication_plugin=mysql_native_password --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysql/mysqld.log --pid-file=/run/mysqld/mysqld.pid --max_connections=200
#查看选项，因为此项与变量同名mysql> show variables like 'max_connections%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.00 sec)
#重启服务[root@localhost ~]# systemctl restart mysqld.service
#再次查看mysql> show variables like 'max_connections%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 200   |+-----------------+-------+1 row in set (0.00 sec)​​​​​​​​​​​​​

#配置文件中的服务器选项，可以写下划线，也可以写中划线[root@localhost ~]# vim /etc/my.cnf[mysqld]#max_connections=200max-connections=201
#重启服务[root@localhost ~]# systemctl restart mysqld.service
#查看mysql> show variables like 'max_connections%';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 201   |+-----------------+-------+1 row in set (0.00 sec)

非服务器选项不能加配置文件​​​​​​​

[root@localhost ~]# vim /etc/my.cnf[mysqld]#max_connections=200max-connections=201character_set_database=utf8
#无法启动服务，因为 character_set_database 不是服务器选项[root@localhost ~]# systemctl restart mysqld.serviceJob for mysqld.service failed because the control process exited with error code.See "systemctl status mysqld.service" and "journalctl -xe" for details.
#查看具体错误信息[root@localhost ~]# tail /var/log/mysql/mysqld.log

1.3.2 服务器系统变量

服务器系统变量分为全局变量和会话变量两种，全局变量表示可以影响到所有连接终端，所有会话，会话变量只影响当前会话。

查看系统变量​​​​​​​

#查看所有全局变量mysql> show global variables 630
#查看所有变量，包括session和globalmysql> show session variablesmysql> show variables
#查看指定变量 SHOW VARIABLES LIKE 'VAR_NAME';mysql> show variables like 'sql_log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    |+---------------+-------+1 row in set (0.00 sec)
mysql> show variables like 'sql_log_%';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    || sql_log_off   | OFF   |+---------------+-------+2 rows in set (0.00 sec)
#查看指定变量 SELECT @@VAR_NAME;mysql> select @@sql_log_bin;+---------------+| @@sql_log_bin |+---------------+| 1             |+---------------+1 row in set (0.00 sec)

修改服务器系统变量

查看帮助

mysql> help set

修改变量时，on|true|1 代表开启，off|false|0 代表关闭。

修改全局变量：修改后全局生效，如果仅是变量，则对于己建立的连接不生效。​​​​​​​

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

仅是变量​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​

#终端mysql> show variables like 'sql_warnings';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_warnings  | OFF   |+---------------+-------+1 row in set (0.00 sec)
#终端Bmysql> select @@sql_warnings;+----------------+| @@sql_warnings |+----------------+| 0              |+----------------+1 row in set (0.00 sec)
#终端A中修改mysql> set global sql_warnings=1;Query OK, 0 rows affected (0.00 sec)
#终端A中查看mysql> show variables like 'sql_warnings';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_warnings  | OFF   |+---------------+-------+1 row in set (0.01 sec)
#终端B中查看mysql> select @@sql_warnings;+----------------+| @@sql_warnings |+----------------+| 0              |+----------------+1 row in set (0.00 sec)
#重新建立连接再查看mysql> select @@sql_warnings;+----------------+| @@sql_warnings |+----------------+| 1              |+----------------+1 row in set (0.00 sec)

又是变量又是选项​​​​​​​

#终端Amysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 100   |+------------------------+-------+1 row in set (0.01 sec)
#终端Bmysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 100   |+------------------------+-------+1 row in set (0.01 sec)
#在终端A中修改mysql> set mysqlx_max_connections=108;ERROR 1229 (HY000): Variable 'mysqlx_max_connections' is a GLOBAL variable and should be set with SET GLOBALmysql> set global mysqlx_max_connections=108;Query OK, 0 rows affected (0.00 sec)
#终端A再次查看mysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 108   |+------------------------+-------+1 row in set (0.00 sec)
#终端B再次查看mysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 108   |+------------------------+-------+1 row in set (0.00 sec)

修改会话变量：仅对当前会话有影响​​​​​​​

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

#终端Amysql> show variables like 'sql_log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    |+---------------+-------+1 row in set (0.00 sec)
#终端Bmysql> show variables like 'sql_log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    |+---------------+-------+1 row in set (0.00 sec)
#在终端A中修改mysql> set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'sql_log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | OFF   |+---------------+-------+1 row in set (0.01 sec)
#再次在终端B中查看，并不受影响mysql> show variables like 'sql_log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_log_bin   | ON    |+---------------+-------+1 row in set (0.00 sec)#重新连接，也不受影响

只读变量无法修改​​​​​​​

#该项的 Dynamic 列值为 No，不能动态修改mysql> show variables like 'admin_port';+---------------+-------+| Variable_name | Value |+---------------+-------+| admin_port    | 33062 |+---------------+-------+1 row in set (0.00 sec)
mysql> set global admin_port=33063;ERROR 1238 (HY000): Variable 'admin_port' is a read only variable

变量无法实现永久保存，重启服务后会被重置​​​​​​​

mysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 108   |+------------------------+-------+1 row in set (0.00 sec)
#重启服务[root@localhost ~]# systemctl restart mysqld.service
#再次查看mysql> show variables like 'mysqlx_max_connections';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| mysqlx_max_connections | 100   |+------------------------+-------+1 row in set (0.00 sec)

1.3.3 服务器状态变量

服务器状态变量：分全局和会话两种，其中许多变量有双重域，既是全局变量，也是会话变量，有相同的名字。

状态变量用于保存 MySQL 运行中的统计数据的变量，只读，不可修改。

查看状态变量​​​​​​​

#查看所有全局状态变量mysql> show global status;
#查看所有状态变量，包括global和sessionmysql> show status;mysql> show session status;

查看指定变量​​​​​​​​​​​​​​

mysql> show status like 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select    | 2     |+---------------+-------+1 row in set (0.00 sec)
mysql> select * from testdb.t1;Empty set (0.00 sec)
#查询次数增加mysql> show status like 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select    | 3     |+---------------+-------+1 row in set (0.00 sec)
#查看全局mysql> show global status like 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select    | 8     |+---------------+-------+1 row in set (0.00 sec)
#查看服务运行时长mysql> show status like 'Uptime';+---------------+-------+| Variable_name | Value |+---------------+-------+| Uptime        | 2503  |+---------------+-------+1 row in set (0.00 sec)
mysql> show global status like 'Uptime';+---------------+-------+| Variable_name | Value |+---------------+-------+| Uptime        | 2507  |+---------------+-------+1 row in set (0.00 sec)

重启服务后状态被重置​​​​​​​

[root@localhost ~]# systemctl restart mysqld.servicemysql> show status like 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select    | 1     |+---------------+-------+1 row in set (0.00 sec)
mysql> show global status like 'Uptime';+---------------+-------+| Variable_name | Value |+---------------+-------+| Uptime        | 4     |+---------------+-------+1 row in set (0.00 sec)

1.3.4 服务器变量 sql_mode

sql_mode 是服务器选项，也是变量，其值会影响 SQL 语句执行的工作模式。

官方文档​​​​​​​

https://mariadb.com/kb/en/library/sql-mode/https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sqlmodehttps://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_sqlmode​​​​​​

#查看mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)
#修改，此处修改重启服务后会还原，如果需要永久生效，则可以写配置文件mysql> set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES1 row in set (0.00 sec)

常见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：将 "||" 视为连接操作符而非 "或" 运算符