Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

innodbMySQLmysql-5.5

I am following this solution here https://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 according 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!

Best Answer

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.

Related Solutions

Mysql – Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6

Fixed it. Listening to /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found, I took a peek inside. Turns out this is the script that's called when the startup item or the preference pane want to start or stop MySQL. Unfortunately, it doesn't seem to work out of the box anymore.

The comments near the top of the script seem to know this, and give you a list of options to make the script work. I took the easiest and added the installation path to the basedir variable assignment.

Mysql – How to safely change MySQL innodb variable ‘innodb_log_file_size’

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.