Tabla de contenidos
InnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDB a otra máquinaInnoDBInnoDBInnoDB y AUTOCOMMITInnoDB y TRANSACTION ISOLATION LEVELSELECT ... FOR UPDATE y
SELECT ... LOCK IN SHARE MODEInnoDBInnoDBInnoDBInnoDBInnoDB
InnoDB dota a MySQL de un motor de
almacenamiento transaccional (conforme a ACID)
con capacidades de commit (confirmación), rollback (cancelación) y
recuperación de fallas. InnoDB realiza
bloqueos a nivel de fila y también porporciona funciones de
lectura consistente sin bloqueo al estilo Oracle en sentencias
SELECT. Estas características incrementan el
rendimiento y la capacidad de gestionar múltiples usuarios
simultáneos. No se necesita un bloqueo escalado en
InnoDB porque los bloqueos a nivel de fila
ocupan muy poco espacio. InnoDB también
soporta restricciones FOREIGN KEY. En consultas
SQL, aún dentro de la misma consulta, pueden incluirse libremente
tablas del tipo InnoDB con tablas de otros
tipos.
InnoDB se diseñó para obtener el máximo
rendimiento al procesar grandes volúmenes de datos. Probablemente
ningún otro motor de bases de datos relacionales en disco iguale
su eficiencia en el uso de CPU.
A pesar de estar totalmente integrado con el servidor MySQL, el
motor de almacenamiento InnoDB mantiene su
propio pool de almacenamiento intermedio para tener un cache de
datos e índices en la memoria principal.
InnoDB almacena sus tablas e índices en un
espacio de tablas, el cual puede consistir de varios ficheros (o
particiones disco). Esto difiere de, por ejemplo, el motor
MyISAM, donde cada tabla se almacena empleando
ficheros separados. Las tablas InnoDB pueden
ser de cualquier tamaño, aún en sistemas operativos donde el
tamaño de los ficheros se limita a 2GB.
En MySQL 5.0, InnoDB viene incluido por defecto
en las distribuciones binarias. El instalador Windows Essentials
configura a InnoDB como el tipo de base de
datos MySQL por defecto en Windows.
InnoDB se utiliza en muchos grandes sitios de
bases de datos que necesitan alto rendimiento. El famoso sitio de
noticias de Internet Slashdot.org corre sobre
InnoDB. Mytrix, Inc. almacena más de 1TB de
datos en InnoDB, y otros sitios manejan una
carga promedio de 800 inserciones y actualizaciones por segundo en
InnoDB.
InnoDB se publica bajo la misma licencia GNU
GPL Versión 2 (de Junio de 1991) que MySQL. Para más
información sobre el licenciamiento de MySQL, consulte
http://www.mysql.com/company/legal/licensing/.
Información de contacto para Innobase Oy, creador del motor
InnoDB:
Sitio web: http://www.innodb.com/
Correo electrónico: <sales@innodb.com>
Teléfonos: +358-9-6969 3250 (oficina)
+358-40-5617367 (móvil)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
En MySQL 5.0, el motor de almacenamiento InnoDB
está habilitado por defecto. Si no se desean emplear tablas
InnoDB, puede agregarse la opción
skip-innodb al fichero de opciones de MySQL.
Dos recursos basados en disco muy importantes que gestiona el
motor de almacenamiento InnoDB son sus ficheros
de datos de espacios de tablas y sus ficheros de registro (log).
Si no se especifican opciones de configuración para
InnoDB, MySQL 5.0 crea en el directorio de
datos de MySQL un fichero de datos de 10MB (autoextensible)
llamado ibdata1 y dos ficheros de registro
(log) de 5MB llamados ib_logfile0 y
ib_logfile1.
Nota: InnoDB
dota a MySQL de un motor de almacenamiento transaccional (conforme
a ACID) con capacidades de commit
(confirmación), rollback (cancelación) y recuperación de fallas.
Esto no es posible si el sistema
operativo subyacente y el hardware no funcionan como se requiere.
Muchos sistemas operativos o subsistemas de disco podrían diferir
o reordenar operaciones de escritura a fin de mejorar el
rendimiento. En algunos sistemas operativos, la propia llamada del
sistema (fsync()), que debería esperar hasta
que todos los datos no guardados de un fichero se graben a disco,
en realidad puede retornar antes de que los datos se guarden en
las tablas de almacenamiento. Debido a esto, una caída del
sistema operativo o un corte en el suministro eléctrico pueden
destruir datos recientemente grabados, o, en el peor de los casos,
corromper la base de datos debido a que las operaciones de
escritura han sido reordenadas. Si la integridad de los datos es
importante, se deberían llevar a cabo algunas pruebas que simulen
caídas (“pull-the-plug”) e interrupciones súbitas,
antes de comenzar el uso para producción. En Mac OS X 10.3 y
posteriores, InnoDB emplea un método especial de volcado a
fichero llamado fcntl(). Bajo Linux, es
aconsejable deshabilitar el write-back
cache.
En discos duros ATAPI, un comando como hdparm -W0
/dev/ puede funcionar.
Hay que tener en cuenta que algunas unidades
o controladores de disco podrían estar imposibilitados de
desactivar el write-back cache.
hda
Nota: Para obtener un buen
desempeño, se deberían proveer expresamente los parámetros de
InnoDB como se explica en los siguientes
ejemplos. Naturalmente, habrá que editar la configuración para
acomodarla a los requerimientos del hardware en uso.
Para configurar los ficheros de espacio de tablas de
InnoDB, debe utilizarse la opción
innodb_data_file_path en la sección
[mysqld] del fichero de opciones
my.cnf. En Windows, se puede emplear en su
lugar my.ini. El valor de
innodb_data_file_path debería ser una lista de
una o más especificaciones de ficheros. Si se incluirá más de
un fichero de datos, habrá que separarlos con punto y coma
(';'):
innodb_data_file_path=espec_fichero_datos1[;espec_fichero_datos2]...
Por ejemplo, la siguiente es una configuración que creará explícitamente un espacio de tablas con las mismas características que el predeterminado:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend
Esto configura un único fichero de 10MB llamado
ibdata1 el cual es autoextensible. No se
suministra la ubicación del fichero, por lo tanto, el directorio
predeterminado es el directorio de datos de MySQL.
El tamaño del fichero se especifica empleando como sufijo las
letras M o G para indicar
unidades de MB o GB.
A continuación se configura un espacio de tablas que contiene un
fichero de datos de tamaño fijo de 50MB llamado
ibdata1 y un fichero autoextensible de 50MB
llamado ibdata2, ambos en el directorio de
datos:
[mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
La sintaxis completa para especificar un fichero de datos incluye el nombre del fichero, su tamaño, y varios atributos opcionales:
nombre_de_fichero:tamaño_de_fichero[:autoextend[:max:tamaño_máximo_de_fichero]]
El atributo autoextend y aquellos que lo siguen
sólo pueden emplearse con el último fichero en la línea de
innodb_data_file_path.
Si se especifica la opción autoextend para el
último fichero de datos, InnoDB incrementará
el tamaño del fichero si se queda sin capacidad para el espacio
de tablas. El incremento es de 8MB cada vez.
Si se agotara la capacidad del disco, podría desearse agregar
otro fichero de datos en otro disco. Las instrucciones para
reconfigurar un espacio de tablas existente se encuentran en
Sección 15.7, “Añadir y suprimir registros y ficheros de datos InnoDB”.
InnoDB no detecta el tamaño máximo de
fichero, por lo tanto, hay que ser cuidadoso en sistemas de
ficheros donde el tamaño máximo sea de 2GB. Para especificar el
tamaño máximo de un fichero autoextensible, se emplea el
atributo max. La siguiente configuración le
permite a ibdata1 crecer hasta un límite de
500MB:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB crea los ficheros de espacios de tablas
en el directorio de datos de MySQL en forma predeterminada. Para
especificar una ubicación expresamente, se emplea la opción
innodb_data_home_dir. Por ejemplo, para crear
dos ficheros llamados ibdata1 e
ibdata2 pero creándolos en el directorio
/ibdata, InnoDB se
configura de este modo:
[mysqld] innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Nota: InnoDB
no crea directorios, de modo que hay que estar seguro de que el
directorio /ibdata existe antes de iniciar el
servidor. Esto se aplica también a cualquier directorio de
ficheros de registro (log) que se configure. Para crear los
directorios necesarios se emplea el comando
mkdir que existe en Unix y DOS.
InnoDB forma el directorio para cada fichero de
datos concatenando el valor textual de
innodb_data_home_dir con el nombre del fichero,
agregando una barra o barra invertida entre ellos si se necesita.
Si la opción innodb_data_home_dir no aparece
en my.cnf, el valor predeterminado es el
directorio ./, lo cual indica el directorio
de datos de MySQL.
Si se especifica una cadena vacía en
innodb_data_home_dir, se pueden especificar
rutas absolutas para los ficheros de datos listados en el valor de
innodb_data_file_path. El siguiente ejemplo es
equivalente al anterior:
[mysqld] innodb_data_home_dir = innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
Un ejemplo sencillo de
my.cnf . Suponiendo que se posee
un ordenador con 128MB de RAM y un disco duro, el siguiente
ejemplo muestra posibles parámetros de configuración
InnoDB en my.cnf o
my.ini incluyendo el atributo
autoextend.
Este ejemplo satisface las necesidades de la mayoría de los
usuarios, tanto en Unix como en Windows, que no deseen distribuir
los ficheros de datos InnoDB en varios discos.
Crea un fichero de datos autoextensible llamado
ibdata1 y dos ficheros de registro (log) de
InnoDB llamados
ib_logfile0 y
ib_logfile1 en el directorio de datos de
MySQL. También, el fichero de registros archivados de
InnoDB
ib_arch_log_0000000000 que MySQL crea
automáticamente, termina ubicado en el directorio de datos.
[mysqld] # Las demas opciones del servidor MySQL pueden escribirse aquí # ... # Los ficheros de datos deben ser capaces de contener datos e índices # Hay que asegurarse de tener suficiente espacio en disco. innodb_data_file_path = ibdata1:10M:autoextend # # Establecer el tamaño del buffer en un 50-80% de la memoria del ordenador set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Establecer el tamaño del fichero de registro (log) en un 25% del tamaño del buffer set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1
Hay que asegurarse de que el servidor MySQL tiene los derechos de acceso apropiados para crear ficheros en el directorio de datos. Más generalmente, el servidor debe tener derechos de acceso a cualquier directorio donde necesite crear ficheros de datos o registro (logs).
Notar que los ficheros de datos deben ser menores de 2GB en algunos sistemas de ficheros. El tamaño combinado de los ficheros de registro debe ser menor de 4GB. El tamaño combinado de los ficheros de datos debe ser de por lo menos 10MB.
Cuando se crea un espacio de tablas InnoDB por
primera vez, es mejor iniciar el servidor MySQL desde la línea de
comandos. Entonces, InnoDB imprimirá en
pantalla la información acerca de la creación de bases de datos,
de forma que se podrá ver lo que está ocurriendo. Por ejemplo,
en Windows, si mysqld-max se ubica en
C:\mysql\bin, se puede iniciar de este modo:
C:\> C:\mysql\bin\mysqld-max --console
Si no se envía la salida del servidor a la pantalla, se puede ver
el fichero de registro de errores del servidor para averiguar lo
que InnoDB imprime durante el proceso de
inicio.
Consulte Sección 15.5, “Crear el espacio de tablas InnoDB” para un ejemplo de cómo
debería lucir la información mostrada por
InnoDB.
¿Dónde deben especificarse las opciones en Windows? Las reglas para ficheros de opciones en Windows son las siguientes:
Solo debe crearse el fichero my.cnf o
my.ini, pero no los dos.
El fichero my.cnf debe colocarse en el
directorio raíz de la unidad C:.
El fichero my.ini debería colocarse en
el directorio WINDIR; por ejemplo,
C:\WINDOWS o
C:\WINNT. Puede utilizarse el comando
SET en una ventana de consola para mostrar
el valor de WINDIR:
C:\> SET WINDIR windir=C:\WINNT
Si el ordenador emplea un gestor de arranque donde la unidad
C: no es la unidad de arranque, sólo es
posible emplear el fichero my.ini.
Si se instaló MySQL empleando los asistentes de instalación
y configuración, el fichero my.ini se
ubica en el directorio de instalación de MySQL. Consulte
Sección 2.3.5.14, “Dónde está el fichero my.ini”.
¿Dónde deben especificarse las opciones en Unix? En Unix, mysqld lee las opciones en los siguientes ficheros, si existen, en el siguiente orden:
/etc/my.cnf
Opciones globales.
$MYSQL_HOME/my.cnf
Opciones específicas del servidor.
defaults-extra-file
El fichero especificado con la opción
--defaults-extra-file.
~/.my.cnf
Opciones específicas del usuario.
MYSQL_HOME representa una variable de entorno
la cual contiene la ruta al directorio que hospeda al fichero
específico de servidor my.cnf.
Si se desea estar seguro de que mysqld lee sus
opciones únicamente desde un fichero determinado, se puede
emplear --defaults-option como la primera
opción en la línea de comandos cuando se inicia el servidor:
mysqld --defaults-file=ruta_a_my_cnf
Un ejemplo avanzado de
my.cnf . Suponiendo que se posee
un ordenador Linux con 2GB de RAM y tres discos duros de 60GB (en
los directorios /, /dr2
y /dr3). El siguiente ejemplo muestra
posibles parámetros de configuración InnoDB
en my.cnf.
[mysqld] # Las demas opciones del servidor MySQL pueden escribirse aquí # ... innodb_data_home_dir = # # Los ficheros de datos deben ser capaces de contener datos e índices innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend # # Establecer el tamaño del buffer en un 50-80% de la memoria del ordenador, # pero hay que asegurarse que en Linux x86 el uso total de memoria es < 2GB innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # innodb_log_files_in_group = 2 # # Establecer el tamaño del fichero de registro (log) en un 25% del tamaño del buffer innodb_log_file_size=250M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 # # Quitar marca de comentario a las siguientes lineas si se desea usarlas #innodb_thread_concurrency=5
Nótese que el ejemplo ubica los dos ficheros de datos en discos
diferentes. InnoDB llena el espacio de tablas
comenzando por el primer fichero de datos. En algunos casos, el
rendimiento de la base de datos mejorará si no se colocan todos los datos
en el mismo disco físico. Colocar los ficheros de registro (log) en un
disco diferente a los datos, a menudo es beneficioso para el rendimiento.
También se pueden utilizar dispositivos en bruto (raw devices) como
ficheros de datos InnoDB, lo cual mejorará la velocidad
de E/S. Consulte Sección 15.14.2, “Usar dispositivos en bruto (raw devices) para espacios de tablas”.
Advertencia: En GNU/Linux x86 de 32 bits,
se debe tener cuidado con no establecer el uso de memoria en un número
demasiado alto. glibc le puede permitir al heap de
proceso que crezca por sobre la pila de los subprocesos, lo cual hará caer el
servidor. Es arriesgado que el resultado del siguiente cálculo exceda los
2GB:
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Cada hilo emplea una pila (a menudo de 2MB, pero de solamente 256KB en los
binarios de MySQL AB) y en el peor caso también empleará una cantidad de
memoria adicional igual a sort_buffer_size +
read_buffer_size.
Compilando MySQL por sí mismo, el usuario puede emplear hasta 64GB de
memoria física en Windows de 32 bits. Consulte la descripción de
innodb_buffer_pool_awe_mem_mb en
Sección 15.4, “Opciones de arranque de InnoDB”.
¿Cómo deben ajustarse otros parámetro del servidor mysqld? Los siguientes son valores típicos adecuados para la mayoría de los usuarios:
[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Establecer key_buffer a un 5 - 50% de la RAM., dependiendo de cuánto se usen
# tablas MyISAM, pero manteniendo key_buffer_size + InnoDB
# buffer pool size < 80% de la RAM
key_buffer_size=value
Esta sección describe las opciones de servidor relacionadas con
InnoDB. En MySQL 5.0, todas son especificadas
con la forma
--
en la línea de comandos o en ficheros de opciones.
opt_name=value
innodb_additional_mem_pool_size
El tamaño del pool de memoria que InnoDB utiliza
para almacenar información del diccionario de datos y otras
estructuras de datos internas. Mientras más tablas se tengan en la
aplicación, mayor será este tamaño. Si InnoDB se
queda sin memoria en este pool, comenzará a tomar memoria del sistema
operativo, y dejará mensajes de advertencia en el log de errores de
MySQL. El valor por defecto es 1MB.
innodb_autoextend_increment
El tamaño a incrementar (en megabytes) cuando se extiende el tamaño de un espacio de tablas autoextensible, luego de llenarse. El valor por defecto es 8. Esta opción puede cambiarse en tiempo de ejecución como una variable de sistema global.
innodb_buffer_pool_awe_mem_mb
El tamaño (en MB) del pool de buffer, si está ubicado en la memoria
AWE en Windows de 32 bits, y sólo relevante en este tipo de sistemas
operativos. Si el sistema operativo Windows de 32 bits en uso soporta
más de 4GB de memoria, usualmente llamado “Address Windowing
Extensions”, se puede ubicar el pool del buffer de
InnoDB dentro de la memoria física AWE utilizando
este parámetro. El máximo valor posible es de 64000. Si se especifica
este parámetro, innodb_buffer_pool_size es la
ventana en el espacio de direcciones de 32 bits de
mysqld donde InnoDB direcciona
la memoria AWE. Un valor adecuado para
innodb_buffer_pool_size son 500MB.
innodb_buffer_pool_size
El tamaño del buffer de memoria que InnoDB emplea
para el almacenamiento intermedio de los datos e índices de sus
tablas. Mientras más grande sea este valor, menores operaciones de E/S
en disco serán necesarias para acceder a los datos de las tablas. En
un servidor de bases de datos dedicado, se puede establecer este valor
en hasta el 80% de la memoria física del ordenador. Sin embargo, no
debe establecerse en un valor demasiado grande porque la pugna por la
memoria física podría causar que el sistema oeprativo comience a
paginar.
innodb_checksums
InnoDB emplea validación por sumas de verificación
(checksums) en todas las páginas leídas desde el disco, para asegurar
una tolerancia extra contra fallas frente a hardware averiado o
ficheros corruptos. Sin embargo, bajo ciertas circunstancias
inusuales (por ejemplo al ejecutar pruebas de rendimiento) esta
característica extra de seguridad es innecesaria. En tales casos, esta
opción (que está habilitada por defecto) puede deshabilitarse con
--skip-innodb-checksums. Esta opción fue agregada
en MySQL 5.0.3.
innodb_data_file_path
Las rutas a los ficheros individuales de datos y sus tamaños. La ruta
de directorio completa a cada fichero de datos se obtiene concatenando
innodb_data_home_dir con cada ruta especificada
aquí. Los tamaños de fichero se especifican en megabytes o gigabytes
(1024MB) agregando M o
G al valor que representa el tamaño. La sumatoria
de los tamaños de fichero debe ser de al menos 10MB. En algunos
sistemas operativos, los ficheros deben tener menos de 2GB. Si no se
indica innodb_data_file_path, el comportamiento
predeterminado de inicio es crear un único fichero autoextensible de
10MB llamado ibdata1. En aquellos sistemas
operativos que soporten ficheros grandes, se puede establecer el tamaño
de fichero en más de 4GB. También pueden utilizarse como ficheros de
datos particiones de dispositivos en bruto. Consulte Sección 15.14.2, “Usar dispositivos en bruto (raw devices) para espacios de tablas”.
innodb_data_home_dir
La porción común de la ruta de directorio para todos los ficheros de
datos InnoDB. Si este valor no se establece, por
defecto será el directorio de datos de MySQL. También puede
especificarse como una cadena vacía, en cuyo caso se podrán utilizar
rutas absolutas en innodb_data_file_path.
innodb_doublewrite
Por defecto, InnoDB almacena todos los datos dos
veces, la primera en el buffer de escritura doble (o doublewrite), y
luego a los ficheros de datos reales. Esta opción puede emplearse para
desactivar dicha funcionalidad. Al igual que
innodb_checksums, esta opción está habilitada por
defecto; puede desactivarse con
--skip-innodb-doublewrite en pruebas de rendimiento
o casos en que el máximo desempeño prevalezca sobre la preocupacion
por la integridad de los datos o las posibles fallas. Esta opción se
agregó en MySQL 5.0.3.
innodb_fast_shutdown
Si se establece a 0, InnoDB efectúa una descarga
completa y vuelca los buffers de inserción antes de llevar a cabo el
cierre del servidor. Estas operaciones pueden tomar minutos o incluso
horas en casos extremos. Si se establece en 1,
InnoDB pasa por alto estas operaciones al cierre.
El valor por defecto es 1. Si se establece en 2 (opción que está
disponible desde MySQL 5.0.5, excepto en Netware), InnoDB simplemente
vuelca a disco sus registros (logs) y se cierra en frío, como si
hubiera ocurrido una caída; ninguna transacción confirmada se perderá,
pero en el siguiente inicio se ejecutará una recuperación ante caídas.
innodb_file_io_threads
El número de subprocesos (threads) de E/S de fichero en
InnoDB. Normalmente esto debería ser dejado en el
valor predeterminado de 4, pero la E/S de disco en Windows puede
beneficiarse con un número mayor. En Unix, incrementar el número no
tiene efecto; InnoDB siempre utiliza el valor
predeterminado.
innodb_file_per_table
Esta opción provoca que InnoDB cree cada
nueva tabla utilizando su propio fichero .ibd
para almacenar datos e índices, en lugar de colocarlo en el espacio de
tablas compartidas. Consulte Sección 15.6.6, “Usar un espacio de tablas para cada tabla”.
innodb_flush_log_at_trx_commit
Cuando innodb_flush_log_at_trx_commit se establece
en 0, una vez por segundo el buffer de registros (log buffer) se graba
en el fichero de registro y se vuelca a disco, pero no se hace nada al
confirmar una transacción. Cuando este valor es 1 (predeterminado),
cada vez que se confirma una transacción el buffer de registros
(log buffer) se graba en el fichero de registro y se vuelca a disco
Cuando se establece en 2, el buffer de registros (log buffer) se graba
en el fichero de registro, pero no se vuelca a disco. Sin embargo, el
volcado a disco del fichero de registro se produce una vez por segundo
también cuando vale 2. Se debe tener en cuenta que el volcado una vez
por segundo no está 100% garantizado que ocurra en cada segundo,
debido a cuestiones de programación (scheduling) de procesos. Se puede
alcanzar un mayor rendimiento estableciendo un valor diferente de 1,
pero en caso de caída se puede perder un segundo de transacciones. Si
se establece el valor en 0, cualquier caída en un proceso de
mysqld puede borrar el último segundo de
transacciones. Si se establece el valor en 2, entonces únicamente una
caída del sistema operativo o una interrupción de energía pueden
borrar el último segundo de transacciones. Hay que notar que muchos
sistemas operativos y algunos tipos de discos puedne ser engañosos en
las operaciones de volcado a disco. Podrían indicarle a
mysqld que el volcado ha tenido lugar, aunque no
sea así. En tal caso la estabilidad de las transacciones no está
garantizada ni aún con el valor 1, y en el peor de los casos una
interrupción de energía puede incluso corromper la base de datos
InnoDB. Utilizar un caché de disco apoyado por baterías en el
controlador de disco SCSI o en el propio disco, acelera los volcados a
disco, y hace más segura la operación. También puede intentarse con el
comando de Unix hdparm, el cual deshabilita el
almacenamiento en caches de hardware de las operaciones de escritura
a disco, o utilizar algún otro comando específico del fabricante del
hardware. El valor por defecto de esta opción es 1
innodb_flush_method
Esta opción solamente es relevante en sistemas Unix. Si se establece
en fdatasync (el valor predeterminado),
InnoDB utiliza fsync() para
volcar tanto los ficheros de datos como de registro (log). Si se
establece en O_DSYNC, InnoDB
emplea O_SYNC para abrir y volcar los ficheros de
registro, pero utiliza fsync() para volcar los
ficheros de datos. Si se especifica O_DIRECT
(disponible en algunas versiones de GNU/Linux),
InnoDB utiliza O_DIRECT para
abrir los ficheros de datos, y fsync() para volcar
tanto los ficheros de datos como de registro. Nótese que
InnoDB emplea fsync() en lugar
de fdatasync(), y no emplea
O_DSYNC por defecto porque han ocurrido problemas
con éste en muchas variedades de Unix.
innodb_force_recovery
Advertencia: Esta opción debería ser definida solamente en una
situación de emergencia cuando se desean volcar las tablas desde una
base de datos corrupta. Los posibles valores van de 1 a 6. Los
significados de estos valores se describen en Sección 15.8.1, “Forzar una recuperación”. Como una medida de seguridad,
InnoDB impide que un usuario modifique datos cuando
esta opción tiene un valor mayor a 0.
innodb_lock_wait_timeout
El límite de tiempo, en segundos, que una transacción
InnoDB puede esperar por un bloqueo antes de ser
cancelada. InnoDB automáticamente detecta bloqueos
mutuos (deadlocks) en su propia tabla de bloqueos, y cancela la
transacción. InnoDB detecta los bloqueos por el uso de la sentencia LOCK
TABLES. El valor predeterminado es de 50 segundos.
Para conseguir la mayor estabilidad y consistencia posibles en una
configuración de replicación, se debería utilizar
innodb_flush_logs_at_trx_commit=1,
sync-binlog=1, y
innodb_safe_binlog en el fichero
my.cnf principal.
innodb_locks_unsafe_for_binlog
Esta opción desactiva el bloqueo de la clave siguiente en búsquedas y
exploraciones de índices InnoDB. El valor por
defecto de esta opción es falso.
Normalmente, InnoDB utiliza un algoritmo denominado
bloqueo de clave siguiente (next-key).
InnoDB
efectúa un bloqueo a nivel de fila de tal forma que cuando busca o
explora el índice de una tabla, establece bloqueos compartidos o
exclusivos en cualquier registro de índice que encuentre. El bloqueo
que InnoDB establece en registros de índice también
afecta al “vacío” que precede a ese registro. Si un usuario
tiene un bloqueo compartido o exclusivo sobre el registro
R en un índice, otro usuario no puede insertar un
nuevo registro de índice inmediatamente antes de
R en el orden del índice. Esta opción provoca que
InnoDB no utilice el bloqueo de clave siguiente en
búsquedas o exploraciones de índices. El bloqueo de clave siguiente es
todavía utilizado para asegurar las restricciones de claves foráneas y
la verificación de claves duplicadas. Nótese que el uso de esta opción
puede provocar problemas secundarios: suponiendo que se deseen leer y
bloquear todos los registros hijos de la tabla child
que tengan un identificador mayor a 100, junto al posterior intento de
actualizar algunas columnas en las filas seleccionadas:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
Supóngase que hay un índice sobre la columna id. La
consulta explora aquel índice comenzando por el primer registro en que
id sea mayor que 100. Si el bloqueo efectuado sobre
los registros del índice no bloquea las inserciones realizadas en los
espacios vacíos, en la tabla se insertará un nuevo registro. Si se
ejecuta el mismo SELECT dentro de la misma
transacción, se verá un nuevo registro en el conjunto de resultados
devuelto por la consulta. Esto también significa que si se agregan
nuevos elementos a la base de datos, InnoDB no garantiza la
serialización; sin embargo, los conflictos de serialización aún están
garantizados. Por lo tanto, si esta opción se utiliza, InnoDB garantiza
como mucho el nivel de aislamiento READ COMMITTED.
A partir de MySQL 5.0.2 esta opción es aún más insegura.
InnoDB en un UPDATE o
DELETE solamente bloquea los registros que se
actualizan o borran. Esto reduce notablemente la probabilidad de
bloqueos mutuos (deadlocks), pero aún pueden ocurrir. Nótese que esta
opción todavía no le permite a operaciones como
UPDATE predominar sobre otras operaciones similares
(como otro UPDATE) aún en el caso en que actúen
sobre registros diferentes. Considérese lo siguiente:
example:
CREATE TABLE A(A INT NOT NULL, B INT); INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;
Si una conexión realiza una consulta:
SET AUTOCOMMIT = 0; UPDATE A SET B = 5 WHERE B = 3;
y la otra conexión ejecuta otra consulta a continuación de la primera:
SET AUTOCOMMIT = 0; UPDATE A SET B = 4 WHERE B = 2;
La consulta dos tendrá que esperar la confirmación o la cancelación de
la consulta uno, porque ésta tiene un bloqueo exclusivo en el registro
(2,3), y la consulta dos, mientras explora los registros,
también intenta colocar un bloqueo exclusivo en la misma fila, cosa
que no puede hacer. Esto se debe a que la consulta dos primero
establece el bloqueo sobre un registro y luego determina si el
registro pertenece al conjunto de resultados, y si no es así libera el
bloqueo innecesario, cuando se emplea la opción
innodb_locks_unsafe_for_binlog.
Por lo tanto, la consulta uno se ejecuta de este modo:
x-lock(1,2) unlock(1,2) x-lock(2,3) update(2,3) to (2,5) x-lock(3,2) unlock(3,2) x-lock(4,3) update(4,3) to (4,5) x-lock(5,2) unlock(5,2)
entonces la consulta dos se ejecuta así:
x-lock(1,2) update(1,2) to (1,4) x-lock(2,3) - wait for query one to commit or rollback
innodb_log_arch_dir
El directorio donde los ficheros de registro (logs) terminados se
archivarán si se utiliza el archivo de ficheros de registro. Si se
utiliza, el valor de este parámetro debería ser el mismo que
innodb_log_group_home_dir. Sin embargo, no es
requerido.
innodb_log_archive
Este valor generalmente debería establecerse a 0. Debido a que la
recuperación a partir de una copia de respaldo es realizada por MySQL
empleando sus propios ficheros de registro (log), en general no
hay necesidad de archivar los ficheros de registro de
InnoDB. El valor predeterminado para esta opción es
0.
innodb_log_buffer_size
El tamaño del buffer que InnoDB emplea para
escribir los ficheros de registro (logs) en disco. Los valores
razonables se encuentran entre 1MB y 8MB. El valor predeterminado es
1MB. Un buffer de fichero de registro grande le permite a las
transacciones extensas ejecutarse sin necesidad de guardar el fichero
de registro en disco antes de que la transacción se confirme. Por lo
tanto, si se manejan grandes transacciones, incrementar el tamaño del
buffer de ficheros de registro ahorra operaciones de E/S en disco.
innodb_log_file_size
El tamaño de cada fichero de registro (log) en un grupo de ficheros de
registro. El tamaño combinado de estos ficheros debe ser inferior a
4GB en ordenadores de 32 bits. El valor predeterminado es de 5MB. El
rango de valores razonables va desde 1MB hasta la
1/N parte del tamaño del pool de buffer,
donde N es la cantidad de ficheros de
registro en el grupo. Mientras mayor es el valor, menor es la cantidad
de guardado de puntos de verificación que se necesitan en el pool de
buffer, ahorrando operaciones de E/S en disco. Pero tener ficheros de
registro más grandes también significa que la recuperación es más
lenta en caso de caídas.
innodb_log_files_in_group
En un grupo de ficheros de registro (logs), es la cantidad de ficheros
que contiene.
InnoDB escribe en los ficheros siguiendo una forma
circular. El valor predeterminado es 2 (recomendado).
innodb_log_group_home_dir
La ruta de directorio a los ficheros de registro (log) de
InnoDB. Debe tener el mismo valor que
innodb_log_arch_dir. Si no se especifican
parámetros de ficheros de registro InnoDB, la
acción predeterminada es crear dos ficheros de 5MB llamados
ib_logfile0 y ib_logfile1 en
el directorio de datos de MySQL.
innodb_max_dirty_pages_pct
Un entero en el rango de 0 a 100. El valor por defecto es 90. El
subproceso (thread) principal en InnoDB intenta
volcar páginas desde el pool de buffer de modo que a lo sumo este
porcentaje de las páginas aún sin volcar sea volcado en un momento
determinado. Si se tiene el privilegio SUPER, este
porcentaje pude cambiarse mientras el servidor está en ejecución:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_max_purge_lag
Esta opción controla la forma de retrasar las operaciones
INSERT, UPDATE y
DELETE cuando las operaciones de descarga (ver
Sección 15.12, “Implementación de multiversión”) están sufiendo demoras. TEl
valor por defecto de este parámetro es cero, lo que significa que no
se retrasarán. Esta opción puede modificarse en tiempo de ejecución
como una variable global de sistema.
El sistema de transacciones de InnoDB mantiene una lista de
transacciones que tienen entradas en los índices marcadas para ser
eliminadas por operaciones
UPDATE o DELETE.
Se deja que la longitud de esta lista sea
purge_lag. Cuando
purge_lag excede a
innodb_max_purge_lag, cada operación de
INSERT, UPDATE y
DELETE se retrasa durante
((purge_lag/innodb_max_purge_lag)*10)-5
milisegundos. El retraso se computa en el comienzo de un lote de
depuración, cada diez segundos. Las operaciones no se retrasan si no
puede ejecutarse la depuración debido a una vista de lectura consistente
(consistent read) anterior que contenga los registros a ser depurados.
Un escenario típico para una carga de trabajo problemática podría ser 1 millón, asumiendo que las transacciones son pequeñas, sólo 100 bytes de tamaño, y se pueden permitir 100 MB de registros sin descargar en las tablas.
innodb_mirrored_log_groups
El número de copias idénticas de grupos de ficheros de registro que se mantienen para la base de datos. Actualmente debería establecerse en 1.
innodb_open_files
Esta opción sólo es relevante si se emplean múltiples espacios de
tablas en InnoDB. Especifica el número máximo de
ficheros .ibd que InnoDB puede
mantener abiertos al mismo tiempo. El mínimo es 10. El valor
predeterminado es 300.
Los descriptores de fichero empleados para ficheros
.ibd son únicamente para
InnoDB. Son independientes de los especificados por
la opción de servidor --open-files-limit, y no
afectan la operación del caché de tablas.
innodb_safe_binlog
Contribuye a asegurar la consistencia entre el contenido de las tablas
InnoDB y el registro binario (binary log). Consulte
Sección 5.10.3, “El registro binario (Binary Log)”.
innodb_status_file
Esta opción provoca que InnoDB cree un fichero
<datadir>/innodb_status.<pid>SHOW INNODB STATUS.
Disponible desde MySQL 4.0.21.
innodb_table_locks
InnoDB respeta lo establecido por LOCK
TABLES, y MySQL no retorna desde un LOCK
TABLE .. WRITE hasta que todos los otros flujos (threads)
han levantado sus bloqueos a la tabla. El valor por defecto es 1, lo
cual significa que LOCK TABLES causará que InnoDB
bloquee una tabla internamente. En aplicaciones que emplean
AUTOCOMMIT=1, los bloqueos internos de tabla de
InnoDB pueden originar bloqueos mutuos (deadlocks). Se puede
establecer innodb_table_locks=0 en
my.cnf (o my.ini en
Windows) para eliminar ese problema.
innodb_thread_concurrency
InnoDB intenta mantener el número de flujos
(threads) del sistema operativo que concurren dentro de
InnoDB en un valor menor o igual al límite
especificado por este parámetro. Antes de MySQL 5.0.8, el valor por
defecto es 8. Si se tienen dificultades de rendimiento, y SHOW INNODB
STATUS indica que hay muchos subprocesos esperando por
semáforos, se podrían tener subprocesos pugnando por recursos, y se
debería establecer este parámetro en un número mayor o menor. Si se
posee un ordenador con varios procesadores y discos, se puede intentar
aumentar el valor para hacer mejor uso de los recursos del ordenador.
Un valor recomendado es la suma del número de procesadores y discos
que tiene el sistema. Un valor de 500 o mayor deshabilitará la
verificación de concurrencia. A partir de MySQL 5.0.8, el valor por
defecto es 20, y la verificación de concurrencia se deshabilita si se
establece en 20 o más.
innodb_status_file
Esta opción provoca que InnoDB cree un fichero
<datadir>/innodb_status.<pid>SHOW INNODB
STATUS.
Suponiendo que se ha instalado MySQL y se editó el fichero de opciones
para que contenga los parámetros de InnoDB necesarios,
antes de iniciar MySQL se debería verificar que los directorios indicados
para los ficheros de datos y de registro (log) InnoDB
existen y que el servidor MySQL tiene permisos de acceso a dichos
directorios. InnoDB no puede crear directorios,
solamente ficheros. Hay que verificar también que se tiene suficiente
espacio en disco para los ficheros de datos y de registro.
Cuando se crea una base de datos InnoDB, es mejor
ejecutar el servidor MySQL mysqld desde la línea de
comandos, no desde el envoltorio mysqld_safe o como
un servicio de Windows. Cuando se lo ejecuta desde la línea de comandos,
se puede ver lo que mysqld imprime y qué está
ocurriendo. En Unix, simplemente debe invocarse mysqld.
En Windows, hay que usar la opción --console.
Cuando se inicia el servidor MySQL luego de la configuración inicial de
InnoDB en el fichero de opciones,
InnoDB crea los ficheros de datos y de registro e
imprime algo como lo siguiente:
InnoDB: The first specified datafile /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: datafile /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created InnoDB: Started mysqld: ready for connections
Se ha creado una nueva base de datos InnoDB. Se puede
conectar al servidor MySQL con los programas cliente acostumbrados, como
mysql. Cuando se detiene el servidor MySQL, con
mysqladmin shutdown, la salida es como la siguiente:
010321 18:33:34 mysqld: Normal shutdown 010321 18:33:34 mysqld: Shutdown Complete InnoDB: Starting shutdown... InnoDB: Shutdown completed
Se puede mirar en los directorios de ficheros de datos y registro y se
verán los ficheros creados. El directorio de registro (log) también
contiene un pequeño fichero llamado
ib_arch_log_0000000000. Ese fichero resulta de la
creación de la base de datos, luego de lo cual InnoDB
desactivó el guardado de registros (log). Cuando MySQL inicia de nuevo,
los ficheros de datos y de registro ya han sido creados, por lo que la
salida es más breve:
InnoDB: Started mysqld: ready for connections
Es posible agregar la opción innodb_file_per_table a
my.cnf, y hacer que InnoDB almacene cada tabla en su
propio fichero .ibd en un directorio de bases de
datos de MySQL. Consulte Sección 15.6.6, “Usar un espacio de tablas para cada tabla”.
Si InnoDB imprime un error de sistema operativo en
una operación de ficheros, generalmente el problema es uno de los
siguientes:
No se creó el directorio para los ficheros de datos o de registros
(log) de InnoDB.
mysqld no tiene los permisos de acceso para crear ficheros en aquellos directorios.
mysqld no puede leer el fichero de opciones
my.cnf o my.ini adecuado,
y por lo tanto no ve las opciones especificadas.
El disco está lleno o se excedió la cuota de disco.
Se ha creado un subdirectorio que tiene el mismo nombre que uno de los ficheros de datos especificados.
Hay un error de sintaxis en innodb_data_home_dir
o innodb_data_file_path.
Si algo va mal durante el intento de InnoDB de
inicializar el espacio de tablas o los ficheros de registro, se deberán
borrar todos los ficheros creados por InnoDB. Esto
comprende todos los ficheros ibdata y todos los
ib_logfile. En caso de haber creado alguna tabla
InnoDB, habrá que borrar del directorio de datos de MySQL
los correspondientes
ficheros .frm de estas tablas (y cualquier fichero
.ibd si se están empleando múltiples espacios de
tablas). Entonces puede intentarse nuevamente la creación de la base de
datos InnoDB. Es mejor iniciar el servidor MySQL
desde una línea de comandos de modo que pueda verse lo que ocurre.
Suponiendo que se ha iniciado el cliente MySQL con el comando
mysql test, para crear una tabla
InnoDB se debe especificar la opción
ENGINE = InnoDB o
TYPE = InnoDB en la sentencia SQL de creación de tabla:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
La sentencia SQL crea una tabla y un índice en la columna
a en el espacio de tablas InnoDB que
consiste en los ficheros de datos especificados en
my.cnf. Adicionalmente, MySQL crea un fichero
customers.frm en el directorio
test debajo del directorio de bases de datos de
MySQL. Internamente, InnoDB agrega a su propio
diccionario de datos una entrada para la tabla
'test/customers'. Esto significa que puede crearse una
tabla con el mismo nombre customers en otra base de
datos, y los nombres de las tablas no entrarán en conflicto dentro de
InnoDB.
Se puede consultar la cantidad de espacio libre en el espacio de tablas
InnoDB dirigiendo una sentencia SHOW
TABLE STATUS para cualquier tabla InnoDB. La
cantidad de espacio libre en el espacio de tablas aparece en la sección
Comment en la salida de SHOW
TABLE STATUS. Un ejemplo:
SHOW TABLE STATUS FROM test LIKE 'customers'
Nótese que las estadísticas que SHOW muestra acerca de
las tablas InnoDB son solamente aproximadas. Se
utilizan en la optimización SQL. No obstante, los tamaños en bytes
reservados para las tablas e índices son exactos.
En forma predeterminada, cada cliente se que conecta al servidor MySQL
comienza con el modo de autocommit habilitado, lo cual automáticamente
confirma (commit) cada sentencia SQL ejecutada. Para utilizar
transacciones de múltiples sentencias se puede deshabilitar el modo
autocommit con la sentencia SQL SET AUTOCOMMIT = 0 y
emplear COMMIT y ROLLBACK para
confirmar o cancelar la transacción. Si se desea dejar activado
autocommit, se pueden encerrar las transacciones entre las sentencias
START TRANSACTION y
COMMIT o ROLLBACK. El siguiente
ejemplo muestra dos transacciones. La primera se confirma, la segunda se
cancela.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
En APIs como PHP, Perl DBI/DBD, JDBC, ODBC, o la interface de llamadas C
estándar de MySQL, se pueden enviar sentencias de control de
transacciones como COMMIT al servidor MySQL en forma
de cadenas, igual que otras sentencias SQL como
SELECT o INSERT. Algunas APIs
también ofrecen funciones o métodos especiales para confirmación y
cancelación de transacciones.
Importante: No se deben convertir las tablas del sistema en la base de
datos mysql (por ejemplo, user o
host) al tipo InnoDB. Las tablas
del sistema siempre deben ser del tipo MyISAM.
Si se desea que todas las tablas que no sean de sistema se creen como
tablas InnoDB, simplemente debe agregarse la línea
default-table-type=innodb a la sección
[mysqld] del fichero my.cnf
o my.ini.
InnoDB no posee una optimización especial para la
creación separada de índices en la misma forma que la tiene el motor de
almacenamiento MyISAM. Por lo tanto, no hay beneficio
en exportar e importar la tabla y crear los índices posteriormente. La
manera más rápida de cambiar una tabla al motor
InnoDB es hacer las inserciones directamente en una
tabla InnoDB. Es decir, utilizar ALTER
TABLE ... ENGINE=INNODB, o crear una tabla
InnoDB vacía con idénticas definiciones e insertar
las filas con INSERT INTO ... SELECT * FROM
....
Si se tienen restricciones UNIQUE sobre claves
secundarias, se puede acelerar la importación de una tabla desactivando
temporalmente la verificación de unicidad durante la sesión de
importación: SET UNIQUE_CHECKS=0;. Para tablas
grandes, esto ahorra gran cantidad de operaciones de E/S en disco, debido
a que InnoDB puede emplear su buffer de inserciones
para grabar registros de índices secundarios en lote.
Para obtener un mejor control sobre el proceso de inserción, podría ser mejor llenar la tablas grandes por partes:
INSERT INTO nuevatabla SELECT * FROM viejatabla WHERE clave > valor1 AND clave <= valor2;
Luego de que todos los registros se hayan insertado, se pueden renombrar las tablas.
Durante la conversión de tablas grandes, se puede reducir la cantidad de
operaciones de E/S en disco incrementando el tamaño del pool de buffer
de InnoDB. No debe usarse más del 80% de la memoria
física. También pueden aumentarse los tamaños de los ficheros de
registro (log) de InnoDB.
Hay que asegurarse de no llenar completamente el espacio de tablas: las
tablas InnoDB necesitan mucho más espacio que las
tablas MyISAM. Si una sentencia ALTER
TABLE se queda sin espacio, realizará una cancelación
(rollback), y esto puede tomar horas si lo hace sobre el disco. Para
las inserciones, InnoDB emplea el buffer de inserción
para combinar en lotes los registros secundarios de índices con los
índices. Esto ahorra gran cantidad de operaciones de E/S en disco.
Durante la cancelación no se emplea ese mecanismo, de modo que puede
llevar más de 30 veces el tiempo insumido por la inserción.
Si se produjera una de estas cancelaciones fuera de control, sino se tienen datos valiosos en la base de datos, puede ser preferible matar el proceso de la base de datos en lugar de esperar que se completen millones de operaciones de E/S en disco. Para el procedimiento completo, consulte Sección 15.8.1, “Forzar una recuperación”.
Si se especifica que una columna de una tabla es
AUTO_INCREMENT, la entrada para la tabla
InnoDB en el diccionario de datos contiene un
contador especial llamado "contador de auto incremento", que se utiliza
para asignar nuevos valores a la columna. El contador de auto incremento
se almacena sólo en la memoria principal, no en disco.
InnoDB utiliza el siguiente algoritmo para
inicializar el contador de auto incremento para una tabla
T que contiene una columna
AUTO_INCREMENT llamada ai_col:
Luego de iniciarse el servidor, cuando un usuario realiza por primera
vez una inserción en una tabla T,
InnoDB ejecuta el equivalente de esta sentencia:
SELECT MAX(ai_col) FROM T FOR UPDATE;
El valor retornado por la sentencia se incrementa en uno y se asigna a
la columna, y al contador de auto incremento de la tabla. Si la tabla
está vacía, se asigna el valor 1. Si el contador aún
no se ha inicializado y se ejecuta una sentencia SHOW TABLE
STATUS que muestre su salida para la tabla
T, el contador se inicializa (pero no se incrementa)
y se almacena para usos posteriores. Nótese que en esta inicialización
se realiza una lectura normal con bloqueo exclusivo y el bloqueo
permanece hasta el final de la transacción.
InnoDB sigue el mismo procedimiento para inicializar
el contador de auto incremento de una tabla recientemente creada.
Nótese que si durante un INSERT el usuario especifica
un valor NULL o 0 para una columna
AUTO_INCREMENT, InnoDB trata a la
columna como si no se hubiera especificado un valor y genera un nuevo
valor para ella.
Luego de que el contador de auto incremento ha sido inicializado, si un
usuario inserta una fila que explícitamente indica para la columna auto
incremental un valor mayor que el valor actual del contador, éste se
establece al valor actual de la columna. Si no se indica un valor,
InnoDB incrementa el valor del contador en uno y lo
asigna a la columna.
Al acceder al contador de auto incremento, InnoDB
emplea un nivel de bloqueo de tabla especial
AUTO-INC, que mantiene hasta el final de la sentencia
SQL actual y no hasta el final de la transacción. Esta estrategia de
bloqueo especial fue introducida para mejorar la concurrencia de
inserciones en una tabla que contiene una columna
AUTO_INCREMENT. Dos transacciones no pueden tener el
bloqueo AUTO-INC simultáneamente en la misma tabla.
Nótese que pueden observarse valores faltantes en la secuencia de
valores asignados a la columna AUTO_INCREMENT si se
cancelan transacciones que ya han obtenido números desde el contador.
El comportamiento del mecanismo de auto incremento no se encuentra definido si un usuario asigna un valor negativo a la columna o si el valor se vuelve mayor que el entero más grande que puede almacenarse en el tipo de entero especificado.
A partir de MySQL 5.0.3, InnoDB soporta la opción
AUTO_INCREMENT = en
sentencias nCREATE TABLE y ALTER
TABLE, para establecer inicialmente o modificar el valor
actual del contador. El efecto de esta acción es cancelado al reiniciar
el servidor, por las razones tratadas anteriormente en esta sección.
InnoDB también soporta restricciones de claves
foráneas. La sintaxis para definir una restricción de clave foránea en
InnoDB es así:
[CONSTRAINTsímbolo] FOREIGN KEY [id] (nombre_índice, ...) REFERENCESnombre_de_tabla(nombre_índice, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Las definiciones de claves foráneas están sujetas a las siguientes condiciones:
Ambas tablas deben ser InnoDB y no deben ser
tablas temporales.
En la tabla que hace referencia, debe haber un índice donde las columnas de clave extranjera estén listadas en primer lugar, en el mismo orden.
En la tabla referenciada, debe haber un índice donde las columnas referenciadas se listen en primer lugar, en el mismo orden. En MySQL/InnoDB 5.0, tal índice se creará automáticamente en la tabla referenciada si no existe aún.
No están soportados los índices prefijados en columnas de claves
foráneas. Una consecuencia de esto es que las columnas
BLOB y TEXT no pueden
incluirse en una clave foránea, porque los índices sobre dichas
columnas siempre deben incluir una longitud prefijada.
Si se proporciona un
CONSTRAINT,
éste debe ser único en la base de datos. Si no se suministra,
símboloInnoDB crea el nombre automáticamente.
InnoDB rechaza cualquier operación
INSERT o UPDATE que intente crear
un valor de clave foránea en una tabla hija sin un valor de clave
candidata coincidente en la tabla padre. La acción que
InnoDB lleva a cabo para cualquier operación
UPDATE o DELETE que intente
actualizar o borrar un valor de clave candidata en la tabla padre que
tenga filas coincidentes en la tabla hija depende de la
accion referencial especificada utilizando las
subcláusulas ON UPDATE y ON
DETETE en la cláusula FOREIGN
KEY. Cuando el usuario intenta borrar o actualizar una fila de
una tabla padre, InnoDB soporta cinco acciones
respecto a la acción a tomar:
CASCADE: Borra o actualiza el registro en la
tabla padre y automáticamente borra o actualiza los registros
coincidentes en la tabla hija. Tanto ON DELETE
CASCADE como ON UPDATE CASCADE están
disponibles en MySQL 5.0. Entre dos tablas, no se deberían definir
varias cláusulas ON UPDATE CASCADE que actúen en
la misma columna en la tabla padre o hija.
SET NULL: Borra o actualiza el registro en la
tabla padre y establece en NULL la o las columnas
de clave foránea en la tabla hija. Esto solamente es válido si las
columnas de clave foránea no han sido definidas como NOT
NULL. MySQL 5.0 soporta tanto ON DELETE SET
NULL como ON UPDATE
SET NULL.
NO ACTION: En el estándar ANSI
SQL-92, NO ACTION significa
ninguna acción en el sentido de que unintento
de borrar o actualizar un valor de clave primaria no sera permitido
si en la tabla referenciada hay una valor de clave foránea
relacionado. (Gruber, Mastering
SQL, 2000:181). En MySQL 5.0, InnoDB
rechaza la operación de eliminación o actualización en la tabla
padre.
RESTRICT: Rechaza la operación de eliminación o
actualización en la tabla padre. NO ACTION y
RESTRICT son similares en tanto omiten la
cláusula ON DELETE u ON
UPDATE. (Algunos sistemas de bases de datos tienen
verificaciones diferidas o retrasadas, una de las cuales es
NO ACTION. En MySQL, las restricciones de claves
foráneas se verifican inmediatamente, por eso,
NO ACTION y RESTRICT son
equivalentes.)
SET DEFAULT: Esta acción es reconocida por el
procesador de sentencias (parser), pero InnoDB
rechaza definiciones de tablas que contengan ON DELETE SET
DEFAULT u ON UPDATE SET
DEFAULT.
InnoDB soporta las mismas opciones cuando se
actualiza la clave candidata en la tabla padre. Con
CASCADE, las columnas de clave foránea en la tabla
hija son establecidas a los nuevos valores de la clave candidata en la
tabla padre. Del mismo modo, las actualizaciones se producen en cascada
si las columnas actualizadas en la tabla hija hacen referencia a claves
foráneas en otra tabla.
Nótese que InnoDB soporta referencias de clave
foránea dentro de una tabla, y, en estos casos, la tabla hija realmente
significa registros dependientes dentro de la tabla.
InnoDB necesita que haya índices sobre las claves
foráneas y claves referenciadas, así las verificaciones de claves
foráneas pueden ser veloces y no necesitan recorrer la tabla. En MySQL
5.0, el índice en la clave foránea se crea automáticamente. Esto
contrasta con versiones más antiguas (anteriores a 4.1.8), donde los
índices debían crearse explícitamente o fallaba la creación de
restricciones de claves foráneas.
Las columnas involucradas en la clave foránea y en la clave referenciada
deben tener similares tipos de datos internos dentro de
InnoDB, de modo que puedan compararse sin una
conversión de tipo. La longitud y la condición de
con o sin signo de los tipos enteros deben ser iguales. La
longitud de los tipos cadena no necesita ser la misma. Si se especifica
una acción SET NULL, hay que asegurarse de que
las columnas en la tabla hija no se han declarado
como NOT NULL.
Si MySQL informa que ocurrió un error número 1005 en una sentencia
CREATE TABLE y la cadena con el mensaje de error se
refiere al errno (número de error) 150, significa que la creación de una
tabla falló debido a una restricción de clave foránea formulada
incorrectamente. Del mismo modo, si un ALTER TABLE
falla y hace referencia al número de error 150, significa que se ha
formulado incorrectamente una restricción de clave extranjera cuando se
alteró la tabla. En MySQL 5.0, puede emplearse SHOW INNODB
STATUS para mostrar una explicación detallada del último error
de clave foránea sufrido por InnoDB en el servidor.
Nota: InnoDB no
verifica las restricciones de claves foráneas en las claves foráneas o
valores de claves referenciados que contengan una columna
NULL.
Una desviación del estándar SQL: Si en
la tabla padre hay varios registros que contengan el mismo valor de
clave referenciada, entonces InnoDB se comporta en
las verificaciones de claves extranjeras como si los demás registros con
el mismo valor de clave no existiesen. Por ejemplo, si se ha definido
una restricción del tipo RESTRICT, y hay un registro
hijo con varias filas padre, InnoDB no permite la
eliminación de ninguna de éstas.
InnoDB lleva a cabo las operaciones en cascada a
través de un algoritmo de tipo depth-first, basado en los registros de
los indices correspondientes a las restricciones de claves foráneas.
Una desviación del estándar SQL: Si
ON UPDATE CASCADE u ON UPDATE
SET NULL vuelven a modificar la misma
tabla que se está actualizando en cascada, el comportamiento
es como en RESTRICT. Esto significa que en una tabla
no se pueden ejecutar operaciones ON UPDATE CASCADE
u ON UPDATE SET NULL que hagan referencia a ella
misma. De ese modo se previenen bucles infinitos resultantes de la
actualización en cascada. En cambio, una operación ON DELETE
SET NULL, puede hacer referencia a la misma tabla donde se
encuentra, al igual que ON DELETE CASCADE. En MySQL
5.0, las operaciones en cascada no pueden anidarse en más de 15 niveles
de profundidad.
Una desviación del estándar SQL: Como
sucede en MySQL en general, en una sentencia SQL que realice
inserciones, eliminaciones o actualizaciones en varias filas, InnoDB
verifica las restricciones UNIQUE y FOREIGN
KEY fila a fila. De acuerdo con el estándar SQL, el
comportamiento predeterminado debería ser que las restricciones se
verifiquen luego de que la sentencia SQL ha sido procesada por completo.
Note: Actualmente, los disparadores no son activados por acciones de claves foráneas en cascada.
Un ejemplo sencillo que relaciona tablas padre e
hijo a través de una clave foránea de una sola
columna:
CREATE TABLE parent(
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child(
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
Aquí, un ejemplo más complejo, en el cual una tabla
product_order tiene claves foráneas hacia otras dos
tablas. Una de las claves foráneas hace referencia a un índice de dos
columnas en la tabla product. La otra hace referencia
a un índice de una sola columna en la tabla customer:
CREATE TABLE product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=INNODB;
CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
InnoDB permite agregar una nueva restricción de
clave foránea a una tabla empleando ALTER TABLE:
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
Debe recordarse crear en primer lugar los índices
necesarios.. También se puede agregar una clave foránea
autoreferente a una tabla empleando ALTER TABLE.
InnoDB también soporta el uso de
ALTER TABLE para borrar claves foráneas:
ALTER TABLEnombre_tablaDROP FOREIGN KEYsímbolo_clave_foránea;
Si la cláusula FOREIGN KEY incluye un nombre de
CONSTRAINT cuando se crea la clave foránea, se puede
utilizar ese nombre para eliminarla. En otro caso, el valor
símbolo_clave_foránea es generado internamente por
InnoDB cuando se crea la clave foránea. Para saber
cuál es este símbolo cuando se desee eliminar una clave foránea, se
emplea la sentencia SHOW CREATE TABLE. Un ejemplo:
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
El procesador de sentencias (parser) de InnoDB
permite emplear acentos graves (ASCII 96) para encerrar los nombres de
tablas y columnas en una clásusula FOREIGN KEY ...
REFERENCES .... El parser de InnoDB también
toma en cuenta lo establecido en la variable de sistema
lower_case_table_names.
InnoDB devuelve las definiciones de claves foráneas
de una tabla como parte de la salida de la sentencia SHOW CREATE
TABLE:
SHOW CREATE TABLE tbl_name;
A partir de esta versión, mysqldump también produce definiciones correctas de las tablas en el fichero generado, sin omitir las claves foráneas.
Se pueden mostrar las restricciones de claves foráneas de una tabla de este modo:
SHOW TABLE STATUS FROMnombre_bdLIKE 'nombre_tabla';
Las restricciones de claves foráneas se listan en la columna
Comment de la salida producida.
Al llevar a cabo verificaciones de claves foráneas,
InnoDB establece bloqueos compartidos a nivel de fila
en los registros de tablas hijas o padres en los cuales deba fijarse.
InnoDB verifica las restricciones de claves foráneas
inmediatamente, la verificación no es demorada hasta la confirmación de
la transacción.
Para facilitar la recarga de ficheros de volcado de tablas que tengan
relaciones de claves foráneas, mysqldump incluye
automáticamente una sentencia en la salida del comando para establecer
FOREIGN_KEY_CHECKS a 0. Esto evita problemas con
tablas que tengan que ser creadas en un orden particular cuando se
recarga el fichero de volcado. También es posible establecer el valor de
esta variable manualmente:
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
Esto permite importar las tablas en cualquier orden si el fichero de
volcado contiene tablas que no están ordenadas según lo requieran sus
claves foráneas. También acelera la operación de importación. Establecer
FOREIGN_KEY_CHECKS a 0 también puede ser útil para
ignorar restricciones de claves foráneas durante operaciones LOAD
DATA y ALTER TABLE.
InnoDB no permite eliminar una tabla que está
referenciada por una restricción FOREIGN KEY, a menos
que se ejecute SET FOREIGN_KEY_CHECKS=0. Cuando se
elimina una tabla, las restricciones que fueron definidas en su
sentencia de creación también son eliminadas.
Si se recrea una tabla que fue eliminada, debe ser definida de acuerdo a las restricciones de claves foráneas que están haciendo referencia a ella. Debe tener los tipos y nombres correctos de columnas, y debe tener índices sobre las tablas referenciadas, como se estableció anteriormente. Si estas condiciones no se cumplen, MySQL devuelve un error número 1005 y menciona el error número 150 en el mensaje de error.
La replicación en MySQL funciona para tablas InnoDB
del mismo modo que lo hace para tablas MyISAM. Es
posible usar replicación en una forma en que el tipo de tabla en el
servidor esclavo no es igual a la tabla original en el servidor maestro.
Por ejemplo, se pueden replicar modificaciones de una tabla
InnoDB en el servidor maestro sobre una tabla
MyISAM en el servidor esclavo.
Para configurar un nuevo esclavo para un servidor maestro, se debe
realizar una copia del espacio de tablas InnoDB y de
los ficheros de registro, así como de los ficheros
.frm de las tablas InnoDB, y
mover las copias al servidor esclavo. El procedimiento adecuado para
esto se encuenta en Sección 15.9, “Trasladar una base de datos InnoDB a otra máquina”.
Si se puede detener el servidor maestro o un esclavo existente, se puede
tomar un backup en frío del espacio de tablas InnoDB
y de los ficheros de registro y utilizarlos para configurar un servidor
esclavo. Para crear un nuevo esclavo sin detener ningún servidor, se
puede utilizar la herramienta comercial
InnoDB
Hot Backup tool.
Una limitación menor en la replicación InnoDB es que
LOAD TABLE FROM MASTER no funciona con tablas de tipo
InnoDB. Hay dos posibles soluciones:
Hacer un volcado de la tabla en el maestro e importarlo dentro del esclavo.
Utilizar ALTER TABLE
en el
maestro antes de realizar la replicación con nombre_tabla TYPE=MyISAMLOAD
TABLE , y luego emplear nombre_tabla
FROM MASTERALTER
TABLE para convertir la tabla en el maestro nuevamente a
InnoDB.
Las transacciones que fallen en el maestro no afectan en absoluto la replicación. La replicación en MySQL se basa en el registro (log) binario donde MySQL registra las sentencias SQL que modifican datos. Un esclavo lee el registro binario del maestro y ejecuta las mismas sentencias SQL. Sin embargo, las sentencias emitidas dentro de una transacción no se graban en el registro binario hasta que se confirma la transacción, en ese momento todas las sentencias son grabadas de una vez. Si una sentencia falla, por ejemplo por infringir una clave foránea, o si se cancela una transacción, ninguna sentencia se guarda en el registro binario y la transacción no se ejecuta en absoluto en el servidor esclavo.
En MySQL 5.0, se puede almacenar cada tabla InnoDB y
sus índices en su propio fichero. Esta característica se llama
“multiple tablespaces” (espacios de tablas múltiples)
porque, en efecto, cada tabla tiene su propio espacio de tablas.
El uso de múltiples espacios de tablas puede ser beneficioso para usuarios que desean mover tablas específicas a discos físicos separados o quienes deseen restaurar respaldos de tablas sin interrumpir el uso de las demás tablas InnoDB.
Se pueden habilitar múltiples espacios de tablas agregando esta línea a
la sección [mysqld] de my.cnf:
[mysqld] innodb_file_per_table
Luego de reiniciar el servidor, InnoDB almacenará
cada nueva tabla creada en su propio fichero
nombre_tabla.ibdMyISAM, pero MyISAM divide la
tabla en un fichero de datos
tbl_name.MYDtbl_name.MYIInnoDB, los datos y los índices se almacenan juntos
en el fichero .ibd. El fichero
tbl_name.frm
Si se quita la línea innodb_file_per_table de
my.cnf y se reinicia el servidor,
InnoDB creará nuevamente las tablas dentro de los
ficheros del espacio de tablas compartido.
innodb_file_per_table afecta solamente la creación de
tablas. Si se inicia el servidor con esta opción, las tablas nuevas se
crearán empleando ficheros .ibd, pero aún se puede
acceder a las tablas existentes en el espacio de tablas compartido. Si se
remueve la opción, las nuevas tablas se crearán en el espacio
compartido, pero aún se podrá acceder a las tablas creadas en espacios
de tablas múltiples.
InnoDB siempre necesita del espacio de tablas
compartido. Los ficheros .ibd no son suficientes
para que funcione InnoDB. El espacio de tablas
compartido consiste de los ya conocidos ficheros
ibdata, donde InnoDB coloca su
diccionario de datos interno y los registros para deshacer cambios (undo
logs).
No se puede mover libremente ficheros
.ibd entre directorios de bases de datos
en la misma forma en que se hace con ficheros de tablas
MyISAM. Esto se debe a que la definición de las
tablas se almacena en el espacio de tablas compartido de
InnoDB, y también porque InnoDB
debe preservar la consistencia de los identificadores de transacciones y
los números de secuencia de registros (log).
Dentro de una determinada instalación MySQL, se puede mover un fichero
.ibd y las tablas asociadas de una base de datos a
otra con la conocida sentencia RENAME
TABLE:
RENAME TABLEnombre_bd_anterior.nombre_tablaTOnombre_bd_nuevo.nombre_tabla;
Si se tiene un respaldo “limpio” de un fichero
.ibd, se lo puede restaurar dentro de la
instalación MySQL de donde proviene del siguiente modo:
Utilizando esta sentencia ALTER TABLE:
ALTER TABLE nombre_tabla DISCARD TABLESPACE;
Precaución: Esto eliminará el actual
fichero .ibd.
Colocando el fichero .ibd nuevamente en el
directorio de la base de datos adecuada.
Utilizando esta sentencia ALTER TABLE:
ALTER TABLE nombre_tabla IMPORT TABLESPACE;
En este contexto, un respaldo “limpio”
de un fichero .ibd significa:
ç
El fichero .ibd no contiene modificaciones
realizadas por transacciones sin confirmar.
No quedan entradas sin combinar en el buffer de inserciones en el
fichero .ibd.
Se han quitado todos los registros de índice marcados para
eliminación en el fichero .ibd.
mysqld ha descargado todas las páginas
modificadas del fichero .ibd desde el buffer
pool hacia el fichero.
Se puede realizar un respaldo limpio del fichero
.ibd con el siguiente método:
Detener toda actividad del servidor mysqld y confirmar todas las transacciones.
Esperar hasta que SHOW INNODB STATUS indique que
no hay transacciones activas en la base de datos, y el estado del
subproceso (trhead) principal de InnoDB sea
Waiting for server activity (Esperando por
actividad del servidor). Entonces, se puede hacer una copia del
fichero .ibd.
Otro método para hacer una copia limpia de un fichero
.ibd es utilizar la herramienta comercial
InnoDB Hot Backup:
Utilizar InnoDB Hot Backup para respaldar la
instalación InnoDB.
Iniciar un segundo servidor mysqld sobre el
respaldo y permitirle limpiar los ficheros .ibd
del mismo.
Figura en la lista de pendientes (TODO) para permitir mover ficheros
.ibd limpios a otra instalación MySQL. Esto
necesita que se inicialicen los IDs (identificadores) de transacciones y
los números de secuencia de registros (log) en el fichero
.ibd.
Esta sección describe lo que se puede hacer cuando el espacio de tablas
InnoDB se queda sin espacio o cuando se desea cambiar
el tamaño de los ficheros de registro (log).
La manera más sencilla de incrementar el tamaño del espacio de tablas
InnoDB es configurarlo desde un principio para que sea
autoextensible, especificando el atributo autoextend
para el último fichero de datos en la definición del espacio de tablas.
Entonces, InnoDB incrementa el tamaño de ese fichero
automáticamente en intervalos de 8MB cuando se queda sin espacio. El
tamaño del intervalo a incrementar puede configurarse estableciendo el
valor de innodb_autoextend_increment, el cual está
expresado en megabytes, y cuyo valor predeterminado es 8.
Alternativamente, se puede incrementar el tamaño del espacio de tablas
agregando otro fichero de datos. Para hacer esto, se debe detener el
servidor MySQL, editar el fichero my.cnf para agregar
un nuevo fichero de datos al final de
innodb_data_file_path, e iniciar nuevamente el
servidor.
Si el último fichero de datos especificado tiene la palabra clave
autoextend, el procedimiento para editar a
my.cnf debe tener en cuenta el tamaño que ha
alcanzado este último fichero. Hay que obtener el tamaño del fichero de
datos, redondearlo hacia abajo a la cantidad de megabytes
(1024 * 1024 bytes) más cercana, y especificar este número explícitamente
en innodb_data_file_path. Entonces se podrá agregar
otro fichero de datos. Hay que recordar que solamente el último fichero de
datos en innodb_data_file_path puede especificarse como
autoextensible.
Como ejemplo, se asumirá que el espacio de tablas tiene sólo un fichero de
datos autoextensible ibdata1:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suponiendo que este fichero de datos, a lo largo del tiempo, ha crecido hasta 988MB, debajo se ve la línea de configuración luego de agregar otro fichero de datos autoextensible.
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
Cuando se agrega un nuevo fichero al espacio de tablas, hay que asegurarse
de que no exista. InnoDB crea e inicializa el fichero
al reiniciar el servidor.
Actualmente no es posible quitar un fichero de datos del espacio de tablas. Para reducir el tamaño del espacio de tablas, emplear este procedimiento:
Utilizar mysqldump para hacer un volcado de todas
las tablas InnoDB.
Detener el servidor.
Eliminar todos los ficheros existentes del espacio de tablas.
Configurar un nuevo espacio de tablas.
Reiniciar el servidor.
Importar el fichero de volcado de tablas.
Si se desea modificar la cantidad o tamaño de los ficheros de registro
(log) de InnoDB, se debe detener el servidor MySQL y
asegurarse de que se cerró sin errores. Luego, copiar los ficheros de
registro antiguos en un lugar seguro, sólo para el caso de que algo haya
fallado en el cierre del servidor y se necesite recuperar el espacio de
tablas. Eliminar los antiguos ficheros de registro del directorio de
ficheros de registro, editar my.cnf para modificar la
configuración de los ficheros de registro, e iniciar nuevamente el
servidor MySQL. mysqld verá al iniciar que no hay
ficheros de registro e informará que está creando nuevos.
La clave de una administración de bases de datos segura es realizar copias de respaldo regularmente.
InnoDB Hot Backup es una herramienta de respaldo en
línea que puede utilizarse para respaldar la base de datos
InnoDB mientras ésta se está ejecutando.
InnoDB Hot Backup no necesita que se detenga la base de
datos y no establece ningún bloqueo ni dificulta el normal procesamiento
de la base de datos. InnoDB Hot Backup es una
herramienta adicional comercial (no grautita) cuyo cargo anual de licencia
es de €390 por cada ordenador en el que se ejecute el servidor MySQL.
Consulte la página de
Internet de InnoDB Hot Backup para obtener
información detallada y ver capturas de pantallas.
Si se está en condiciones de detener el servidor MySQL, puede realizarse
una copia de respaldo binaria, que consiste en todos los ficheros usados
por InnoDB para administrar sus tablas. Se utiliza el
siguiente procedimiento:
Detener el servidor MySQL y asegurarse de que lo hace sin errores.
Copiar todos los ficheros de datos (ficheros
ibdata e .ibd) en un lugar
seguro.
Copiar todos los ficheros ib_logfile en un lugar
seguro.
Copiar el o los ficheros de configuración my.cnf
en un lugar seguro.
Copiar todos los ficheros .frm de las tablas
InnoDB en un lugar seguro.
La replicación funciona con tablas InnoDB, de forma que
puede emplearse para mantener una copia de la base de datos en sitios de
bases de datos que necesiten alta disponibilidad.
Adicionalmente a la realización de copias de respaldo binarias como se ha
descripto, también se deberían realizar regularmente volcados de las
tablas con mysqldump. El motivo de esto es que un
fichero binario podría corromperse sin que el usuario lo note. El volcado
de las tablas se almacena en ficheros de texto que son legibles por los
seres humanos, facilitando la localización de corrupción en las tablas.
Además, puesto que el formato es más simple, las probabilidades de una
corrupción seria de datos son menores. mysqldump
también tiene una opción --single-transaction que puede
usarse para capturar una imagen consistente de la base de datos sin
bloquear otros clientes.
Para estar en condiciones de recuperar una base de datos
InnoDB a partir del respaldo binario descripto
anteriormente, se debe ejecutar el servidor MySQL con el registro binario
(binary logging) activo. Entonces se puede aplicar el log binario al
respaldo de la base de datos para lograr la recuperación a una fecha y
hora determinadas:
mysqlbinlog nombre_de_host-bin.123 | mysql
Para recuperarse de una caida del servidor, sólo se requiere reiniciarlo.
InnoDB verifica automáticamente los registros (logs) y
ejecuta una recuperación de la base de datos del tipo roll-forward, es
decir, hasta el momento anterior a la falla. InnoDB
revierte automáticamente las transacciones no grabadas que existían al
momento de la caída. Durante la recuperación, mysqld
muestra información parecida a esta:
InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 13674004 InnoDB: Doing recovery: scanned up to log sequence number 0 13739520 InnoDB: Doing recovery: scanned up to log sequence number 0 13805056 InnoDB: Doing recovery: scanned up to log sequence number 0 13870592 InnoDB: Doing recovery: scanned up to log sequence number 0 13936128 ... InnoDB: Doing recovery: scanned up to log sequence number 0 20555264 InnoDB: Doing recovery: scanned up to log sequence number 0 20620800 InnoDB: Doing recovery: scanned up to log sequence number 0 20664692 InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745 InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncom