aws rds监控慢sql_使用AWS Lambda函数自动启动/停止AWS RDS SQL Server

最新推荐文章于 2023-12-05 13:18:03 发布

culuo4781

最新推荐文章于 2023-12-05 13:18:03 发布

阅读量758

点赞数

文章标签： python java 数据库大数据 linux

原文链接：https://www.sqlshack.com/automatically-start-stop-an-aws-rds-sql-server-using-aws-lambda-functions/

版权

aws rds监控慢sql

This article gives you an overview of the AWS Lambda function to automatically start and stop AWS RDS SQL Server.

本文概述了自动启动和停止AWS RDS SQL Server的AWS Lambda函数。

介绍 (Introduction)

In the article, How to stop an AWS RDS SQL Server using web console and AWS CLI, we explored the importance of stopping RDS instance for cost optimization of your AWS resources. It covered stop and start RDS using AWS web console and CLI.

在文章如何使用Web控制台和AWS CLI停止AWS RDS SQL Server中，我们探讨了停止RDS实例对于优化AWS资源的成本的重要性。它介绍了使用AWS Web控制台和CLI停止和启动RDS。

Usually, we can decide to stop RDS in the night and start it again in the morning. It is not feasible to do this task manually daily at a fixed schedule. You can also forget or skip it, and it could cost you big bucks the end of the day. AWS provides ways to automate Start and Stop RDS instances. You need to configure it once, and it follows the schedules defined by you.

通常，我们可以决定在晚上停止RDS，并在早晨重新启动。每天以固定的时间表手动执行此任务是不可行的。您也可以忘记或跳过它，并且一天结束时可能要花大钱。 AWS提供了自动启动和停止RDS实例的方法。您只需配置一次，它就会按照您定义的时间表进行。

Let’s follow this article to automate the RDS instance Start and Stop activity.

让我们按照本文来自动化RDS实例的“开始”和“停止”活动。

Lambda函数可自动执行AWS RDS SQL Server启动 (Lambda function to automate AWS RDS SQL Server Startup)

AWS provides a useful Lambda service to automate code execution without managing the infrastructure. We can supply the code in any supported language such as Python, Node.js, Ruby, Java, and it executes the code in scalable and highly available systems. It does not charge for any resources, and you pay only for the compute time of your code.

AWS提供了有用的Lambda服务，无需管理基础架构即可自动执行代码。我们可以使用任何受支持的语言（例如Python，Node.js，Ruby，Java）提供代码，并且它可以在可伸缩且高度可用的系统中执行代码。它不收取任何资源费用，您只需为代码的计算时间付费。

We can configure a lambda function for an event response. For example, we can trigger a code once the user uploads a file in the S3 bucket. We can deploy a web application by supplying the code in lambda functions. It supports serverless Framework.

我们可以为事件响应配置一个lambda函数。例如，一旦用户在S3存储桶中上传了文件，我们就可以触发代码。我们可以通过在lambda函数中提供代码来部署Web应用程序。它支持无服务器框架。

为AWS RDS SQL Server创建AWS Lambda函数的步骤 (Steps to create an AWS Lambda function for AWS RDS SQL Server)

We use the following steps to configure a lambda function.

我们使用以下步骤来配置lambda函数。

创建IAM策略 (Create an IAM policy)

The first step is to define an IAM policy to gain access to RDS actions and AWS Cloud Watch Log Events. Navigate to IAM in the services and click on Policies-> Create Policy.

第一步是定义一个IAM策略，以获取对RDS操作和AWS Cloud Watch日志事件的访问权限。导航到服务中的IAM ，然后单击“策略”->“创建策略”。

To create the Policy, we can either use the Visual editor or specify the JSON statement. It requires following permissions in this Policy

要创建策略，我们可以使用可视化编辑器或指定JSON语句。它需要遵循此政策中的权限

RDS: RDS：
- DescribeDBInstances: It returns information about existing RDS instances for your account
  DescribeDBInstances：返回有关您帐户的现有RDS实例的信息
- StopDBInstance: Permissions to stops the AWS RDS SQL Server
  StopDBInstance：停止AWS RDS SQL Server的权限
- StopDBInstance: Permissions to start the RDS instance
  StopDBInstance：启动RDS实例的权限
