Deleting entries from mysql database table webactivity_logs
Remove entries from the webactivity_logs table in a MySQL database?
Overview: This article explains how to delete entries from Ezeelogin webactivity_logs Table. It would be required to reduce the size of the webactivity_logs table 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 webactivity logs
Step 1(A): To view the webactivity logs login to Ezeelogin GUI, navigate to Users -> WebActivity -> Web Activity Logs.
2. Export webactivity logs
Step 2(A): Before truncating the webactivity_logs you can export the logs from GUI for audit purposes under Users -> Web 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 webactivity logs
Step 3(A): Run the below command to generate a backup of the webactivity 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)webactivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)webactivity_logs_$(date +%Y-%m-%d).sql
Step 3(B): After taking backup, run below command to delete the entries in the table webactivity _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_webactivity_logs where created < '2023-01-01' "
Step 4: The webactivity _logs table would looks as follows:
mysql> use db_name;
mysql> desc hnil_webactivity_logs;
+-------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+-------------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| user_id | int unsigned | NO | MUL | 0 | |
| controller | varchar(200) | YES | MUL | NULL | |
| function | varchar(200) | YES | | NULL | |
| objective | text | YES | | NULL | |
| description | text | YES | | NULL | |
| finished | tinyint unsigned | NO | | 0 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+------------------+------+-----+-------------------+-------------------+
8 rows in set (0.04 sec)
Related Articles:
Deleting entries in the mysql database table serveractivity_logs
Deleting entries in the mysql database table gwactivity_logs