MySQL 服务器配置与管理＜二＞

目录

日志

日志简介

⼀般查询⽇志和慢查询⽇志的输出形式 

​编辑 ⼀般查询⽇志

​编辑 慢查询⽇志

​编辑 错误⽇志

二进制日志 

Redo Log 和 Undo Log

服务器⽇志维护

配置⽇志输出位置 

mysql System Schema (mysql系统库)

 在⼀台机器上运⾏多个MySQL实例

日志

日志简介

mysql服务器在运行的过程中会接受到各种请求，执行各种sql语句，记录各种数据，这些操作都会被记录为一个事件，以日志的形式保存到对应的日志文件中

1.默认情况下，除 Windows 上的错误⽇志外，不启⽤任何⽇志，Linux下默认开启错误⽇志和⼆进制⽇志

2.在服务器运⾏期间可以控制⼀般查询和慢查询⽇志的禁⽤与开启，也可以更改⽇志⽂件名

3.⼀般查询⽇志和慢查询⽇志记录可以写⼊⽇志表、⽇志⽂件或两者同时写⼊

4. 默认情况下，所有启⽤的⽇志将写⼊数据⽬录，可以通过刷新⽇志强制服务器关闭并重新打开⽇志 ⽂件

5. 通过 FLUSH LOGS 语句刷新⽇志来强制服务器关闭并重新打开⽇志⽂件，也可以使⽤

mysqladmin的 flush-logs 或 refresh 参数,或mysqldump 的 --flush-logs 或 --master-data 选项

6.中继⽇志仅⽤于主从复制过程中的从服务器

⼀般查询⽇志和慢查询⽇志的输出形式 

(1)log_output 系统变量指定⽇志输出的形式，但并不会真正的启⽤⽇志。 log_output 可以有

三个值，分别是： TABLE (表)、 FILE (⽂件)、 NONE (不输出)，可以同时指定多个值，并⽤逗号隔开，未指定值时默认是 FILE ，如果列表中存在 NONE 则其他的不⽣效，也就是说 NONE 的优先级最⾼。

(2)通过设置 general_log 系统变量的值来控制⼀般查询⽇志的 开启 1 与 禁⽤ 0 ，如果要为⽇志指定⾃定义的绝对路径或⽂件名可以使⽤ general_log_file 系统变量

(3) 通过设置 slow_query_log 系统变量的值来控制慢查询⽇志的 开启 1 与 禁⽤ 0 ，如果要为

⽇志指定⾃定义的绝对路径或⽂件名可以使⽤ slow_query_log_file 系统变量

(4)一般查询日志记录了所有的查询语句，慢查询日志只记录查询时间超时的查询语句，即慢查询日志是一般查询日志的子集

示例1：将⼀般查询⽇志写⼊⽇志表和⽇志⽂件

 查询默认的路径

示例2：仅将⼀般查询⽇志和慢查询⽇志写⼊⽇志表

示例3：仅将慢查询⽇志写⼊⽇志⽂件 

示例4:将⼀般查询⽇志和慢查询⽇志写⼊⽇志⽂件，并指定⾃定义的⽇志路径

示例5:将⼀般查询⽇志和慢查询⽇志写⼊⽇志⽂件和日志表，并指定⾃定义的⽇志文件名

重启mysql服务，再查询一下确认修改的内容

2.运⾏时的⽇志控制

(1)在运⾏时修改 log_output 的值，以更改⽇志的输出形式，通过语句控制:

语法：SET [GLOBAL|SESSION] variable_name=value

如:SET GLOBAL log_output=[FILE, TABLE, NONE] 

(2)general_log[={0|1}] 和 slow_query_log[={0|1}] 可以表⽰启⽤和禁⽤⼀

   般查询⽇志和慢查询⽇志

(3)general_log_file 和 slow_query_log_file 表⽰通⽤查询⽇志和慢查询⽇志⽂件名称

(4)只对当前会话禁⽤或启⽤⼀般查询⽇志记录，将 SESSION 作⽤域的 sql_log_off 变量设置

为 ON 或 OFF。即 SET SESSION sql_log_off =ON | OFF

3.使⽤⽇志表优点

(1) 可以通过 SQL 语句的条件查询过滤⽇志内容(使用where 条件)，从⽽选择满⾜特定条件的⽇志记录。⽐如，某个客⼾端的⽇志；

(2)可以通过客⼾端程序连接到服务器并查询表中的⽇志信息，⽆需登录服务器主机访问⽂件系统

(3)⽇志记录具有标准格式，可看⽇志表的结构，可以使⽤以下语句：

SHOW CREATE TABLE mysql.general_log; # ⼀般查询⽇志

SHOW CREATE TABLE mysql.slow_log; # 慢查询⽇志

 ⼀般查询⽇志

General query log - ⼀般查询⽇志，记录客⼾端连接或断开连接的信息，也会记录从客⼾端接收的每个SQL语句。如果开启将会产⽣⼤量的内容，⾮常耗费服务器资源，所以默认为关闭(不开启)，要启⽤⼀般查询⽇志可以使⽤:   -- general_log[={0|1}]

默认⽇志⽂件名为 host_name.log ，可以使⽤ general_log_file=file_name 修改； 

记当客⼾端连接的⽇志⾏，使⽤ connection_type 来指⽰⽤于建⽴连接的协议。 TCP/IP 表

⽰不使⽤SSL建⽴的TCP/IP连接、 SSL/TLS 表⽰使⽤SSL建⽴的TCP/IP连接、 Socket 表⽰

Unix套接字⽂件连接、 Named Pipe 表⽰Windows命名管道连接、 Shared Memory 表⽰

Windows共享内存连接

Mysqld按照接收到SQL语句的顺序将语句写⼊查询⽇志，这个顺序可能与语句执⾏的顺序不同 

查看 General query log 一般查询日志这个表的建表字段

执行的命令为: mysql> SHOW CREATE TABLE mysql.general_log;

 示例:

(1)查看是否开启一般查询日志及输出形式

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

(2)通过SQL语句查询日志 

在argument中存的就是记录的SQL语句，但直接看的话是看不懂的，需要使用CAST()函数把编码后的内容转换为可读的字符类型，CAST函数的语法为: CAST(要转换的内容 AS 目标数据类型)

(4) 查看磁盘上一般查询日志⽂件中的内容

root@guangchen-002:/var/lib/mysql# cat general.log   //使用此命令进行查看

 慢查询⽇志

慢查询⽇志由执⾏时间超过系统变量 long_query_time 指定的秒数(默认为10s)的 SQL 语句组成，并且检查的⾏数⼤于系统变量 min_examined_row_limit 指定值(查询语句返回的结果集中所包含的行数)。被记录的慢查询需要进⾏优化，可以使⽤ mysqldumpslow客⼾端程序对慢⽇志进⾏分析汇总。

获取初始锁的时间不计⼊执⾏时间，mysqld在执⾏完SQL语句并释放所有锁后才将符合条件的语句写⼊慢速查询⽇志，因此⽇志顺序可能与执⾏顺序不同。

 一条sql语句执行需要经历的阶段:

(1)执行线程要获取到锁，如果线程没有获取到锁，就会阻塞等待

(2)执行sql语句并返回结果

(3)释放锁

慢查询日志记录的时间为(2)和(3)

慢查询日志的参数

(1) long_query_time 的默认值是10，最⼩值是0；  

(2)默认情况下，不记录管理语句，也不记录不使⽤索引的查询

(3)要记录管理语句，启⽤ log_slow_admin_statements 系统变量。管理语句包括 ALTER

TABLE 、 ANALYZE TABLE 、 CHECK TABLE 、 CREATE INDEX 、 DROP INDEX 、

OPTIMIZE TABLE 和 REPAIR TABLE

(4) 要记录不使⽤索引的查询，启⽤ log_queries_not_using_indexes 系统变量。当记录不使

⽤索引的查询时，⽇志会快速增⻓，通过设置系统变量

