Retrieve MySQL error entries from Cloudwatch logs

 AWS has enabled the push of MySQL RDS and Aurora to Cloudwatch logs. They are easy to query in the logs insight console where you can filter, download and store them as long as you like.

It used to be in the RDS console under the log section, but there was not user-friendly and you had to download each single log file one by one. They were split by hour so you would have 24 logs each day and then you could work them locally.

Using the Cloudwatch insight query console is far easier. 

However, I'm going to demonstrate how to query the log stream via CLI so that you can still access the logs entries via terminal and then you can handle them as you like

How to query the log in the Cloudwatch insight console

Go to the Cloudwatch -> Log insights page

Then you can learn some basic queries to retrieve the log data. It is worth mentioning the main commands that can be used [1][2]

• Fields: Retrieves the specified fields from log events for display.
• Filter: Apply some conditions to filter the result data
• Stats: Calculates aggregate statistics based on the values of log fields.
• Sort 
• Limit 

Example

aggregate by 5min (bin(5m)) 
fields @timestamp, bin(5m), class, method
| stats count(*) by class, method
| sort @timestamp desc
| limit 10000

fields @timestamp, payload

| filter @message like 'price'

| sort @timestamp desc

fields @message

    | parse @message "[*] *" as loggingType, loggingMessage

    | filter loggingType = "ERROR"

    | display loggingMessage

How to query the log via CLI

When I write this guide, the last awscli version is aws-cli/2.2.37 Python/3.8.8 Linux/5.4.0-89-generic exe/x86_64.ubuntu.18 prompt/off

Retrieve the list of the log groups available

aws logs describe-log-groups --query "logGroups[].logGroupName"

Once you have found the log group name, you can add it to the next cli command

Describe the log stream

aws logs describe-log-streams --log-group-name "/var/doggy/backend/logs/payment.log" {     "firstEventTimestamp": 1489584623000,     "lastEventTimestamp": 1495651970000,     "creationTime": 1489584633464,     "uploadSequenceToken": "xxxxxxxxxxxxxxxxxxxxxxxxxx",     "logStreamName": "i-xxxxxxxxxxxxx/doggy_api",     "lastIngestionTime": 1495651976555,     "arn": "arn:aws:logs:eu-west-1:xxxxxxxxxxx:log-group:/var/doggy/backend/logs/payment.log:log-stream:i-xxxxxxxxxxxxx/doggy_api",     "storedBytes": 7106853 }

Some useful parameters

Order by item
--order-by LastEventTime

Filter-Pattern

--filter-pattern "tag1xxxx" 

How to retrieve the events

aws logs get-log-events --log-group-name "/var/doggy/backend/logs/payment.log" --log-stream-name "i-xxxxxxxx/doggy_api" aws logs filter-log-events --log-group-name "/var/doggy/backend/logs/payment.log" --log-stream-name "i-xxxxxxxxxx/doggy_api" --filter-pattern "tag1dBhxxxxx"

Useful tools

https://github.com/jorgebastida/awslogs

Searching a tag in various log streams

If a log group have a lot of log streams and you don't want to call the AWS cli command for each log stream, you can use the following script to speed your research 

bash #!/usr/bin/env bash # '29/10/2017 16:00:00' -e '30/10/2017 11:30:00' tag=$1 stard=$2 end=$3 echo $tag $start $end today=$(date '+%d-%m-%Y') for group in $(awslogs groups -p /var/doggy/backend/logs/); do echo "Tag: $tag Group: $group"; awslogs get $group -s "$stard" -e "$end" -f  $tag;  done > ${today}.${tag}.running echo "DONE" >> ${today}.${tag}.running mv ${today}.${tag}.running ${today}.${tag}.done Run ./looking_tag_awslogs tagXXXXXXXX '2017-10-30 06:00:00' '2017-10-30 08:30:00' & Output  31-10-2017.tagXXXXXXXX.done

[1] https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html#CWL_QuerySyntax-regex
[2] https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-examples.html