> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ahmadraza.in/llms.txt
> Use this file to discover all available pages before exploring further.

# AuditLogsViewTable

### RDS MySQL Audit Logging Setup

**Purpose:**
Enable logging in RDS MySQL for auditing and troubleshooting. This allows you to track which user executed which query and monitor slow or general queries.

***

### Steps:

**1. Create or Modify a Custom DB Parameter Group**

* Create or use an existing custom DB parameter group for your RDS MySQL instance.
* Set the following parameters:

| Parameter         | Value  | Notes                                         |
| ----------------- | ------ | --------------------------------------------- |
| `general_log`     | 1 (ON) | Logs all queries                              |
| `slow_query_log`  | 1 (ON) | Logs queries slower than `long_query_time`    |
| `audit_log`       | 1 (ON) | Logs queries via audit plugin                 |
| `log_output`      | TABLE  | Stores logs in MySQL tables                   |
| `long_query_time` | 0.5–1  | Threshold for slow queries (adjust as needed) |

* Attach the parameter group to your DB instance.
* **Reboot the RDS instance** for changes to take effect.

***

**2. View General Query Log**

* General queries are stored in the `mysql.general_log` table.
* Format of `user_host` column: `username[username] @ [IP]`
* Retrieve last queries executed by a specific user:

```sql theme={null}
SELECT event_time,
       user_host,
       command_type,
       CONVERT(argument USING utf8) AS sql_text
FROM mysql.general_log
WHERE command_type = 'Query'
  AND user_host LIKE '%username[%] @%'
ORDER BY event_time DESC
LIMIT 20;
```

> Replace `username` with the MySQL username.
> `CONVERT(argument USING utf8)` converts hex-encoded queries to readable SQL.

***

**3. View Slow Queries**

```sql theme={null}
SELECT start_time,
       user_host,
       query_time,
       sql_text
FROM mysql.slow_log
WHERE user_host LIKE '%username[%] @%'
ORDER BY start_time DESC
LIMIT 20;
```

* Use this to identify queries exceeding the `long_query_time` threshold.

***

**4. View Audit Log Queries**

```sql theme={null}
SELECT event_time,
       user_host,
       action,
       sql_text
FROM mysql.audit_log
WHERE user_host LIKE '%username[%] @%'
ORDER BY event_time DESC
LIMIT 20;
```

* Shows all queries executed by the user via the audit plugin.
* Useful for auditing and compliance purposes.

***

**5. Notes**

* Queries executed **before enabling logging** will **not appear** in logs.
* Using **TABLE logging** makes queries easy to inspect but logs can grow fast.
* Periodically truncate logs to save space:

```sql theme={null}
TRUNCATE TABLE mysql.general_log;
TRUNCATE TABLE mysql.slow_log;
```