MySQL/MariaDB logging to a table
- July 9th, 2020
- Write comment
OS: CentOS 7
There are a few ways to enable query logging in MariaDB. I did it by editing the server.cnf file.
# cd /etc/my.cnf.d
vi server.cnf
…
[mariadb]
log_output=TABLE
general_log
…
Restart mariadb:
# systemctl restart mariadb
The output is put in the mysql.general_log table. Fortunately, I had used unique userid for each of my applications. This allowed me to be able to query the table to find those queries for a particular database. For example, an application using dbuser for database access:
MariaDB [mysql]> SELECT * FROM general_log WHERE user_host like ‘%dbuser%localhost%’;
You are going to want to truncate this log once in a while, because it will get very large depending the on the activity.
MariaDB [mysql]> TRUNCATE mysql.general_log