我要评论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/
文档说明
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-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
在命令行中设置服务器选项
[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_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)
又是变量又是选项
#终端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)
修改会话变量：仅对当前会话有影响
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 | 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 | +---------------+-------+ | 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服务器配置和状态内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论