MySQL服务器配置与管理

一.MySQL 服务器简介

通常所说的 MySQL 服务器指的是mysqld程序，当运行mysqld后对外提供MySQL 服务。

1.服务器配置和默认值

mysqld 有很多选项和系统变量可以在启动时进行配置，要查看服务器的默认选项和系统变量值，可以执行以下命令：

查看服务器在运行时系统变量的值，连接到MySQL并执行以下语句：

查看服务器在运行时的⼀些统计和状态指示器，连接到MySQL并执行以下语句：

系统变量和状态信息也可以使用 mysqladmin命令来查看：

关于服务器的选项、系统变量、状态变量参考以下官网列表

• 选项、系统变量、状态变量汇总
• 系统变量
• 状态变量

注意：系统变量、状态变量的作用域分为： Global (全局)， Session (当前会话或连接)，或 两
者都支持

2.系统变量和选项

1）简介

• 当通过mysqld启动数据库服务器时，可以通过选项文件或命令行中提供选项，但是，在大多数情
  况下，为确保服务器每次运行时都使用相同的选项，最好的方法是在选项文件中指定相应的选项。

• mysqld从选项文件中的 [mysqld] 和 [server] 组(节点)中读取选项内容
  

• mysqld接受的选项可以通过 mysqld --verbose --help 查看，列表中的有些项目是可以在服务器启动时设置的系统变量，系统变量可以在连接MySQL后使用 SHOW VARIABLES 语句查看，但有些内容只在 --help 中存在，使用 SHOW VARIABLES 时并没有显示，这是因为它们只是选项而不是系统变量。

注意：选项值可以通过命令行和选项文件设置，部分系统变量可以通过SET语句动态设置值

2）常⽤选项

3.使用系统变量

1. 以上我们介绍了通过选项文件和命令行设置相应系统变量的值，设置系统变量的语法与命令选项的
   语法相同，指定变量名称时，破折号和下划线可以互换使用。例如， --general_log=ON 和 --general-log=ON 是等价的。
2. 当使用选项设置⼀个数值的变量时，可以带有后缀 K 、 M 或 G （大小写不限）表示 1024 、
   1024^2 或 1024^3 ；从MySQL 8.0.14 开始，后缀也可以用 T 、 P 和 E 来表示 1024^4 、1024^5 或 1024^6 。

示例：为服务器指定 256 KB 的排序缓冲区大小 1 GB 的最大数据包大小

3.统变量有两个作用域，分别是 Global (全局)和 Session (会话)， Global 全局变量影响服务器的整体操作， Session 会话变量影响各个客户端连接的操作。给定的系统变量可以同时具有全局值和会话值，它们的关系如下：

• 服务器启动时，会将每个全局变量初始化并设置默认值，具体的值可以通过命令⾏或选项⽂件
  更改。
• 服务器为每个客⼾端维护⼀组 Session 变量，在客⼾端连接时使⽤相应全局变量的当前值进
  ⾏初始化。

4.大部分系统变量是动态的，在服务器运行时可以通过 SET 语句动态更改，并且无需停止和重新启
动服务器。在服务器运行时，使用 SET 语句设置系统变量，需要指定作用域(也可以在前⾯加上@@ 修饰符)，然后指定系统变量的名称，名称必须使用下划线而不是破折号，如下所示：

• a. 设置全局系统变量最⼤连接数为1000
  

• b. 将全局系统变量持久化到 mysqld-auto.cnf （安装MySQL是不会有这个文件，需要执行了PERSIST命令才会存在）文件（同时设置运行时值）：

• 

• c. 将全局系统变量持久化到 mysqld-auto.cnf 文件（不设置运行时值）：
  

删除持久化的系统变量可以使⽤语句

• d. 设置 Session 系统变量，时区为"+8:00"：
  

5. 对于数值型的系统变量，用带有后缀的值指定时，只适用于选项方式，而不能用在 SET 方式中；
   SET 方式可以使用表达式为系统变量指定值，而在选项方式中不允许，如下所示：
   说明：在设置全局变量时需要指定GLOBAL关键字的原因是为了防止出现以下问题:

• 如果要删除的SESSION变量与GLOBAL变量名相同，那么具有修改全局变量权限的客户端可能会
  意外地更改GLOBAL变量，而不仅仅是只修改SESSION变量。