log_throttle_queries_not_using_indexes 限制每分钟写⼊慢查询⽇志同类查询的数量，默认值是0，表⽰⽆限制。(两者必须同时开启或者关闭)

(5)默认为关闭(不开启)，要启⽤慢查询⽇志可以使⽤：  --slow_query_log[={0|1}]

(6) 默认⽇志⽂件名为 host_name -slow.log ，可以使⽤slow_query_log_file= file_name 修改

(7)使⽤ --log-short-format 选项，以简要格式记录慢查询⽇志

慢查询⽇志内容--FILE格式

如果启⽤慢查询⽇志并将 FILE 作为输出⽬标，每条语句前⾯都⽤⼀⾏来表⽰⽇志的字段，该⾏以 # 字符开头并包含以下内容

 启⽤--log-slow-extra[={OFF|ON}]系统变量会将额外字段写⼊到FILE中，TABLE形式不受影响

 

Read_first: 索引中第⼀个条⽬被读取的次数，如果这个值很⾼，表明服务器正在执⾏⼤

量完整索引扫描

Read_last: 读取索引中最后⼀个键的请求数，使⽤ ORDER BY 时关注

......

 慢查询⽇志内容--表格式

查看慢查询⽇志表的表结构:   mysql> SHOW CREATE TABLE mysql.slow_log;

 错误⽇志

错误⽇志⼀般会记录mysqld 启动和关闭的次数、诊断消息，以及服务器运⾏期间发⽣的错误和警告；例如MySQL需要⾃动检查或修复⼀个表，就会在错误⽇志中写⼊⼀条记录。错误⽇志默认使⽤UTF-8 ( utf8mb3 )编码格式，并使⽤英语⽣成记录

注:当mysql服务启动失败时，应该首先查看一下错误日志 

1.配置错误⽇志

错误⽇志输出的位置，可以是控制台或指定⽂件，"控制台"表⽰ stderr 标准错误输出

(1)Windows 的默认错误⽇志路径

在Windows系统中，mysqld使⽤ --log-error(文件路径)  和 --console(控制台)  选项来确定默认的错误⽇志⽬标是控制台还是⽂件，其中有一些规则如下:

1.如果指定了 --console 选项，默认在控制台输出错误⽇志，如果 --console 和 --log-error 同时指定，则 --console 优先级更⾼，并且 --log-error 将失效

2.如果没有指定 --log-error 或者没有指定具体的⽂件名，默认在数据⽬录中⽣成名为

host_name .err 的⽇志⽂件。

3.可以通过指定绝对路径，来更改默认的⽇志位置,格式如下：

[mysqld]

log-error=D:/log/MySQL/Error/error_log.err # ⾃定义错误⽇志的路径

(2)Linux 系统的默认错误⽇志路径

在 Unix 和Linux 系统中，mysqld使⽤ --log-error 选项来指定默认错误⽇志⽬标，可以指定控制台或是⽂件，如果是⽂件，规则如下：

 

1.如果错误⽇志输出⽬标是控制台，则服务器将 log_error 系统变量设置为 stderr . 否则，将

以⽂件形式输⼊错误⽇志，并以 log_error 的值为⽂件名

2.如果显⽰写出 - -log-error 但没有指定具体⽂件，则默认路径是数据⽬录中 host_name .err 的⽂件；

3.如果明确指定  - -log-error ⽂件名，则在数据目录下以指定文件名生成错误日志(如果没有指定文件后缀，则以.err为结尾)，可以通过指定绝对路径，来更改默认的⽇志位置

4.可以通过指定绝对路径，来更改默认的⽇志位置

[mysqld]

log-error=/var/log/mysql/error_log.err # ⾃定义错误⽇志的路径

可选错误事件字段 ：

OS_errno ：操作系统错误号；

OS_errmsg ：操作系统错误消息；

label ：与值对应的 prio 描述 ；

user ：客⼾端⽤⼾；

host ：客⼾端主机；

thread ：产⽣错误事件 的线程的 ID；

query_id ：查询 ID。

