Reclaim disk space ibdata1 file in /var/lib/mysql is huge

[kr3wchris (Chris King)]

Our FreePBX hard drive is getting filled up and need to find the proper method to deal with this.

In my /var/lib/mysql folder there is a 81 gigabyte file called ibdata1

I’m assuming the file is growing larger overtime because of CDRs and CELs accumulating. I’m trying to figure out a way I can remove all CDR and CELs that are older than 1 year and also shrink the ibdata1 file as much as possible.

[root@freepbx mysql]# ls -lh
total 81G
-rw-rw----. 1 mysql mysql  16K Nov 12 09:13 aria_log.00000001
-rw-rw----. 1 mysql mysql   52 Nov 12 09:13 aria_log_control
drwx------  2 mysql mysql  16K Nov 15 08:02 asterisk
drwx------  2 mysql mysql  132 Feb 28  2022 asteriskcdrdb
-rw-rw----  1 mysql mysql  81G Nov 17 13:12 ibdata1
-rw-rw----  1 mysql mysql 5.0M Nov 17 13:12 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Nov 17 13:11 ib_logfile1
drwx------  2 mysql mysql   20 Oct 14  2021 iot
drwx------. 2 mysql root  4.0K Oct 14  2021 mysql
srwxrwxrwx  1 mysql mysql    0 Nov 12 10:52 mysql.sock
drwx------. 2 mysql mysql 4.0K Oct 14  2021 performance_schema

[billsimon]

Ibdata1 filling up disk space

Appreciate it. Dumping the asteriskcdrdb database after having deleted CEL data older than 6 months was only 740 mb. If it grows really quick again I’ll look into storing it remotely. In the interest of having it fully documented for anyone else like me that isnt as versed in mysql as maybe they could be First create copies of the databases from the CLI. If you want to trim down the CEL table you can do that first. cd /var/lib/mysql mysqldump asteriskcdrdb > asteriskcdrdb.sql; mysqldump ast…

[kr3wchris (Chris King)]

I saw that article in my research earlier. Will that delete all my CDRs and CELs too? Is there not mysql commands that need to be run on the database as well? I just want to make sure myself and others can still pull CDR reports and view CELs for up to a year.

[billsimon]

The process described won’t delete any records. You should do that first.

You can run SQL queries like this to trim your CDR and CEL tables:

delete from cdr where calldate < date_sub(now(), interval 1 year);

delete from cel where eventtime < date_sub(now(), interval 1 year);

And then proceed with the dump / delete / restore and you will have much smaller files.