• 如果已经有一个SESSION变量而且与GLOBAL变量同名，那么本意是要修改GLOBAL变量，可能只是修改了SESSION变量的值

所以没有明确指定 GLOBAL 和 SESSION 时，对于当前客户端来说SESSION 的优先级更高

6. 要显示系统变量名称和值，请使用以下 SHOW VARIABLES 语句：
   

7. 可以使用 LIKE 子句显示与指定内容匹配的变量，也可以使用通配符

8. 一部分系统变量是内置的，也有一些需要通过安装服务器插件或组件才可以使用

• 比如用于审计插件 audit_log 实现了名为 audit_log_policy 的系统变量
• 错误日志过滤组件 log_filter_dragnet 实现了名为 log_error_filter_rules 的系统变量。

9. 可以动态设置的系统变量，参考链接

4.示例：服务器常用配置

• Linux系统下编辑 /etc/mysql/my.cnf

• Windows系统下打开C:/ProgramData/MySQL/MySQL Server 8.0/my.ini 注意：

• 编辑前先备份原始文件

• 如果要修改数据木录选项建议先停止MySQL服务，并把原data目录整体复制到新路径，配置完成后重启服务

• 在 [mysqld] 节点下添加以下内容

5.查看状态变量

MySQL服务器维护着当前系统信息的状态变量

可以使用 SHOW [GLOBAL | SESSION] STATUS [like status_name]; 语句查看这些变量和对应的值。

GLOBAL显示所有连接的值，SESSION显示当前连接的值。
具体的状态变量及表⽰的意义可以参考官网文档

二. MySQL数据目录

MySQL 服务器的管理信息、业务数据、日志文件、磁盘缓冲文件默认存储在数据目录下，数据目录⼀
般包含以下内容：

1. 数据目录下的每个子目录都是一个数据库目录，对应服务器管理的⼀个数据库，包括MySQL 安装
   成功后创建的标准数据库：
   • a. mysql目录对应于mysql系统库，包含mysql服务器运行时所需的信息，该数据库包含数据字典表和系统表；
   • b. performance_schema目录对应于Performance Schema，提供了在运行时用于检查服务器内部执行的信息；
   • c. sys目录对应于sys系统库，提供⼀组对象来帮助解释性能模式相关信息；
   • d . 其他⼦目录对应于用户或应 用程序创建的数据库，也就是说我们每创建一个数据库，就会在数
     据目录生成一个同名的目录来保存对应的数据。
2. 服务器写入 的日志文件
3. InnoDB 表空间和日志文件
4. 默认或自动生成的 SSL 和 RSA 证书和密钥文件
5. 服务器进程 ID 文件（当服务器运行时）
6. mysqld-auto.cnf 文件用来存储持久化全局系统变量设置

通过选项重新配置服务器，可以将上述某些项⽬重新定位到指定目录。

使用 --datadir 选项允许更改数据目录本身的位置。

三.日志简介

MySQL Server 有以下几种日志，可以记录服务器正在发生的活动。

• 默认情况下，除 Windows 上的错误日志外，不启用任何日志，Linux下默认开启错误日志和二进制
  日志
• 在服务器运行期间可以控制⼀般查询和慢查询日志的禁用与开启，也可以更改日志文件名
• ⼀般查询日志和慢查询日志记录可以写入日志表、日志文件或两者同时写 入
• 默认情况下，所有启用的日志将写入数据目录，可以通过刷新日志强制服务器关闭并重新打开日志
  文件
• 通过 FLUSH LOGS 语句刷新日志来强制服务器关闭并重新打开日志文件，也可以使用mysqladmin 的 flush-logs 或 refresh 参数，或mysqldump 的 --flush-logs 或 --master-data 选项
• 中继日志仅用于主从复制过程中的从服务器。

1.⼀般查询日志和慢查询日志的输出形式

如果启用⼀般查询日志和慢查询日志，日志的输出方式可以指定为日志文件或 mysql 系统库中
的 general_log 和 slow_log 表，也可以两者同时指定。

1）启动时的日志控制

