Amazonrds 简明教程

Amazon RDS - MySQL DBA Tasks

与其他所有数据库一样，Amazon RDS MYSQL 也需要 DBA 任务来微调数据库并定期执行运行状况检查等。但由于 AWS 平台不允许通过外壳访问数据库，因此与 MySQL 的本地安装相比，可以执行的 DBA 任务数量有限。以下是 AWS RDS MySQL 数据库中可以执行的常见 DBA 任务及其说明。

As with every other database, Amazon RDS MYSQL also needs DBA tasks to fine tune the database and do periodic health checks etc. But as the AWS platform does not allow the shell access to the DB, there are a limited number of DBA tasks that can be performed as compared to the on-premise installation of MySQL. Below is a list of common DBA tasks that can be performed in AWS RDS MySQL database and their descriptions.

Accessing Error Logs

可以使用 Amazon RDS 控制台或使用 Amazon RDS CLI 检索日志来查看 MySQL 错误日志 (mysql-error.log)。mysql-error.log 每 5 分钟刷新一次，其内容附加到 mysql-error-running.log。然后，mysql-error-running.log 文件每小时旋转一次，并且保留过去 24 小时内生成的每小时文件。

The MySQL error log ( mysql-error.log) file can be viewed by using the Amazon RDS console or by retrieving the log using the Amazon RDS CLI. mysql-error.log is flushed every 5 minutes, and its contents are appended to mysql-error-running.log. The mysql-error-running.log file is then rotated every hour and the hourly files generated during the last 24 hours are retained.

Using RDS Console

下方是上述两个日志文件的链接。

Below there are links to two log files described above.

mysql rds log1

Using CLI

使用 CLI 将日志文件作为 JSON 对象发布到 CloudWatch 日志中。

Using CLI the log files are published to CloudWatch Logs as a JSON Object.

aws rds modify-db-instance \
    --db-instance-identifier mydbinstance \
    --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}' \
    --apply-immediately

Killing a Long Running Session or Query

DBA 有时需要中止执行时间较长的会话或查询，因为它们达不到足够快的结果。此 DBA 任务通过首先查找查询的进程 ID，然后使用 RDS 函数中止查询来完成。以下命令是示例。

Sometimes the DBA needs to kill a long running session or query which is not giving the result quick enough. This DBA task is done by first finding the process ID of the query and then using a RDS function to kill the query. The below commands are the examples.

# get the ID
Select * from INFORMATION_SCHEMA.PROCESSLIST
#Apply the Kill Function
CALL mysql.rds_kill(processID);

Improve Crash recovery Time

我们可以通过设置称为 innodb_file_per_table 的 DB 参数来改善崩溃的恢复时间。我们可以在 RDS 控制台中找到此参数，如下所示。

We can improve the recovery time from a crash by setting a DB parameter called innodb_file_per_table. We can find this parameter in the RDS console as shown below.

mysql DBA parameters

接下来我们可以按以下所示的方式搜索参数名称。

Next we can Search for the parameter name as shown below.

mysql innodb file param

Amazon RDS 将 innodb_file_per_table 参数的默认值设置为 1，这允许您删除单独的 InnoDB 表并回收 DB 实例中这些表使用的存储。这加快了崩溃的恢复时间。

Amazon RDS sets the default value for innodb_file_per_table parameter to 1, which allows you to drop individual InnoDB tables and reclaim storage used by those tables for the DB instance. This speeds up the recovery time from the crash.

Stop and Reboot DB

停止 DB、重启它或创建快照等操作可以通过 RDS 控制台轻松完成，如下图所示。

Stopping a DB, Rebooting it or creating snapshots etc can be done easily through RDS console as shown in the below diagram.

mysql db stop reboot