CloudWatch Logs CloudWatch日志
- CreateLogGroup: It creates a log group in the CloudWatch. The log group name remains unique in the account
  CreateLogGroup：它在CloudWatch中创建一个日志组。日志组名称在帐户中保持唯一
- CreateLogStream: It creates a log stream for the specified log group created using the CreateLogGroup
  CreateLogStream：它为使用CreateLogGroup创建的指定日志组创建日志流。
- PutLogEvents: It uploads a batch of events in the specified log stream
  PutLogEvents：它在指定的日志流中上传一批事件

{
    "Version": "2012-10-17",
     "Statement": [
         {
             "Effect": "Allow",
             "Action": [
                 "rds:DescribeDBInstances",
                 "rds:StopDBInstance",
                 "rds:StartDBInstance"
             ],
             "Resource": "*"
         },
         {
             "Effect": "Allow",
        "Action": [
                 "logs:CreateLogGroup",
                 "logs:CreateLogStream",
                 "logs:PutLogEvents"
             ],
             "Resource": "*"
         }
     ]
}

You can copy the above JSON policy and paste in the Create Policy, as shown below.

您可以复制上面的JSON策略并粘贴到Create Policy中，如下所示。

In the next step, give a name, description for it. You can also view the summary of permissions we assign for the RDS and CloudWatch logs.

在下一步中，为其提供名称和描述。您还可以查看我们为RDS和CloudWatch日志分配的权限摘要。

We can see that the customer-managed policy RDS_Stop_Start_policy is created.

我们可以看到创建了客户管理策略RDS_Stop_Start_policy 。

创建一个IAM角色并为AWS RDS SQL Server附加RDS_Stop_Start_policy策略 (Create an IAM Role and attach RDS_Stop_Start_policy Policy for AWS RDS SQL Server)

In this step, we create an IAM role and attach the Policy created in the previous step. Click on Roles -> Create Role in the IAM dashboard.

在此步骤中，我们将创建一个IAM角色，并附加上一步中创建的策略。在IAM仪表板中单击“角色”->“创建角色”。

Select the type of trusted entity: Select 选择受信任实体的类型 ：选择AWS Service AWS服务
Choose a use case: We require AWS lambda function to perform the task for us. Select the 选择一个用例：我们需要AWS lambda函数来为我们执行任务。选择Lambda user case Lambda用户案例

Create an IAM Role for AWS RDS SQL Server

In the next step, search the Policy we wish to attach with this Role. In my case, it is RDS_Stop_StartPolicy.

在下一步中，搜索我们希望为此角色附加的政策。就我而言，它是RDS_Stop_StartPolicy 。

We can skip the tags. It is an optional field. Review the role definition, specify a role name and create the IAM role.

我们可以跳过标签。这是一个可选字段。查看角色定义，指定角色名称并创建IAM角色。

In this step, we created the IAM role RDS_Lambda.

在此步骤中，我们创建了IAM角色RDS_Lambda。

创建一个AWS Lambda函数 (Create an AWS Lambda Function)

Once we have the IAM policy and IAM role configured, we can create lambda functions. We can find Lambda service in the Compute section of services.

一旦配置了IAM策略和IAM角色，就可以创建lambda函数。我们可以在服务的“计算”部分中找到Lambda服务。

We should create the lambda function in the same region where our RDS instance exists. You can see existing utilized resources in the AWS lambda dashboard. We need different functions for stopping and starting the RDS instance.

我们应该在RDS实例存在的同一区域中创建lambda函数。您可以在AWS Lambda仪表板中查看已利用的现有资源。我们需要不同的功能来停止和启动RDS实例。

Lambda函数启动AWS RDS SQL Server (Lambda function to start AWS RDS SQL Server)

Here, we create a function to start the AWS RDS SQL Server. Click on Create function in the dashboard.

在这里，我们创建一个函数来启动AWS RDS SQL Server。单击仪表板上的创建功能 。

In the Create function, do the following tasks.

在“创建”功能中，执行以下任务。

Select the option – Author from Scratch

选择选项– 从头开始创作
Function name: Specify a lambda function name. I specify the 函数名称：指定一个lambda函数名称。我指定RDSStartFunction name to start my RDS instance RDSStartFunction名称来启动我的RDS实例
RunTime: As specified earlier, the lambda function supports multiple languages such as Python, Ruby 运行时 ：如前所述，lambda函数支持多种语言，例如Python，Ruby
Permissions: Click on 权限：单击“ Use an existing role and choose the IAM role 使用现有角色”，然后选择我们之前创建的IAM角色RDS_Lambda that we created earlier RDS_Lambda

