Mysql: Innodb storage issues- ibdata1

Published by

Posted on November 21, 2011

We encountered a System Administrator Nightmare. Received notice that a server was running low on /var storage. After checking the server we found that the mysql folder was using 9GB. Come to find out it was only one particular file that was using 7GB which was ibdata1. When the server was launch we did not disable Innodb which use this file for storage. The file grows and grows without regard to server space. There is no truncate or shrink option for this file.
So my question is why use Innodb? Send me a response on Twitter , G+ or Facebook. I want to know seriously , you can my contact info on the about page

Below is what I had to do to correct the issue.
1. Perform a entire dump of OF THE databases on the server. Roughly about 2GB of data .
2. Stop the service
3. Move the current mysql data folder to another partition.
Command: mv /var/lib/mysql /home/mysql_backup
4. Recreate the folder /var/lib/mysql
Command: mkdir /var/lib/mysql and then: Chown mysql:mysql /var/lib/mysql
Note: if the permissions are not correct you will receive a error such as “ERROR! Manager of pid-file quit without updating file.”
5. next you will need to locate and run mysql_install_db
Note: mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist.
6. you may want to add the following to your /etc/my.cnf file
innodb_file_per_table

7. Start the service
8. Enter the mysql console and run each command individually
SET FOREIGN_KEY_CHECKS=0;
SOURCE backup.SQL; < when running this command it may be best to be in the directory when you backed up the mysqldump. or you can specify the path to the file. Replace backup.sql with the actual file name of the dump SET FOREIGN_KEY_CHECKS=1; 9. Restart the service and confirm the data. Estimated turn around time: 1-2 hours depending on the database size MysqlDump

Categories: Uncategorized