• log_output 系统变量指定日志输出的形式，但并不会真正的启用日志。 log_output 可以有三个值，分别是： TABLE (表)、 FILE (文件)、 NONE (不输出)，可以同时指定多个值，并用逗号隔开，未指定值时默认是 FILE ，如果列表中存在 NONE 则其他的不生效，也就是说 NONE 的优先级最高。
• 通过设置 general_log 系统变量的值来控制⼀般查询日志的 开启 1 与 禁用 0 ，如果要为日志指定自定义的路径或文件名可以使用 general_log_file 系统变量
• 通过设置 slow_query_log 系统变量的值来控制慢查询日志的 开启 1 与 禁⽤ 0 ，如果要为日志指定自定义的路径或文件名可以使用 slow_query_log_file 系统变量
• ⽰例，以选项文件中的配置为例：

  • 将⼀般查询日志写入日志表和日志文件
    

  • 仅将一般查询日志和慢查询日志写入日志表

  • 仅将慢查询日志写入日志文件

• 将⼀般查询日志和慢查询日志写入日志文件，并指定自定义的日志路径

2）运行时的日志控制

• 在运行时修改 log_output 的值，以更改日志的输出形式，通过语句控制
• 语法：SET [GLOBAL|SESSION] variable_name=value
• general_log[={0|1}] 和 slow_query_log[={0|1}] 可以表示启用和禁用⼀般查询日志和慢查询日志
• general_log_file 和 slow_query_log_file 表示通用查询日志和慢查询日志文件名称
• 只对当前会话禁用或启用⼀般查询日志记录，将 SESSION 作用域的 sql_log_off 变量设置为 ON 或 OFF

3）使用日志表优点

• 可以通过 SQL 语句的条件查询过滤日志内容，从而选择满足特定条件的日志记录。比如，某个客户
  端的日志；
• 可以通过客户端程序连接到服务器并查询表中的日志信息，无需登录服务器主机访问文件系统。
• 日志记录具有标准格式，可看日志表的结构，可以使用以下语句
  

2.⼀般查询日志

• 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语句的顺序将语句写⼊查询日志，这个顺序可能与语句执行的顺序不同。
• 表结构如下

