Deleting entries in the mysql database table serveractivity

Deleting entries in the mysql database table serveractivity_logs

385 Vishnupriya March 23, 2021 General 4134

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.

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.

 #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’" 

The serveractivity _logs mysql table structure would looks as follows.

[root@~gate]# MariaDB [ezlogin_wggmp]> desc thwm_serveractivity_logs;

+------------------+---------------------------------+------+-----+---------------------+--------------------------

+------------------+---------------------------------+------+-----+---------------------+-------------------------------+

| 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 | |

| input_idle_time | int(10) unsigned | NO | | 0 | |

| output_idle_time | int(10) unsigned | NO | | 0 | |

+------------------+---------------------------------+------+-----+---------------------+---------------------------

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) 