七、MySql服务器选项、系统和状态变量

2020年4月23日 1318点热度 0人点赞 0条评论

服务器配置

mysqld选项，服务器系统变量和服务器状态变量
https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置
获取mysqld的可用选项列表：
mysqld --help –verbose
mysqld --print-defaults 获取默认设置
服务器系统变量：分全局和会话两种
服务器状态变量：分全局和会话两种
获取运行中的mysql进程使用各服务器参数及其值
mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
设置服务器选项方法：
在命令行中设置:
shell> ./mysqld_safe –-skip-name-resolve=1;
在配置文件my.cnf中设置：
skip_name_resolve=1;

服务器端设置

修改服务器变量的值：
mysql> help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
mysql> SET GLOBAL system_var_name=value;
mysql> SET @@global.system_var_name=value;
修改会话变量：
mysql> SET [SESSION] system_var_name=value;
mysql> SET @@[session.]system_var_name=value;
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
mysql> SHOW GLOBAL STATUS;
mysql> SHOW [SESSION] STATUS;

示例：

set global character_set_results=gb2312    更改全局变量，对全部用户有效
show global variables like '%char%';   查看全局变量

set  character_set_results=gb2312   更改会话变量，只对当前会话有效
show variables like '%char%';   查看会话变量
注意：会话级别变量优先级高于全局级别变量，而且一旦重启服务变量设置会恢复默认

服务器选项和服务器变量的区别：

服务器选项：可以加入配置文件中/etc/my.cnf，以-作为分隔符，重启服务才能生效
mysqld --verbose --help   查看服务器选项列表
服务器变量：不支持加入配置文件，以_作为分隔符，如果支持动态dynamic，则可以不重启服务生效，使用set命令设置，查询命令show variables like ' '

服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/
常见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架构

查询的执行路径

知识扩展：

查看状态变量
show status like '';
show  status like '%select%';
MariaDB [hellodb]> show  status like '%select%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert_select      | 0     |
| Com_replace_select     | 0     |
| Com_select             | 2     |
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
| Select_range           | 0     |
| Select_range_check     | 0     |
| Select_scan            | 8     |
+------------------------+-------+
其中的com_select对应的值为当前用户select查询次数

MariaDB [hellodb]> show  status like '%insert%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Com_insert                    | 0     |
| Com_insert_select             | 0     |
| Delayed_insert_threads        | 0     |
| Innodb_ibuf_discarded_inserts | 0     |
| Innodb_ibuf_merged_inserts    | 0     |
| Innodb_rows_inserted          | 0     |
| Qcache_inserts                | 0     |
+-------------------------------+-------+
其中的com_insert对应的值为当前用户insert插入数据次数

查看当前用户连接数
MariaDB [hellodb]> show variables like '%connect%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| connect_timeout          | 10              |
| extra_max_connections    | 1               |
| init_connect             |                 |
| max_connect_errors       | 10              |
| max_connections          | 151             |
| max_user_connections     | 0               |
+--------------------------+-----------------+
其中max_connections为当前数据库最大连接数为151 
调整最大连接数：查看该变量是否也是服务器选项，是否能够写入配置文件
去官方网站查询https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html，发现既是选项又是变量
如果使用set命令更改只是临时更改，重启服务就会失效
set global max_connections = 1000   只支持global
永久更改需要写入配置文件
vim /etc/my.cnf
global max_connections = 1000 

查看当前有多少用户正在连接
MariaDB [hellodb]> show  status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_connected表示连接数据库的线程数为1 
Threads_connected表示连接数据库且正在工作的线程数为1 

skip_name_resolve  是否支持反向解析ip地址 
OFF 不忽略名字解析
ON  忽略名字解析  
show  variables like 'skip_name_resolve';  查看该选项的设置
默认情况下为OFF，即不忽略名字解析，会尝试把ip地址解析为名字，可能会导致连接数据库失败，如做集群时会用到该选项
该选项不支持动态更改，为global级别变量
vim /etc/my.cnf
skip_name_resolve=ON     设置为忽略名字解析
重启数据库服务

skip_networking    进入维护模式

七、MySql服务器选项、系统和状态变量

文章评论