MySQL-Server 服务端管理

发布于: 2021-07-19
更新于: 2023-05-18
分类:
开发 376
标签:
MySQL 22 数据库 45
访问统计:
总量 加载中...
  • 加载中...

查看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 查看最大并发连接数

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

即 mysql 运行过程中曾经达到的最大连接数
show global status like '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语句。
二进制日志记录了对MySQL数据库执行更改的所有操作，若操作本身没有导致数据库发生变化，该操作可能也会写入二进制文件。
但是不包括select和show这类操作（因为这些操作对数据本身不会进行修改）
binlog默认是关闭的。

哪些sql会写入binlog？

DDL DML ，比如 create table等，insert update 等，会写入操作，即使 update 操作没有命中行，也会写入binlog
select 等查询操作不会写入 binlog

何时会创建新的binlog文件？

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

为什么MySQL有binlog，还有redo log?

这个是因为MySQL体系结构的原因，MySQL是多存储引擎的，不管使用那种存储引擎，都会有binlog，而不一定有redo log，简单的说，binlog是MySQL Server层的，redo log是InnoDB层的。

二进制日志的几种作用：
恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

binlog_format 格式

16.1.6.4 Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format

5.4.4.2 Setting The Binary Log Format
https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

该参数影响了记录二进制日志的格式。在MySQL 5.1版本之前没有这个参数，只支持 STATEMENT 格式的 bin log，MySQL 5.1开始引入了这个参数。
设置 binlog_format 参数并不会激活 binlog 日志，设置 log_bin 才会开启 binlog

查看binlog格式
show variables like '%binlog%'

STATEMENT

STATEMENT STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句
Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。
但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。
所以使用 Statement 格式会出现一些数据一致性问题。

ROW(默认值)

ROW 默认值，ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况
从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。
不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。
基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别）。同时，对上述体积的Statement格式下复制的问题予以解决
从MySQL 5.1版本开始，如果设置了binlog_format为ROW，可以将InnoDB的事务隔离基本设为READ COMMITED，以获得更好的并发性

MIXED

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。
在 Mixed 模式下，系统会自动判断该用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog;对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。
Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

应该选择哪种binlog格式？
即使不使用 READ COMMITTED 的事务隔离级别，也应该考虑将二进制日志的格式更换为ROW，因为这个格式记录的是行的变更，而不是简单的SQL语句，可以避免一些不同现象的产生，进步一保证数据的同步。

内部XA事务(binlog和Innodb事务之间)

最为常见的内部XA事务存在于binlog与InnoDB存储引擎之间

由于复制的需要，因此目前绝大多数的数据库都开启了binlog的功能。在事务提交时，先写二进制日志，再写InnoDB存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入InnoDB存储引擎时发生了宕机，那么slave可能会接收到master传过去的二进制日志并执行，最终导致了主从不一致的情况。

MySQL为了保证master和slave的数据一致性，就必须保证binlog和InnoDB redo日志的一致性（因为备库通过二进制日志重放主库提交的事务，而主库binlog写入在commit之前，如果写完binlog主库crash，再次启动时会回滚事务。但此时从库已经执行，则会造成主备数据不一致）。

为了解决这个问题，MySQL数据库在binlog与InnoDB存储引擎之间采用XA事务。

所以在开启Binlog后，如何保证binlog和InnoDB redo日志的一致性呢？为此，MySQL引入二阶段提交（two phase commit or 2pc），MySQL内部会自动将普通事务当做一个XA事务（内部分布式事物）来处理：
– 自动为每个事务分配一个唯一的ID（XID）。
– COMMIT会被自动的分成Prepare和Commit两个阶段。
– Binlog会被当做事务协调者(Transaction Coordinator)，Binlog Event会被当做协调者日志。

当事务提交时，InnoDB会先做一个PREPARE操作，将操作的xid写入，接着进行二进制日志的写入
如果在InnoDB存储引擎提交前，MySQL数据库发生宕机了，那么MySQL数据库在重启后会先检查准备的UXID事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作

