How To Enable MySQL General Query Log On Linux

The Server writes information to this file when the client connects and disconnects. MySQL query used to find out the error in a client and want to know what exactly the client sent to mysqld. This writes to log each SQL statement received from the client. Let’s see how to enable the MySQL general query log example.

How To Enable MySQL General Query Log On Linux:

You can Enable Query Log for MySQL below 5.1.29 Version, Then use the below settings in [mysqld] section.

Check Out: How To Enable Slow Query In MySQL Server In Linux 

There’re many log formats for MySQL logs which can help you to find out the issues in the database or in tables.

Method 1: you can add permanently in my.cnf file for the Mysql version below 5.1.29.

vim /etc/my.cnf
log = /home/general-query.log

Set via MySQL Console,

SET general_log = 1;

Start the Service

systemctl restart mysqld

Check Out: Solve MySQL Too Many Connections Error On Redhat 7

Method 2: You can Enable General Queries for MySQL version above 5.1.29.

general_log_file = /path/to/general-query.log
general_log = 1

Set via MySQL Console,

SET general_query_log = 1;

Check Out: Install MySQL 5.6 Database Server Using Repository In Linux

Create a file general-query.log where you want to keep these logs. 

touch general-query.log
chown -R mysql:mysql general-query.log
systemctl restart mysqld

Enable Query Log Without Restart:

If you don’t want to restart the service of MySQL, you can follow the below solutions. 

Create a table on MySQL server using the below query and table source here.

CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

Now we need to write a table for the General log as shown below. 

CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Once you’ve created the above tables. we need to enable logging on the MySQL server using the below command.

Check Out: Enable and Disable Journaling On EXT4 File Systems In Linux

Login to the server using MySQL -u root -p and run the below queries. 

SET global general_log = 1; 
SET global log_output = 'table';

If you want to check the logs, you can use the below query.

select * from mysql.general_log;

Disable the query using SET global general_log = 0;

YouTube Link:

You’re done

Share on:

I'm the founder of Curious Viral. I hope this blog will provide you complete information about Linux Technology & I would like to share my technical knowledge with you which I have learned during this period.

Other Posts You May Like...

Leave a comment