当前位置 : 首页 » 文章分类 :  开发  »  MySQL-Server 服务端管理

MySQL-Server 服务端管理

MySQL 服务端配置、变量

Chapter 5 MySQL Server Administration
https://dev.mysql.com/doc/refman/5.7/en/server-administration.html


查看mysql版本号

未连接到MySQL服务器mysql -v

没有连接到MySQL服务器,就想查看MySQL的版本。打开cmd,切换至mysql的bin目录,运行下面的命令即可:

e:\mysql\bin>mysql -V
mysql  Ver 14.14 Distrib 5.6.32, for Win32 (AMD64)

版本为 5.6.32

或者:

e:\mysql\bin>mysql -v

这个命令可以查看到更为详细的信息,因为它会用账号 ODBC,连接上MySQL服务器,默认连接到localhost上的3306端口。

select version();

已连接到mysql服务器后

MariaDB [uds]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.1.34-MariaDB |
+-----------------+
1 row in set (0.023 sec)

status;\s

mysql 命令行执行命令 status;\s

MariaDB [uds]> status;
--------------
mysql  Ver 15.1 Distrib 10.3.7-MariaDB, for osx10.13 (x86_64) using readline 5.1

Connection id:        4487583
Current database:    uds
Current user:        root@10.111.159.88
SSL:            Not in use
Current pager:        less
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.1.34-MariaDB MariaDB Server
Protocol version:    10
Connection:        t-awsbj-uds-01.clap5vvkrarj.rds.cn-north-1.amazonaws.com.cn via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            30 days 15 hours 35 min 44 sec

Threads: 797  Questions: 934282188  Slow queries: 12453  Opens: 743201  Flush tables: 25  Open tables: 200  Queries per second avg: 352.806
--------------

SQL Modes(SQL模式)

5.1.10 Server SQL Modes
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

查看当前sql_mode

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

设置sql_mode

SET GLOBAL sql_mode = ‘modes’;
SET SESSION sql_mode = ‘modes’;

my.cnf中配置sql-mode

