We have a bunch of the AWS RDS with MariaDB.
Backend-developers asked me to enable Slow requests logs so they can debug their application.
So the task is: enable AWS RDS logging and configure export to the CloudWatch Logs for further analysis.
As everything else – our RDS instances are configured via CloudFormation templates, so will add some examples as well.
Beside standard general/error/slow logs – there is also Audit Plugin for MariaDB to enable Audit-log, but this is topic for another post. You can check its documentation here>>>.
DB Parameter Groups – enabling logs
Currently, our Parameter Group looks like next:
...
"MasterDBParamGroup": {
"Type": "AWS::RDS::DBParameterGroup",
"Properties": {
"Description": "Master Database Parameter Group",
"Family": "mariadb10.0",
"Parameters" : {
"net_read_timeout": 60
},
"Tags" : [
{"Key" : "Name", "Value" : { "Fn::Join" : [ "-", [ {"Ref" : "AWS::StackName"}, "master-db-params"] ] } },
{"Key" : "Env", "Value" : {"Ref" : "ENV"} }
]
}
},
...
RDS has four logs types:
- audit
- error
- general
- slowquery
To enable them need to update such parameters:
slow_query_loggeneral_log-
log_error(enabled by default)
Also good to update those:
-
long_query_time– time in seconds after which a request will be logged to the Slow log -
log_output– set to FILE to enable export to the CloudWatch Logs
At first – let’s do it via AWS UI, and then will update a CloudFormation template.
Find an instance’s Parameter Group:
Add the necessary options:
Check them:
$ aws rds --profile bm-backend describe-db-parameters --db-parameter-group-name mobilebackend-dev-masterdbparamgroup-258i35vgxvdi | grep -A1 'slow_query_log\|general_log\|log_error'
"ParameterName": "general_log",
"ParameterValue": "1",
--
"ParameterName": "general_log_file",
"ParameterValue": "/rdsdbdata/log/general/mysql-general.log",
--
"ParameterName": "log_error",
"ParameterValue": "/rdsdbdata/log/error/mysql-error.log",
--
"ParameterName": "slow_query_log",
"ParameterValue": "1",
--
"ParameterName": "slow_query_log_file",
"ParameterValue": "/rdsdbdata/log/slowquery/mysql-slowquery.log",
Logs have to appear in the Logs & events.
Also – do not forget to set log_output to the FILE type (default value is TABLE):
Save changes.
CloudWatch Logs export
Go to an RDS instance and click Modify:
List to the bottom and chose logs to be exported to the CloudWatch:
Press Continue, check the list of settings to be updated, click Modify DB instance:
Wait a couple of minutes:
Go to the CloudWatch Logs – and voila:
Logs data:
CloudFormation template
The last thing is to update our CloudFormation template to apply changes to all servers.
Here we need to update the AWS::RDS::DBParameterGroup resource and add the following parameters:
...
"MasterDBParamGroup": {
"Type": "AWS::RDS::DBParameterGroup",
"Properties": {
"Description": "Master Database Parameter Group",
"Family": "mariadb10.0",
"Parameters" : {
"log_output": "FILE",
"general_log": 1,
"slow_query_log": 1,
"long_query_time": 10,
"log_queries_not_using_indexes": 1,
"net_read_timeout": 60
},
"Tags" : [
{"Key" : "Name", "Value" : { "Fn::Join" : [ "-", [ {"Ref" : "AWS::StackName"}, "master-db-params"] ] } },
{"Key" : "Env", "Value" : {"Ref" : "ENV"} }
]
}
},
...
Next – enable the Export: update the AWS::RDS::DBInstance resource and add the EnableCloudwatchLogsExports with logs list to be exported – error, general, slowquery:
...
"DB1MasterRDS" : {
"Type" : "AWS::RDS::DBInstance",
"Properties" : {
"DBInstanceIdentifier" : { "Fn::Join" : [ "-", [ {"Ref" : "AWS::StackName"}, "db1-master-rds"] ] },
"MasterUsername" : { "Ref" : "DB1RootUser" },
"MasterUserPassword" : { "Ref" : "DB1RootPassword" },
"AllocatedStorage" : { "Ref" : "DB1AllocatedStorage" },
"Engine" : "mariadb",
"StorageType": "gp2",
"AutoMinorVersionUpgrade": false,
"PubliclyAccessible" : false,
"DBInstanceClass" : { "Ref" : "DB1MasterDBInstanceClass" },
"DBSubnetGroupName" : { "Ref" : "DB1SubnetGroup" },
"DBParameterGroupName" : {"Ref" : "MasterDBParamGroup" },
"EnableCloudwatchLogsExports": [
"error",
"general",
"slowquery"
],
"BackupRetentionPeriod": {"Ref" : "BackupRetentionPeriod" },
"PreferredBackupWindow": {"Ref" : "PreferredBackupWindow" },
"VPCSecurityGroups" : [ { "Fn::GetAtt" : [ "MasterDBSecurityGroup", "GroupId" ] } ],
"MultiAZ": {"Ref" : "MultiAZ" },
"Tags" : [
{"Key" : "Name", "Value" : { "Fn::Join" : [ "-", [ {"Ref" : "AWS::StackName"}, "db1-master-rds"] ] } },
{"Key" : "Env", "Value" : {"Ref" : "ENV"} }
]
},
"DeletionPolicy" : "Snapshot"
},
...
Run a stack update:
Check log groups:
Done.
Similar posts
- 05/29/2018 MariaDB: AWS RDS – Lost connection to MySQL Server During Query (0)
- 10/27/2017 AWS: CloudFormation – шаблон для RDS PostgreSQL (0)
- 03/02/2018 AWS: RDS – Connection refused и StorageFull (0)
- 05/17/2018 AWS: GDPR и RDS – миграция на encrypted RDS (0)
Top comments (0)