mysql> SHOW CREATE TABLE mysql.general_log;
CREATE TABLE `general_log` (
# 发⽣时间
`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`thread_id` bigint unsigned NOT NULL,
`server_id` int unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='General log'

1）⼀般查询日志示例

• 查询表中的日志内容

# 查看⽇志
SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time,
user_host,
thread_id,
server_id,
command_type,
CAST(argument AS CHAR) AS query
FROM mysql.general_log;

• 查看文件中的日志内容

root@guangchen-002:/var/lib/mysql# cat general.log

3.慢查询日志

• 慢查询日志由执行时间超过系统变量 long_query_time 指定的秒数的 SQL 语句组成，并且检查的行数大于系统变量 min_examined_row_limit 指定值。被记录的慢查询需要进 行优化，可以使用mysqldumpslow客⼾端程序对慢日志进行分析汇总。
• 获取初始锁的时间不计 入执行时间，mysqld在执行完SQL语句并释放所有锁后才将符合条件的语句写入慢速查询日志，因此日志顺序可能与执行顺序不同。

1）慢查询日志参数

• long_query_time 的默认值是10，最小值是0；
• 默认情况下，不记录管理语句，也不记录不使用索引的查询
• 默认为关闭(不开启)，要启用慢查询日志可以使用：请使用 --slow_query_log[={0|1}] .
• 默认日志文件名为 host_name-slow.log ，可以使用slow_query_log_file=file_name 修改；
• 使用 --log-short-format 选项，以简要格式记录慢查询日志
• 要记录管理语句，启用 log_slow_admin_statements 系统变量。管理语句包括 ALTERTABLE 、 ANALYZE TABLE 、 CHECK TABLE 、 CREATE INDEX 、 DROP INDEX 、 OPTIMIZE TABLE 和 REPAIR TABLE 。
• 要记录不使用索引的查询，启用 log_queries_not_using_indexes 系统变量。当记录不使
  用索引的查询时，日志会快速增长，通过设置系统变量log_throttle_queries_not_using_indexes 限制每分钟写入慢查询日志同类查询的数量，默认值是0，表示无限制。

2 ）慢查询日志内容

FILE格式

• 如果启用慢查询日志并将 FILE 作为输出目标，每条语句前面都用一行来表示日志的字段，该行以 # 字符开头并包含以下内容：

  • Query_time: ：SQL语句的执行时间，单位秒
  • Lock_time: 获取锁的时间，单位秒
  • Rows_sent: 发送到客户端的行数
  • Rows_examined: 服务器检查的行数

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

  • Thread_id: 线程标识符
  • Errno: 错误码，没有发生错误则为 0
  • Killed: 如果语句被终止，用错误码表示原因，如果语句正常终止则为 0。
  • Bytes_received: 接收到SQL语句的Bytes值。
  • Bytes_sent: 返回给客户端的Byte值。
  • Read_first: 索引中第一个条目被读取的次数，如果这个值很搞，表明服务器正在执行大量完整索引扫描
  • Read_last: 读取索引中最后⼀个键的请求数，使用 ORDER BY 时关注
  • Read_key: 基于索引读取一行数据的请求数。如果这个值很搞，表明表为当前查询建立了正确的索引
  • Read_next: 按索引排序读取下一行的请求数，查询具有范围约束的索引列，或者进行索引
    扫描，此值将递增。
  • Read_prev: 按索引排序读取前一行的请求数。主要 用于优化ORDER BYRDESC。
  • Read_rnd: 基于固定位置读取一行的请求数。这个值很高表示，正在执行大量需要对结果进
    ⾏排序的查询，可能有很多查询进行了全表扫描整，或者没有正确使⽤索引的连接。
  • Read_rnd_next: 读取数据文件中下一行的请求数。如果进行大量的表扫描，这个值会很
    ⾼。通常，表示表没有建立正确地索引，或者查询没有利用索引。
  • Sort_merge_passes: 排序算法完成的归并次数，如果这个值很大，考虑增加sort_buffer_size 系统变量的值。
  • Sort_range_count: 使用范围进行排序的次数。
  • Sort_rows: 排序的行数。
  • Sort_scan_count: 通过扫描表完成的排序数。
  • Created_tmp_disk_tables: 服务器在执行语句时创建内部磁盘临时表的数量。
  • Created_tmp_tables: 服务器在执行语句时创建的内部临时表的数量。
  • Start: 执行SQL语句开始时间
  • End: 执行SQL语句结束时间

TABLE格式
慢查询日志表的表结构如下：

mysql> SHOW CREATE TABLE mysql.slow_log;
CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int NOT NULL,
`rows_examined` int NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int NOT NULL,
`insert_id` int NOT NULL,
`server_id` int unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='Slow log'

4.错误日志

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

1）配置错误日志

错误日志输出的位置，可以是控制台或指定文件，"控制台"表示 stderr 标准错误输出。

Windows 的默认错误日志路径
在Windows系统中，mysqld使用 --log-error 和 --console 选项来确定默认的错误日志目标是控制台还是文件，规则如下：

• 如果指定了 --console 选项，默认在控制台输出错误日志，如果 --console 和 --logerror 同时指定，则 --console 优先级更高，并且 --log-error 将失效。
• 如果没有指定 --log-error 或者没有指定具体的文件名，默认在数据目录中生成名为 host_name.err 的日志文件。
• 可以通过指定绝对路径，来更改默认的日志位置。
  

2）错误日志中事件的字段

核心错误事件字段

time ： 件时间戳，精度为微秒；
msg ： 事件消息字符串；
prio ： 事件优先级，包括 System event - 系统(0)、 Error event - 错误(1)、 Warningevent - 警告(2)或 Note/information event - 通知/提示事件(3)，值越小优先级越高；
err_code ： 事件错误代码；
err_symbol ： 以字符串形式表示的事件错误符，例如 ‘ER_DUP_KEY’ ；
SQL_state ： 事件 SQLSTATE 值，与 err_symbol 对应，例如 ‘ER_DUP_KEY’ 对应的SQLSTATE为23000
subsystem ： 事件发生的子系统。可能的值： InnoDB (InnoDB存储引擎）、 Repl (复制子系
统)、 Server (其他)。

可选错误事件字段
OS_errno ：操作系统错误号；
OS_errmsg ：操作系统错误消息；
label ：与值对应的 prio 描述 ；
user ：客户端⽤户；
host ：客户端主机；
thread ：产生错误事件 的线程的 ID；
query_id ：查询 ID。

可以通过以下SQL查看已定义的错误类型

错误代码参考：Error Summary Tables
Global Error Message Reference

示例：

	                 时间                   优先级    错误码     子系统   日志详情

3）刷新错误日志文件和重命名

• 如果使用 FLUSH ERROR LOGS 、 FLUSH LOGS 语句或 mysqladmin flush-logs 命令刷新错误日志，服务器会将正在写入的任何错误日志文件关闭并重新打开。
• 如果要手动重命名错误日志文件，可以在重命名操作之后执行刷新操作，服务器会以原文件名生成
  ⼀个新的错误日志文件，例如日志文件名为 host_name.err ，可以按以下步骤操作：
  

5.⼆进制日志

1）介绍

• 二进制日志包含数据库更改的"事件"，不会记录 SELECT 和 SHOW ，例如：记录表的创建操作或
  表数据的更改，二进制日志还包含每个语句更新数据时花费的时间信息，启动二进制日志，对服务
  器性能稍微有些影响；
• 除了基于行的日志模式，它还包含可能进行更改数据的语句事件，例如 DELETE 操作没有匹配到
  查找到的行；
• ⼆进制日志的作用：
  • 主从节点数据复制：从节点服务器读取主节点服务器上的二进制日志文件，并根据二进制日志
    中记录的事件在从节点上执行相同的操作，保证主从节点服务器上数据一致，实现数据复制功能。
  • 数据恢复：从某个时间点恢复备份数据后，将重新执行备份时间点之后记录在二进制日志中的
    事件。这些事件使数据库从备份点更新到当前最新状态。
• ⼆进制日志的语句中如果涉及用户的密码，则由服务器进行加密，不会以纯文本形式出现。

2）选项和变量

• 查看二进制日志相关的系统变量

• 查看二进制日志相关的状态变量
  

• 默认情况下启用二进制日志， log_bin 系统变量为 ON ；

• 禁用二进制日志，可以指定 –skip-log-bin 或 –disable-log-bin 选项。如果同时指定了 –log-bin 则后指定的选项优先；

• 选项 –log-bin[=base_name] 用于指定二进制日志文件的基本名称，如果不指定 –logbin 选项，默认基本名称为 binlog ，建议为二进制日志指定一个基本名；

• 二进制日志文件名是由基本名+数字扩展名组成的，服务器每次创建一个新的日志文件时，数字扩
  展名都会增加，从而保证有序的文件系列，发⽣以下事件时，服务器都会在创建一个新的日志文件:
  ◦ 服务器已启动或重新启动
  ◦ 服务器刷新日志
  ◦ 当前日志文件的大小达到 max_binlog_size (单个日志文件的最大字节数，最小值 4096 字节，最大值和默认值 1GB) 。

二进制日志文件大小可能会超出 max_binlog_size 设定的值，因为二进制日志在记录事务时，会完整的记录整个事务，不存在把一个事务拆分的情况，如果遇到一个大事务时，即使记录整个事务会超过日志大小限制，也会保证事务的完整性

• mysqld还会创建⼀个包含二进制日志文件名的日志索引文件，默认情况下，这与二进制日志文件
  具有相同的基本名称，扩展名为 .index . 可以使用选项 --log-bin-index[=file_name]修改索引文件名;
• ⼆进制日志文件和索引文件的默认位置是数据目录。可以使用 --log-bin[=file_name] 选项指定自定义路径， file_name 格式 = 绝对路径+基本名。 --log-bin 对应的系统变量是log_bin_basename ；
• MySQL 5.7 中，启用二进制日志必须指定服务器 ID，对应 server_id 选项，否则服务器将无法启动。在 MySQL 8.0 中， server_id 系统变量默认设置为 1，在集群环境中，每台MySQL服务器必须有唯一的 server_id ；
• ⼆进制日志记录事件支持三种格式类型：基于行的日志记录、基于语句的日志记录和混合日志记
  录。
• ⼆进制日志记录在语句或事务完成之后，释放锁或在提交完成之前进行。这样做是为了确保按照提
  交顺序记录日志；
• 在⼀个未提交的事务中，对支持事务的表(如InnoDB表)的更改都会被缓存(UPDATE，DELETE或
  INSERT)，直到服务器收到 COMMIT 语句，mysqld在执行 COMMIT 之前将整个事务写入二进制日志；
• 如果事务回滚，则在整个事务中记录一个 ROLLBACK 语句，但是对非事务性表(如MyISAM表)的修改不能回滚，所以这些修改将被复制到从节点；
• 对非事务表的更新在执行后立即存储在二进制日志中；
• 当处理事务的线程启动时，它会分配一个大小为 binlog_cache_size 的缓冲区来缓存语句。如果语句 ⼤⼩⼤于 缓冲区的值，线程则打开一个临时文件来存储事务，临时文件在线程结束时删除；
• Binlog_cache_use 状态变量显示使用该缓冲区(可能还有临时文件)存储事务的数量；Binlog_cache_disk_use 状态变量显示有多少事务实际上使用了临时文件。结合这两个变量可以把 binlog_cache_size 调优到⼀个足够大的值，从而避免使用临时文件；
• 系统变量 max_binlog_cache_size （默认值和最大值都是 4GB，最小值为 4096）用于限制缓存区大小，如果事务语句大于这个值指定的字节数，事务将会失败并回滚；
• 如果使用基于行的日志记录方式， 为了保证日志的准确性， CREATE … SELECT 或 INSERT … SELECT 语句的并发插⼊将转换为普通插入；如果使用基于语句的日志记录方式，则将原始语句写⼊日志。
• 由于服务器崩溃或其他原因，导致对二进制日志文件无法进行写入、刷新或者同步到磁盘。那么主
  从节点上的日志就会出现不⼀致，当遇到这种问题时，可以通过系统变量binloglog_error_action 控制处理方式：
  • 默认值 ABORT_SERVER ，服务器停止二进制日志记录并关闭，排查完问题并重启后，服务器按意外停止执行恢复操作；
  • IGNORE_ERROR 表示，服务器继续进行当前的事务并记录错误，然后停止日志记录，排查问题后，需要确认启用 log_bin ，然后再次启动服务器，对日志要求不高的场景可以设置此值，不推荐在集群环境使用；
• 默认情况下， sync_binlog=1 ，表示每个事务在写入缓存后立即同步到磁盘，也可以设置为其他值，比如 sync_binlog=N 表示N次事务提交到缓存之后再同步到磁盘，如果sync_binlog=0 则MySQL不控制同步磁盘的频率，完全由操作系统控制。需要注意：如果当sync_binlog 的值设置为 0 或 N 那么当服务器崩溃时，缓上存中的有些日志不能同步到磁盘，可能造成一些更改丢失，所以 sync_binlog=1 是最安全的，但同时效率也是最低的。
• 可以使用 RESET MASTER 语句删除所有二进制日志文件，
• 或者使用 PURGE binary LOGS 删除一部分二进制日志文件，具体演示：

术语 “⼆进制日志文件” 通常表示包含单独编号的日志文件。术语 “二进制日志” 表示 所有⼆进制⽇志⽂件和索引⽂件

3）查看二进制日志

使用客户端工具mysqlbinlog查看
通过SQL语句查看

4）二进制日志格式

记录二进制日志时使用的格式有以下几种：

• 基于语句的日志格式，最初MySQL 是基于 SQL 语句复制实现主从节点同步，通过指定选项 –
binlog-format=STATEMENT 使用此格式 。
• 基于行的日志格式(默认)中，主节点将事件写入二进制日志，表示各个表的行受到的影响，可以通
过指定选项 --binlog-format=ROW 使用此格式 。
• 混合日志记录格式，默认情况下使用基于语句的日志记录，如果MySQL认为基于语句的格式不能保证主从复制过程中的数据安全时，会自动切换到基于行的日志格式，比如主节点在语句中用了UUID() 函数，那么日志文件中记录的是UUID生成的真实值而不是直接使用原始的SQL语句，使用混合日志格式中以指定选项 --binlog-format=MIXED 。

设置二进制日志格式–binlog-format=[STATEMENT|ROW|MIXED]

基于语句与基于行的区别：

6.服务器日志维护

MySQL 服务器可以创建多种不同的日志文件来帮助我们查看服务器的活动。但是必须定期清理这些文件，以免日志占用过多的磁盘空间。在启用日志的情况下，通常希望备份和删除旧的日志文件，并把日志写到新文件。

• 默认二进制日志的过期时间为30天，过期后将自动删除，要指定自定义过期时间，可以使用系统变
  量 binlog_expire_logs_seconds=N 单位为秒，在下一次启动服务器和刷新日志时删除过期日志文件；

• 强制使用新的日志文件可以手动刷新日志，当执行 FLUSH LOGS 语句或 mysqladmin flushlogs 、 mysqladmin refresh 、 mysqldump --flush-logs 、 mysqldump --master-data 命令时，会发生日志刷新。此外当二进制日志文件大小达到 max_binlog_size 系统变量指定的值时，服务器会自动刷新二进制日志。

• FLUSH LOGS 支持可选的修饰符以启用个别日志的选择性刷新：

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

• 要在运行时重命名一般查询日志或慢查日志

  • ⾸先连接到服务器并禁用日志：
  • 在禁用日志的情况下，重命名日志文件，例如用 mv 命令从命令行执行重命名操作
  • 再次启用日志：
  • 这种方法适用于任何平台且不需要重启服务器

7.配置日志输出位置

四.mysql System Schema (mysql系统库)

Mysql Schema是一个系统库，表中存储了MySQL服务器运行时所需的信息。广义上，mysql schema包含存储数据库对象元数据的数据字典和用于其他操作目的的系统表。数据字典表和系统表位于数据目录下一个名为 mysql.ibd 的表空间文件中，使用的是InnoDB存储引擎。

1.数据字典

• MySQL 的数据字典，用来存储有关数据库对象自身的信息，不可以随意修改，否则可能造成服务
  器无法运行：以下列出几个常见的数据字典表：

  • character_sets ：有关可用字符集的信息◦ check_constraints ：有关表上定义的 CHECK 约束的信息
  • collations ：每个字符集的排序规则信息
  • column_type_elements ：列类型的信息
  • columns ：有关表中列的信息
  • indexes : 有关表索引的信息
  • tables ：有关数据库中表的信息
  • tablespace_files ：有关表空间使用的文件信息
  • tablespaces ：有关活动表空间的信息
  • triggers ：有关触发器的信息

• 数据字典表是受保护的，只能在调试版本中访问，在发行版中没有权限访问，如果在发行版本中查
  询表中的数据，出提示拒绝访问：

• INFORMATION_SCHEMA 实现了某些数据字典的视图，可以通过视图查看某些数据字典的内容：

2.系统表

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

1）权限授予系统表

包含有关用户帐户及帐户拥有的权限授权信息，主要的表有:

• user ：用户帐户、全局权限和其他列。
• global_grants ：为用户分配的动态全局权限；
• db ：数据库级权限。
• tables_priv ：表级权限。
• columns_priv : 列级权限。
• procs_priv : 存储过程和函数权限。
• proxies_priv : 代理用户权限。
• default_roles ：列出了用户连接和认证后要激活的默认角色。
• role_edges ： user 表的关联表，区分 user 表中某一行记录是帐户还是角色
• password_history ：密码更改的信息

2）对象信息系统表

包含有关组件、可加载的服务器插件和函数的信息：
component ：使⽤ INSTALL COMPONENT 安装的服务器组件，表中列出了在服务器启动期间安装
的组件。
func : 使⽤ CREATE FUNCTION 安装的可加载函数，表中列出了在服务器启动期间加载的函数。
plugin ：使⽤ INSTALL PLUGIN 安装的服务器插件，表中列出了在服务器启动期间安装的插件。

3）日志系统表

服务器使用日志系统表进行日志记录：

• general_log： 一般查询日志表。
• slow_log ： 慢查询日志表。

⽇志表使⽤ CSV 存储引擎。

4）服务器端帮助系统表

包含服务器端帮助信息：

• help_category ： 有关帮助类别的信息。
• help_keyword ： 与帮助主题关联的关键字。
• help_relation ： 帮助关键字和主题之间的映射。
• help_topic ： 帮助主题内容。

5）时区系统表

包含时区信息：

• time_zone ： 时区 ID 以及是否使用闰秒。
• time_zone_leap_second ： 发生闰秒时如何修正。
• time_zone_name : 时区 ID 和名称之间的映射。
• time_zone_transition , time_zone_transition_type : 时区说明及偏移量。

6）复制系统表

服务器使用以下系统表来支持复制，

• gtid_executed ： 用于存储 GTID 的值。
• ndb_binlog_index ： 用于NDB Cluster 复制的⼆进制日志信息。只有在支持 NDBCLUSTER 的服务器才会创建此表。
• slave_master_info，slave_relay_log_info ， slave_worker_info : 用于存储从节点服务器上的复制信息。

以上几张表都使用 InnoDB 存储引擎。

7）优化器系统表

这些系统表供优化器使用：

• innodb_index_stats， innodb_table_stats ： 用于 InnoDB 的持久优化器统计信息。
• server_cost ， engine_cost ： 优化器成本模型使用的表，包含查询期间发生的操作成本估算信息。 server_cost 包含服务器操作的优化器成本估算。 engine_cost 包含对特定存储引擎操作的估计。

8）杂项系统表

• audit_log_filter ， audit_log_user： 如果安装了 MySQL Enterprise Audit，存储审计日志过滤器定义和用户帐户。
• firewall_group_allowlist ， firewall_groups ， firewall_memebership ，firewall_users ，firewall_whitelist ： 如果安装了 MySQL Enterprise Firewall，这些存储表防火墙的使用信息。
• servers ： 由 FEDERATED 存储引擎使用。
• innodb_dynamic_metadata ： 由 InnoDB 存储引擎用来存储快速变化的表元数据，例如自动递增计数器值和索引树损坏标志。

五.在一台机器上运行多个MySQL实例

• 有时候我们希望在一台机器上运行多个 MySQL 实例，比如在开发环境测试新的MySQL版本，同时
  又要保证原有的版本不受影响；MySQL 允许在一台机器上安装不同版本的发行版，比如同时运行
  5.7和8.0版本，只是要做一些相应的配置。
• 为了降低配置难度，本节将在Windows系统中使用安装器演示配置多个MySQL实例的方法。

1.使用MySQL Installer安装MySQL实例

下载不同版本的MySQL Installer，并完成安装，安装过程中需要注意以下几点：

• 为不同的版本指定不同的安装目录，安装过程和安装完成后如下所示：

• 为不同的MySQL版本指定不同的服务名，安装完成后可以在服务列表中查看：

• 安装完成后会在C:\ProgramData\MySQL下生成对应版本的数据目录，如下所示：

2.配置不同的版本选项文件

不同的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）指定基本目录

• 使用选项 --basedir=dir_name 为每个实例指定基本目录，通常指定为安装目录，不同的实例会自动使用不同的数据目录、日志文件和PID文件，这些文件的默认值都是相对于基本目录的，当然也可以自定义这些文件：

2）设置数据目录

• 使用安装器安装成功后不同的版本的数据目录已经自动生成，如果要自定义数据目录，那么也需要
  按不同的版本分别设置别其他不同的目录

• 在MySQL服务正常停止的状态下，复制C:\ProgramData\MySQL\MySQL Server x.x\Data目录到目标位置即可，如下所示：

• 在对应版本的 my.ini 文件中指定自定义数据目录，通过指定 --datadir=dir_name 实现

注意：永远不要让两台服务器对同⼀个数据库执行数据更新

3）设置端口号

通过选项 --port 设置 TCP/IP 连接的端口号。如果主机有多个网络地址，通过设置 bind_address 系统变量指定不同侦听地址

4）设置日志目录

默认情况下如果开启一般查询日志，慢查询日志，错误日志，二进制日志，会在默认的数据目录下生
成日志文件，也可以自定义目录：

5）其他设置

还有一些其他设置可以通过选项文件指定，比如服务器Id，临时文件目录等等：

3.运行多个 MySQL 实例

• 在计算机管理程序中，找到相应的服务，点击启动

• 查看启动结果，状态显示已启动表示服务启动成功

• 查看端口

4.在多服务器环境中使用客户端程序

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

六.面

介绍一下你知道的MySQL服务器配置文件的选项:

这个问题主要考察选项的使用，回答这个问题可以先总体描述选项⽂件的使用场景，再介绍几个常见
的选项即可

1. 选项文件主要作用是设置MySQL服务常用的系统变量初始值，以便服务器启动时自动读取；
2. 选项文件中可以用 [程序名] 的形式分别为不同的程序指定具体选项的值，也可以用[client] 、 [server] 的形式分别指定客户端程序和服务器程序公共配置。
3. 常见的选项有，客户端和服务器使用的字符集，客户端默认的用户名和密码，服务器的排序规则，
   服务器的端⼝号，自定义数据目录的路径，日志文件的路径，服务器允许的最大连接数，还有⼀些
   关于存储引擎的选项也可以用于性能调优，比如关于INNODB的选项，等等。