Disk full - consumed by ibdata1 MySQL file

[electsystech]

Version: Freepbx 14
If your system has run out of disk space, start by confirming that ibdata1 is the culprit.
A backup is recommended if you still have enough disk space to perform it. A disk that is maxed out will not be able to run the backup task.

1. SSH into your system using Putty or similar terminal software and check your disk. Username: root
   Show disk info: df -h
   cd /var/lib/mysql
   du -shx ./* | sort -h
   If ibdata1 is consuming all your data, it will show up as the largest file in this directory
2. Now that you’ve confirmed ibdata1 is consuming all your hard drive space, you need to shutdown services
   fwconsole stop
   You might also ensure MySQL is stopped
   service mysqld stop
   or
   systemctl stop mariadb
3. Download a database tool such as dBeaver and connect it to your PBX MySQL database
   Open DBeaver > Connect > Main tab
   Server host: localhost
   Port: 3306
   Database: leave empty
   Username: root
   Password: leave empty
   Local Client: MySQL Binaries
   Click on the SSH Tab:
   Host/IP: Enter IP of your PBX
   Port: 22
   Username: root
   Password: yourrootpassword
   Test for proper function and save the connection
4. Find out which table is eating up all the disk space by expanding it and looking for the file size. Likely cause is Databases > asteriskcdrdb > cel
5. Remove the data in a table:
   Right click on the table > Tools > Truncate
6. Export the tables to you PC for importing later on
   Right click on a table > Tools > Dump Database > Select Databases to dump (Make sure to include the schema tables) > Next > Select Output folder to a location on your PC > Start
7. Browse to the folder on your PC and confirm that you have a .sql file for each table.
8. Delete the ibdata1 ib_logfile0 and ib_logfile1 files
   cd /var/lib/mysql
   rm ib-logfile0
   rm ib-logfile1
   rm ibdata1
9. Restart the PBX. This will create a new ibdata1 & log files
   shutdown -r now
10. Go back to dBeaver and restore the previously dumped / truncated tables to the new database
    
    
11. Confirm your database has all it’s tables by running this command from the SSH Terminal:
    mysqlshow
    
12. Restart the PBX again
    shutdown -r now
13. From SSH console confirm new database is reduced in size
    Show disk info: df -h
    cd /var/lib/mysql
    du -shx ./* | sort -h
14. Browse to the PBX GUI and make sure the system settings all look good.

CEL (Channel Event Log) settings from GUI

Related links that might be helpful:
Backup and Restore MySQL database
Shrink MySQL ibdata1
How to save each table in your database to a separate file. I believe this file is located at /etc/my.cnf.d/server.cnf Add the following line:
innodb_file_per_table=1

Access MySQL from your SSH console
mysql -u root -p

[dicko]

Running out of disk space can be catastrophic for mysql and lots of other things, I would install logwatch and make sure you are getting it’s emails. It will warn you way before you get into deep doo-doo

Consider purging old asteriskcdrdb.cel records on a timely fashion, they can get big.