Save HDD space removing the old logs

If your HDD is almost full, delete (or export) the old logs data and optimize the MySQL disk utilization. 

Introduction

To perform the following queries and commands, you can use the mysql command line or a MySql client such as WorkBench or SQLyog, etc..

Depending on the database size, the following procedure can take some minutes and have an impact on the server performance.

We suggest you to execute the DB optimization when your callcenter is closed or in a low traffic time period.

Anyway, the optimization procedure needs to lock the tables so we recommend to stop all callcenter activities.

First of all, backup the data!

Perform a database dump is not a complex operation, but you need to know which is exactly the database size because you will need enough space to save the dump file.

You can get the DB size by running the following query

SELECT table_schema "Data Base Name", 
SUM( data_length + index_length ) / 1024 / 
1024 "Data Base Size in MB"
FROM information_schema.TABLES 
GROUP BY table_schema ;

Once you know how many hdd space you need to save your dump file, you can proceed to backup the database. In this example we use the MySQL command mysqldump. 

mysqldump -u root -p xcally > xcally_dump.sql

and enter the MySQL root password.

When the dump will be completed, you'll find a file like follows

-rw-r--r-- 1 root root  16084420 Nov  7 12:19 xcally_dump.sql

Delete or export the old records

To reduce the size of the database, we need to remove the old records from the tables.

In the xCally database, the tables with the larger amount of records are:

agent_log

cdr

call_log

queue_log

To check the size for each table you can just run the SQL command:

SHOW TABLE STATUS

or check the database info, for example in the SQLyog

With SQLyog we can easily delete the old records and