Removing entries from the serveractivity_logs table in MySQL
Overview: This Article Explains How to Reduce the Size of the Ezeelogin serveractivity_logs Table: It details the necessity of truncating the table based on login times to improve software upgrade speed, outlines steps for exporting logs for audit purposes, and provides commands for backing up and deleting older entries.
It would be required to reduce the size of the Ezeelogin mysql database table serveractivity_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 serveractivity_logs would be displayed in the GUI under Users ->Server Activity ->Server Activity Logs as shown.
Before truncating the serveractivity_logs you can export the logs from GUI for audit purposes under Users ->Server Activity ->Export
The following command would delete all entries with Login Time before 01 January 2020 from the serveractivity_logs table in the Ezeelogin mysql database.
#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_serveractivity_logs > ezlogin_wggmp_thwm_serveractivity_logs.sql
#Deleting the entries in the table serveractivity _logs using the Ezeelogin query runner script.
[root@~gate]# php /usr/local/ezlogin/ez_queryrunner.php "delete from prefix_serveractivity_logs where login_time < '2020-01-01'"
Note:
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.
The serveractivity _logs mysql table structure would looks as follows.
[root@~gate]# MariaDB [ezlogin_wggmp]> desc thwm_serveractivity_logs;
+------------------+---------------------------------+------+-----+---------------------+--------------------------
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------------------+------+-----+---------------------+-------------------------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | 0 | |
| server_id | int(10) unsigned | NO | | 0 | |
| gwactivity_id | int(20) unsigned | NO | | 0 | |
| login_time | datetime | YES | | NULL | |
| logout_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| input_idle_time | int(10) unsigned | NO | | 0 | |
| output_idle_time | int(10) unsigned | NO | | 0 | |
| status | varchar(100) | NO | | | |
| type | enum('SCP','SHELL','CMD','RDP') | NO | | SHELL | |
+------------------+---------------------------------+------+-----+---------------------+---------------------------
Alternatively, the mysql command to delete the entries based on the user Login Time would be
mysql ezlogin_wggmp
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4241
Server version: 10.4.14-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [ezlogin_wggmp]>
MariaDB [ezlogin_wggmp]> delete from thwm_serveractivity_logs where login_time < '2020-01-01';
Query OK, 235 rows affected (0.009 sec)
Related Articles
Deleting entries in the mysql database table gwactivity_logs
How to truncate the ssh session logs recorded
Migrating Ezeelogin database manually when the table size is large for faster Ezeelogin software upgrades
ERROR db sshlogs: Incorrect string value