Recently I changed a few settings in my mysql configuration. I did the following: - stop mysql - save the changed settings - delete all ibdata and ib_logfiles - start mysql

Normally this works fine but recently, everytime I do it, all my InnoDB tables get currupted. What am I doing wrong?

My settings:

# The MySQL server
port        = 3306
socket      = /var/lib/mysql/mysql.sock
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
thread_cache = 16
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

server-id   = 1

# Uncomment the following if you want to log updates

# Uncomment the following if you are NOT using BDB tables

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 30M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 400M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

max_allowed_packet = 16M

# Remove the next comment character if you are not familiar with SQL

key_buffer = 8M
sort_buffer_size = 8M

key_buffer = 8M
sort_buffer_size = 8M

  • 1
    Why are you deleting the ibdata file? This is probably the source of your corrupted tables. Can you post error logs info from mysql startup? Sep 5, 2011 at 13:50
  • When I change the innodb_log_file_size, I have to remove the file. The same thing happens when I do NOT delete this file.
    – markus
    Sep 5, 2011 at 14:47
  • I'll post the error logs after checking a few more things.
    – markus
    Sep 5, 2011 at 14:54
  • 1
    @marcus to change the innodb_log_file_size all you should have to do is delete the log files (not ibdata). See dba.stackexchange.com/questions/1261/… Sep 5, 2011 at 14:56
  • @DTest, yes you're absolutely right. I don't know where I had it from that the ibdata files had to be deleted as well...
    – markus
    Sep 5, 2011 at 21:22

2 Answers 2


markus you want to read the very last paragraph on mysql.com docs for resizing log file http://dev.mysql.com/doc/refman/5.1/en/innodb-data-log-reconfiguration.html - you only need to remove the ib_ log files and not the ibdata1 file

Stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.


I'm not sure what you're trying to accomplish by resizing and deleting log files, but I'll take it at face value that it's a required workflow.

If you wish to delete your ibdata and ib_logfiles, I recommend setting up your config file include multiple tablespaces:


Then import/create your schemas.

This will separate out the table data and structure from the default location, thus making it less confusing for you for your re-size and delete required work flow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.