Al exportar una base de datos con mysqldump y quererla reimportar deberemos vigilar si nos añade el use database por si queremos importarla con otro nombre.

Las opciones de mysqldump al respecto son:

$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

Para exportar una base de datos, podemos usar indistintamente mysqldump <db> o mysqldump –databases <db>, pero existe una pequeña diferencia:

Con la opción –databases podemos indicar más de una única base de datos, por lo que nos añadirá el comando use db antes de exportar cada base de datos. Por lo tanto, si luego queremos importar el dump en otra base de datos …

En un MySQL con replicación, todo lo que acaba en el binlog se consideran modificaciones que se traspasan a los slaves para que sean modificados igual que el master.

En el caso de los analyze y optimize, no se ve tan claro como un UPDATE:

• analyze: Actualiza la información que utiliza el optimizador sobre la distribución de las filas en la tabla
• optimize: Elimina el espacio que ya no se usa: Cuando un dato es eliminado es marcado como eliminado en lugar de eliminar-se, con este comando recreamos la tabla eliminando el espacio no utilizado

Estos comandos acaban en el binlog, por lo que no hace falta tener estas tareas programadas en los slaves. Mediante el comando mysqlbinlog lo podemos ver: …

En MySQL existen dos timeouts diferentes según si el cliente se identifica como una conexión interactiva o no. Las variables que definen estos timeouts son wait_timeout y interactive_timeout

Si definimos en el my.cnf los siguientes timeouts:

wait_timeout=60
interactive_timeout=90

Con el siguiente código PHP:

<?php
$link = mysql_connect('localhost', 'root', '');

print_r(mysql_fetch_assoc(mysql_query("show variables like 'wait%';")));

mysql_close($link);
?>

Obtendremos el valor de wait_timeout de la sessión no interactiva:

$ php wait.php 
Array
(
    [Variable_name] => wait_timeout
    [Value] => 60
)

Pero si nos conectamos con el cliente de consola el valor de wait_timeout tomará el …

El error “Unknown table engine InnoDB” puede ser que tengamos el engine deshabilitado en el my.cnf con skip-innodb pero también puede ser que nos enfrentemos a problemas de memoria:

En el caso que en el my.cnf no tengamos el skip-innodb, deberemos comprobar el log del MySQL ya que nos podemos encontrar con:

120413 19:40:18  InnoDB: Error: cannot allocate 157302784 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: …

Toda migración acaba obteniendo una cierta complejidad si queremos minimizar el tiempo de corte y poder garantizar haya una “vuelta atrás” en caso de problemas. Vamos a ver una forma de migrar un MySQL master de un disco de una cabina a otro disco de otra cabina minimizando el tiempo de corte gracias a un slave que transformamos en master y volvemos a transformar en slave.

Suponiendo que tenemos un MySQL con un slave y queremos migrar de un disco en una cabina antigua a otro disco de una cabina nueva: Mientras se traspasan los datos podemos usar el slave como master y cuando acabe podemos sincronizar los dos MySQL y volver a dejar el MySQL con su rol habitual.

La hora de ruta sería la siguiente:

Previo a la intervención:

• Apagar procesos batch …

Como ya comenté anteriormente ahora mis esfuerzos de blogging se centran más en generar contenido para MySQL High Performance ? Para los que quieran un rápido listado de mis últimos posts, aquí lo tenéis:

• Avoiding auto-increment holes on InnoDB with INSERT IGNORE
  
• Statement based replication with Stored Functions, Triggers and Events
  
• Actively monitoring replication connectivity …

Un MySQL puede estar compilado para soportar varios STORAGE ENGINES, vamos a ver de los que disponemos y sus características:

El comando para ver el tipo de tablas que podemos usar en el MySQL es con SHOW STORAGE ENGINES o SHOW ENGINES:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO …

He usado herramientas como MySQL Migration Toolkit (*) y Bullzip’s Access To MySQL, ambos hacen un excelente trabajo pero no exporta las relaciones entre tablas. Hacer esta tarea nos puede tomar varias horas (hasta ahora):

Escribí un código VBA para identificar las relaciones generando código MySQL con las sentencias de creación, esto puede ser muy útil después de la migración usando cualquier herramienta libre.

Option Explicit
'Copiar y pegar esta función en un módulo nuevo/existente de MS Access.

Public Sub printRelations()
    Dim sql, fk As String
    Dim i, j As Integer
    For i = 0 To CurrentDb.Relations.Count - 1
        sql = "ALTER TABLE `" & CurrentDb.Relations(i).ForeignTable & _
            "` ADD CONSTRAINT `" & CurrentDb.Relations(i).Name & "` FOREIGN KEY ("
        fk = "("
        For j = 0 To CurrentDb.Relations(i).Fields.Count - 1
            sql = sql & "`" & CurrentDb.Relations(i).Fields(j).ForeignName & "` ," …

En ciertas bases de datos dónde el tiempo de recuperación de una tabla es crítico podemos mantener un conjunto de snapshots online (penalizando el rendimiento del disco) con el fin de disponer de puntos de recuperación muy cercanos al estado actual. Vamos a ver como configurarlo usando mylvmbackup

Primero lo descargamos:

cd /usr/local/src
wget http://www.lenzg.net/mylvmbackup/mylvmbackup-0.13.tar.gz
tar xzf mylvmbackup-0.13.tar.gz 

E instalamos normalmente:

cd mylvmbackup-0.13
make prefix=/usr/local/ install

A continuación deberemos indicar en el fichero de configuración (/etc/mylvmbackup.conf) los datos para acceder al MySQL:

[mysql]
user=root
password=passwdroot
host=localhost
port=3306
socket=/tmp/mysql.sock
mycnf=/etc/my.cnf

En la secciones siguientes definimos el LVM que contiene el datadir del …

Acabo de modificar una base de datos, no debió demorar las dos horas que demoré en realizar la tarea si no fuera por que MySQL no ha mejorado aun sus mensajes de error.

La tarea fue agregar una nueva clave foránea a una tabla existente, veamos como reproducir lo que me ha pasado:

-- Crear las tablas foo y bar
create table foo (
        id integer not null primary key,
        bar_id int not null
);
create table bar (
        id integer not null primary key
);
-- Intentar crear una clave foránea
alter table foo
        add foreign key(bar_id) references bar(ANY_FIELD) ;

Al ejecutar la ultima sentencia MySQL devuelve un error genérico que puede significar muchas cosas:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Todo hubiera sido mas fácil si me hubiera dado cuenta que escribí mal el campo de la tabla de deferencia bar(ANY_FIELD) (algunas veces pasa), si …