MySQL?中的服務(wù)器配置和狀態(tài)詳解(MySQL?Server?Configuration?and?Status)

更新時間：2025年02月24日 14:36:55 作者：Linux運(yùn)維老紀(jì)

MySQL服務(wù)器配置和狀態(tài)設(shè)置包括服務(wù)器選項(xiàng)、系統(tǒng)變量和狀態(tài)變量三個方面,可以通過命令行、配置文件或SQL語句進(jìn)行設(shè)置和查看,服務(wù)器選項(xiàng)和系統(tǒng)變量可以是全局或會話級別的,狀態(tài)變量只讀且不可修改,sql_mode是一個特殊的變量,影響SQL語句的執(zhí)行模式,感興趣的朋友一起看看吧

MySQL 之服務(wù)器配置和狀態(tài)

1 MySQL 架構(gòu)和性能優(yōu)化

1.1 服務(wù)器配置和狀態(tài)

設(shè)置 MySQL 服務(wù)的特性，可以通過 mysqld 服務(wù)選項(xiàng)，服務(wù)器系統(tǒng)變量和服務(wù)器狀態(tài)變量這三個方面來進(jìn)行設(shè)置和查看。

官方文檔

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

文檔說明

Name	Cmd-Line	OptionFile	SystemVar	StatusVar	VarScope	Dynamic
名稱	是否能在命令行下設(shè)置	是否能寫配置文件	是否是系統(tǒng)變量	是否是狀態(tài)變量	作用范圍	是否能動態(tài)修改

Cmd-Line 和 Opton File 列的值如果是 Yes，則表示該項(xiàng)是服務(wù)器選項(xiàng)

System Var 列的值如果是 Yes，則表示該項(xiàng)是系統(tǒng)變量

Status Var 列的值如果是 Yes，則表示該項(xiàng)是狀態(tài)變量

Option File 指配置文件

服務(wù)器選項(xiàng)通常在命令行后面添加或在配置文件中設(shè)置

狀態(tài)變量表示的是當(dāng)前的一個狀態(tài)值

變量生效范圍有三種，分別是全局，會話，全局和會話，Var Scope 列對應(yīng)的值分別是 Global，Session，Both

Dynamic 列表示是否可以動態(tài)修改，如果該列值為 No，則表示不可修改，狀態(tài)變量都不可修改，部分系統(tǒng)變量也不可修改

一個配置項(xiàng)可以同時是服務(wù)器選項(xiàng)，系統(tǒng)變量，狀態(tài)變量這三種中的兩種，但不會同時是三種角色

1.1.1 服務(wù)器選項(xiàng)

查看所有可用選項(xiàng)列表

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

查看服務(wù)啟動時在命令行下添加的選項(xiàng)

[root@localhost ~]# ps aux | grep mysqld
mysql 2423 0.6 23.7 1836108 433416 ? Ssl 13:01 3:27 /usr/libexec/mysqld --basedir=/usr

#這個選項(xiàng)是配置在服務(wù)腳本中的
[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

查看當(dāng)前服務(wù)啟動選項(xiàng)

[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
#這些選項(xiàng)都是寫在配置文件中的
[root@localhost ~]# cat /etc/my.cnf.d/mysql-server.cnf
......
[mysqld]
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-default-authentication-plugin.cnf
......
[mysqld]
default_authentication_plugin=mysql_native_password

在命令行中設(shè)置服務(wù)器選項(xiàng)

[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)

在配置文件中設(shè)置服務(wù)器選項(xiàng)

[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
#查看選項(xiàng)，因?yàn)榇隧?xiàng)與變量同名
mysql> show variables like 'max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
#重啟服務(wù)
[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)?????????????
#配置文件中的服務(wù)器選項(xiàng)，可以寫下劃線，也可以寫中劃線
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#max_connections=200
max-connections=201
#重啟服務(wù)
[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)

非服務(wù)器選項(xiàng)不能加配置文件???????

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#max_connections=200
max-connections=201
character_set_database=utf8
#無法啟動服務(wù)，因?yàn)?character_set_database 不是服務(wù)器選項(xiàng)
[root@localhost ~]# systemctl restart mysqld.service
Job 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.1.2 服務(wù)器系統(tǒng)變量

服務(wù)器系統(tǒng)變量分為全局變量和會話變量兩種，全局變量表示可以影響到所有連接終端，所有會話，會話變量只影響當(dāng)前會話。

查看系統(tǒng)變量???????

#查看所有全局變量
mysql> show global variables 630
#查看所有變量，包括session和global
mysql> show session variables
mysql> 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)

修改服務(wù)器系統(tǒng)變量

查看幫助

mysql> help set

修改變量時，on|true|1 代表開啟，off|false|0 代表關(guān)閉。

修改全局變量：修改后全局生效，如果僅是變量，則對于己建立的連接不生效。???????

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)
#終端B
mysql> 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)