MySQL(InnoDB剖析):11---文件之（日志文件.log(错误日志/慢查询日志/查询日志/二进制日志)）
https://blog.csdn.net/qq_41453285/article/details/104110026

MySQL中Redo 和 Binlog 顺序一致性问题
https://blog.csdn.net/LYK_for_dba/article/details/79565570

binlog相关配置

show variables like "%log_bin%"
show variables like '%binlog%'

mysql> show variables like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.01 sec)
--log-bin 开关(启动参数)

16.1.6.4 Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_log-bin

--log-bin=file_name 开启 binlog 功能。
设置 --log-bin 启动参数（无论是否有值）会使 log_bin 系统变量的值变为 ON，否则为 OFF。设置的文件名参数会成为 log_bin_basename 变量。

假如设置 --log-bin=/var/lib/mysql/mysql-bin 则产生的 binlog 文件名为：
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004

my.cnf 中配置二进制文件

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

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

log_bin 是否开启binlog(系统变量)

16.1.6.4 Binary Logging Options and Variables
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_bin
log_bin 表示 binlog 是否开启，不是动态参数，不能运行中动态修改。
设置 --log-bin 启动参数（无论是否有值）会使 log_bin 系统变量的值变为 ON，否则为 OFF。
log_bin 只反应是否设置了 --log-bin 启动参数，不反应具体参数值。

log_bin_index 索引文件

log_bin_index 二进制索引文件的路径与名称

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

binlog index 文件就是个文本文件，里面每行是一个 binlog 文件的绝对路径。

# head mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004
/var/lib/mysql/mysql-bin.000005
/var/lib/mysql/mysql-bin.000006
/var/lib/mysql/mysql-bin.000007
/var/lib/mysql/mysql-bin.000008
/var/lib/mysql/mysql-bin.000009
/var/lib/mysql/mysql-bin.000010
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 值并使用临时文件来保存事务中的语句的事务数量

binlog_cache_size

事务提交前binlog记录到缓冲区中，等事务提交后将缓冲区中的binlog写入binlog文件
当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交的二进制日志会被记录到一个缓冲中去，等待事务提交时直接将缓冲中的二进制日志写入二进制日志文件，该缓冲的大小由该参数决定。默认值为32K

binlog_cache_size参数
当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交的二进制日志会被记录到一个缓冲中去，等待事务提交时直接将缓冲中的二进制日志写入二进制日志文件，该缓冲的大小由该参数决定。默认值为32K

max_binlog_size 单个binlog文件最大值

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

sync_binlog binlog刷新策略

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_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 信息都会丢失

expire_logs_days binlog过期时间

查看 binlog 过期时间，0 表示永远保留
show variables like 'expire_logs_days';
或
select @@global.expire_logs_days;

设置保留7天
set global expire_logs_days=7;

设置之后不会立即清除，触发条件是：
binlog 大小超过 max_binlog_size
手动执行 flush logs
重新启动时(MySQL 将会new一个新文件用于记录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: 2023-11-15T17:18:06.638666+08:00
# 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);

慢查询日志以 # Time 作为起始符，包括3个#开头的行，时间戳行，sql行

Time 表示何时执行了慢查询，它显示的是日志记录的时间，即查询完成时的日期和时间。
User@Host：表示用户 和 慢查询查询的ip地址。
Query_time: 表示SQL查询持续时间，**单位秒** Lock_time: 表示获取锁的时间，单位秒
Rows_sent: 表示发送给客户端的行数。 Rows_examined: 服务器层检查的行数。
set timestamp ：慢SQL 记录时的时间戳。
其中 select sleep(6) 则表示慢SQL语句。

log_queries_not_using_indexes 记录没使用索引的sql

set global log_queries_not_using_indexes = "ON";
设置该属性后，只要SQL未走索引，即使查询时间小于 long_query_time 值，也会记录在慢SQL日志文件中。

文章目录