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

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

Install Tomcat on Linux using binary distribution

Install Tomcat on Linux Aim: In this tutorial, we will install Tomcat 9.0.21 on Linux. Step 1: Installing JDK Tomcat 9 requires Java 8 or later versions. Check Java installation: $ java -version If Java is not installed, run the following commands to install Java: $ sudo apt-get update $ sudo apt-get install default-jdk -y After installation, verify Java installation: $ java -version Step 2: Creating a Tomcat user and group Create a group and user for Tomcat: $ sudo groupadd tomcat $ sudo useradd -s /bin/false -g tomcat -d /opt/tomcat tomcat Step 3: Download and Install Tomcat 9 Change directory to /opt and download Tomcat 9 to that directory: $ cd /opt $ sudo wget https:/...
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