又是變量又是選項(xiàng)???????

#終端A
mysql> show variables like 'mysqlx_max_connections';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| mysqlx_max_connections | 100   |
+------------------------+-------+
1 row in set (0.01 sec)
#終端B
mysql> 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 GLOBAL
mysql> 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)

修改會話變量：僅對當(dāng)前會話有影響???????

SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;
??????
#終端A
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)
#終端B
mysql> 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)
#重新連接，也不受影響

只讀變量無法修改???????

#該項(xiàng)的 Dynamic 列值為 No，不能動態(tài)修改
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

變量無法實(shí)現(xiàn)永久保存，重啟服務(wù)后會被重置???????

mysql> show variables like 'mysqlx_max_connections';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| mysqlx_max_connections | 108   |
+------------------------+-------+
1 row in set (0.00 sec)
#重啟服務(wù)
[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.1.3 服務(wù)器狀態(tài)變量

服務(wù)器狀態(tài)變量：分全局和會話兩種，其中許多變量有雙重域，既是全局變量，也是會話變量，有相同的名字。

狀態(tài)變量用于保存 MySQL 運(yùn)行中的統(tǒng)計數(shù)據(jù)的變量，只讀，不可修改。

查看狀態(tài)變量???????

#查看所有全局狀態(tài)變量
mysql> show global status;
#查看所有狀態(tài)變量，包括global和session
mysql> 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)
#查詢次數(shù)增加
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)
#查看服務(wù)運(yùn)行時長
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)

重啟服務(wù)后狀態(tài)被重置???????

[root@localhost ~]# systemctl restart mysqld.service
mysql> 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.1.4 服務(wù)器變量 sql_mode

sql_mode 是服務(wù)器選項(xiàng)，也是變量，其值會影響 SQL 語句執(zhí)行的工作模式。

官方文檔???????

https://mariadb.com/kb/en/library/sql-mode/
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sqlmode
https://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_SUBSTITUTION
1 row in set (0.00 sec)
#修改，此處修改重啟服務(wù)后會還原，如果需要永久生效，則可以寫配置文件
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_TABLES
1 row in set (0.00 sec)

常見MODE

NO_AUTO_CREATE_USER：禁止 GRANT 創(chuàng)建密碼為空的用戶

NO_ZERO_DATE：在嚴(yán)格模式，不允許使用 '0000-00-00' 的時間

ONLY_FULL_GROUP_BY：對于 GROUP BY 聚合操作，如果在 SELECT 中的列，沒有在 GROUP BY 中出現(xiàn)，那認(rèn)為這個 SQL 是不合法的

NO_BACKSLASH_ESCAPES：反斜杠 "\" 作為普通字符而非轉(zhuǎn)義字符

PIPES_AS_CONCAT：將 "||" 視為連接操作符而非 "或" 運(yùn)算符

到此這篇關(guān)于MySQL 中的服務(wù)器配置和狀態(tài)詳解(MySQL Server Configuration and Status)的文章就介紹到這了,更多相關(guān)mysql服務(wù)器配置和狀態(tài)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: