Ibdata1 filling up disk space

[bksales]

Had the same issue a lot of people have had, though this one filled up very quickly so I think there is something wrong. I tried deleting everything out of the cel table and then optimizing said table, but that caused the database to double in size. What are my options? Is there a way to delete the temporary table it built while trying to optimize?

[bksales]

I’m assuming it’s the CEL table, but dont know for sure.

It’s grown another couple hundred mb since posting this.

[billsimon]

The bad news is that the file cannot be shrunk. But it can be recreated and will be smaller.

Delete any data you don’t want from the CDR and CEL tables using mysql CLI or other tool.

Then use steps like these: How to Shrink MySQL ibdata1 Size using innodb_file_per_table (or search for similar instructions on Google using search terms “shrink ibdata1”)

In short, the process is to use mysqldump to backup your databases, drop the databases, delete the large ibdata file, and then restore the databases.

[bksales]

Thanks. Will need to practice my sql. Can I optimize each of these to create a copy?

Or do I need to go into each database and optimize each table? Hoping not.

[billsimon]

Optimize won’t do anything for you. Don’t bother. See MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Statement . None of the conditions listed there apply.

[spioli (Santiago Pioli)]

Just dump your Asterisk and asteriskcdrdb databases. As @billsimon mentioned, it’s going to take less time if you remove old entries first.
Drop both databases and then stop the mysqld service, remove the ibdata1 files + log files. Start the service back and restore the backed up databases.

[dicko]

And then do the same in a few months when the cel database grows like Topsy :-)’

(sounds like a cronjob to me)

[bksales]

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 asterisk > asterisk.sql;

then log into myqsl and drop the databases

mysql
DROP DATABASE asterisk;
DROP DATABASE asteriskcdrdb;

Then stop mysql, the command is now service mariadb stop

Delete the ibdata1 and the two log files

Restart mariadb using service mariadb restart , this also takes a long while (like 2 minutes)

Then in the CLI (not logged into mysql) load the tables you backed up

mysql asteriskcdrdb < asteriskcdrdb.sql
mysql asterisk < asterisk.sql