mysql> SELECT * FROM performance_schema.events_errors_summary_global_by_error

WHERE SUM_ERROR_RAISED <> 0 \G

示例:

 在官网中可以查看一个错误码对应的描述

3.刷新错误⽇志⽂件和重命名 

二进制日志 

1.二进制日志的使用场景:

在最初负载量较小时，读写操作是都在一台机器上的，但随着数据量的增加，单台服务器可能会因为访问量过大，机器配置较低等出现性能降低的情况，这时解决方式为在集群环境中部署多台服务器，其中选个服务器作为主服务器用于写入操作，其他服务器作为从节点

当数据写入到主结点之后，如何把新写入的数据同步到从结点上呢?

做法为:将所有的修改操作都生成二进制日志存储在主结点中，从节点拷贝这个二进制日志，生成自己的中继日志，再从结点执行中继日志中的所有修改记录，这样就达到了数据的同步

5. ⼆进制⽇志的语句中如果涉及⽤⼾的密码，则由服务器进⾏加密，不会以纯⽂本形式出现

6.选项和变量

(1)查看⼆进制⽇志相关的系统变量: mysql> show variables like '%bin%';

(2)查看⼆进制⽇志相关的状态变量：mysql> show status like '%bin%';

(3)默认情况下启⽤⼆进制⽇志， log_bin 系统变量为 ON ；

(4)禁⽤⼆进制⽇志，可以指定 --skip-log-bin 或 --disable-log-bin 选项。如果同时指

定了 --log-bin 则后指定的选项优先(不用指定值)

(5) 选项 --log-bin[= base_name] ⽤于指定⼆进制⽇志⽂件的基本名称，如果不指定 --log

bin 选项，默认基本名称为 binlog ，建议为⼆进制⽇志指定⼀个基本名；

(6) ⼆进制⽇志⽂件名是由基本名+数字扩展名组成的，服务器每次创建⼀个新的⽇志⽂件时，数字扩 展名都会增加，从⽽保证有序的⽂件系列，发⽣以下事件时，服务器都会在创建⼀个新的⽇志⽂件:

   服务器已启动或重新启动

服务器刷新⽇志

   当前⽇志⽂件的⼤⼩达到 max_binlog_size (单个⽇志⽂件的最⼤字节数，最⼩值 4096 字

   节，最⼤值和默认值 1GB)

   

⼆进制⽇志⽂件⼤⼩可能会超出 max_binlog_size 设定的值，因为⼆进制⽇志在记录事务

时，会完整的记录整个事务，不存在把⼀个事务拆分的情况，如果遇到⼀个⼤事务时，即使记录整个事务会超过⽇志⼤⼩限制，也会保证事务的完整性(说明二进制日志以事物单位进行记录)

(7)mysqld还会创建⼀个包含⼆进制⽇志⽂件名的⽇志索引⽂件，默认情况下，这与⼆进制⽇志⽂件具有相同的基本名称，扩展名为 .index . 可以使⽤选项 --log-bin-index[=file_name]

修改索引⽂件名;

 (8)⼆进制⽇志⽂件和索引⽂件的默认位置是数据⽬录。可以使⽤ --log-bin[=file_name] 选项

指定⾃定义路径， file_name 格式 = 绝对路径+基本名。 --log-bin 对应的系统变量是

log_bin_basename ；

(9)MySQL 5.7 中，启⽤⼆进制⽇志必须指定服务器 ID，对应 server_id 选项，否则服务器将⽆法启动。在 MySQL 8.0 中， server_id 系统变量默认设置为 1，在集群环境中，每台MySQL服务器必须有唯⼀的 server_id ；

7.使⽤客⼾端⼯具mysqlbinlog查看⼆进制⽇志

8. 通过SQL语句查看⼆进制⽇志

mysql> show binlog events in 'binlog.000001' from N limit S;

举例：

 9.记录⼆进制⽇志时使⽤的几种格式：

