MySQL 中的服务器配置和状态详解(MySQL Server Configuration and Status)

本文主要是介绍MySQL 中的服务器配置和状态详解(MySQL Server Configuration and Status),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

《MySQL中的服务器配置和状态详解(MySQLServerConfigurationandStatus)》MySQL服务器配置和状态设置包括服务器选项、系统变量和状态变量三个方面,可以通过...

MySQL 之服务器配置和状态

1 MySQL 架构和性能优化

1.1 服务器配置和状态

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

官方文档

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/ 

MySQL 中的服务器配置和状态详解(MySQL Server Configuration and Status)

文档说明

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.1.1 服务器选项

查看所有可用选项列表

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

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

[root@localhost ~]# ps aux | grep mysqld
mysql 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-serverjs.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

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

[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=200
max-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=200
max-connections=201
character_set_database=utf8
#无法启动服务,因为 character_set_database 不是服务器选项
[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 服务器系统变量

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

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

#查看所有全局变量
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)

修改服务器系统变量

查看帮助

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)
#终端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_npythoname | 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)

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

#终端A
mysql> show variables like 'mysqlx_max_connections';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+php
| 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)

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

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)
#重新连接,也不受影响

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

#该项的 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.1.3 服务器状态变量

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

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

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

#查看所有全局状态变量
mysql> show global status;
#查看所有状态变量,包括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)
#查询次数增加
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     js   | 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.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 |
+---------------+php-------+
| Uptime        | 4     |
+---------------+-------+
1 row in set (0.00 sec)

1.1.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_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)
#修改,此处修改重启服务后会还原,如果需要永久生效,则可以写配置文件
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 创建密码为空的用户

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

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

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

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

到此这篇关于MySQL 中的服务器配置和状态详解(MySQL Server Configuration and Status)的文章就介绍到这了,更多相关mysql服务器配置和状态内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)!

这篇关于MySQL 中的服务器配置和状态详解(MySQL Server Configuration and Status)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1153529

相关文章

IDEA自动生成注释模板的配置教程

《IDEA自动生成注释模板的配置教程》本文介绍了如何在IntelliJIDEA中配置类和方法的注释模板,包括自动生成项目名称、包名、日期和时间等内容,以及如何定制参数和返回值的注释格式,需要的朋友可以... 目录项目场景配置方法类注释模板定义类开头的注释步骤类注释效果方法注释模板定义方法开头的注释步骤方法注

一文详解如何在Python中从字符串中提取部分内容

《一文详解如何在Python中从字符串中提取部分内容》:本文主要介绍如何在Python中从字符串中提取部分内容的相关资料,包括使用正则表达式、Pyparsing库、AST(抽象语法树)、字符串操作... 目录前言解决方案方法一:使用正则表达式方法二:使用 Pyparsing方法三:使用 AST方法四:使用字

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

Python列表去重的4种核心方法与实战指南详解

《Python列表去重的4种核心方法与实战指南详解》在Python开发中,处理列表数据时经常需要去除重复元素,本文将详细介绍4种最实用的列表去重方法,有需要的小伙伴可以根据自己的需要进行选择... 目录方法1:集合(set)去重法(最快速)方法2:顺序遍历法(保持顺序)方法3:副本删除法(原地修改)方法4:

如何在Mac上安装并配置JDK环境变量详细步骤

《如何在Mac上安装并配置JDK环境变量详细步骤》:本文主要介绍如何在Mac上安装并配置JDK环境变量详细步骤,包括下载JDK、安装JDK、配置环境变量、验证JDK配置以及可选地设置PowerSh... 目录步骤 1:下载JDK步骤 2:安装JDK步骤 3:配置环境变量1. 编辑~/.zshrc(对于zsh

python logging模块详解及其日志定时清理方式

《pythonlogging模块详解及其日志定时清理方式》:本文主要介绍pythonlogging模块详解及其日志定时清理方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录python logging模块及日志定时清理1.创建logger对象2.logging.basicCo

售价599元起! 华为路由器X1/Pro发布 配置与区别一览

《售价599元起!华为路由器X1/Pro发布配置与区别一览》华为路由器X1/Pro发布,有朋友留言问华为路由X1和X1Pro怎么选择,关于这个问题,本期图文将对这二款路由器做了期参数对比,大家看... 华为路由 X1 系列已经正式发布并开启预售,将在 4 月 25 日 10:08 正式开售,两款产品分别为华

前端CSS Grid 布局示例详解

《前端CSSGrid布局示例详解》CSSGrid是一种二维布局系统,可以同时控制行和列,相比Flex(一维布局),更适合用在整体页面布局或复杂模块结构中,:本文主要介绍前端CSSGri... 目录css Grid 布局详解(通俗易懂版)一、概述二、基础概念三、创建 Grid 容器四、定义网格行和列五、设置行

Node.js 数据库 CRUD 项目示例详解(完美解决方案)

《Node.js数据库CRUD项目示例详解(完美解决方案)》:本文主要介绍Node.js数据库CRUD项目示例详解(完美解决方案),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考... 目录项目结构1. 初始化项目2. 配置数据库连接 (config/db.js)3. 创建模型 (models/

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表