Reduce InnoDB Database File Size

After the database file (ibdata1) is increased, the file size is not reduced even though records in the database are deleted. In this case, the database file size is huge while actual records in the database are small.

Unfortunately, there is no automatic command to reduce the database file size. To reduce the database file size, the database file needs to be deleted and recreated from the backup file.

  1. Stop mysql.

    # service mysqld stop
    
  2. Backup the database.

    mysqldump -u'user_id' -p'password' -databases 'db_name' > backup.sql
    
  3. Delete the database file (ibdata1) and log files (ib_logfile0 and ib_logfile1).

    # rm ibdata1
    # rm ib_logfile0 ib_logfile1
    
  4. Restart mysql.

    # service mysqld start
    
  5. Restore the database from the backup file.

    mysql -u'user_id' -p'password' < backup.sql