InnoDB log file size – the size of the transaction log files that are needed to cancel transactions and restore the database in case of failure.
First, let’s look at the current value (I have 50331648 bytes displayed):
mysql -u root -p
SELECT @@innodb_log_file_size;
show variables like 'innodb_log_file_size';
show variables like 'innodb_log_files_in_group';
exit
To change the value, open the MySQL server configuration file in a text editor, for example:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
And specify the size:
innodb_log_file_size = 1G
innodb_log_files_in_group can be omitted, since it defaults to 2, that is, in our case, two 1Gb files will be created.
Usually innodb_log_file_size is 25% of innodb_buffer_pool_size, that is, for example if innodb_buffer_pool_size = 2G, then you can specify innodb_log_file_size = 256M.
Stop the MySQL server:
/etc/init.d/mysql stop
Let’s look at the current size of log files (ib_logfile0 and ib_logfile1):
ls -lah /var/lib/mysql/
Let’s start the MySQL server:
/etc/init.d/mysql start
Check the logs, make sure that InnoDB saw that the value of innodb_log_file_size was changed, closed and deleted the old log files, and then created and opened new log files:
tail -n 100 //var/log/mysql/error.log
Make sure that the size of the log files has changed:
ls -lah /var/lib/mysql/
See also my articles:
Configuring InnoDB Buffer Pool size in MySQL
Installing and using mysqltuner.pl
Solution “InnoDB Error ib_logfile0 is of different size”