Deleting entries in the mysql database table gwactivity_logs

Deleting entries in the Ezeelogin database table gwactivity_logs for house keeping.

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

ssh gateway activity log

 

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

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

 

 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_gwactivity_logs where login_time < '2020-01-01';

Query OK, 235 rows affected (0.009 sec) 

 

 

 

 

 

 

0 (0)
Article Rating (No Votes)
Rate this article
    Attached Files
    There are no attachments for this article.
    Related Articles RSS Feed
    Jump server user password expired
    Viewed 85 times since Tue, Dec 22, 2020
    404 error on login with nginx
    Viewed 3769 times since Wed, Jun 14, 2017
    main admin user account suspended after entering incorrect security code
    Viewed 3446 times since Wed, Jun 14, 2017
    npm ERR! cb.apply is not a function
    Viewed 1498 times since Wed, Oct 7, 2020
    no matching host key type found. Their offer: ssh-rsa,ssh-dss [preauth]
    Viewed 2758 times since Mon, Jan 28, 2019
    Unable to connect to your database server using the provided settings.
    Viewed 9191 times since Thu, Jun 15, 2017
    Google Recaptcha shutdown issue
    Viewed 1156 times since Tue, Jul 3, 2018
    Will i be able to access my server if the ezeelogin license server goes down?
    Viewed 2197 times since Wed, Jun 14, 2017
    Error: Reached maximum number of servers allowed by current license. Please upgrade license to add more servers.
    Viewed 2546 times since Thu, Nov 2, 2017
    Error on login to Cpanel/WHM from ezeelogin {"status":"success"}
    Viewed 420 times since Thu, Nov 12, 2020