Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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? – Derek Downey Sep 5 '11 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 '11 at 14:47
I'll post the error logs after checking a few more things. – markus Sep 5 '11 at 14:54
@marcus to change the innodb_log_file_size all you should have to do is delete the log files (not ibdata). See… – Derek Downey Sep 5 '11 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 '11 at 21:22
up vote 5 down vote accepted

markus you want to read the very last paragraph on docs for resizing log file - 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.

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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