Amazon RDS MySQL：如何获取原生慢查询日志及CSV转原生格式？

1. Fetching Native MySQL Slow Query Logs from RDS #

You have a few straightforward ways to get the text-based native slow query logs instead of CSV:

Via RDS Console #

This is the simplest GUI-based method:

• Log into your AWS RDS Console and navigate to your target MySQL instance.
• Switch to the Logs & events tab.
• Scroll down to the Logs section—look for entries named like mysql-slow.log (you might see multiple timestamped entries for rotated logs).
• Click the Download button next to the log you need (do NOT use the "Export to CSV" option). The downloaded file will be in MySQL's native slow query log format.

Via AWS CLI #

If you prefer command-line tools, use the download-db-log-file-portion command to pull the native log directly:

aws rds download-db-log-file-portion \
  --db-instance-identifier your-db-instance-name \
  --log-file-name mysql-slow.log \
  --output text > mysql-slow-native.log

The --output text flag ensures you get raw, native log content instead of structured formats like JSON or CSV.

Via CloudWatch Logs (If Configured) #

If your RDS instance is set up to send slow query logs to CloudWatch Logs:

• Go to the CloudWatch Console, find the corresponding log group for your RDS instance's slow logs.
• You can download the raw log events directly, or use AWS CLI aws logs commands to export them in native format.

2. Converting CSV Slow Logs to Native Format (Or Just Get the Native One Directly) #

First off: always prioritize fetching the native log directly via the methods above—it avoids conversion hassle and potential formatting errors. But if you only have the CSV file, here's how to convert it:

Understanding the Format Mapping #

RDS's CSV slow query logs typically include columns like:
timestamp, user_host, query_time, lock_time, rows_sent, rows_examined, db, sql_text (exact columns might vary slightly by RDS/MySQL version).

MySQL's native slow log format looks like this:

Time: 2024-05-20T14:22:10.123456Z #

User@Host: app_user[app_user] @ [10.0.0.5] Id: 456 #

Query_time: 5.200000 Lock_time: 0.001000 Rows_sent: 0 Rows_examined: 150000 #

SET timestamp=1716224530;
DELETE FROM old_data WHERE created_at < '2023-01-01';

Python Script Example for Conversion #

Here's a quick script to map CSV columns to the native format:

import csv

# Adjust input/output paths and CSV column names if needed
input_csv = "rds-slow-query.csv"
output_log = "native-slow-query.log"

with open(input_csv, 'r', encoding='utf-8') as csv_file, open(output_log, 'w', encoding='utf-8') as log_file:
    reader = csv.DictReader(csv_file)
    for row in reader:
        # Write the standard native log headers
        log_file.write(f"# Time: {row['timestamp']}\n")
        log_file.write(f"# User@Host: {row['user_host']}\n")
        log_file.write(f"# Query_time: {row['query_time']}  Lock_time: {row['lock_time']}  Rows_sent: {row['rows_sent']}  Rows_examined: {row['rows_examined']}\n")
        
        # Convert timestamp to Unix epoch for the SET timestamp line
        # Adjust this parsing if your timestamp format differs
        ts_parts = row['timestamp'].split('T')
        date_part = ts_parts[0].replace('-', '')
        time_part = ts_parts[1].split('.')[0].replace(':', '')
        unix_ts = str(int(f"{date_part}{time_part}"[:10]))  # Truncate to 10 digits for Unix epoch
        log_file.write(f"SET timestamp={unix_ts};\n")
        
        # Write the actual SQL query
        log_file.write(f"{row['sql_text']}\n\n")

Note: Test this with a small sample first—depending on your CSV's exact structure, you might need to tweak column names or timestamp parsing.

内容的提问来源于stack exchange，提问作者dr_