Deleting entries in the mysql database table gwactivity_logs
Deleting entries in the Ezeelogin database table gwactivity_logs for house keeping.
Overview: This article outlines how to expedite Ezeelogin software upgrades by truncating the 'gwactivity_logs' MySQL table based on users' login times, thereby preventing significant slowdowns during the process."
- 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.
- The table can be truncated based on the ssh gateway users 'login time' so that the upgrade operations completes quickly.
- This data from the gwactivity_log would be displayed in the GUI under Users ->Shell Activity ->Gateway Activity Logs as shown.
- Before truncating the gatewayactivity_logs you can export the logs from GUI for audit purposes under Users > Shell Activity > Export.
1. The following command would delete all entries with Login Time before 01 January 2020 from the gwactivity_logs table in the Ezeelogin MySQL database.
Generate a MySQL dump of the Ezeelogin database or a table dump before you operate in case you need to revert due to any unforeseen reasons.
#Determine the Ezeelogin database in use
[root@otp ~]# grep db_name /usr/local/etc/ezlogin/ez.conf
db_name ezlogin_wggmp
#Generate a backup of the db.
[root@otp ~]# mysqldump ezlogin_wggmp > ezlogin_wggmp.sql
#Generate a backup of the gateway activity logs table.
[root@otp ~]# mysqldump ezlogin_wggmp thwm_gwactivity_logs > ezlogin_wggmp_thwm_gwactivity_logs.sql
#Deleting the entries in the table gwactivity_logs using the Ezeelogin query runner script.
[root@~gate]# php /usr/local/ezlogin/ez_queryrunner.php "delete from prefix_gwactivity_logs where login_time < '2020-01-01'"
The gwactivity_logs mysql table structure would look as follows.
[root@~gate]#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 | | | |
+-------------+---------------------+------+-----+---------------------+-------------------------------+
2. Alternatively, the MySQL command to delete the entries based on the user's Login Time would be
MariaDB [ezlogin_wggmp]> delete from thwm_gwactivity_logs where login_time < '2020-01-01';
Query OK, 235 rows affected (0.009 sec)
Related Articles: