Habilitar InnoDB en MySQL

InnoDB es uno de los motores de almacenamiento que incluye MySQL por defecto desde hace varias versiones, y a partir de la 5.5 va a ser el utilizado por defecto en decrimento de MyISAM. Su características principales son el soporte de transacciones, lo que permite una gestión de los bloqueos a nivel de tabla/fila mucho más inteligente, y también permite realizar backups incrementales en caliente, pero además tiene soporte de integridad referencial, por lo que podemos crear claves ajenas o foráneas entre tablas.

La versión de Drupal de alto rendimiento Presflow recomienda InnoDB como storage engine y Drupal 7 lo utiliza por defecto, así que es una buena recomendación habilitarlo en nuestros sistemas.

Foto: Iban Nieto

Este artículo no pretende ser una comparativa de que engine es mejor o de qué sistema de base de datos es mejor, simplemente es un recopilatorio de soluciones para quien desee habilitar InnoDB en su sistema.

Habilitar InnoDB si no aparece ejecutando el comando show engines

Lo primero que tenemos que hacer es conectarnos con un usuario con suficientes privilegios a nuestro MySQL y ejecutar el comando show engines.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | 
| ARCHIVE    | YES     | Archive storage engine                                         | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | 
+------------+---------+----------------------------------------------------------------+

Si InnoDB no aparece en el listado de show engines, deberemos hacer que MySQL lo cargue, para esto, tenemos algunas opciones, y según nuestra configuración funcionará una u otra:

Utilizando AppArmor: Si estamos usando AppArmor, deberemos editar el fichero /etc/apparmor.d/usr.sbin.mysqld y añadirle las siguientes líneas:

/usr/lib/mysql/plugin/ r, 
/usr/lib/mysql/plugin/* mr,

Después recargamos AppArmor y reiniciamos MySQL, por ejemplo así:

sudo /etc/init.d/apparmor reload
sudo /etc/init.d/mysql restart

Si no usamos AppArmor, tenemos la opción de cargar las librerías en el fichero my.cnf de MySQL que suele estar en /etc/my.cnf o en /etc/mysql/my.cnf. Si estamos trabajando en una versión suficientemente moderna, podremos añadirle las librerías de alto rendimiento recientemente incluidas con la distribución de InnoDB como muy bien apunta NITEMAN en los comentarios, para ello le añadiremos las siguientes líneas al comienzo de la sección de InnoDB

ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;
  innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;

Esto hará que la carga de las librerías de innodb se fuerce e ignore la versión por defecto, una vez cambiado el fichero, reiniciamos MySQL y en el show engines, ya nos debería aparecer el engine como disponible.

Si InnoDB ya aparece como motor disponible haciendo show engines

Si cuando nos conectamos con un usuario con suficientes privilegios y ejecutamos show engines, InnoDB ya aparece como disponible, pero no está habilitado, como por ejemplo así:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | 
| ARCHIVE    | YES     | Archive storage engine                                         | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | 
| InnoDB     | NO     | Supports transactions, row-level locking, and foreign keys      |
+------------+---------+----------------------------------------------------------------+

La solución pasa por modificar nuestro fichero de configuración de MySQL para decirle al sistema que utilice InnoDB, editamos el my.cnf, que habitualmente suele estar en /etc/my.cnf o /etc/mysql/my.cnf y buscamos una línea que pone skip-innodb, la comentamos y ponemos una serie de parámetros básicos para nuestras bases de datos en InnoDB, por ejemplo:

# Comentamos la linea para que tome InnoDB como opción.
# skip-innodb
# Directorio de datos de MySQL
innodb_data_home_dir =  /var/lib/mysql
# Funcionamiento de los ficheros de datos
innodb_data_file_path = ibdata1:10M:autoextend
#Ubicación de los ficheros de log
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_arch_dir = /var/lib/mysql/arch
# Tamaño de la memoria para InnoDB
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 2M
# Tanali de kis ficheros de log, log file suele ser el 25% del pool size.
innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
# Parámetros para control de transacciones
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Nota importante: Las ubicaciones y los tamaños de los ficheros deberán ser acordes al tamaño, uso y configuración de cada caso, estos son solo a modo de ejemplo, podéis encontrar un listado de todos los disponibles en la documentación de MySQL.

Recomendación muy aconsejable, siempre que cambieis el tamaño de los ficheros de log para InnoDB (parámetro innodb_log_file_size), es vital que borreis los antiguos porque de lo contrario la base de datos no arrancará. Esos ficheros se llaman ib_logfile_* (con un número al final) y se encuentran donde tengáis configurado el directorio de logs (parámetro innodb_log_group_home).

Después de cambiar el fichero de configuración, reiniciamos MySQL, con /etc/init.d mysql restart o service mysql restart o vuestro método preferido y en el show engines, ya podéis ver InnoDB con el valor Support a YES.

Hacer que InnoDB sea el motor por defecto

Puede que ya tengais InnoDB activado, pero que queráis que sea el engine por defecto, esto se comprueba con show engines:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | 
| ARCHIVE    | YES     | Archive storage engine                                         | 
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance         | 
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     |
+------------+---------+----------------------------------------------------------------+

Esto se consigue simplemente editando el fichero my.cnf y añadiendole o cambiandole el siguiente parámetro:

default-storage-engine=InnoDB

Convertir tablas de MyISAM a InnoDB

Finalmente, puede que ya tengamos bases de datos o tablas en MyISAM que queramos convertir a InnoDB una vez que ya hemos conseguido habilitarla, para esto yo sugeriría dos métodos.

El primero es utilizando ALTER TABLE, en cada tabla, esto es efectivo si son pocas tablas o el cambio es muy localizado, ejemplo:

ALTER TABLE mybbdd.mitabla engine = InnoDB

Si por el contrario, necesitamos convertir bases de datos enteras, seguramente querremos echarle un vistazo a un script que suele venir con algunas versiones, normalmente ubicado en /usr/bin/mysql_convert_table_format es un proceso que, invocado desde línea de comandos permite convertir todas las tablas de un esquema o base de datos de un engine a otro. Tendremos que editar este fichero para modificar el Engine por defecto de conversión, ya que viene configurado para MyISAM, en la línea que pone $opt_engine, ponemos InnoDB

$opt_engine="InnoDB";

Y podremos ejecutar el fichero desde línea de comandos, pasándole como parámetro el nombre de la base de datos:

./usr/bin/mysql_convert_table_format nombre_bbdd

Recursos

Para algunos de los métodos descritos me he basado en información de estos recursos, puede que queráis echarles un vistazo para completar información:

categorías: 

Comentarios

Buenas,

Quería precisar que las lineas:
"
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;
innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;

"

Lo que hacen es activar el plugin de alto rendimiento desarrollado por Oracle y que se incluye no hace demasiado tiempo en la distribución estandar de MySQL, no la versión normal del motor InnoDB (fuente: http://download.oracle.com/docs/cd/E17952_01/refman-5.1-en/replacing-bui...)

Por ese motivo es posible que dependiendo de nuestra versión (ej. Debian Lenny sin paquetes de dotdeb.org) esa configuración nos de problemas.

Salu2

<p>Muchas gracias por el aporte tocayo! lo he añadido ya en el artículo :)</p><p>Tenía la duda de por qué hacía falta el&nbsp;ignore_builtin_innodb, pero ahora ya lo tengo claro :)&nbsp;</p>

Excelente artículo pedro como siempre, gracias!

Hola Pedro,

Muy buen artículo, gracias por compartirlo.

Solo aclarar un tema porque no lo veo muy claro en el artículo, la reciente versión de MySQL (MySQL 5.5) trae de manera integrada (Build-In) el llamado InnoDB Plugin usado en la versión 5.1. Es decir, MySQL 5.5 usa InnoDB 1.1, que contiene toda la funcionalidad del InnoDB Plugin además de nuevas características y mejoras.

Sin embargo, como indica la documentación, InnoDB 1.1 se recomienda para pruebas de compatibilidad, rendimiento y funcionalidad pero no para despliegues de misión crítica por el momento.

Un saludo.

<p>William, gracias por aclararlo.</p>

solo para enviar mi respuesta yo batalle mucho para habilitar esto y les tengo la unika solucion ke me a funcionado, ya que en algunas me pedia modificar archivos my.ini y otros ademas reiniciar mysql pero todo era asi de sencillo...
desistale appserv y al iniciar la instalacion una vez mas me di cuenta de que hay una pestaña que dice enable innodb que no estaba palomiada, la palomie y segui todo igual y en phpmyadmin al crear la tabla ya me apare innodb, y al poner la clave en consulta me aparece habilitado

hola como le hago para editar el my.cnf en phpmyadmin que esta en un host gratis?

Codial saludo;
Soy un poco nuevo en el mundo MySQL, tengo instalada la versión WamperServer 2.1 y su motor de almacenamiento está por defecto InnoDB, tengo un problema. Cuando realizaba bases de datos en la versión 2.0 del Wamperver cuyo motor estaba en MyISAM y generaba los archivos de su tablas .frm, .MYD y .MYI, al llevarlas a otro equipo funciona (se ven). Ahora, con el motor InnoDB crea un solo archivo y al llevarlo a otro equipo no se ven las tablas a pesar de estar copiadas en le directorio específico. ¿Qué hago para que sea compatible?

Gracias.

a mi me aparece es: innodb DISABLE en este caso, como puedo hacer?

Excelente, me fue de mucha ayuda, muchas gracias. No quería funcionar reiniciando el mysql, tuve que reiniciar el equipo y así funcionó. Saludos.

Añadir nuevo comentario

 
 
 

Creative Commons License
Excepto donde se indique lo contrario, el contenido de este sitio está sujeto a una licencia de Creative Commons.