基于语句的⽇志格式，最初MySQL 是基于 SQL 语句复制实现主从节点同步，通过指定选项 -- binlog-format=STATEMENT 使⽤此格式 。(总结为执行什么语句，就直接记录对应的sql)

基于⾏的⽇志格式(默认)中，主节点将事件写⼊⼆进制⽇志，表⽰各个表的⾏受到的影响，可以通过指定选项 --binlog-format=ROW 使⽤此格式 。(当使用mysql内部的一些函数时，必须以行的格式记录日志)

混合⽇志记录格式，默认情况下使⽤基于语句的⽇志记录，如果MySQL认为基于语句的格式不能保证主从复制过程中的数据安全时，会⾃动切换到基于⾏的⽇志格式，⽐如主节点在语句中⽤了 UUID() 函数，那么⽇志⽂件中记录的是UUID⽣成的真实值⽽不是直接使⽤原始的SQL语句，使⽤混合⽇志格式中以指定选项 --binlog-format=MIXED

# 基于语句，记录执⾏的SQL语句
update student set age = 18 where id between 10 and 20;
# 基于⾏，记录每⼀⾏的更改
update student set age = 18 where id = 10;
update student set age = 18 where id = 11;
update student set age = 18 where id = 12;
...
update student set age = 18 where id = 19;
update student set age = 18 where id = 20;

强制使⽤新的⽇志⽂件可以⼿动刷新⽇志，当执⾏ FLUSH LOGS 语句或 mysqladmin flush-

logs 、 mysqladmin refresh 、 mysqldump --flush-logs 、 mysqldump --

master-data 命令时，会发⽣⽇志刷新。此外当⼆进制⽇志⽂件⼤⼩达到

max_binlog_size 系统变量指定的值时，服务器会⾃动刷新⼆进制⽇志

FLUSH LOGS ⽀持可选的修饰符以启⽤个别⽇志的选择性刷新：

FLUSH BINARY LOGS # 刷新⼆进制⽇志

FLUSH ERROR LOGS # 刷新错误⽇志

FLUSH GENERAL LOGS # 刷新⼀般查询⽇志

FLUSH RELAY LOGS # 刷新中继⽇志

FLUSH SLOW LOGS # 刷新慢查询⽇志

刷新⼀般查询⽇志、慢查询⽇志或错误⽇志只是关闭并重新打开⽇志⽂件，如果要备份可以先重命名再执⾏刷新操作，⽐如⼀般查询⽇志、慢查询⽇志或错误⽇志⽂件名分别为： mysql.log 、 mysql-slow.log 和 err.log ，可以在命令⾏中使⽤如下⼀系列命令：

cd mysql-data-directory # 进⼊⽇志⽬录

mv mysql.log mysql.log.old # 重命名⼀般查询⽇志

mv mysql-slow.log mysql-slow.log.old # 重命名慢查询⽇志

mv err.log err.log.old # 重命名错误⽇志

mysqladmin flush-logs # 刷新⽇志

要在运⾏时重命名⼀般查询⽇志或慢查询⽇志:

⾸先连接到服务器并禁⽤⽇志：

SET GLOBAL general_log = 'OFF' ;

SET GLOBAL slow_query_log = 'OFF' ;

 在禁⽤⽇志的情况下，重命名⽇志⽂件，例如⽤ mv 命令从命令⾏执⾏重命名操作

再次启⽤⽇志：

SET GLOBAL general_log = 'ON' ;

SET GLOBAL slow_query_log = 'ON' ;

这种⽅法适⽤于任何平台且不需要重启服务器

配置⽇志输出位置 

# 服务器节点
[mysqld]
# ⼀般查询⽇志和慢查询⽇志记录⽅式为⽂件
log-output=FILE
# 开启⼀般查询⽇志
general-log=1
# ⼀般查询⽇志路径和⽂件名
general_log_file=/var/log/mysql/general.log
# 开启慢查询⽇志
slow-query-log=1
# 慢查询⽇志路径和⽂件名
slow_query_log_file=/var/log/mysql/slow-query.log
# 慢查询⽇志时间限制
long_query_time=10
# 错误⽇志路径和⽂件名
log-error=/var/log/mysql/error.err
# ⼆进制⽇志路径和基本名
log-bin=/var/log/mysql/binlog
# 服务器编号
server-id=1

