Obtain traffic files for a database instance

Enable the audit logging feature

1. (Optional) Create a custom option group.

   Before enabling the audit logging feature, you must create a custom option group. If you have already created one, skip this step.

   1. Log in to the Aurora and RDS console.

   2. In the left navigation pane, click Option Group.

   3. On the Option Group page, click Create Group in the upper-right corner.

   4. On the Create Option Group page, configure the parameters.

      Parameter	Description
      Name	The name of the custom option group.
      Description	A brief description of the option group.
      Engine	Select MySQL.
      Major Engine Version	Select the appropriate MySQL version.

   5. Click Create.

2. Add options to the option group.

   1. On the Option Group page, click the name of the target option group.

   2. In the Option section of the details page, click Add Option in the upper-right corner.

   3. On the Add Option page, configure the parameters.

      Parameter	Description
      Option Name	Select MARIADB_AUDIT_PLUGIN.
      Option Setting	For the SERVER_AUDIT_EVENTS option, set the value based on your actual situation. For example, CONNECT,QUERY,QUERY_DDL,QUERY_DML,QUERY_DCL. Note You cannot set both values QUERY_DML and QUERY_DML_NO_SELECT. Choose one based on your actual situation. Separate multiple values with commas (,). Do not include spaces.
      Apply Now	Choose whether to apply immediately based on your actual situation.

   4. Click Add Option in the lower-right corner of the page.

Export audit logs to CloudWatch

You can publish the logs of an Amazon Aurora MySQL instance in a configured database cluster to Amazon CloudWatch Logs.

1. On the homepage of the Amazon RDS console, click Database in the left navigation pane.

2. On the Database page, select the target database cluster and click Modify.

3. In the Other Monitoring Configurations > Log Export section, select the log type to publish to Amazon CloudWatch Logs.

   Notice

   Audit Log is required here. Select other log types as needed.

4. In the Other Configurations > Database Options section, select the option group created earlier from the Option Group drop-down list.

5. Click Continue.

6. In the Scheduled Modification section of the Modify Database Instance page, set When to Apply Modifications to Apply Immediately.

7. Click Modify Database Instance.

Export audit logs from CloudWatch to S3

Note

• Using the S3 service incurs fees. Proceed with caution.

• You can choose for the audit logs and the target Amazon S3 bucket to be in the same account or different accounts. This topic uses the operation within the same account as an example. For export methods between different accounts, refer to the AWS official documentation.

Preparation

Before creating a task to export audit logs to S3, you need to complete the preparation work. For details, see Export log data to Amazon S3 using the console.

Create an export task

1. Log in to the CloudWatch console using an authorized account, and select the target region in the upper-right corner of the page.

2. In the left navigation pane, click Logs > Log Groups.

3. On the Log Groups page, select the target log group, and click Actions > Export Data to Amazon S3 in the upper-right corner of the page.

4. In the Define Data Export section of the Export Data to Amazon S3 page, select the time range for which you want to export data.

5. In the Select S3 Bucket section, configure the parameters.

   Parameter	Description
   Select Account	Includes This Account and Another Account. The example in this topic is This Account.
   S3 Bucket Name	From the drop-down list, select the name of the created S3 bucket.
   S3 Bucket Prefix (Optional)	Customize the prefix for the S3 bucket.

6. Click Export.

View an export task

After successfully creating an export task, you will automatically be redirected to the log group details page, where you can view the details of the export task.

1. Click View Export Task in the upper-right corner of the page.

2. On the Export Task page, select a completed export task and click View in Amazon S3 in the upper-right corner to view all exported logs.

The Cloud Log Service supports quickly enabling the log collection feature. After enabling the audit log collection feature, the SQL insight (SQL audit) feature is automatically enabled for eligible RDS instances.

The Cloud Log Service supports collecting logs from cloud services such as storage, network, and databases. After collection is complete, the logs are automatically stored in the corresponding Logstore or Metricstore.

First configuration

Notice

This operation only needs to be performed once.

1. Log in to the Cloud Log Service console.

2. On the Log Application > Audit and Security tab, click Cloud Log Service.

   

3. Complete the authorization as prompted on the page.

   After authorization is completed, the Cloud Log Service will use the service-associated role AliyunServiceRoleForSLSAudit to collect logs from cloud services.

Enable log collection

1. Enable the SQL audit log collection feature.

   1. Go to the Cloud Product Access > Global Configuration page of the Cloud Log Service.

   2. On the Global Configuration page, click Modify in the upper-right corner.

      

   3. In the drop-down list of Region where the Centralized Log Storage Project is Located, select the target region for centralized log storage.

      

   4. In the cloud product list, click the SQL Audit Logs switch next to RDS.

      

      To obtain audit logs for a PolarDB MySQL instance, click the Audit Logs switch next to PolarDB.

      

   5. Review the content in the Prompt dialog box, and then click Go to Collection Policy.

2. In the Collection Policy Configuration dialog box, configure the collection policy.

   The Log Service supports configuring collection policies through Default Collection Policy or Advanced Edit Mode. For more information, see Collection Policy.

   Notice

   If you configure the Default Collection Policy, all instances in all regions will be collected by default, which may result in unexpected costs. It is recommended that you use Advanced Edit Mode to configure the instances that require log collection.

   

   1. In the Policies to Add section, select the attribute as Instance ID, the operator as Exact Match, and enter the RDS MySQL instance ID or PolarDB MySQL instance ID for which logs need to be collected in the text box on the right.

      This attribute configuration is only an example; you can modify it according to your actual situation.

   2. Click Add Policy in the lower-right corner of the dialog box.

   3. After confirming the configuration is correct, click OK.

3. Return to the Global Configuration page, click OK, and wait for the configuration modification to complete.

