MySQL Tutorial – MySQL Error Log, General Query Log and Slow Query Log

MySQL Tutorial – MySQL Error Log, General Query Log and Slow Query Log

Introduction
Hello guys welcome back.
In this tutorial, I will cover 3 important types of MySQL database server logs.
These are very important log files.
They will allow you to log all errors, slow queries and basically all queries that your database server ran.
Before we get started don’t forget to subscribe to my channel to stay up to date with all my latest training videos. If you have any questions, post it in a comment below.
Caution!
Before we get start I would like to note that you should probably not enable these logs on your production server.
Enabling certain types of logs will have an increased overhead on your server and it will have an impact on your server and its performance.
I recommend you use this feature locally on your dev environment. It’s useful for debugging and optimizations. I highly encourage you to turn it off once you’re done. Especially the General Query log.
The General Query Log
The general query log contains a record every time there’s a connect and disconnect request that runs on your database server. It also includes all queries that run on your database server.
There is two methods that you can use to turn on general query log.

Method 1 (Using Query) – No MySQL Restart Required.
For the SQL method, you don’t need to restart your server. You will need to run the following queries:

SET GLOBAL general_log = 'OFF';
SET GLOBAL general_log = 'ON';

Method 2 (Conf file) – Restart Required
Place inside your MySQL server configuration file.
[mysqld]
general_log = 1
The Slow Query Log
This is a cool log feature. As the name suggests, the Slow Query log basically consists of all the slow queries that ran on your database server.
This is a great log file as because you can use this information to find all the slow queries that you’re running so you can optimize them.

Similar to what we did for the general_log, we have two approaches to configure and turn on the slow_query_log. We can do by running a query or by configuring database server configuration file. The query method doesn’t require you to restart your server.
Method 1 (Using Query) – No MySQL Restart Required.
SET GLOBAL slow_query_log = 'OFF';
SET GLOBAL long_query_time = X; # time in seconds
SET GLOBAL slow_query_log = 'ON';
# to test run SELECT SLEEP(X);

Method 2 (Conf file) – Restart Required
Place inside your MySQL server configuration file.
[mysqld]
long_query_time = 2

The Error Log
The error log contains information related to errors, warnings and notes that occur during your server is started or during shut down.
It also includes any errors, warnings and notes that occur while your server is running.
Unfortunately guys the error log is not a dynamic system variable. To enable and disable the error log, you will need to restart your MySQL server.
You can turn it on and off by setting the following values inside your MySQL configuration file.
[mysqld]

Thank you for watching. I hope you guys learnt something new.
If you found this tutorial useful, then please leave me a thumbs up, drop a comment and subscrive to my channel to stay up to date on my latest videos.

Follow us

Chapters
0:00 Introduction
0:28 Caution
1:06 General Query Log
1:48 General Query - Method 1 - SQL
4:18 General Query - Method 2 - Configuration File
6:38 Slow Query Log
7:21 Slow Query - Method 1 - SQL
10:01 Slow Query - Method 2 - Configuration File
10:57 Error Log

[dameshbillava]

This guy should get more likes for this informational video ..

[tech-networking]

Underrated channel, his tutorials are so helpful! Thank you and keep it up!!

[UmerFarooq-fryy]

I recently stumbled upon your channel. I just want to say Thank you very much for sharing your knowledge. The stuff you are sharing is GOLD, any experienced development would confirm that.
Subscribed!

[CaboLabsHealthInformatics]

Note in linux setting the general log file outside var/log/mysql could cause a file access permission error

[CaboLabsHealthInformatics]

Great stuff, directly to the point, no BS. Congratulations.

[АлександрВыскребцев]

Отлично! Excellent ! Превосходно! Восхитительно! Thank you !