MySQL?中的服務(wù)器配置和狀態(tài)詳解(MySQL?Server?Configuration?and?Status)
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)變量這三個(gè)方面來(lá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)變量 | 作用范圍 | 是否能動(dòng)態(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)前的一個(gè)狀態(tài)值
變量生效范圍有三種,分別是全局,會(huì)話,全局和會(huì)話,Var Scope 列對(duì)應(yīng)的值分別是 Global,Session,Both
Dynamic 列表示是否可以動(dòng)態(tài)修改,如果該列值為 No,則表示不可修改,狀態(tài)變量都不可修改,部分系統(tǒng)變量也不可修改
一個(gè)配置項(xiàng)可以同時(shí)是服務(wù)器選項(xiàng),系統(tǒng)變量,狀態(tài)變量這三種中的兩種,但不會(huì)同時(shí)是三種角色
1.1.1 服務(wù)器選項(xiàng)
查看所有可用選項(xiàng)列表
[root@localhost ~]# mysqld --verbose --help
查看服務(wù)啟動(dòng)時(shí)在命令行下添加的選項(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
#這個(gè)選項(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ù)啟動(dòng)選項(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 #查看,此時(shí)配置在在配置文件中可見,但并沒有生效,需要重啟 [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ú)法啟動(dòng)服務(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. #查看具體錯(cuò)誤信息 [root@localhost ~]# tail /var/log/mysql/mysqld.log
1.1.2 服務(wù)器系統(tǒng)變量
服務(wù)器系統(tǒng)變量分為全局變量和會(huì)話變量?jī)煞N,全局變量表示可以影響到所有連接終端,所有會(huì)話,會(huì)話變量只影響當(dāng)前會(huì)話。
查看系統(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
修改變量時(shí),on|true|1 代表開啟,off|false|0 代表關(guān)閉。
修改全局變量:修改后全局生效,如果僅是變量,則對(duì)于己建立的連接不生效。???????
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)
修改會(huì)話變量:僅對(duì)當(dāng)前會(huì)話有影響???????
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) #重新連接,也不受影響
只讀變量無(wú)法修改???????
#該項(xiàng)的 Dynamic 列值為 No,不能動(dòng)態(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
變量無(wú)法實(shí)現(xiàn)永久保存,重啟服務(wù)后會(huì)被重置???????
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)變量:分全局和會(huì)話兩種,其中許多變量有雙重域,既是全局變量,也是會(huì)話變量,有相同的名字。
狀態(tài)變量用于保存 MySQL 運(yùn)行中的統(tǒng)計(jì)數(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)行時(shí)長(zhǎng) 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),也是變量,其值會(huì)影響 SQL 語(yǔ)句執(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ù)后會(huì)還原,如果需要永久生效,則可以寫配置文件 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' 的時(shí)間
ONLY_FULL_GROUP_BY:對(duì)于 GROUP BY 聚合操作,如果在 SELECT 中的列,沒有在 GROUP BY 中出現(xiàn),那認(rèn)為這個(gè) 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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- 在linux服務(wù)器上配置mysql并開放3306端口的操作步驟
- MySQL配置主從服務(wù)器(一主多從)
- Ubuntu14.04服務(wù)器環(huán)境下配置PHP7.0+Apache2+Mysql5.7的方法
- CentOS7服務(wù)器中apache、php7以及mysql5.7的安裝配置代碼
- MySQL 4G內(nèi)存服務(wù)器配置優(yōu)化
- 查看linux服務(wù)器上mysql配置文件路徑的方法
- Linux下安裝Python3和django并配置mysql作為django默認(rèn)服務(wù)器方法
- CentOS 6.6安裝配置LAMP服務(wù)器(Apache+PHP5+MySQL)
- win2008 r2 服務(wù)器php+mysql+sqlserver2008運(yùn)行環(huán)境配置(從安裝、優(yōu)化、安全等)
相關(guān)文章
MySQL數(shù)據(jù)庫(kù)閉包Closure Table表實(shí)現(xiàn)示例
本文主要介紹了MySQL數(shù)據(jù)庫(kù)閉包Closure Table表實(shí)現(xiàn)示例,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01win11系統(tǒng)下mysql8.4更改數(shù)據(jù)目錄問題解決
更改數(shù)據(jù)庫(kù)目錄是指修改MySQL數(shù)據(jù)庫(kù)的存儲(chǔ)路徑,本文主要介紹了win11系統(tǒng)下mysql8.4更改數(shù)據(jù)目錄問題解決,具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07mysql 5.7.17 免安裝版配置方法圖文教程(windows10)
這篇文章主要為大家詳細(xì)介紹了windows10下mysql 5.7.17 免安裝版配置方法圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-01-01不使用MySQL數(shù)據(jù)庫(kù)的五個(gè)給力理由解析
眾所周知,MySQL數(shù)據(jù)庫(kù)雖然功能未必很強(qiáng)大,但因?yàn)樗拈_源、廣泛傳播,導(dǎo)致很多人都了解到這個(gè)數(shù)據(jù)庫(kù)。2011-03-03