Disk full - consumed by ibdata1 MySQL file


(ElectsysTech) #1

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
    tables
  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
celsettings

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


#2

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.


(system) closed #3

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.