Delete old data from database

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

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:

To check the disc space use below command:

df -h

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

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

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

2. Delete data from voipswitch database

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

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:

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';

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