/
How to delete old data from database

How to 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

Please notice that you can do this at your own risk and we do not take responsibility for any damage caused by incorrect use of scripts or managing database !!!

If you have any issues following the below tutorial we can delete the date for you and you can contact our sales for such additional support service.

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:

Please make sure that after every backup the echo command will return 0 (backup is valid)

The echo command can return 3 results:

0 for Success
1 for Warning
2 for Not Found

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

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 dump 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 call records from the voipswitch.calls table

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 call records from the voipswitch.callsfailed table

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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_cost records from the voipswitch.calls_costs table

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.

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 old data from the bi.da_conversation_history table

3.1.1 Delete old call records from the table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 call records from the table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 the table (calls, failed calls, chats and sms)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 old data from the bi.da_conversation_history_summary table

3.2.1 Delete old call records from the table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 call records from the table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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 the table (calls, failed calls, chats and sms)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

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

4. Delete data from core database

4.1 Delete old call records from the core.calls_history table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

To delete calls from the table you use a command:

delete from core.calls_history where created<date and connected is not NULL;

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

delete from core.calls_history where created<'2023-10-26' and connected is not NULL;

4.2 Delete old failed call records from the core.calls_history table(without affecting other data)

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

To delete calls from the table you use a command:

delete from core.calls_history where created<date and connected is NULL;

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

delete from core.calls_history where created<'2023-10-26' and connected is NULL;

4.3 Delete all data from the core.calls_history table

Log in to the MySQL database if you are not already logged in

sudo mysql -u root

To delete calls from the table you use a command:

delete from core.calls_history where created<date;

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

delete from core.calls_history where created<'2023-10-26';

5. Monitoring the process

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