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.

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size to 4G and later 1G (also 1024M) in addition to the log file size, but mysql wont start with those values. If I put it back to 512M mysql starts fine.

How can I solve this? My server is a 16GB one, and accordign to Webmin sysinfo:

Real memory 15.62 GB total, 3.13 GB used

Meanwhile I found the error log as well:

` 120529 10:29:32 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

120529 10:29:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

120529 10:29:33 [Note] Plugin 'FEDERATED' is disabled.

120529 10:29:33 InnoDB: The InnoDB memory heap is disabled

120529 10:29:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins

120529 10:29:33 InnoDB: Compressed tables use zlib 1.2.3

120529 10:29:33 InnoDB: Using Linux native AIO

120529 10:29:33 InnoDB: Initializing buffer pool, size = 1.0G

120529 10:29:33 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

share|improve this question
    
Can you please share the error logs for that... –  Abdul Manaf May 29 '12 at 8:49
    
Thanks, I managed to find it. I think this will be easier. I should have also added initially that I increased the log file size as well to match the increase in the buffer pool (I did add this info now). –  giorgio79 May 29 '12 at 9:06
    
Ok @giorgio79... –  Abdul Manaf May 29 '12 at 9:09
    
Try to delete the log file(s). –  dezso May 29 '12 at 12:55
    
Did you delete log file size first? did you drop all the databases first? Please add more info about what did you do exactly? –  john.locke May 29 '12 at 14:23
show 1 more comment

3 Answers 3

up vote 5 down vote accepted

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

share|improve this answer
    
Thanks, yep I did not delete them first. Just wanted to see how mysql behaves. After performing step 3 restarting worked. –  giorgio79 May 30 '12 at 9:22
    
I think one shouldn't delete the log files, but rather move them elsewhere, and delete them a while later when you've successfully changed the log file size. Otherwise, you'd be in trouble if MySQL crashed before you ran service mysqld stop. –  KajMagnus Oct 11 '13 at 7:48
add comment

Based on the error in the log, I'm guessing you did this:

  • shut down mysql
  • edited my.cnf to change the innodb log file size
  • tried to start mysql (then it failed)

If you change the log file size, you need to remove the old log files. Innodb will not start successfully if the existing files do not match the specified size in the config file. If you move them elsewhere, innodb will create new transaction log files of the correct size when it starts.

I would recommend moving the old files to another directory instead of just deleting them, until the server is up and running with new log files and everything looks OK.

share|improve this answer
add comment

The buffer_pool should be set to about 70% of available RAM if you are running InnoDB only.

The log size does not matter a lot. The optimal is to set it so that (Uptime * innodb_log_file_size / Innodb_os_log_written) is roughly 3600 (1 hour).

To change the log size, one must

  1. shut down mysqld cleanly
  2. delete the value in my.cnf (my.ini)
  3. delete the log files
  4. retstart -- new log files will be rebuilt.
share|improve this answer
    
Thanks, yes this is a correct answer as well. I could only accept one though. +1d this too. –  giorgio79 May 30 '12 at 9:23
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.