Amazon Aurora DB (MySQL)

Owned by Nadav Razam (Unlicensed)
Last updated: Jun 26, 2022 by Noga Aviram (Unlicensed)
2 min read



Background

Amazon Aurora DB log analysis App automatically Collect - Read - Parse - Analyzes - Reports all database generated error logs, slow logs & general logs data of the server and presents a comprehensive set of graphs and reports to analyze DB generated data. Use a predefined set of dashboards and widgets to visualize and address the system software, code written, and infrastructure during development, testing, and production. This logs analysis App helps measure, troubleshoot, and optimize your data bases integrity, stability and quality with the several visualization and investigation dashboards.

Steps:

  1. Aurora DB App is running on General Log, Slow Log & Error Log.
    When adding/editing the logs to XpoLog it is mandatory to apply the correct log type(s) to each of the logs:

    1. aurora - all logs that the application will analyze must have aurora as a log type.

    2. database -  all logs that the application will analyze must have database as a log type.

    3. error - only the Error log must also be configured to have error as a log type.

    4. general - only the General log must also be configured to have general as a log type.

    5. slow -  only the Slow log must also be configured to have slow as a log type.



  2. By default, the logs are disabled and there is a need to enable them to write the logs to files.

Once the required information is set, click next and edit the log pattern, this step is crucial to the accuracy and deployment of the Aurora DB Server App. Use the following pattern for the log:

  1.  

    1. Error Log:
      {date:Date,yyyy-MM-dd HH:mm:ss} {number:Process,ftype=processid}{block,start,emptiness=true} [{priority:Priority,ftype=status,Note;Debug;Warning;Error;Critical;Alert}]{block,end,emptiness=true} {regexp:host,ftype=host;refName=message,(Host name \u0027|Hostname \u0027)[XPLG_PARAM([^\u0027]+)].*}{regexp:ip,ftype=ip;refName=message,(resolve to \u0027|resolves to \u0027|- |IP address \u0027|IP: \u0027)[XPLG_PARAM(\d+\.\d+\.\d+\.\d+)].*}{text:message,ftype=message}

    2. General Log:
      {date:Date,yyMMdd HH:dd:ss;yyyy-MM-dd HH:mm:ss}{tab}{text:thread_id,charsLength=5;,} {text:command_type,ftype=commandtype;,}{tab}{regexp:user,ftype=user;refName=message;multiLine=true,(\S+)\u0040\S+\s.*}{regexp:host,ftype=remoteip;refName=message;multiLine=true,\S+\u0040(\S+)\s.*}{regexp:DB name,ftype=db;refName=message;multiLine=true,\S+\u0040\S+\son ([^\u0009]+).*}{regexp:argument_content,ftype=argument;refName=message;multiLine=true,\d+\s\S+\u0009([^\u000a]+)}{string:message,ftype=message}

    3. Slow Log:
      # Time: {date:Date,yyMMdd HH:mm:ss;MM/dd/yyyy HH:mm:ss}{regexp:User,ftype=user;refName=message;multiLine=true,\# User\@Host\: ([^\u005b]+)}{regexp:Host,refName=message;multiLine=true,\s\@\s([^\s]+).*}{regexp:Host IP,ftype=remoteip;refName=message;multiLine=true,\s\@\s\S+\s\[([^\u005d]+).*}{regexp:ID,refName=message;multiLine=true,Id:\s+(\d+).*}{regexp:Query Time,ftype=querytime;refName=message;multiLine=true,\# Query_time\: (\S+).*}{regexp:Lock Time,ftype=locktime;refName=message;multiLine=true,Lock_time\: (\S+).*}{regexp:Rows Sent,refName=message;multiLine=true,Rows_sent\:\s(\d+).*}{regexp:Rows Examined,refName=message;multiLine=true,Rows_examined\:\s(\d+).*}{regexp:SQL Text,refName=message;multiLine=true,Rows_examined\:\s\d+([^\u0023]+)}{string:message,ftype=message}