[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

常用sql_mode

ONLY_FULL_GROUP_BY(5.7.5及之后默认启用)

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
对于 GROUP BY 聚合操作,如果在 SELECT 中的列、HAVING 或者 ORDER BY 子句的列,没有在 GROUP BY 中出现,那么这个 SQL 是不合法的。是可以理解的,因为不在 group by 的列查出来展示会有矛盾。
在 5.7.5 及之后版本中默认启用,所以在实施 5.6 升级到 5.7 的过程需要注意。

ANSI_QUOTES

启用 ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符,作用与反引号 ` 一样。
设置它以后,update t set f1=”” …,会报 Unknown column ‘’ in ‘field list 这样的语法错误。

STRICT_TRANS_TABLES

设置它,表示启用严格事务模式。
注意 STRICT_TRANS_TABLES 不是几种策略的组合,单独指 INSERT、UPDATE 出现少值或无效值该如何处理:
1、前面提到的把 ‘’ 传给int,严格模式下非法,若启用非严格模式则变成0,产生一个warning
2、Out Of Range,变成插入最大边界值
3、A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

NO_AUTO_CREATE_USER

字面意思不自动创建用户。在给MySQL用户授权时,我们习惯使用 GRANT … ON … TO dbuser 顺道一起创建用户。设置该选项后就与oracle操作类似,授权之前必须先建立用户。5.7.7开始也默认了。

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题)
http://seanlook.com/2016/04/22/mysql-sql-mode-troubleshooting/


Server Status Variables 服务端状态变量

Max_used_connections 查看最大并发连接数

即 mysql 运行过程中曾经达到的最大连接数
show global status like 'Max_used_connections';

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Max_used_connections

Threads_connected 查看当前实时连接数

show global status like 'Threads%';
Threads_connected 当前的实时连接数,
Threads_created 表示创建过的线程数
Threads_running 这个数值指的是激活的连接数,这个数值一般远低于connected数值

MariaDB [uds]> show global status like 'Threads%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Threads_cached    | 0        |
| Threads_connected | 1369     |
| Threads_created   | 15899277 |
| Threads_running   | 1        |
+-------------------+----------+

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_connected


Server System Variables 服务端系统变量

5.1.7 Server System Variables
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

MySQL 服务端维护了很多系统变量,用来对 MySQL server 的行为进行配置。每个系统变量都有一个默认值,并且都有对应的 server 命令行启动参数。多数系统变量都可以通过 set 语句进行动态配置,也就是说不需要重启 MySQL server 就可以改配置。

InnoDB 存储引擎的系统变量在 14.15 节
14.15 InnoDB Startup Options and System Variables
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

show variables 查看所有variable

show variables; 查看所有变量

show variables like '%变量名%' 查看某个具体变量值


autocommit(默认on) 自动提交是否打开

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_autocommit

SHOW VARIABLES LIKE 'autocommit';

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

foreign_key_checks(默认on) 外键检查

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_foreign_key_checks

是否进行外键检查,默认是打开的,即1
作用域:Global, Session

mysql> show variables like '%foreign_key_checks%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

当前Session设置:
SET FOREIGN_KEY_CHECKS=0;
全局设置:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

比如想强行删除被引用的外建行的话,可以暂时关闭外键检查:

SET FOREIGN_KEY_CHECKS = 0;
delete from user where user_id=81681419;
SET FOREIGN_KEY_CHECKS = 1;

foreign_key_checks
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks

Does MySQL foreign_key_checks affect the entire database?
https://stackoverflow.com/questions/8538636/does-mysql-foreign-key-checks-affect-the-entire-database


max_connections(151) 最大连接数

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections

查看 MySQL 最大连接数:
show variables like 'max_connections';
默认值151,最小值1,最大可设为 100000

修改 MySQL 最大连接数:
1、可以在 /etc/my.cnf 里面设置数据库的最大连接数,之后需要重启 MySQL

[mysqld]
max_connections = 1000

2、set global max_connections=10000;


max_prepared_stmt_count(16382) 最大预编译语句数

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_prepared_stmt_count

启动参数:--max-prepared-stmt-count=#
默认值:16382
可设置的最大值:1048576
此变量限制服务端最大预编译语句的个数,即所有 session 的预编译语句之和。

设置全局

set global max_prepared_stmt_count=500000;

预编译语句超过此配置值时会报错: Can’t create more than max_prepared_stmt_count statements (current value: 16382)


connect_timeout(10秒) 连接超时时间

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_connect_timeout
连接响应超时时间。服务器端在这个时间内如未连接成功,则会返回连接失败。
默认值 10 秒

增加连接超时时间可能有助于解决客户端频繁遇到 Lost connection to MySQL server at ‘XXX’, system error: errno. 错误


wait_timeout(8小时) 非交互连接空闲超时时间

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

服务器关闭非交互连接之前等待活动的秒数。可以认为是服务器端连接空闲的时间,空闲超过这个时间将自动关闭。

在线程启动时,根据全局 wait_timeout 值或全局 interactive_timeout 值初始化会话的 wait_timeout 值,取决于客户端类型(由 mysql_real_connect() 的连接选项 CLIENT_INTERACTIVE 定义)。
参数默认值:28800秒(8小时)

show global variables like 'wait_timeout';
show variables like 'wait_timeout';
空闲连接等待时间(秒)
Mysql服务器默认的“wait_timeout”是8小时(28800 秒),也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。

interactive_timeout(8小时) 交互连接空闲超时时间

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout
参数含义:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
参数默认值:28800秒(8小时)


net_read_timeout(30秒) 服务端读超时时间

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_net_read_timeout
服务端数据读取超时时间。在终止读之前,从一个连接获得数据而等待的时间秒数;当服务端正在从客户端读取数据时,net_read_timeout 控制何时超时。当服务端向客户端写入数据时,net_write_timeout 控制何时超时。
默认值 30 秒

net_write_timeout(60秒) 服务端写超时时间

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_net_write_timeout
服务端数据写超时时间。和 net_read_timeout 意义类似,在终止写之前,等待多少秒把block写到连接;当服务正在写数据到客户端时,net_write_timeout 控制何时超时。
默认值 60 秒

等待将一个block发送给客户端的超时,一般在网络条件比较差的时,或者客户端处理每个block耗时比较长时,由于 net_write_timeout 导致的连接中断很容易发生。


max_statement_time sql执行超时时间(5.6)

max_execution_time(0无限) select语句执行超时时间(5.7)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

mysql 5.6 及以后,有语句执行超时时间变量,用于在服务端对 select 语句进行超时时间限制;
mysql 5.6 中,名为: max_statement_time (毫秒)
mysql 5.7 以后,改成: max_execution_time (毫秒)

默认值 0 表示不开启此选项,即无限制。
注意只针对 select 语句

超过这个时间,mysql 就终止 select 语句的执行,客户端抛异常:
1907: Query execution was interrupted, max_execution_time exceeded.


max_allowed_packet(4MB)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

server 可接收的最大数据包大小,单位字节(B, bytes),大的查询或插入sql可能因为超过这个值二报错。
默认值
Default Value (>= 5.6.6) 4194304 B 即 4MB
Default Value (<= 5.6.5) 1048576 B
允许的最大值: 1073741824 B, 即 1GB
允许的最小值: 1024 B, 即 1KB

mysql 的数据包缓冲区(packet buffer) 初始值是 net_buffer_length 字节,可按需自动增长为 max_allowed_packet 字节

查看
show VARIABLES like '%max_allowed_packet%';

PacketTooBigException

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; SQL []; Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (13631257 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_allowed_packet


tx_isolation(REPEATABLE-READ) 事务隔离级别(5.7.19及之前)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_tx_isolation

废弃:5.7.20
默认值 REPEATABLE-READ
可取值:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

1、查看当前会话隔离级别
select @@tx_isolation;

2、设置当前会话隔离级别
set session transaction isolatin level repeatable read;

3、查看系统当前隔离级别
select @@global.tx_isolation;

4、设置系统当前隔离级别
set global transaction isolation level repeatable read;


transaction_isolation(REPEATABLE-READ) 事务隔离级别(5.7.20及之后)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_isolation

可用版本:>= MySQL 5.7.20
启动参数 --transaction-isolation=name
默认值:REPEATABLE-READ
可取值:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation 变量从 MySQL 5.7.20 版本开始引入,用来替换之前的 tx_isolation 变量。
tx_isolation 变量已被废弃,会在 MySQL 8.0 中删除。

1、设置全局隔离级别:
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
此后创建的新 session 都会使用新的隔离级别,但已存在的 session 不受影响

2、设置 session 隔离级别:

SET @@SESSION.transaction_isolation = value;
SET SESSION transaction_isolation = value;
SET transaction_isolation = value;

3、查看事务隔离级别

> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

为什么MySQL5.7中要废弃tx_isolation?
5.7.20 之前,启动参数 --transaction-isolation 对应 tx_isolation 系统变量, 启动参数 --transaction-read-only 对应 tx_read_only 系统变量。
为了启动参数和系统变量的命名一致,从 5.7.20 开始,引入 transaction_isolation 系统变量作为 tx_isolation 的别名,引入 transaction_read_only 系统变量作为 tx_read_only 的别名。
系统变量 tx_isolationtx_read_only 已被标为废弃,并计划在 MySQL 8.0 中删除。

1.3 What Is New in MySQL 5.7
https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html


transaction_read_only(OFF) 事务只读开关(5.7.20及之后)

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_read_only

可用版本:>= MySQL 5.7.20
启动参数 --transaction-read-only[={OFF|ON}]
默认值:OFF

transaction_read_only 变量从 MySQL 5.7.20 版本开始引入,用来替换之前的 tx_read_only 变量。
tx_read_only 变量已被废弃,会在 MySQL 8.0 中删除。

事务只读开关,可设置为 OFF(可读可写,默认值),或 ON(只读)

如果设置 transaction_read_only 为 ON 表示无法写入数据,此时向表中写入数据,会产生报错,如下:
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.


time_zone 当前时区

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_time_zone
默认值为 SYSTEM,表示使用系统时区变量 system_time_zone 的值。

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

system_time_zone 系统时区

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_system_time_zone

服务器的系统时区,mysql服务器启动时会读取所在服务器的系统时区。
建议:在启动mysql服务器前,将系统时区设为需要的时区,比如 Asia/Shanghai,避免单独设置mysql时区,其他地方比如jvm中和mysql时区不一致出问题。

设置mysql系统时区

方法一:设置系统变量

mysql> set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间,即我们所在的东8区
mysql> set time_zone = '+8:00';  ##修改当前会话时区
mysql> flush privileges;  #立即生效

我这样设置之后好像没生效

方法二:通过修改my.cnf配置文件来修改时区

# vim /etc/my.cnf  ##在[mysqld]区域中加上
default-time_zone = '+8:00'

# sudo service mysqld restart ##重启mysql使新时区生效

注意一定要在 [mysqld] 之下加 ,否则会出现 unknown variable ‘default-time-zone=+8:00’


datadir 数据文件路径

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_datadir

show global variables like "%datadir%";


bind_address 服务器绑定ip

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bind_address

查看mysql server绑定ip
show variables like 'bind_address';
例如:

mysql> show variables like 'bind_address';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+
1 row in set (0.21 sec)

bind-address是MYSQL用来监听某个单独的TCP/IP连接,只能绑定一个IP地址,被绑定的IP地址可以映射多个网络接口.
可以是IPv4,IPv6或是主机名,但需要在MYSQL启动的时候指定(主机名在服务启动的时候解析成IP地址进行绑定).
默认是*,表示接收所有的IPv4 或 IPv6 连接请求

在/etc/my.cnf中配置:

  • 接收所有的IPv4 或 IPv6 连接请求

0.0.0.0 接受所有的IPv4地址
:: 接受所有的IPv4 或 IPv6 地址
IPv4-mapped 接受所有的IPv4地址或IPv4邦定格式的地址(例 ::ffff:127.0.0.1)
IPv4(IPv6) 只接受对应的IPv4(IPv6)地址


同步复制与binlog系统变量

16.1.6 Replication and Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/5.7/en/replication-options.html

server_id

https://dev.mysql.com/doc/refman/5.7/en/replication-options.html#sysvar_server_id

当你使用主从拓扑时,一定要对所有MySQL实例都分别指定一个独特的互不相同的server-id。默认值为0,当server-id=0时,对于主机来说依然会记录二进制日志,但会拒绝所有的从机连接;对于从机来说则会拒绝连接其它实例。

server-id用于标识数据库实例,防止在链式主从、多主多从拓扑中导致SQL语句的无限循环:
标记binlog event的源实例
过滤主库binlog,当发现server-id相同时,跳过该event执行,避免无限循环执行。
如果设置了replicate-same-server-id=1,则执行所有event,但有可能导致无限循环执行SQL语句。


auto_increment_increment(1) 自增id间隔

https://dev.mysql.com/doc/refman/5.7/en/replication-options-source.html#sysvar_auto_increment_increment

问题:
mysql 自增 id 从小到大是 1, 3, 5, 7 … 以2递增

原因:
变量 auto_increment_increment 的值配置为 2 了

auto_increment_increment 表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535

auto_increment_offset 表示自增长字段从那个数开始,他的取值范围是1 .. 65535


上一篇 MySQL-DataTypes 数据类型

下一篇 MySQL-INFORMATION_SCHEMA

阅读
评论
4k
阅读预计17分钟
创建日期 2021-07-19
修改日期 2021-07-21
类别

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论