MySQL service stuck at starting after increasing ‘innodb_log_file_size’

MySQL

Cross posting this question here, since I haven't gotten an answer there: https://superuser.com/questions/1476105/mysql-service-stuck-at-starting-after-increasing-innodb-log-file-size

Good day everyone, We are trying to install the 'ESET Enterprise Inspector' on a server, which requires mysql. we installed MySQL Server 8.0 in the requirement page it says we have to use these parameters:

But when we try to start the mysql service, the progress bar goes to end (for two minutes) and it says "the service did not respond to the start or control request in a timely fashion" and it is stuck in starting status. the eset installer doesn't continue to install if we haven't used those parameters. The server has 32G of ram and we use innodb_buffer_pool_size=26G and innodb_log_file_size=13G

Does anyone know why the service doesn't start? Here is the my.ini file. Thanks in advance.

Best Answer

I recommend you change back that setting.

The problem is that the old versions of MySQL did not have a way to change that setting without some extra manual effort, namely removing the log files.

Unless the setting was terribly small, the failure to change it won't impact performance much.

Hmmm... Are you upgrading from some old version to 8.0? If so, there are other steps.

Meanwhile,...

Increase the value of open_files_limit to at least 30000.

That seems very high, unless you expect to have thousands of tables, which is another problem to discuss.

Change the value of innodb_flush_log_at_trx_commit to 0.

There are 3 options for that variable; I wonder why they picked the worst.

Related Solutions

TheSQL 5.1 changed innodb_log_file_size getting error on restart

Yup, you'll need to remove the log files to change their sizes. They're in where ever your DATADIR is. Just make sure you remove them when the server is shutdown. Then restart and you'll be fine (it will recreate them).

DO NOT remove any ibdata files, though. That has your actual persistent data.

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

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.

Best Answer

Related Solutions

TheSQL 5.1 changed innodb_log_file_size getting error on restart

Thesql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

Related Question