Deleting entries in the mysql database table serveractivity_logs
How to removing entries from the serveractivity_logs table in MySQL?
Overview: This article explains how to reduce the size of the Ezeelogin serveractivity_logs table. Reducing the size of this table is essential as it would cause the Ezeelogin software upgrade to slow down significantly ( 2-4 hours ) because of the huge size of the database table. It also outlines steps for exporting logs for audit purposes, and provides commands for backing up and deleting older entries.
1. View server activity logs
Step 1(A): To view the server activity logs login to Ezeelogin GUI, navigate to Users -> Server Activity -> Server Activity Logs.
2. Export server activity logs
Step 2(A): Before truncating the server activity logs you can export the logs from GUI for audit purposes under Users -> Server 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 server activity logs
Step 3(A): Run the below command to generate a backup of the server activity 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)serveractivity_logs > $(grep -oP 'db_prefix\s+\K\S+' /usr/local/etc/ezlogin/ez.conf)serveractivity_logs_$(date +%Y-%m-%d).sql
Step 3(B): After taking backup, run below command to delete the entries in the table serveractivity _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_serveractivity_logs where login_time < '2023-01-01'"
Step 4: The serveractivity _logs table would looks as follows:
MariaDB [ezlogin_wggmp]> desc thwm_serveractivity_logs;
+------------------+---------------------------------+------+-----+---------------------+------------------------+
Alternatively, you can remove entries using MySQL commands.
MariaDB [ezlogin_wggmp]> delete from thwm_serveractivity_logs where login_time < '2023-01-01';
Query OK, 235 rows affected (0.009 sec)
Related Articles:
Deleting entries in the mysql database table gwactivity_logs