MySQL 数据库实例的数据库管理员常见任务
您可以在下面了解到一些常见数据管理员任务的 Amazon RDS 特定实施描述,这些任务适用于运行 MySQL 数据库引擎的数据库实例。为了提供托管服务体验,Amazon RDS 未提供数据库实例的 Shell 访问权限。并且它还限制了对需要高级权限的某些系统程序和表的访问。
有关在 Amazon RDS 上使用 MySQL 日志文件的信息,请参阅 MySQL 数据库日志文件。
结束会话或查询
您可使用 rds_kill
和 rds_kill_query
命令结束数据库实例上的用户会话或查询。首先连接到 MySQL 数据库实例,然后发出正确的命令,如下所示。有关更多信息,请参阅“连接到运行 MySQL 数据库引擎的数据库实例”。
CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)
例如,要结束线程 99 上运行的会话,可以键入以下内容:
CALL mysql.rds_kill(99);
要结束线程 99 上运行的查询,可以键入以下内容:
CALL mysql.rds_kill_query(99);
跳过当前的复制错误
如果错误导致只读副本停止响应但不影响数据的完整性,您可以跳过只读副本错误。
请先验证是否可以安全地跳过错误。在 MySQL 实用工具中,连接到只读副本并运行以下 MySQL 命令。
SHOW REPLICA STATUS\G
有关返回值的信息,请参阅 MySQL 文档
以前的 MySQL 版本使用的是 SHOW SLAVE STATUS
而不是 SHOW REPLICA STATUS
。如果您使用的 MySQL 版本低于 8.0.23,那么请使用 SHOW SLAVE STATUS
。
您可以通过以下方式跳过只读副本上的错误。
调用 mysql.rds_skip_repl_error 过程
Amazon RDS 提供了一个存储过程,您可以调用该过程以跳过只读副本上的错误。首先连接到您的只读副本,然后发出正确的命令,如下所示。有关更多信息,请参阅 连接到运行 MySQL 数据库引擎的数据库实例。
要跳过错误,请发出以下命令。
CALL mysql.rds_skip_repl_error;
如果您在未遇到复制错误的源数据库实例或只读副本上运行此命令,则此命令不会产生任何影响。
有关更多信息 (如支持 mysql.rds_skip_repl_error
的 MySQL 版本),请参阅 mysql.rds_skip_repl_error。
如果您尝试调用 mysql.rds_skip_repl_error
并遇到以下错误:ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist
,请将您的 MySQL 数据库实例升级到最新次要版本或是 mysql.rds_skip_repl_error 中列出的最低次要版本之一。
设置 slave_skip_errors 参数
要跳过一个或多个错误,您可以设置在只读副本上设置 slave_skip_errors
静态参数。您可以将此参数设置为跳过一个或多个特定的复制错误代码。目前,您只能为适用于 MySQL 5.7 的 RDS 数据库实例设置此参数。更改此参数的设置后,确保重启数据库实例,以使新设置生效。有关设置此参数的信息,请参阅 MySQL 文档
我们建议在单独的数据库参数组中设置此参数。您只能将此数据库参数组与需要跳过错误的只读副本关联。遵循此最佳实践可以减少对其他数据库实例和只读副本的潜在影响。
为此参数设置非原定设置值可能会导致复制不一致。只有在用尽其他选项来解决问题并且确定对只读副本数据的潜在影响时,才将此参数设置为非原定设置值。
使用 InnoDB 表空间改善崩溃恢复时间
MySQL 中的所有表均由表定义、数据和索引组成。MySQL 存储引擎 InnoDB 将表数据和索引存储在表空间中。InnoDB 创建全局共享的表空间,其中包括数据字典和其他相关元数据,并且它可能包含表数据和索引。InnoDB 还可以为每张表创建单独的表空间和分区。这些单独的表空间存储在后缀名为 .ibd 的文件中,并且每个表空间的头部包含一个唯一识别编号。
innodb_file_per_table
提供一个属于 MySQL 参数组的参数,名称为 Amazon RDS。这些参数控制 InnoDB 是否(通过将参数值设置为 0)向共享表空间或(通过将参数值设置为 1)向单个表空间添加新的表数据和索引。Amazon RDS 将 innodb_file_per_table
参数的默认值设置为 1,以便删除单独的 InnoDB 表并回收数据库实例的表所占用的存储空间。在大多数使用案例中,推荐将 innodb_file_per_table
参数设置为 1。
当表特别多时 (例如,当使用标准 (磁性) 或通用 SSD 存储时,有超过 1000 张表,或当使用预置的 IOPS 存储时,有超过 10000 张表),应将 innodb_file_per_table
参数设置为 0。当将该参数设置为 0 时,不会创建独立表空间,这样可以节省数据库崩溃恢复所需的时间。
MySQL 在崩溃恢复循环期间处理每个元数据文件 (包括表空间)。当有多个表空间时,与 MySQL 用于处理数以千计的表空间文件的时间相比,MySQL 用于处理共享表空间中的元数据信息的时间可以忽略不计。因为表空间编号存储在每个文件的头部,读取所有表空间文件的总时间可能需要长达数小时。例如,在崩溃恢复循环期间,标准存储空间上的一百万个 InnoDB 表空间可能需要 5 到 8 个小时的处理时间。在一些案例中,InnoDB 可能会在一个崩溃恢复循环后确定它需要额外清理,因此,它会开始另一个崩溃恢复循环,这会使恢复时间延长。请记住,崩溃恢复循环也会引起回滚事务、修复损坏页面和除表空间信息处理以外的其他操作。
因为 innodb_file_per_table
参数存储在参数组中,所以可通过编辑数据库实例所使用的参数组来更改该参数值,无需重新启动该数据库实例。更改该设置后,例如从 1 (创建独立的表) 改为 0 (使用共享的表空间),新的 InnoDB 表将添加到共享的表空间,而现有的表继续使用独立的表空间。要将 InnoDB 表移动到共享的表空间,必须使用 ALTER TABLE
命令。
将多个表空间迁移到共享的表空间
您可将 InnoDB 表的元数据从其自己的表空间移至共享的表空间,这将按照 innodb_file_per_table
参数设置重建表的元数据。首先连接到 MySQL 数据库实例,然后发出正确的命令,如下所示。有关更多信息,请参阅“连接到运行 MySQL 数据库引擎的数据库实例”。
ALTER TABLE
table_name
ENGINE = InnoDB, ALGORITHM=COPY;
例如,以下查询为不在共享表空间中的每个 InnoDB 表返回 ALTER TABLE
语句。
对于 MySQL 5.6 和 5.7 数据库实例:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
对于 MySQL 8.0 数据库实例:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
重建一个 MySQL 表以便将该表的元数据移动到共享的表空间,这需要额外的临时存储空间以重建该表,因此,该数据库实例必须有可用的存储空间。在重建期间,该表会被锁定,无法访问查询。对于不经常访问的小型表或普通表,这可能不是问题。对于在大量并发环境中经常访问的大型表或普通表,您可以在只读副本上重建表。
您可以创建只读副本,并将表元数据迁移到只读副本上的共享表空间。虽然 ALTER TABLE 语句会阻止访问只读副本,但源数据库实例不会受到影响。尽管只读副本会在表重建过程中滞后,但源数据库实例将继续生成其二进制日志。由于重建需要额外的存储空间,并且重放日志文件可能会变得很大,因此,您应该创建只读副本,并向其分配大于源数据库实例的存储空间。
要创建只读副本和重建 InnoDB 表以便使用共享表空间,请遵循以下步骤:
-
确保在源数据库实例上启用备份保留,以便启用二进制日志记录。
-
使用 Amazon Web Services Management Console或 Amazon CLI 创建源数据库实例的只读副本。由于创建只读副本涉及的流程与崩溃恢复一样多,因此,如果存在大量 InnoDB 表空间,创建过程可能会需要一些时间。在只读副本上分配的存储空间应大于源数据库实例上当前使用的空间。
-
创建只读副本后,使用参数设置
read_only = 0
和innodb_file_per_table = 0
创建参数组,然后将该参数组与只读副本关联。 -
针对您要在副本中迁移的所有表发出以下 SQL 语句。
ALTER TABLE
name
ENGINE = InnoDB -
在只读副本中完成所有
ALTER TABLE
语句后,验证只读副本是否连接至源数据库实例,并且两个实例是否同步。 -
使用控制台或 CLI 将只读副本提升为实例。确保用于新的独立数据库实例的参数组已将
innodb_file_per_table
参数设置为 0。更改新的独立数据库实例的名称,并将所有应用程序指向新的独立数据库实例。
管理 Global Status History
MySQL 保存了很多状态变量,用于提供与操作相关的信息。状态变量值可以帮助您检测数据库实例上的锁定或内存问题。这些状态变量的值会从上次启动数据库实例时开始累积。可以使用 FLUSH STATUS
命令将大多数状态变量重置为 0。
Amazon RDS 提供了一套程序,这些程序会随着时间为这些状态变量的值创建快照,并将它们及上次创建快照后所做的任何更改写入一个表中,从而可以随时间监控这些值。此基础设施名为 Global Status History (GoSH),安装在从版本 5.5.23 开始的所有 MySQL 数据库实例上。默认情况下 GoSH 处于禁用状态。
要启用 GoSH,请首先在数据库参数组中将参数 event_scheduler 设置为“ON”,从而启用事件调度程序。有关创建和修改数据库参数组的信息,请参阅 使用参数组。
然后可以使用下表中的程序启用和配置 GoSH。首先连接到 MySQL 数据库实例,然后发出正确的命令,如下所示。有关更多信息,请参阅“连接到运行 MySQL 数据库引擎的数据库实例”。对于每个步骤,请键入以下命令:
CALL
procedure-name
;
其中,procedure-name 是表中一项程序。
程序 |
描述 |
---|---|
|
启用 GoSH,按照 |
|
指定快照之间的间隔,以分钟为单位。默认值是 5。 |
|
禁用快照。 |
|
根据需求创建快照。 |
|
按照 |
|
指定表交替之间的间隔,以天为单位。默认值是 7。 |
|
禁用表交替。 |
|
根据需求将 |
GoSH 运行时,您可以查询 GoSH 要写入的表。例如,要查询 Innodb 缓冲池的命中率,您可以发送以下查询请求:
select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'