第 7 章MySQL服务器管理
服务器配置验证
MySQL提供了一个名为--validate-config的选项，该选项允许用户在正常操作模式之外检查MySQL服务器的启动配置中是否存在问题。使用此选项时，如果配置没有问题，服务器将终止运行并返回退出代码0；如果发现配置错误，服务器将显示诊断消息并返回退出代码1。
例如，如果你运行一个包含未知选项的命令，如--no-such-option，服务器会返回一个错误消息，并返回退出代码1：
mysqld --validate-config --no-such-option
如果你的命令产生了警告，例如关于未识别的布尔值，这些警告会根据log_error_verbosity的值显示，但不会阻止验证过程，并且退出代码将是0：
mysqld --validate-config --log_error_verbosity=2 --read-only=s --transaction_read_only=s
如果命令同时产生警告和错误，那么错误消息和警告都会显示，并且退出代码为1：
mysqld --validate-config --log_error_verbosity=2 --no-such-option --read-only=s --transaction_read_only=s
--validate-config选项仅检查服务器能够进行的配置，而不会初始化存储引擎或其他插件和组件。因此，与这些未初始化的子系统相关的配置选项不会被验证。
这个选项特别适用于升级后的场景，可以检查旧版服务器使用的任何选项是否在新版本中已被弃用或标记为过时。例如，假设你从MySQL 5.7升级到8.4，并且旧配置中包含已删除的系统变量，使用--validate-config将会显示这些变化。
可以使用--defaults-file选项与--validate-config结合使用，以便只验证特定文件中的选项。但是，--defaults-file必须是命令行上的第一个选项，否则会产生错误消息。
服务器 SQL 模式
MySQL服务器可以在不同的SQL模式下运行，并且可以应用这些模式 对于不同的客户端不同，具体取决于sql_mode系统变量的值。DBA 可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序都可以设置其会话SQL模式根据自己的要求。
模式会影响MySQL支持的SQL语法和数据验证检查的执行情况。这使得在不同的环境中使用MySQL变得更加容易，并将MySQL与其他数据库服务器一起使用。
要设置SQL模式，可以使用以下方法：
-
在服务器启动时设置SQL模式：在命令行中使用 --sql-mode=“modes”选项，或在选项文件中使用 sql-mode=“modes”，例如（Unix操作系统）或（Windows）。modes是不同模式的列表，用逗号分隔。
-
要在运行时更改SQL模式，请使用SET语句：
- SET GLOBAL sql_mode = ‘modes’;
- SET SESSION sql_mode = ‘modes’;
设置变量需要SYSTEM_VARIABLES_ADMIN权限（或已弃用的SUPER权限）并影响连接的所有客户端的操作从那时起。设置变量仅影响当前会话。每个客户端都可以更改其会话值sql_mode为任何值。GLOBAL SESSION
-
要确定当前全局或会话sql_mode设置，请选择其价值：
- SELECT @@GLOBAL.sql_mode;
- SELECT @@SESSION.sql_mode;
MySQL 8支持多种SQL模式，以下是一些常见的SQL模式：
- ANSI_QUOTES：启用ANSI引用符（双引号）而不是MySQL的反引号。
- PIPES_AS_CONCAT：将管道符（|）视为字符串连接操作符，而不是OR运算符。
- IGNORE_SPACE：忽略SQL语句中的空格。
- NO_AUTO_CREATE_USER：禁止自动创建用户账户。
- NO_BACKSLASH_ESCAPES：禁用反斜杠转义字符。
- STRICT_TRANS_TABLES：在事务中遇到无效或丢失的值时，使事务回滚。
- SQL_MODE=‘STRICT_ALL_TABLES’：对所有表执行严格模式检查。
- NO_ZERO_IN_DATE：不允许日期中的月份和天数为零。
- NO_ZERO_DATE：不允许日期为零值。
- INVALID_DATES：将无效日期视为NULL。
- ERROR_FOR_DIVISION_BY_ZERO：除以零时产生错误。
- NO_ENGINE_SUBSTITUTION：不允许在查询中使用未定义的存储引擎。
- PERSIST：持久化设置，即使重启服务器也不会丢失。
要查看当前MySQL实例的所有SQL模式，可以使用以下查询：
SELECT @@GLOBAL.sql_mode;
或者查看特定会话的SQL模式：
SELECT @@SESSION.sql_mode;
Mysql服务器日志
选择常规查询日志和慢速查询日志输出目标
要将常规查询日志条目写入日志表，并且日志文件，可以使用以下命令：
mysqld --log_output=TABLE,FILE --general_log
仅将常规查询日志条目和慢速查询日志条目写入日志表，可以使用以下命令：
mysqld --log_output=TABLE --general_log --slow_query_log
要仅将慢速查询日志条目写入日志文件，可以使用以下命令：
mysqld --log_output=FILE --slow_query_log
在运行时，可以通过以下方式启用或禁用常规查询日志和慢速查询日志：
SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';
或者
SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';
错误日志
错误日志文件刷新和重命名的步骤如下：
- 首先，手动重命名错误日志文件。例如，如果错误日志文件名为
host_name.err，可以使用以下命令重命名文件并创建一个新的文件：
mv host_name.err host_name.err-old
在Windows上，使用rename而不是mv。
-
然后，使用
mysqladmin flush-logs error命令刷新错误日志。这将关闭并重新打开任何正在写入的错误日志文件。 -
最后，将重命名后的错误日志文件移动到备份目录。例如：
mv host_name.err-old backup-directory
如果在刷新错误日志时服务器未写入命名错误日志文件，则无需重命名日志文件。在这种情况下，只需执行第2步即可刷新错误日志。
二进制日志
二进制日志包含对数据库表的更改描述，例如表创建操作或数据修改。二进制日志还包括可能已经更改的语句（例如，没有任何匹配行的DELETE），除非使用基于行的日志记录。二进制日志还包含有关每个语句花费更新数据的时间的信息。
二进制日志有两个重要用途：
对于复制，源服务器上的二进制日志提供要发送到副本的数据更改的记录。源将其二进制日志中包含的信息发送到它的副本，这些副本再现这些事务以进行对源所做的相同数据更改。请参见第19.2节 “复制实现”。
某些数据恢复操作需要使用二进制文件日志。还原备份后，将备份后记录的二进制日志是重新执行。这些事件使数据库从备份点。请参见第9.5节 “时间点（增量）恢复”。
二进制日志不用于不修改数据的语句（如 SELECT 或 SHOW）。自记录所有语句（例如，识别问题查询），使用常规查询日志。请参见第7.4.3节 “常规查询日志”。
运行启用二进制日志记录的服务器可以稍微慢一点。但是，二进制登录的好处使您能够设置复制和还原操作通常超过这种轻微的性能下降。
二进制日志可灵活应对意外停止。仅完成的事件或事务将被记录或回读。
写入二进制日志的语句中的密码由服务器不会以纯文本形式出现。另外请参见第8.1.2.3节 “密码和日志记录”。
MySQL二进制日志文件和中继日志文件可以加密，帮助保护这些文件和潜在的敏感数据包含在其中，以免被外部攻击者滥用，也来自操作系统用户未经授权的查看它们的存储位置。您可以通过在MySQL服务器上启用加密将binlog_encryption系统变量设置为。欲了解更多信息，请参见第19.3.2节 “加密二进制日志文件和中继日志文件”。
慢速查询日志
慢速查询日志是MySQL数据库中用于记录执行时间较长的SQL语句的一种日志。这些语句通常需要优化以提高性能。慢速查询日志可以帮助数据库管理员找到需要优化的查询，从而提高整个数据库系统的性能。
慢速查询日志的参数包括：
- long_query_time：指定记录慢查询的时间阈值，单位为秒。默认值为10秒。
- min_examined_row_limit：指定记录慢查询所需的最小检查行数。默认值为0，表示不限制。
- log_slow_admin_statements：是否记录管理语句，如ALTER TABLE、ANALYZE TABLE等。默认值为OFF。
- log_queries_not_using_indexes：是否记录未使用索引进行查找的查询。默认值为OFF。
- slow_query_log：是否启用慢查询日志。默认值为OFF。
- slow_query_log_file：指定慢查询日志文件的名称。默认值为host_name-slow.log。
- log_output：指定慢查询日志和常规查询日志的输出目标。默认值为FILE。
- log_timestamps：控制消息中时间戳的时区。默认值为UTC。
- log_slow_replica_statements：是否在副本上记录复制的查询。默认值为OFF。
- log_slow_extra：是否记录额外的信息，如线程ID、错误号等。默认值为OFF。
要处理慢速查询日志文件并总结其内容，可以使用mysqldumpslow工具。例如，要查看慢速查询日志文件中的前10条最慢的查询，可以运行以下命令：
mysqldumpslow -s t -t 10 /path/to/your/slow-query.log
这将按照查询时间（t）对慢查询日志进行排序，并显示前10条最慢的查询。
第 8 章安全
访问控制和帐户管理
授权表概述
这些数据库表包含 grant 信息：mysql
用户： 用户帐户、静态全局权限等 nonprivilege 列。
global_grants： 动态全局权限。
分贝： 数据库级权限。
tables_priv： 表级权限。
columns_priv： 列级权限。
procs_priv： 存储过程和函数权限。
proxies_priv： 代理用户权限。
default_roles： 默认用户角色。
role_edges： 角色子图的边缘。
password_history： 密码更改历史记录。
有关静态和动态之间差异的信息 全局权限，请参阅静态权限与动态权限。
在 MySQL 8.4 中，授权表使用存储引擎并且是事务性的。 在 MySQL 8.4 之前，授权表使用存储引擎，并且是 非交易性。授权表存储引擎的此更改 启用对 帐户管理语句，例如 CREATE USER 或 GRANT。以前，一个 命名多个用户的帐户管理语句可以 对某些用户来说成功，对其他用户来说失败。现在，每个语句 是事务性的，并且对所有指定用户都成功，或者 回滚，如果发生任何错误，则不起作用。InnoDBMyISAM
每个授权表都包含范围列和权限列：
范围列确定表中每一行的作用域; 即应用该行的上下文。例如 包含 和 值的表行 并适用于身份验证 客户端从主机到服务器建立的连接，该客户端指定 用户名 。同样，当从主机连接以访问数据库时，将应用具有 、 和 列值的表行。和 表包含作用域列 指示每个表或表/列组合 行适用。作用域列 指示每行应用的存储例程。userHostUser’h1.example.net’‘bob’h1.example.netbobdbHostUserDb’h1.example.net’‘bob’'reports’bobh1.example.netreportstables_privcolumns_privprocs_priv
权限列指示表行的权限 拨款;也就是说，它允许哪些操作 执行。服务器将 各种授权表，形成对 用户的权限。第 8.2.7 节 “访问控制，第 2 阶段：请求验证”， 描述了这方面的规则。
此外，授权表可能包含用于目的的列 范围或权限评估除外。
服务器按以下方式使用授权表：
表范围列确定 是拒绝还是允许传入连接。为 允许的连接，则表中授予的任何权限都表示用户的静态 全局权限。此表中授予的任何权限 应用于服务器上的所有数据库。useruser
谨慎
因为任何静态全局权限都被视为 所有数据库的特权，任何静态全局特权 使用户能够使用 SHOW DATABASES 或按 检查 SCHEMATA 表 的 ，数据库除外 在数据库级别受到部分限制 撤销。INFORMATION_SCHEMA
下表列出了当前 为用户帐户分配动态全局权限。 对于每一行，范围列确定哪个用户具有 在“权限”列中命名的权限。global_grants
表范围列确定 哪些用户可以从哪些主机访问哪些数据库。这 权限列确定允许的操作。一个 在数据库级别授予的特权适用于 数据库和数据库中的所有对象，例如表 和存储的程序。db
和表与表类似，但粒度更细：它们 在表和列级别应用，而不是在 数据库级别。在表级别授予的特权 适用于表及其所有列。特权 在列级别授予仅适用于特定的 列。tables_privcolumns_privdb
该表适用于存储的 例程（存储过程和函数）。特权 在常规级别授予仅适用于单个 过程或功能。procs_priv
该表指出了哪个 用户可以充当其他用户的代理，以及用户是否 可以授予 PROXY 权限 给其他用户。proxies_priv
和表包含信息 关于角色关系。default_rolesrole_edges
该表保留了 以前选择的密码以启用限制 密码重用。请参见第 8.2.15 节 “密码管理”。password_history
服务器将授权表的内容读入内存 当它开始时。您可以通过发出以下命令来告诉它重新加载表 FLUSH PRIVILEGES 语句或 执行 mysqladmin flush-privileges 或 mysqladmin reload 命令。对 授权表如第 8.2.13 节 “权限更改生效时”中所述生效。
修改帐户时，最好验证一下 您的更改具有预期的效果。检查权限 对于给定帐户，请使用 SHOW GRANTS 声明。例如，要确定 授予具有用户名和 和 的主机名值 ，使用以下语句：bobpc84.example.com
SHOW GRANTS FOR ‘bob’@‘pc84.example.com’;
要显示帐户的非特权属性，请使用 SHOW CREATE USER：
SHOW CREATE USER ‘bob’@‘pc84.example.com’;
mysql加密连接
在MySQL中配置加密连接涉及几个步骤，包括服务器端和客户端的配置。以下是这些步骤的详细说明：
服务器端启动配置
- 启用
require_secure_transport系统变量: 这要求所有客户端使用加密连接进行通信。 - 指定证书和密钥文件:
ssl_ca: CA证书文件的路径。ssl_cert: 服务器公钥证书文件的路径。ssl_key: 服务器私钥文件的路径。
- 自动发现证书和密钥文件: 如果服务器在数据目录中找到名为
ca.pem,server-cert.pem, 和server-key.pem的有效证书和密钥文件，它将支持客户端的加密连接。 - 其他系统变量:
ssl_cipher: 允许用于连接加密的密码列表。ssl_crl: 包含证书吊销列表的文件的路径。tls_version和tls_ciphersuites: 允许的加密协议和密码套件。
客户端配置
- SSL模式选项:
--ssl-mode=PREFERRED: 默认设置，尝试建立加密连接，如果失败则回退到未加密连接。--ssl-mode=REQUIRED: 需要加密连接，如果无法建立则失败。--ssl-mode=DISABLED: 使用未加密连接。--ssl-mode=VERIFY_CA或--ssl-mode=VERIFY_IDENTITY: 需要加密连接，并验证服务器的CA证书和主机名。
- 指定证书和密钥文件:
--ssl-ca: CA证书文件的路径。--ssl-cert: 客户端公钥证书文件的路径。--ssl-key: 客户端私钥文件的路径。
- 其他选项:
--ssl-cipher: 允许用于连接加密的密码列表。--ssl-crl: 包含证书吊销列表的文件的路径。--tls-version,--tls-ciphersuites: 允许的加密协议和密码套件。
注意事项
- 使用自签名证书时，主机名身份验证不适用，因为自签名证书不包含服务器名称作为公用名值。
- 如果帐户有特定的加密要求，客户端可能需要指定额外的选项来建立加密连接。
通过正确配置这些参数，可以确保MySQL服务器和客户端之间的通信是安全的，使用加密连接来保护数据不被未授权访问。
第 9 章 备份和恢复
备份和恢复类型
物理备份和逻辑备份是两种不同的数据库备份方法。物理备份是指将数据库的物理文件（如数据文件、日志文件等）复制到另一个位置，而逻辑备份是指通过查询数据库结构信息和内容信息来生成备份文件。这两种备份方法各有优缺点，具体取决于用户的需求和数据库的特点。
物理备份的优点包括速度快、备份文件紧凑以及可以移植到具有相同或相似硬件特性的系统。然而，物理备份需要数据库处于离线状态，且只能针对整个数据库进行备份。此外，物理备份可能无法恢复某些特定类型的数据，如存储在内存中的数据。
逻辑备份的优点包括可以针对整个数据库或特定表进行备份，且可以在数据库运行时进行备份。逻辑备份还可以生成易于理解和编辑的文件，便于在不同环境中恢复数据。然而，逻辑备份的速度较慢，且输出文件通常比物理备份文件大。
在选择备份方法时，用户需要权衡备份速度、恢复能力、文件大小等因素。对于大型数据库或需要快速恢复的场景，物理备份可能是更好的选择；而对于小型数据库或需要灵活备份的场景，逻辑备份可能更合适。此外，用户还可以结合使用这两种备份方法，以满足不同场景的需求。
数据库备份方法
mysqldump 是一个用于备份 MySQL 数据库的命令行工具。它可以将数据库的内容导出为 SQL 语句，以便在需要时重新创建数据库。默认情况下，mysqldump 将信息写入 SQL 语句添加到标准输出。您可以将其输出保存到文件中，以便稍后使用。
以下是一些常用的 mysqldump 命令示例：
- 转储所有数据库到一个名为 dump.sql 的文件：
$> mysqldump --all-databases > dump.sql
- 仅转储特定数据库（例如 db1、db2 和 db3）到一个名为 dump.sql 的文件：
$> mysqldump --databases db1 db2 db3 > dump.sql
- 转储单个数据库（例如 test）到一个名为 dump.sql 的文件：
$> mysqldump --databases test > dump.sql
或者省略 --databases 选项：
$> mysqldump test > dump.sql
- 仅从数据库中转储特定表（例如 t1、t3 和 t7）：
$> mysqldump test t1 t3 t7 > dump.sql
在使用 mysqldump 时，还可以使用一些选项来控制输出内容，例如 --add-drop-database 选项会在每个数据库的转储输出之前添加一个 DROP DATABASE 语句，以确保在重新加载转储文件时删除每个数据库。
数据库恢复方法
要还原二进制日志中的数据，需要知道二进制日志文件的名称和位置。默认情况下，服务器会在数据目录中创建二进制日志文件，但可以使用 --log-bin 选项指定其他路径。查看所有二进制日志文件的列表，可以使用以下命令：
mysql> SHOW BINARY LOGS;
要确定当前二进制日志文件的名称，可以使用以下命令：
mysql> SHOW BINARY LOG STATUS;
mysqlbinlog 实用程序可以将二进制日志文件中的事件从二进制格式转换为文本格式，以便查看或应用。根据事件的时间或位置选择二进制日志部分，可以使用 mysqlbinlog 的选项。
应用二进制日志中的事件会导致数据恢复。这使您可以在给定的时间跨度内恢复数据更改。要将二进制日志文件中的事件应用于数据库，可以使用以下命令：
$> mysqlbinlog binlog_files | mysql -u root -p
如果二进制日志文件已加密，可以使用 --read-from-remote-server 选项从服务器读取它们：
$> mysqlbinlog --read-from-remote-server --host=host_name --port=3306 --user=root --password --ssl-mode=required binlog_files | mysql -u root -p
当您需要确定事件时间或位置以执行事件时，可以将 mysqlbinlog 输出发送到寻呼程序：
$> mysqlbinlog binlog_files | more
或者将输出保存在文件中，然后在文本编辑器中编辑文件：
$> mysqlbinlog binlog_files > tmpfile
$> ... edit tmpfile ...
编辑文件后，可以使用以下命令应用内容：
$> mysql -u root -p < tmpfile
如果您有多个二进制日志文件要在MySQL服务器上应用，可以使用单个连接处理所有二进制日志文件：
$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
另一种方法是将所有二进制日志写入单个文件，然后处理该文件：
$> mysqlbinlog binlog.000001 > /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"
在还原数据库时，需要关注二进制日志文件的最后一个位置，这个位置将在恢复完成后显示在服务器上。然而，这个位置不一定准确，因为可能存在DDL事件和非InnoDB更改发生在所显示的时间之后。因此，建议使用备份和还原工具来获取准确的二进制日志位置。
如果需要恢复到特定的时间点，可以使用mysqlbinlog实用程序来查看二进制日志内容。通过指定–start-datetime和–stop-datetime选项，可以查看特定时间段内的事件。例如：
$> mysqlbinlog --start-datetime="2020-03-11 20:05:00"
--stop-datetime="2020-03-11 20:08:00" --verbose
/var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"
从输出中可以找到感兴趣的事件及其在二进制日志中的位置。例如，如果找到了一个DROP TABLE语句，它发生在日志位置232之后，并且在语句之后的位置355。
要将二进制日志文件中的事件应用到服务器，可以使用以下命令：
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456
| mysql -u root -p
这个命令将从开始位置恢复所有事务，直到停止位置之前。恢复的数据和相关的MySQL日志将反映事务的原始执行时间。
如果需要在感兴趣的时间点之后重新执行所有语句，可以使用以下命令：
$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456
| mysql -u root -p
这个命令将从指定的位置开始，将所有后续事件应用于服务器，但跳过了之前选择的事件。
扫一扫
2071
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