Obtain traffic files for an instance

You can obtain the traffic files for a target instance by saving the traffic files from the Simple Logging Service (SLS) to Object Storage Service (OSS) or by downloading the SQL audit logs locally. The corresponding methods are the Data Collected From options on the Configure Data Collection page when creating a performance assessment task. These options include Import to Object Storage Service (OSS) and Upload File.

• If you save the traffic files from SLS to OSS, select Import to Object Storage Service (OSS) to import the traffic files for assessment.

• If you download the SQL audit logs locally, select Upload File to import the traffic files for assessment.

Save traffic files from SLS to OSS

Note

Migration assessment only supports traffic files delivered in real time; it does not support files retrospectively delivered from SLS. For example, if you enable OSS delivery 2 hours after enabling SLS log monitoring, migration assessment will not receive traffic files from the previous 2 hours.

1. Return to the Log Service console.

2. In the Project List area, click the name of the target project to go to the Log Library page.

   For details on creating a project, see Manage projects.

3. In the left navigation pane, expand Data Processing > Export under the target Logstore, hover over OSS (Object Storage), and click the displayed + icon.

4. In the Create Delivery Task dialog box, select OSS Export and click OK.

5. In the OSS Delivery Feature dialog box, configure the parameters to export logs to OSS. For details, see Create an OSS delivery task (new version).

   Note

   When configuring the OSS Delivery Feature dialog box, follow these rules:

   • File Delivery Directory: This field is required.
   • Partition Format: %Y/%m/%d/%H/%M.
   • Storage Format: Only the JSON format is supported.
   • Compress: Only Compress (Snappy) is supported.

6. After configuration, click OK.

Download SQL audit logs

1. Return to the Log Service console.

2. In the Project List area, click the name of the target project to go to the Log Library page.

   For more information about how to create a project, see Manage projects.

   

3. In the left-side navigation pane, click the name of the target logstore to open its details page.

   For more information about how to create a logstore, see Manage logstores.

4. On the Raw Logs tab, click the download icon and select Download Logs.

   

   You can also select a time period to view logs and statistics.

   

5. In the Log Download dialog box, select Offline Download and configure the parameters as prompted.

   Note

   Currently, only the gzip compression format is supported.

   

6. After configuration, click OK.

7. After the task succeeds, in the Download Task dialog box that appears, click Download.

   

Create a database instance

1. Log in to the Google Cloud Console.

2. Click Select a project in the upper-left corner of the page, and then click the name of the target project in the Select a resource dialog box.

3. On the Cloud SQL Console homepage, click CREATE INSTANCE.

4. On the Create an instance page, click Choose MySQL in the Choose your database engine section.

5. On the Create a MySQL instance page, select the Cloud SQL version, configure the database version, instance ID, root user password, and other instance information as needed. Also, select a region and enable public IP. For configuration details, see Create instances.

6. Add database flags to export audit logs.

   1. In the Customize your instance section, click SHOW CONFIGURATION OPTIONS.

   2. Expand the Flags option and click ADD A DATABASE FLAG.

   3. Select the general_log flag from the drop-down list, set its value to On, and click DONE.

   4. Click ADD A DATABASE FLAG, select the log_output flag from the drop-down list, set its value to FILE, and click DONE.

7. Click CREATE INSTANCE.

8. After the instance is created, add the source IP or IP range to the database's authorized networks.

   1. Return to the Cloud SQL Console homepage, click the name of the target instance, and go to the instance details page.

   2. Click Connections in the left navigation pane.

   3. On the Connections page, click the NETWORKING tab.

   4. In the Authorized networks section, click ADD A NETWORK.

   5. In the New network dialog box, enter the network name and source IP/IP range, then click DONE.

   6. Click SAVE at the bottom of the Connections page.

9. Create a database and a user in the Cloud SQL instance (optional). For more information, see Create and manage databases and Create a user.

Create a bucket and obtain the AK/SK

1. Log in to the Cloud Storage console of the corresponding project.

2. Click + CREATE BUCKET on the OVERVIEW tab.

3. On the Create a bucket page, configure the information.

   1. In the Get Started section, enter a globally unique permanent name that complies with naming guidelines. Decide whether to enable hierarchical namespaces and add tags based on your actual situation, then click CONTINUE.

   2. In the Choose where to store your data > Location type section, select Region, choose the corresponding region from the drop-down list, then click CONTINUE.

   3. In the Choose a storage class for your data > Set a default class section, select Standard, then click CONTINUE.

   4. In the Choose how to control access to objects > Access control section, select Uniform, then click CONTINUE.

4. Click CREATE at the bottom of the page.

5. After creation is complete, obtain the AK/SK for Cloud Storage. For more information, see Create an access key.

Create a log router

1. Go to the Logs Explorer console.

2. Select a log source.

   1. On the Logs Explorer page, click All resources.

   2. In the drop-down list, sequentially select Cloud SQL Database, the target database ID, and the corresponding region.

   3. Click Apply.

3. Select the corresponding log type file.

   1. On the Logs Explorer page, click All log names.

   2. In the Select log names drop-down list, select the log type file for Cloud SQL.

   3. Click Apply.

4. Copy the command line displayed on the Logs Explorer page for log filtering by the log router later.

5. Create a log router to route logs to Cloud Storage.

   1. Go to the Log router console.

   2. On the Log Router page, click Create sink.

   3. On the Create logs routing sink page, configure the parameters.

      1. In the Sink details section, enter the sink name and description. Click Next.

      2. In the Sink destination section, select Cloud Storage bucket as the sink service, then click Browse to select the created bucket. Click Next.

      3. In the Choose logs to include in sink section, paste the command line copied from the Logs Explorer page. Click Next.

      4. Click Create sink at the bottom of the page.