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_cached 缓存中的线程数
Threads_running 这个数值指的是激活的连接数（非sleep状态），这个数值一般远低于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_connect_errors(100)

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

unblock with ‘mysqladmin flush-hosts’

错误：
Host xxx is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’；

原因：
同一个客户端IP地址，在短时间内访问服务器的MySQL产生了太多的错误，超过mysql数据库max_connect_errors的最大值，因此被服务器MySQL中断了数据库连接而导致的阻塞！

解决：
一、改大 max_connection_errors 的值
set global max_connect_errors=1000;
临时修改，重启MySQL后会失效
查看 show variables like “max_connection_errors”;

二、root登录后，执行 flush hosts 刷新 hosts 文件
或者 mysqladmin flush-hosts -h localhost -P 3306 -uxxx -pxxx
或者不执行第一步直接 flush hosts 也行

配置有master/slave主从数据库的要把主库和从库都修改一遍

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(28800/8小时) 非交互连接空闲超时时间

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

空闲连接等待时间（秒），服务器关闭非交互连接之前等待活动的秒数。可以认为是服务器端连接空闲的时间，空闲超过这个时间将自动关闭。
参数默认值：28800 秒（8小时）
Mysql 服务器默认的 wait_timeout 是8小时(28800 秒)，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection。

在线程启动时，根据全局 wait_timeout 值或全局 interactive_timeout 值初始化会话的 wait_timeout 值，取决于客户端类型(由 mysql_real_connect() 的连接选项 CLIENT_INTERACTIVE 定义)。

show global variables like 'wait_timeout';
show variables like 'wait_timeout';

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_isolation 和 tx_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）地址

MySQL日志

5.4 MySQL Server Logs
https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

默认情况下，MySQL 中的所有日志都是关闭的（除了Windows上的错误日志）。

错误日志(error log)

记录启动、运行、停止 mysqld 时的错误。
默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为hostname.err，其中，hostname为服务器主机名。

log-error配置错误日志

为了方便管理，用户可以根据自己的需求在 my.cnf 中配置错误日志存储位置和日志级别，配置参数如下:

log-error=/var/log/mysqld_log.err

show variables like “log_error”

mysql> show variables like "log_error";
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+

日常query日志(General query log)

记录 mysql 的日常日志，包括查询、修改、更新等的每条sql。
Mysql打开general log日志后，所有的查询语句都可以在general log文件中输出，如果打开会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

general-log-file配置查询日志

查询日志的输出文件可以在 my.cnf 中添加

general-log-file = [filename]

show global variables like “%general_log%”

查看mysql是否启用了查询日志

mysql> show global variables like "%general_log%";
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/lightsail.log |
+------------------+------------------------------+

二进制日志(binlog)

二进制日志，包含一些事件，这些事件描述了数据库的改动，如建库删库、建表删表、数据增删改等，主要用于备份、恢复、审计等操作。
注意binlog不记录SELECT、SHOW等那些不修改数据的SQL语句。
binlog默认是关闭的。

log_bin配置二进制日志

my.cnf 中配置二进制文件

log_bin = /var/log/mysql-bin.log

如果只添加了 log_bin 配置项，没有指定文件，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如 mysql-bin.000001，所在目录为数据库所在目录（datadir）

show variables like “%log_bin%”

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

binlog index文件

为了管理所有的binlog文件，MySQL额外创建了一个base-name.index文件，它按顺序记录了MySQL使用的所有binlog文件。如果你想自定义index文件的名称，可以设置 log_bin_index=file 参数。千万不要在mysqld运行的时候手动修改index文件的内容，这样会使mysqld产生混乱。

何时会创建新的binlog文件？

对于二进制文件，当满足下面任意情况时会创建新的文件，文件后缀会自增。
文件大小达到 max_binlog_size 参数设置值时。
执行 flush logs 命令。
重启 mysqld 进程。

3种binlog格式

binlog相关配置

log_bin

log_bin 设置此参数表示启用binlog功能，并指定路径名称

log_bin_index

log_bin_index 设置此参数是指定二进制索引文件的路径与名称

binlog_do_db

binlog_do_db 此参数表示只记录指定数据库的二进制日志

binlog_ignore_db

binlog_ignore_db 此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size

max_binlog_cache_size 此参数表示binlog使用的内存最大的尺寸

binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。binlog_cache_use：使用二进制日志缓存的事务数量

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size

max_binlog_size Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog

sync_binlog 这个参数直接影响mysql的性能和完整性

sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

中继日志(relay log)

relay log 是复制过程中产生的日志，很多方面都跟binary log差不多，区别是: relay log 是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上。

慢查询日志(Slow query log)

MySQL 慢查询日志记录执行时间超过 long_query_time 值的 SQL
默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。
开启慢查询日志会或多或少带来一定的性能影响

slow_query_log 是否开启慢查询日志，1 表示开启，0 表示关闭，默认关闭。
log-slow-queries 旧版（5.6以下版本）MySQL 数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件 host_name-slow.log
slow-query-log-file 新版（5.6及以上版本）MySQL 数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件 host_name-slow.log
long_query_time 慢查询阈值（单位秒），当查询时间多于设定的阈值时记录慢查询日志，默认值 10s。
log_queries_not_using_indexes 未使用索引的查询也被记录到慢查询日志中（可选项），默认关闭。
log_output 日志存储方式。log_output=’FILE’ 表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’ 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。MySQL 数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

show variables like “%slow%”

mysql> show variables like "%slow%";
+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF                               |
| slow_query_log_file       | /var/lib/mysql/lightsail-slow.log |
+---------------------------+-----------------------------------+

slow_query_log=on 开启慢查询日志(默认关)

set global slow_query_log = on; 或 set global slow_query_log=1; 开启慢查询日志，重启后失效。
或者在 my.cnf 中配置来永久生效

slow_query_log=1
slow-query-log-file = /tmp/mysql-slow.log
long_query_time = 1 #设置满请求时间, 设置查多少秒的查询算是慢查询

long_query_time 慢查询时间阈值(默认10秒)

long_query_time: 慢查询指定时间设置，表示”多长时间的查询”被认定为”慢查询”，单位是秒(s)，默认是10s，即超过10s的查询都被认定为慢查询。

mysql> show variables like "%long_query_time%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------

set global long_query_time=5; 修改慢查询时间阈值为5s
注意 global 修改后立即查询还是默认的10s，需要新开一个会话才更新

慢查询日志格式

# Time: 200310 13:30:57
# User@Host: root[root] @ localhost []  Id: 21528
# Query_time: 6.000164  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1583818257;
select sleep(6);

慢查询日志以#作为起始符。
User@Host：表示用户和慢查询查询的ip地址。
Query_time: 表示SQL查询持续时间，单位 (秒)。
Lock_time: 表示获取锁的时间，单位(秒)。
Rows_sent: 表示发送给客户端的行数。
Rows_examined: 表示：服务器层检查的行数。
set timestamp ：表示慢SQL 记录时的时间戳。
其中 select sleep(6) 则表示慢SQL语句。

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