This tutorial will provide a quick guide how to backup your data and delete old date like calls, calls failed and chats from database in Linux

1. Make full backup of all databases

1.1 Login to the system as root

Use command “sudo su” and type the root password if required

sudo su
image-20240207-101729.png

1.2.Create backup folder with current date and check the folder

To create the folder use below commands:

cd /home
mkdir 20240207

To access the new created folder use the below command:

cd 20240207

1.3 Backup the data with commands:

Before creating the backup please make sure that you have enough disc space on your drive

To check the disc space use below command:

df -h

In below example you can see the disc has 75GB free space:

image-20240207-110627.png

To create the backup use the commands below one by one in the console:

mysqldump -u root --databases api  > /home/20240207/api.sql
mysqldump -u root --databases bi  > /home/20240207/bi.sql
mysqldump -u root --databases storage > /home/20240207/storage.sql
mysqldump -u root --databases invoices > /home/20240207/invoices.sql
mysqldump -u root --databases voipswitch > /home/20240207/voipswitch_full.sql
mysqldump -u root --databases core > /home/20240207/core.sql

If you just want to clean the calls/callsfailed data only voipswitch and bi databases have to be backed up, but we always advice to make full backup

To check the backup progress you can use a second instance of SSH and check MySQL status:

watch 'echo "show processlist;" | mysql -uroot';

1.5 Make sure the DB has been properly backup and the files exist in the folder

To verify if the database has correctly backup use below commands in the console:

cd /home/20240207
ls -ltr
image-20240207-102730.png

2. Delete data from voipswitch database

Before deleting any data from database always make sure that you have a valid backup!!!

If you delete the data without backup the data is lost and can not be recovered!!!

Please notice that deleting data from database can take few minutes or few hours. All depends how big is your current database.

The operation to delete data should be never done when you have life traffic as this can impact the database performance as well as switch performance and even lock the tables in the DB

2.1 Delete old calls from voipswitch database

To delete old calls base on date you need to use this command:

delete from voipswitch.calls where call_start<date;

Replace the “date“ with the date till which you want to remove the calls example:

delete from voipswitch.calls where call_start< '2023-10-25';

2.2 Delete old failed calls from voipswitch database

To delete old calls base on date you need to use this command:

delete from voipswitch.callsfailed where call_start<date;

Replace the “date“ with the date till which you want to remove the calls failed example:

delete from voipswitch.callsfailed where call_start< '2023-10-25';

2.3 Delete old calls_costs from voipswitch database

In the voipswitch database the calls_cost table contains all the billing data for invoices and can also get very big. This table does not contain any date column so the data has to be deleted base on id_cc column from the calls table.

To delete data from calls_cost table first you have to delete calls.

After deleting calls you need to get the current min ID_CC with command:

select min(id_cc) from voipswitch.calls;

This entry will give you a value which you will later use for clearing the data:

image-20240207-103720.png

To delete the data from this table you will use a command:

delete from voipswitch.calls_costs where id_cc < X;

In the example we can see the returned value was 401. The X has to be replaced with the value:

delete from voipswitch.calls_costs where id_cc < 401;

3. Delete data from bi database

3.1 Delete data from table bi.da_conversation_history

3.1.1 Delete old calls only without deleting other data

To delete calls from the table you use a command:

delete from bi.da_conversation_history where id_ts<date and source='call';

Replace the “date“ with the date till which you want to remove the calls example:

delete from bi.da_conversation_history where id_ts<'2023-10-26' and source='call';

3.1.2 Delete old failed calls only without deleting other data

To delete calls failed from the table you use a command:

delete from bi.da_conversation_history where id_ts<date and source='callfailed';

Replace the “date“ with the date till which you want to remove the failed calls example:

delete from bi.da_conversation_history where id_ts<'2023-10-26' and source='callfailed';

3.1.3 Delete all old data from this table (calls, failed calls, chats and sms)

To delete all data from the table you use a command:

delete from bi.da_conversation_history where id_ts<'date';

Replace the “date“ with the date till which you want to remove the data example:

delete from bi.da_conversation_history where id_ts<'2023-10-26';

3.2 Delete data from table bi.da_conversation_history_summary

3.2.1 Delete old calls only without deleting other data

To delete calls from the table you use a command:

delete from bi.da_conversation_history_summary where id_ts<date and source='call';

Replace the “date“ with the date till which you want to remove the calls example:

delete from bi.da_conversation_history_summary where id_ts<'2023-10-26' and source='call';

3.2.2 Delete old failed calls only without deleting other data

To delete calls failed from the table you use a command:

delete from bi.da_conversation_history_summary where id_ts<date and source='callfailed';

Replace the “date“ with the date till which you want to remove the failed calls example:

delete from bi.da_conversation_history_summary where id_ts<'2023-10-26' and source='callfailed';

3.2.3 Delete all old data from this table (calls, failed calls, chats and sms)

To delete all data from the table you use a command:

delete from bi.da_conversation_history_summary where id_ts<'date';

Replace the “date“ with the date till which you want to remove the data example:

delete from bi.da_conversation_history_summary where id_ts<'2023-10-26';

You can monitor the progress of backup or delete data from DB using below command on another SSH session 

watch 'echo "show processlist;" | mysql -uroot';
image-20240207-105948.png