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