本教程详细介绍如何使用Alibaba Cloud SDK for Java查看RDS实例慢日志。

前提条件

在使用本教程之前,请确保已完成以下操作:
  • 使用RDS管理控制台或通过API获取到想要查看日志的RDS实例ID,API的相关操作,请参见DescribeDBInstances
  • 使用Alibaba Cloud SDK for Java,您需要一个阿里云账号和访问密钥(AccessKey)。 请在阿里云控制台中的AccessKey管理页面上创建和查看您的AccessKey。
  • 确保您已经安装了Alibaba Cloud SDK for Java,准确的SDK版本号,请参见 阿里云开发工具包(SDK)
    <dependencies>
        <!-- https://mvnrepository.com/artifact/com.aliyun/aliyun-java-sdk-core -->
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>aliyun-java-sdk-core</artifactId>
            <version>4.4.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.aliyun/aliyun-java-sdk-rds -->
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>aliyun-java-sdk-rds</artifactId>
            <version>2.3.9</version>
        </dependency>
    </dependencies>

操作示例

import com.aliyuncs.DefaultAcsClient;
import com.aliyuncs.IAcsClient;
import com.aliyuncs.exceptions.ClientException;
import com.aliyuncs.exceptions.ServerException;
import com.aliyuncs.profile.DefaultProfile;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogRecordsRequest;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogRecordsResponse;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogsRequest;
import com.aliyuncs.rds.model.v20140815.DescribeSlowLogsResponse;
import com.google.gson.Gson;
import java.util.List;

/**
 * DescribeSlowLogRecords    查看实例的慢日志明细
 * DescribeSlowLogs            查看慢日志统计情况
 */
public class TestDescribeSlowLog {

    public static void main(String[] args) {
        IAcsClient client = Initialization();
        List<DescribeSlowLogsResponse.SQLSlowLog> logs = DescribeSlowLogs(client, "YOUR_RDS_DBINSTANCE_ID");
        for (DescribeSlowLogsResponse.SQLSlowLog sqlSlowLog : logs) {
            // 此参数为SQL语句的唯一标识(SQLHASH)
            String sqlHash = sqlSlowLog.getSQLHASH();
            String logRecords = DescribeSlowLogRecords(client, "YOUR_RDS_DBINSTANCE_ID", sqlHash);
            System.out.println(logRecords);
        }
    }

    private static IAcsClient Initialization() {
        // 初始化请求参数
        DefaultProfile profile = DefaultProfile.getProfile(
                "<your-region-id>",// 地域ID
                "<your-access-key-id>",// 您的AccessKey ID
                "<your-access-key-secret>");// 您的AccessKey Secret
        return new DefaultAcsClient(profile);
    }

    private static List<DescribeSlowLogsResponse.SQLSlowLog> DescribeSlowLogs(IAcsClient client, String id) {
        DescribeSlowLogsRequest request = new DescribeSlowLogsRequest();
        // RDS实例ID。
        request.setDBInstanceId(id);
        // 查询开始时间。
        request.setStartTime("2019-06-01Z");
        // 查询结束时间,不能小于查询开始日期,与查询开始日期间隔不超过31天。
        request.setEndTime("2019-06-20Z");
        // 数据库名称。
        request.setDBName("test11221");
        // 排序依据,仅SQL Server 2008 R2实例支持本参数。取值:
        // TotalExecutionCounts:总执行次数最多;
        // TotalQueryTimes:总执行时间最多;
        // TotalLogicalReads:总逻辑读最多;
        // TotalPhysicalReads:总物理读最多。
        request.setSortKey("TotalExecutionCounts");
        try {
            DescribeSlowLogsResponse response = client.getAcsResponse(request);
            return response.getItems();
        } catch (ServerException e) {
            e.printStackTrace();
        } catch (ClientException e) {
            System.out.println("ErrCode:" + e.getErrCode());
            System.out.println("ErrMsg:" + e.getErrMsg());
            System.out.println("RequestId:" + e.getRequestId());
        }
        return null;
    }

    /**
     * DescribeSlowLogRecords    查看实例的慢日志明细
     */
    private static String DescribeSlowLogRecords(IAcsClient client, String id, String SQLHASH) {
        DescribeSlowLogRecordsRequest request = new DescribeSlowLogRecordsRequest();
        // 实例ID。
        request.setDBInstanceId(id);
        // 查询开始时间。
        request.setStartTime("2019-06-01T16:00Z");
        // 查询结束时间,需要大于查询开始时间,与查询开始时间间隔小于31天。
        request.setEndTime("2019-06-20T16:00Z");
        // 数据库名称。
        request.setDBName("test11221");
        // 慢日志统计里的SQL语句唯一标识符,可用于获取该SQL语句的慢日志明细。
        request.setSQLHASH(SQLHASH);
        // 每页显示条数
        request.setPageSize(50);
        // 页码
        request.setPageNumber(1);
        try {
            DescribeSlowLogRecordsResponse response = client.getAcsResponse(request);
            return new Gson().toJson(response);
        } catch (ServerException e) {
            e.printStackTrace();
        } catch (ClientException e) {
            System.out.println("ErrCode:" + e.getErrCode());
            System.out.println("ErrMsg:" + e.getErrMsg());
            System.out.println("RequestId:" + e.getRequestId());
        }
        return null;
    }
}

执行结果

正确的返回结果类似如下:
{
    "DescribeSlowLogRecordsResponse": {
        "Items": {
            "SQLSlowRecord": {
                "ReturnRowCounts": "1", 
                "HostAddress": "192.101.2.11", 
                "SQLText": "update test.zxb set id=0 limit 1", 
                "LockTimes": "12", 
                "ExecutionStartTime": "2011-06-11T15:00:08Z", 
                "ParseRowCounts": "125", 
                "QueryTimes": "123", 
                "DBName": "test"
            }
        }, 
        "PageNumber": "1", 
        "TotalRecordCount": "1", 
        "DBInstanceID": "rm-uf6wjk5xxxxxxx", 
        "RequestId": "542BB8D6-4268-45CC-A557-B03EFD7AB30A", 
        "Engine": "MySQL", 
        "PageRecordCount": "1"
    }
}