Skip to Content

Deleting entries from mysql database table authlogs

791  Jisna Joseph September 16, 2025  Migration & Maintenance

How to remove entries from the authlogs table in a Ezeelogin MySQL database?

Overview: This article explains how to delete entries from Ezeelogin authlogs table. It would be required to reduce the size of the  authlogs table as it would cause the Ezeelogin software upgrade to slow down significantly ( 2-4 hours ) because of the huge size of the database table. 

1. View Authentication logs
Step 1(A): To view the webactivity logs login to Ezeelogin GUI, navigate to Users -> Authentication Log.

2. Export Authentication logs
Step 2(A): Before truncating the authentication logs you can export the logs from GUI for audit purposes under Users -> Authentication Log -> Export

Generate a mysql dump of the Ezeelogin database or a table dump before you perform the operation in case you need to revert due to any unforeseen reasons. Run below command to take the database dump. Be sure to replace the database name with the actual name found in /usr/local/etc/ezlogin/ez.conf.

root@gateway:~# mysqldump ezlogin_wggmp > ezlogin_wggmp.sql

 

3. Truncate Authentication logs
Step 3(A): Run the below command to generate a backup of the Authentication logs table.

root@gateway:~# mysqldump -u root -p $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf) $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs_$(date +%Y-%m-%d).sql

root@gateway:~# ls
egbleq_authlogs_2025-09-15.sql

Run below command to check the row count of authlog table:

mysql -u root -p -e "USE $(grep -oP 'db_name\s+\K\S+' /usr/local/etc/ezlogin/ez.conf); SELECT 'authlogs', COUNT(*) FROM $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)authlogs;"
Enter password:
+--------------+----------------+
| authlogs  | COUNT(*)  |
+--------------+-----------------+
| authlogs  | 538966       |
+---------------+----------------+

Step 3(B): After taking backup, run below command to delete the entries in the table authlogs using the Ezeelogin query runner script.It would remove the entries before the date 2023-01-01 from the logs table.

root@gateway:~# php /usr/local/ezlogin/ez_queryrunner.php "delete from prefix_webactivity_logs where created < '2023-01-01' "

Step 4: The authlogs table would looks as follows:

MariaDB [(none)]> use db_name; 

MariaDB [ezlogin_warme]> desc tabel_prefix_authlogs;

For Example:

MariaDB [(none)]> use ezlogin_warme;  -------------> replace the database name with the actual name found in /usr/local/etc/ezlogin/ez.conf.

MariaDB [ezlogin_warme]> desc egbleq_authlogs; --------------> replace the table_prefix with the actual name found in /usr/local/etc/ezlogin/ez.conf.
+--------------+------------------------------------------------------------+------+-----+---------+----------------+
| Field        | Type                                                       | Null | Key | Default | Extra |
+--------------+------------------------------------------------------------+------+-----+---------+----------------+
| id           | bigint(20) unsigned                                        | NO   | PRI | NULL    | auto_increment |
| uid          | int(10) unsigned                                           | NO   |     | 0       |                |
| username     | varchar(100)                                               | YES  | MUL | NULL    |                |
| user_id      | int(10) unsigned                                           | NO   | MUL | 0       |                |
| usergroup_id | int(10) unsigned                                           | NO   | MUL | 0       |                |
| email        | varchar(250)                                               | YES  |     | NULL    |                |
| type         | enum('login','logout')                                     | YES  | MUL | NULL    |                |
| status       | enum('success','fail','incomplete','timeout','web_logout') | YES  | MUL | NULL    |                |
| reason       | varchar(250)                                               | YES  |     | NULL    |                |
| interface    | enum('WEB','EZSH')                                         | YES  |     | NULL    |                |
| mode         | varchar(50)                                                | YES  |     | NULL    |                |
| client_addr  | varchar(60)                                                | YES  |     | NULL    |                |
| server_addr  | varchar(60)                                                | YES  |     | NULL    |                |
| node         | enum('primary','secondary','unknown')                      | YES  |     | NULL    |                |
| nodestate    | enum('master','slave')                                     | YES  |     | NULL    |                |
| expiry       | datetime                                                   | YES  |     | NULL    |                |
| created      | datetime                                                   | YES  |     | NULL    |                |
+--------------+------------------------------------------------------------+------+-----+---------+----------------+
17 rows in set (0.009 sec)

Related Articles: 

Deleting entries in the mysql database table serveractivity_logs

Deleting entries in the mysql database table gwactivity_logs

Deleting entries from mysql database table webactivity_logs

Truncate the ssh session logs recorded

 
Not Rated