It creates the lambda function, as shown below. You can see ARN (Amazon resource name) at the top of the dashboard. We require this ARN (Amazon Resource Name) in the next step of the lambda function configuration.

它将创建lambda函数，如下所示。您可以在仪表板顶部看到ARN（Amazon资源名称）。在lambda函数配置的下一步中，我们需要此ARN（Amazon资源名称）。

ARN: arn:aws:lambda:us-east-1:147081669821:function:RDSStartFunction

ARN： arn：aws：lambda：us-east-1：147081669821：function：RDSStartFunction

在现有IAM角色中添加内联策略 (Add inline Policy in existing IAM role)

Now, open a new tab for the IAM role and edit the existing Role RDS_Lambda. In the summary page, click on Add Inline Policy.

现在，为IAM角色打开一个新选项卡，然后编辑现有的Role RDS_Lambda 。在摘要页面中，点击添加内联策略 。

In the Inline policy editor, paste the following JSON. Here, you note that we used the AWS lambda ARN in the resource section. You can copy ARN for your existing lambda ARN.

在内联策略编辑器中，粘贴以下JSON。在这里，您注意到我们在资源部分中使用了AWS lambda ARN。您可以为现有的lambda ARN复制ARN。

Lambda ARN follows the format: arn:aws:lambda:<Region>-<AWS Account o>:function:<lambda function Name>

Lambda ARN遵循以下格式： arn：aws：lambda：<区域>-<AWS帐户o>：function：<lambda函数名称>

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "lambda:GetFunctionConfiguration",
            "Resource": "arn:aws:lambda:us-east-1:147081669821:function:RDSStartFunction"
        }
    ]
}

Click on Review policy, specify the policy name and Create inline Policy.

单击查看策略，指定策略名称并创建内联策略。

Now, we can see two policies in the RDS_Lambda function:

现在，我们可以在RDS_Lambda函数中看到两个策略：

Managed policy: RDS_Stop_Start_Policy 托管策略： RDS_Stop_Start_Policy
Inline Policy: Manual_RDS_Schedule 内联策略 ：Manual_RDS_Schedule

Now, go back to the lambda function. In the designer, we can see function and options to add trigger and destination.

现在，返回到lambda函数。在设计器中，我们可以看到添加触发器和目标的功能和选项。

Function Code: Scroll down and paste the Python code inside the editor. You need to select appropriate language in the run time. I go with the latest version Python 3.8

功能代码：向下滚动并将Python代码粘贴到编辑器中。您需要在运行时选择适当的语言。我使用最新版本的Python 3.8

In this code, we do the following tasks:

在此代码中，我们执行以下任务：

- Botocore works as a base for the AWS CLI and boto3 module
  Botocore充当AWS CLI和boto3模块的基础
- Boto3 is the AWS SDK for Python
  Boto3是适用于Python的AWS开发工具包
lambdaFunc.get_function_configuration() function gets configuration of the lambda function
lambdaFunc.get_function_configuration（）函数获取lambda函数的配置
It uses the AWS CLI function rds.start_db_instance() to start function programmatically
它使用AWS CLI函数rds.start_db_instance（）以编程方式启动函数

We can create another function to stop RDS instance with similar steps. We only need to modify the function rds.start_db_instance() to rds.stop_db_instance():
response = rds.stop_db_instance
(
DBInstanceIdentifier=DBinstance
)

我们可以创建另一个函数以类似的步骤停止RDS实例。我们只需要修改功能rds.start_db_instance（）来rds.stop_db_instance（）：
响应= rds。 stop_db_instance
（
DBInstanceIdentifier = DBinstance
）