mysql> select * from mysql.character_sets; # 访问可⽤字符集的信息
ERROR 3554 (HY000): Access to data dictionary table 'mysql.catalogs' is
rejected.

(3)INFORMATION_SCHEMA 实现了某些数据字典的视图,可以通过视图查看某些数据字典的内容：

# 通过INFORMATION_SCHEMA中的视图访问可⽤字符集的信息
mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

2.系统表

系统表按功能⼤致可以分为以下⼏类：权限授予系统表、对象信息系统表、⽇志系统表、服务器端帮助系统表、时区系统表、复制系统表、优化器系统表、杂项系统表。mysql系统库中的表如下

 在⼀台机器上运⾏多个MySQL实例

右键结束任务

然后在任务管理器开启新的mysql服务

安装完成后会在C:\ProgramData\MySQL下⽣成对应版本的数据⽬录

1.配置不同的版本选项⽂件

不同的MySQL版本启动时，会在默认的数据⽬录下读取相应版本的 my.ini ⽂件，⽐如5.7版本启动时会读取C:\ProgramData\MySQL\MySQL Server 5.7\my.ini⽂件，8.0版本启动时会读取C:\ProgramData\MySQL\MySQL Server 8.0\my.ini⽂件，为了让不同版本的实例在启动时避免冲突，⽐如端⼝冲突，需要对不同版本的实例进⾏⼀些差异配置

(1) 指定基本⽬录

# C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

# 路径中的空格要⽤双引号引起来

[mysqld] # mysqld 节点

basedir="C:/Program Files/MySQL/MySQL Server 5.7" #5.7 版本的⾃定义的基本⽬录

# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld] # mysqld 节点

basedir="C:/Program Files/MySQL/MySQL Server 8.0" #8.0 版本的⾃定义的基本⽬录

 (2)设置数据⽬录

 在对应版本的 my.ini ⽂件中指定⾃定义数据⽬录，通过指定 --datadir=dir_name 实现

# C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

[mysqld] # mysqld 节点

datadir=D:/database/MySQL/data5.7 #5.7 版本的⾃定义的数据⽬录

# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld] # mysqld 节点

datadir=D:/database/MySQL/data8.0 #8.0 版本的⾃定义的数据⽬录

(3)设置端⼝号 

(4)设置⽇志⽬录

# C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

[mysqld] # mysqld 节点

# ⽇志输⼊⽬标

log-output=FILE

# ⼀般查询⽇志

general-log=1

general_log_file=D:/log/MySQL/5.7/general.log

# 慢查询⽇志

slow-query-log=1

slow_query_log_file=D:/log/MySQL/5.7/slow_query.log

long_query_time=10

# 错误⽇志

log-error=D:/log/MySQL/5.7/error_log.err

# ⼆进制⽇志基本名

log-bin=D:/log/MySQL/5.7/bin_log

# ##################################################################

# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld] # mysqld 节点

# ⽇志输⼊⽬标

log-output=FILE

# ⼀般查询⽇志

general-log=1

general_log_file=D:/log/MySQL/8.0/general.log

# 慢查询⽇志

slow-query-log=1

slow_query_log_file=D:/log/MySQL/8.0/slow_query.log

long_query_time=10

# 错误⽇志

log-error=D:/log/MySQL/8.0/error_log.err

# ⼆进制⽇志基本名

log-bin=D:/log/MySQL/8.0/bin_log

(5) 其他设置

2.在多服务器环境中使⽤客⼾端程序

使⽤ mysql 客⼾端程序，根据不同版本的MySQL实例配置的IP和端⼝号，指定相应的选项，连接不同的MySQL服务器。指定端⼝通过 --port= port_number 选项实现，如果绑定了不同的⽹卡通

过 --host= host_name 选项实现，如果不指定 --host 选项，则使⽤默认值 loclhost