MYSQL Integration using KloudMate Agents

the mysql integration delivers near real time health and performance metrics for your mysql databases these metrics are visualized using a built in dashboard, and you can configure alerts to notify your team about important mysql conditions this integration utilizes the kloudmate agent(based on opentelemetry) to collect, forward, and visualize mysql telemetry with this setup, you can monitor key metrics, establish performance baselines, and proactively detect issues to ensure smooth, reliable operation of your mysql database pre requisites mysql installed and running kloudmate agent installed on the mysql host (see agent installation for https //docs kloudmate com/linux agent and https //docs kloudmate com/windows agent ) mysql username and password for authentication the mysql logs must be enabled from configurations step 1 access agents and opentelemetry collector configuration navigate to the kloudmate platform and log into your account go to settings > agents section here you can view all installed agents access configuration settings for each agent review default configurations provided by kloudmate to edit an agent's collector configuration select the desired agent click the actions menu (or agent action tab) choose collector configuration a text editor ui will open where you can edit the agent’s yaml configuration directly within the platform add the suitable extensions extensions health check pprof endpoint 0 0 0 0 1777 zpages endpoint 0 0 0 0 55679 file storage create directory true in this step, two receivers are integrated to retrieve telemetry data from mysql mysql receiver to retrieve metrics file log receiver to retrieve logs to get the metrics use only mysql receiver receivers mysql endpoint localhost 3306 username root #change the username password 'yourstr0ngpassword!' #change the password collection interval 60s initial delay 1s statement events digest text limit 120 time limit 24h limit 250 filelog/mysqlerror include \[/var/log/mysql/error log] exclude \[/var/log/mysql/ gz] storage file storage retry on failure enabled true operators \ type regex parser parse to body regex '(?p\<time>\d{4} \d{2} \d{2}t\d{2} \d{2} \d{2} \d+z)\s(?p\<id>\d+)\s\\\[(?p\<severity>\[^ ] )\\]\s\\\[(?p\<name>\[^ ] )\\]\s\\\[(?p\<server>\[^ ] )\\]\s(?p\<message> )' timestamp parse from body timestamp layout '%y %m %d %h %m %s %f %z' severity parse from body severity mapping info \ statement \ log \ system \ type remove field body severity \ type remove field body timestamp filelog/mysqlslowquery include \[/var/log/mysql/ slow\ log] exclude \[/var/log/mysql/ gz] storage file storage multiline line start pattern '^# time \d{4} \d{2} \d{2}t\d{2} \d{2} \d{2}\\ \d{6}z' retry on failure enabled true operators \ type regex parser parse to body regex '(?p\<time>\d{4} \d{2} \d{2}t\d{2} \d{2} \d{2}\\ \d+z)\n#\suser\@host \s(?p\<user> )\s+id \s+(?p\<id>\d+)\n# query time \s(?p\<query time>\d+ \d+)\s+lock time \s(?p\<lock time>\d+ \d+)\srows sent \s(?p\<rows sent>\d+)\s+rows examined \s(?p\<rows examined>\d+)\n(?p\<query>\[\s\s]+)' timestamp parse from body timestamp layout '%y %m %d %h %m %s %f %z' severity parse from body severity mapping info \ statement \ log \ type remove field body severity \ type remove field body timestamp step 2 set up the processor component to identify resource information from the host and either append or replace the resource values in the telemetry data with this information please choose one of the following options for configuration based on your provider (aws ec2, azure vm or on premises server) server(can be on premise, non cloud or cloud) processors batch send batch size 5000 timeout 10s resourcedetection detectors \[system] system resource attributes host name enabled true host id enabled true os type enabled false resource attributes \ key service name action insert from attribute host name aws ec2 (optional) to retrieve aws ec2 instance tags along with logs and metrics, users need to associate an iam role with the ec2 instance that includes the ec2\ describetags ec2\ describetags policy processors batch send batch size 5000 timeout 10s resourcedetection/ec2 detectors \["ec2"] ec2 tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute ec2 tag name \ key service instance id action insert from attribute host id azure virtual machines processors batch send batch size 5000 timeout 10s resourcedetection/azure detectors \["azure"] azure tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute azure vm name \ key service instance id action insert from attribute host id step 3 configure the exporter, extension and save the configuration set up the exporter as the kloudmate backend by mentioning the endpoint and authorization key in the agent configuration file, and configure the pipeline exporters debug verbosity detailed otlphttp endpoint 'https //otel kloudmate com 4318' headers authorization xxxxxxxx # use the auth key service pipelines metrics receivers \[mysql] processors \[batch, resourcedetection, resource] #use the correct resourcedetection exporters \[otlphttp] logs receivers \[filelog/mysqlslowquery, filelog/mysqlerror] processors \[batch, resourcedetection, resource] #use the correct resourcedetection exporters \[otlphttp] extensions \[health check, pprof, zpages, file storage] step 4 save configuration and restart agent after updating the configuration, click “ save configuration ” in the ui or, from your server’s console, run for linux execute the following commands systemctl restart kmagent systemctl status kmagent these commands will restart the kloudmate agent and display its current status for windows open the services window press win + r win + r , type services msc services msc , and press ok ok alternatively, search for " services services " in the windows start menu in the services window, locate the " kloudmate agent " service right click the service and select " restart restart " subsequently, monitor the metrics on the kloudmate dashboard and set up an alarm to receive notifications if the potential metrics for a specific application rise default metrics metric name description mysql operations total operations including fsync, reads and writes mysql buffer pool data pages number of data pages for an innodb buffer pool mysql buffer pool limit configured size of the innodb buffer pool mysql buffer pool operations number of operations on the innodb buffer pool mysql buffer pool page flushes sum of requests to flush pages for the innodb buffer pool mysql mysqlx connections total mysqlx connections mysql tmp resources number of temporary resources created mysql uptime number of seconds since the server has been up mysql index io wait count total time of i/o wait events for a particular index mysql handlers number of requests to various mysql handlers mysql locks total mysql locks mysql log operations number of innodb log operations mysql index io wait time total time of i/o wait events for a particular index mysql row locks total innodb row locks present mysql buffer pool usage number of bytes in the innodb buffer pool mysql page operations total operation on innodb pages mysql double writes number of writes to the innodb doublewrite buffer pool mysql table io wait count total i/o wait events for a specific table mysql table io wait time total wait time for i/o events for a table mysql prepared statements number of times each type of prepared statement command got issued mysql sorts total mysql sort execution mysql row operations total row operations executed mysql threads current state of mysql threads mysql opened resources total opened resources mysql buffer pool pages sum of pages in the innodb buffer pool for full details on metrics, click https //github com/open telemetry/opentelemetry collector contrib/blob/main/receiver/mysqlreceiver/documentation md