Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
[mysqld]
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

#skip-networking
server-id   = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you are NOT using BDB tables
skip-bdb

# 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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout
share|improve this question
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? –  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
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/… –  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
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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:

[mysqld]
innodb_file_per_table

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
add comment

Your Answer

 
discard

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.