import sys
            import botocore
            import boto3
            from botocore.exceptions import ClientError
            def lambda_handler(event, context):
             rds = boto3.client('rds')
             lambdaFunc = boto3.client('lambda')
             print ('Trying to get Environment variable')
              try:
                    funcResponse = lambdaFunc.get_function_configuration(
                        FunctionName='RDSStartFunction'
                   )
                    DBinstance = funcResponse['Environment']['Variables']['DBInstanceName']
                    print ('Starting RDS service for DBInstance : ')
                    except ClientError as e:
                   print(e)    
               try:
                   response = rds.start_db_instance
            (
                        DBInstanceIdentifier=DBinstance
                    )
                    print ('Success :: ') 
                    return response
                except ClientError as e:
            
                    print(e)    
            
                return
            
                {
            
                    'message' : "Script execution completed. See Cloudwatch logs for complete output"

Environment variable: We used the environment variable in the above script to get the RDS instance name.

环境变量：我们在上面的脚本中使用了环境变量来获取RDS实例名称。

Scroll down in the environment variable section. We need to map the existing RDS instance using the environment variable

在环境变量部分中向下滚动。我们需要使用环境变量来映射现有的RDS实例

In the edit environment variables, click on the Add environment variable

在编辑环境变量中，单击添加环境变量。

AWS Lambda uses a key-value pair for the environment variable:

AWS Lambda将键值对用于环境变量：
- Key: DBInstanceName ( Do not change the key name)密钥： DBInstanceName（不要更改密钥名称）
- Value: Specify the RDS instance name in this value. You can get the RDS instance name from the RDS dashboard
  
  值：使用此值指定RDS实例名称。您可以从RDS仪表板获取RDS实例名称
Click on Save, and you get a confirmation in the green box:

单击保存，然后在绿色框中获得确认：

Click on Test, and it gives the option to Configure Test. You get the hello-world event template with the event name and key-value pairs. We do need any changes here. Specify an event name and click on Create.

单击测试，它提供了配置测试的选项。您将获得带有事件名称和键值对的hello-world事件模板。我们确实需要任何更改。指定事件名称，然后单击创建。

We can see an event name in the highlighted box of the below screenshot. Now, we are ready to start the RDS instance. Click on Test to execute the lambda function.

我们可以在以下屏幕快照的突出显示框中看到事件名称。现在，我们准备启动RDS实例。单击测试以执行lambda函数。

You see the logs, and it begins the process of starting the RDS instance.

您会看到日志，它开始启动RDS实例的过程。

You can click on the Monitoring tab, and it shows the CloudWatch requests, duration, billed duration.

您可以单击Monitoring选项卡，它显示CloudWatch请求，持续时间，计费持续时间。

We can refresh the RDS dashboard, and it changes instance status from Stopped to Starting.

我们可以刷新RDS仪表板，它将实例状态从Stopped更改为Starting 。

创建CloudWatch规则以自动启动/停止RDS (Create a CloudWatch rule to automatic Start/Stop RDS)

Once we created a lambda function, we can schedule it using the CloudWatch rules. Go to CloudWatch and Click on Rules -> Create Rule.

创建lambda函数后，我们可以使用CloudWatch规则进行调度。转到CloudWatch，然后单击规则->创建规则。

步骤1：建立规则： (Step 1: Create Rule: )

Select the event source as Schedule

选择事件源作为时间表
Select the target as Lamda function and choose the function we created earlier from the drop-down
选择目标作为Lamda函数，然后从下拉列表中选择我们之前创建的函数
Specify the Schedule in the crontab format. In the below, we specified expression to execute lambda function daily at 5 AM GMT automatically. You see the next 10 trigger dates once you specified the Cron expression
以crontab格式指定计划。在下面，我们指定了表达式，以在格林尼治标准时间每天凌晨5点自动执行lambda函数。指定Cron表达式后，您将看到接下来的10个触发日期

You can refer Cron Expressions to learn the Schedule using Cron expressions.

您可以引用Cron表达式来学习使用Cron表达式的计划。

Click on Configure details and enter the rule name, description. You can also enable and disable the Rule as well.

单击配置详细信息，然后输入规则名称，描述。您也可以启用和禁用规则。

步骤2：配置规则详细信息 (Step 2: Configure rule details)

It creates the rules to execute the lambda function automatically.

它创建规则以自动执行lambda函数。

结论 (Conclusion)

In this article, we explored the lambda function to automatically start an RDS instance. You can configure a similar function for stopping AWS RDS SQL Server. It helps to manage the RDS costs effectively without manual intervention. You can also create bash scripts to manage RDS instances stop/start. I will cover it in further articles. Stay tuned!

在本文中，我们探讨了lambda函数来自动启动RDS实例。您可以配置类似的功能来停止AWS RDS SQL Server。它有助于有效地管理RDS成本，而无需人工干预。您还可以创建bash脚本来管理RDS实例的停止/启动。我将在后续文章中介绍。敬请关注！