Cómo redimensionar innodb_log_file_size en MySQL

A la hora de cambiar el valor del parámetro innodb_log_file_size en MySQL, hay que tener en cuenta algunos puntos para evitar encontrarnos con que el servicio no arranca tras los cambios. Este parámetro indica el tamaño de los archivos de log que almacenarán las transacciones pendientes de commit.

Los pasos son los siguientes:

  • Parar el servidor MySQL de forma ordenada.
  • Eliminar/Mover (guardar un backup) los archivos de log actuales.
  • Cambiar el parámetro
  • Arrancar de nuevo MySQL.

Antes de parar MySQL, es necesario confirmar el valor asignado a la variable innodb_fast_shutdown:

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
{code}

If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

A partir de aquí, podemos hacer un fast shutdown de Mysql y guardar un backup de los REDO LOG actuales:

# ls -ltrh /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5.0M Aug 20 13:53 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Aug 20 13:53 /var/lib/mysql/ib_logfile1

Cambiar/añadir el parámetro en el archivo de configuración my.cnf:

innodb_log_file_size=256M

Y finalmente arrancar MySQL. En los logs podréis ver si el shutdown ha sido ordenado y si arranca correctamente. Creará automáticamente los nuevos archivos de log con el tamaño indicado:

Parada:

180820 16:14:43 [Note] /usr/libexec/mysqld: Normal shutdown

180820 16:14:43 [Note] Event Scheduler: Purging the queue. 0 events
180820 16:14:43  InnoDB: Starting shutdown...
180820 16:14:47  InnoDB: Shutdown completed; log sequence number 30 3482301584
180820 16:14:47 [Note] /usr/libexec/mysqld: Shutdown complete

Arranque:

180820 16:14:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180820 16:15:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180820 16:15:23  InnoDB: Initializing buffer pool, size = 8.0M
180820 16:15:23  InnoDB: Completed initialization of buffer pool
180820 16:15:23  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
180820 16:15:24  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
180820 16:15:25  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
180820 16:15:25  InnoDB: Started; log sequence number 30 3482301964
180820 16:15:25 [Note] Event Scheduler: Loaded 0 events
180820 16:15:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution