Skip to main content

Comprehensive Guide to Enabling and Monitoring MySQL General Log

MySQL General Log Setup and Monitoring: A Comprehensive Guide

Aim :

Enable and monitor the MySQL general log on a Linux system.

MySQL General Log Setup Steps

Step 1: Edit MySQL Configuration File

  • Open the MySQL configuration file (`my.cnf` or `my.ini`): 
    sudo nano /etc/mysql/my.cnf
  • Add or modify the following lines under the `[mysqld]` section: 
    [mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
  • Save and close the file.

Step 2: Restart MySQL Server

  • Restart the MySQL service to apply the changes: 
    sudo systemctl restart mysql

Step 3: Enable General Log Dynamically (Optional)

  • Connect to MySQL and enable the general log dynamically: 
    mysql -u root -p
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-general.log';

Step 4: Verify General Log Status

  • Check the status of the general log: 
    mysql -u root -p
SHOW VARIABLES LIKE 'general_log%';

Verification:

Verify the general log by checking the log file at `/var/log/mysql/mysql-general.log` for recorded queries.

Monitoring MySQL Performance

Method 1: Using MySQL Workbench

  • Open MySQL Workbench and connect to your MySQL server.
  • Navigate to the "Server Status" or "Performance Reports" to monitor various metrics.

Method 2: Using Prometheus and Grafana

  • Install and configure `mysqld_exporter` to expose MySQL metrics: 
    wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.13.0.linux-amd64.tar.gz
cd mysqld_exporter-0.13.0.linux-amd64
./mysqld_exporter --config.my-cnf=/path/to/my.cnf
  • Configure Prometheus to scrape metrics from `mysqld_exporter`.
  • Set up Grafana dashboards to visualize MySQL metrics.

Method 3: Using MySQL Enterprise Monitor

  • Install MySQL Enterprise Monitor for comprehensive monitoring and performance insights.

For more details on monitoring MySQL, refer to the MySQL Query Log Documentation

Written by: A.M.Rinas

Contact: mohomadrinas00@gmail.com

Comments

Post a Comment

Popular posts from this blog

Use Katalon Studio in ubuntu

How to Install and Use Katalon Studio for Web Testing Step-by-Step Guide to Installing Katalon Studio Katalon Studio is a powerful tool for automating web, API, and mobile testing. In this guide, we'll walk you through the installation process and show you how to create your first test case for a web application. Step 1: Download Katalon Studio Start by downloading the latest version of Katalon Studio from the official website. Choose the version that suits your operating system. Step 2: Extract and Run the Application Once the download is complete, extract the contents of the downloaded file. Inside the extracted folder, locate the katalon.sh file (for Linux users) and run it to launch Katalon Studio: ./katalon If you're on Windows...
Post a Comment
Read more

Grafana Installation on Ubuntu using Docker

Grafana Installation on Ubuntu using Docker Aim : Install Grafana OSS (open-source version) on Ubuntu using Docker. Grafana Installation Steps Step 1: Pull and Run the Grafana Container Pull and start the Grafana container: sudo docker run -d -p 3000:3000 --name=grafana grafana/grafana-oss -d : Detaches the container and runs it in the background. -p 3000:3000 : Maps port 3000 on your host machine to port 3000 inside the container, enabling access to Grafana's web interface. --name=grafana : Specifies the name of the container as grafana . Step 2: Access Grafana Once the container is running, access Grafana by opening a web browser and navigating to: http://localhost:3000 If Grafana is running on a remote serv...
Post a Comment
Read more

MySQL 8.0 Binary Installation: A Step-by-Step Guide

MySQL 8.0 Installation: A Comprehensive Guide Aim : Install MySQL 8.0 using the binary distribution on a Linux system. MySQL 8.0 Installation Steps Step 1: Prepare System and User Create a MySQL group: groupadd mysql Create a MySQL user with restricted shell access: useradd -r -g mysql -s /bin/false mysql Step 2: Download and Extract MySQL Navigate to the installation directory(Not compulsory): cd /usr/local Extract the MySQL tarball: tar xvf /path/to/mysql-VERSION-OS.tar.xz Create a symbolic link: ln -s full-path-to-mysql-VERSION-OS mysql Navigate to the MySQL directory: cd mysql Create a directory for MySQL files: mkdir mysql-files Set ownership and permissio...
Post a Comment
Read more