Debugging and monitoring all database operations with log files (MySQL or MariaDB)

 Recently I got involved with a new software project of a customer based in Melbourne, where they have a web application used to support all the operations in the business. The web application uses a MariaDB database in the backend to store all the data. There was a bug reported on the web application in production environment where it throws an exception when performing an operation related to inserting some data into the database. There were many tables affected and I was not having much time to be familiar with the database design. With enabling the query logs on MariaDB, I was able to quickly identify the exact queries/commands getting executed against the database and that helped me to identify where the bug was. Here I am explaining all the steps involved.

First we need to modify the server configuration to enable the logs. For MariaDB, we need to modify the /etc/mysql/mariadb.conf.d/50-server.cnf file. You can locate the corresponding file for Windows or Mac installations as this is the file for Linux based installations. 

 Then you need to add the following configuration under [mysqld] instance in the configuration file.

general_log_file = /var/log/mysql/mysql.log general_log = 1 log_output = FILE

Note that you can change the log file as you wish. Then we need to restart the server to get the changes affected.

sudo systemctl restart mariadb

Above commands is for mariadb on Ubuntu. You can find the corresponding one for MySQL or installations on other platforms like Mac or Windows.

If the log file is already having previous logs, you may take a backup of it and add an entry to the file for easy identification of the starting point. Note that this overwrites the file and you will lose previous logs. Otherwise you can just append it using >> instead of >.

echo "--- TEST BEGIN ---" > /var/log/mysql/mysql.log

Then perform the operation which generates the content of the log file with all the queries and commands getting executed against the database. Finally, you can append a line to mark the end of testing.

echo "--- TEST END ---" >> /var/log/mysql/mysql.log

This way you can easily extract only the logs related to the task you performed.