Obtain traffic files from a database instance

Enable the audit log feature

1. (Optional) Create a custom option group.

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

   1. Log in to the Amazon and RDS console.

   2. In the left-side navigation pane, click Option groups.

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

      

   4. On the Create 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 MySQL version as needed.

   5. Click Create.

2. Add an option to the option group.

   1. Return to the Option groups page and click the name of the target option group.

   2. In the Options 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 settings	In the SERVER_AUDIT_EVENTS field, specify the value as needed. For example, CONNECT,QUERY,QUERY_DDL,QUERY_DML,QUERY_DCL. Note Do not set both QUERY_DML and QUERY_DML_NO_SELECT. Select one as needed. Separate multiple values with commas (,), and do not include spaces.
      Apply immediately	Specify whether to apply the settings immediately.

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

Export audit logs to CloudWatch

You can export the logs of an Amazon Aurora MySQL instance to Amazon CloudWatch Logs.

1. Return to the Amazon and RDS console homepage and click Databases in the left-side navigation pane.

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

   

3. In the Additional monitoring configuration > Log exports section, select the log type to export to Amazon CloudWatch Logs.

   Notice

   Audit log is a required field. Select other log types as needed.

   

4. In the Additional configuration > Database options section, select the option group that you created earlier.

   

5. Click Continue.

6. In the Plan the modification section on the Modify database instance page, set When to apply the modification to Immediately.

7. Click Modify database instance.

Export audit logs from CloudWatch to S3

Note

• Using the S3 service will incur costs. Proceed as needed.

• You can choose to export audit logs to an Amazon S3 bucket in the same account or a different account. This example shows the process for the same account. For a different account, see the AWS docs.

Prerequisites

Before you create a task to export audit logs to S3, complete the following prerequisites. For more information, see Export log data to Amazon S3 using the console.

Create an export task

1. Log in to the CloudWatch console with an account that has been authorized and select the target region in the upper-right corner of the page.

2. In the left-side navigation pane, select 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.

   

4. In the Export data to Amazon S3 section on the Define data export page, select the time range of data to export.

   

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

   

   Parameter	Description
   Select account	Select This Account or Another Account. In this example, This Account is selected.
   S3 bucket name	Select the name of the created S3 bucket from the drop-down list.
   S3 bucket prefix - Optional	Enter a custom prefix for the S3 bucket.

6. Click Export.

View an export task

After you create an export task, you will be automatically redirected to the log group details page. On this page, 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 the completed export task and click View in Amazon S3 in the upper-right corner to view all the exported logs.

   

Log Audit Service allows you to quickly enable log collection. After you enable audit log collection, SQL Explorer (SQL Audit) will be automatically activated for eligible RDS instances.

Log Audit Service can collect logs from cloud products including storage, network, and databases. After collection, logs are automatically stored in the corresponding Logstore or Metricstore.

Initial configuration

Notice

This operation only needs to be performed once.

1. Log in to the Log Service console.

2. Under Log Application > Audit & Security tab, click Log Audit Service.

   

3. Complete the authorization following the page prompts.

   After authorization, Log Audit Service will use the service-linked role AliyunServiceRoleForSLSAudit to collect logs from cloud products.

Enable log collection

1. Enable SQL audit log collection.

   1. Go to Log Audit Service > Cloud Product Access > Global Configurations page.

   2. On the Global Configurations page, click <Modify in the upper right corner.

      

   3. From the Central Project Region dropdown list, select the target region for centralized log storage.

      

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

      

      If you need audit logs from PolarDB MySQL instances, click the Audit Logs switch next to PolarDB.

      

   5. After confirming the content in the Prompt dialog box, click Configure Collection Policy.

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

   Log Service supports configuring collection policies through either Default Collection Policy or Advanced Editing Mode. For more information, see Configure log collection policies.

   Notice

   If you configure the Default Collection Policy, it will collect all instances across all regions by default, which may result in unexpected costs. We recommend that you use Advanced Editing Mode to configure data collection for specific instances that need replay.

   

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

      This attribute configuration is an example. You can modify it according to your actual needs.

   2. Click Add Policy at the bottom right of the dialog box.

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

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

Obtain the traffic files of an instance

You can obtain the traffic files of the target instance by saving the traffic files in Log Service SLS to Alibaba Cloud OSS and downloading the SQL audit logs to your local device. When you create a performance assessment task, the Configure Data Collection page displays the Data Collected From. The options are Import Object Storage (OSS) and Upload File.

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

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

Save the traffic files in SLS to OSS

Notice

Migration assessment supports only real-time traffic files. It does not support traffic files that are supplemented from SLS. For example, if you enable OSS traffic delivery after two hours of enabling SLS log monitoring, the migration assessment does not receive traffic files before the two hours.

1. Return to the Log Service console.

2. In the Projects section, click the target Project name to enter the Logstore page.

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

   

3. In the left-side navigation pane, expand the target Logstore and click Data Processing > Export. Hover the pointer over Alibaba Cloud OSS and click the + icon that appears.

   

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

5. In the OSS Delivery dialog box, configure the parameters and export the logs to Alibaba Cloud OSS. For more information, see Create an OSS delivery task (new).

   Notice

   When you configure parameters in the OSS Delivery dialog box, observe the following rules:

   • File Directory: Required.
   • Partition Format: %Y/%m/%d/%H/%M.
   • Storage Format: Only JSON is supported.
   • Compression: Only Snappy is supported.

6. After the parameters are configured, click OK.

Download SQL audit logs

1. Return to the Log Service console.

2. In the Projects section, click the target Project name to enter the Logstore page.

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

   

3. In the left navigation pane, click the target Logstore name to open the log details page.

   For more information on creating a Logstore, see Manage a logstore.

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

   

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

   

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

   Note

   Currently, only gzip compression is supported.

   

6. After configuration, click OK.

7. Once the task is successful, click Download in the Download Task dialog box that appears.

   

Create a database instance

1. Log in to the Google Cloud Console.

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

   

3. Go to the Cloud SQL Console, 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 database version, instance ID, root password and other instance information as needed, and select region and enable public IP. For more information, see Create instances.

   

6. Add database flags for exporting audit logs.

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

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

   3. Select the flag general_log from the dropdown list, set the value to On, and click DONE.

      

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

      

7. Click CREATE INSTANCE.

8. After creation, add the source IP/IP range to the database's authorized network list.

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

   2. Click Connections in the left navigation bar.

   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, click DONE.

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

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

Create storage bucket and obtain AK/SK

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

2. Click + CREATE BUCKET under the OVERVIEW tab.

   

3. Configure the information on the Create a bucket page.

   1. In the Obtain Started section, enter a globally unique permanent name that complies with naming guidelines, decide whether to enable namespace hierarchy and add labels based on actual needs, then click CONTINUE.

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

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

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

4. Click CREATE at the bottom of the page.

5. After creation, obtain the Cloud Storage AK/SK. For more information, see Create AccessKeys.

Create log router

1. Go to the Logs Explorer Console.

2. Select log source.

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

      

   2. Select Cloud SQL Database, target database ID and corresponding region from the dropdown list.

      

   3. Click Apply.

3. Select the corresponding log type file.

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

   2. Select the Cloud SQL log type file from the Select log names dropdown list.

      

   3. Click Apply.

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

   

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. Configure parameters on the Create logs routing sink page.

      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, click Browse, 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.