Deleting entries in the mysql database table gwactivity_logs
Removing entries in the Ezeelogin database table gwactivity_logs for house keeping.
Overview: This article explains how to delete entries from ’gwactivity_logs’ table. It would be required to reduce the size of the Ezeelogin MySQL database table gwactivity_logs 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 gateway activity logs
Step 1(A): To view the gateway activity logs login to Ezeelogin GUI, navigate to Users -> Shell Activity -> Gateway Activity Logs
2. Export gateway activity logs
Step 2(A): Before truncating the gatewayactivity_logs export the logs from GUI for audit purposes under Users -> Shell Activity -> 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 gateway activity logs
Step 3(A): Run the below command to generate a backup of the gateway activity 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)gwactivity_logs > $(grep -oP ’db_prefix\s \K\S ’ /usr/local/etc/ezlogin/ez.conf)gwactivity_logs_$(date %Y-%m-%d).sql
Step 3(B): After taking backup, run below command to delete the entries in the table gateway activity logs 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_gwactivity_logs where login_time < ’2023-01-01’"
Step 4: The gwactivity_logs table structure would look as follows.
MariaDB [ezlogin_wggmp]> desc thwm_gwactivity_logs;
------------- --------------------- ------ ----- --------------------- -------------------------------
| Field | Type | Null | Key | Default | Extra |
------------- --------------------- ------ ----- --------------------- -------------------------------
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | 0 | |
| uid | int(10) unsigned | NO | | 0 | |
| remote_ip | varchar(45) | NO | | | |
| remote_port | char(5) | NO | | | |
| local_ip | varchar(45) | NO | | | |
| local_port | char(5) | NO | | | |
| login_time | datetime | YES | | NULL | |
| logout_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| idle_time | int(10) unsigned | NO | | 0 | |
| remote_time | int(10) unsigned | NO | | 0 | |
| status | varchar(100) | NO | | | |
------------- --------------------- ------ ----- --------------------- -------------------------------
Alternatively, you can remove entries using MySQL commands.
MariaDB> use db_name;
MariaDB [ezlogin_wggmp]> delete from thwm_gwactivity_logs where login_time < ’2023-01-01’;
Query OK, 235 rows affected (0.009 sec)
Related Articles: