Convertir ficheros mdb (MS Access) a MySQL

En un reciente proyecto me han pasado los datos en un fichero de Microsoft Access, un fichero con extensión .mdb

Antes de instalarme un Windows con Office en una máquina virtual, cosa que detesto, he preferido buscar opciones para hacerlo con mi Linux Ubuntu 10.04.

Tras varios intentos he decidido convertir las tablas a formato SQL y cargarlas en el MySQL de mi servidor.

Por si alguien lo necesita hacer, los pasos son estos:

1.- mdb-tools

Antes de nada hay que instalar las herramientas mdb-tools, en Ubuntu es sencillo:

sudo apt-get install mdbtools

La página de esta fantástica herramienta es http://mdbtools.sourceforge.net.

2.- Convertir el fichero

Las mdbtools son un conjunto de pequeños programas que permiten extraer información de un fichero de Access. Las herramientas que usaremos son estas:

1. mdb-schema  para extraer el esquema de las tablas en formato SQL.
2. mdb-tables  para extraer una lista de las tablas
3. mdb-export  para extraer los datos de cada tabla

El primer paso es extraer el esquema de la tabla (los CREATE TABLE), para ello hay que hacer lo siguiente:

mdb-schema -S  mysql

Esto escribe en la salida estándar las instrucciones en formato MySQL. La herramienta puede generar otros sublenguajes SQL diferente del MySQL, para eso es el último parámetro.

La opción ‘-S’ arregla los nombres de tablas y campos raros: sustituye espacios y acentos por guiones bajos, etc.

El formato generado tiene algunas pegas que se solucionan concatenando una instrucción ‘sed’ detrás:

mdb-schema -S  mysql | sed -e 's/^DROP TABLE/DROP TABLE IF EXISTS/g'
-e 's/^---/-- /' -e 's/^);$/) DEFAULT CHARSET=utf8;/g'

Básicamente esto sustituye “DROP TABLE xxxx” por “DROP TABLE IF EXISTS xxxxx”, y coloca un “DEFAULT CHARSET=utf8″ al final de las CREATE TABLE.

Luego necesitamos los datos de las tablas, para ello usaremos ‘mdb-export’, pero como hay que pasarle el nombre de la tabla a extraer necesitamos una lista, que obtendremos así:

mdb-tables -1

Con esta lista vamos volcando los datos una a una:

mdb-export -S -I   | sed -e 's/)$/)\;/'

La instrucción ‘sed’ final coloca el muy útil ‘;’ detrás de las instrucciones, curiosamente ‘mdb-export’ no los pone.

La opción ‘-S’ ya la conocemos, en cambio la opción ‘-I’ indica que el volcado de datos se haga en SQL y no en CSV.

3.- Poniéndolo todo junto

Pues ahora sólo falta ponerlo en un útil script para que la próxima vez no tengamos que leer este artículo

#!/bin/bash
 
if [ $# != 1 -o ! -f "$1" ]; then
	echo "Uso: $0 fichero-mdb"
	exit 1
fi
 
echo "SET NAMES 'utf8';"
 
mdb-schema -S "$1" mysql | sed -e 's/^DROP TABLE/DROP TABLE IF EXISTS/g' 
-e 's/^---/-- /' -e 's/^);$/) DEFAULT CHARSET=utf8;/g'
 
tables=`mdb-tables -1 "$1" | tr " " "_"`
 
for table in $tables; do
	new_table=`echo $table | tr "_" " "`
	mdb-export -S -I "$1" "${new_table}" | sed -e 's/)$/)\;/' 
done

El funcionamiento es sencillo, si hemos llamado al script ‘convert_mdb_to_mysql.sh’, lo ejecutaremos así:

bash convert_mdb_to_mysql.sh fichero.mdb

La salida la redireccionamos a un fichero y ya lo podemos cargar en nuestro MySQL.

Problema resuelto.

Ubuntu 10.04, problemas con xdebug, Cache_Lite y DBA

Llevo un par de días utilizando el nuevo Ubuntu 10.04 (versión 64 bits).

La verdad es que es muy bueno, arranca mucho más rápido que los anteriores y visualmente me gusta.

Sin embargo en cuanto he comenzado a trabajar con PHP en esta nueva versión me he encontrado con un problema.

La versión de PHP que instala es la 5.3.2. Para el que no había trabajado antes con ella es un poco duro. Para empezar han desaparecido algunas funciones que estaban marcadas como “deprecated” y que ahora ya no están disponibles.

Esto está bien siempre que los módulos que instale ubuntu estén acordes con este cambio. Pero ya me he encontrado con 3 módulos de PHP que simplemente no funcionan bien:

1. Xdebug
2. PEAR::Cache_Lite
3. DBA

Vamos por partes, como dijo Jack The Ripper:

Xdebug

Normalmente uno esperaría que al instalar el módulo de ubuntu todo funcionara (normalmente hay que modificar el fichero de configuración del xdebug para que funcione).

La cuestión es que una vez instalado, todo parece que funciona, debuga bien hasta que se te ocurre inspeccionar una variable local, entonces siempre están indefinidas.

Solución: compilar el xdebug manualmente

Primer paso, quitar el xdebug del ubuntu:

$ sudo apt-get remove php5-xdebug
$ sudo apt-get install php5-dev

Luego, bajar las fuentes del xdebug y compilarlo:

$ svn co svn://svn.xdebug.org/svn/xdebug/xdebug/trunk xdebug
$ cd xdebug
$ phpize
$ ./configure --enable-xdebug
$ make
$ sudo make install
Installing shared extensions:     /usr/lib/php5/20090626/

Al terminar el “make install” indica el directorio donde se ha colocado el módulo, ahora basta con editar o crear el fichero /etc/php5/conf.d/xdebug.ini con los siguientes contenidos:

zend_extension=/usr/lib/php5/20090626/xdebug.so
[xdebug]
xdebug.remote_enable=1
xdebug.remote_host="localhost"
xdebug.remote_port=9000
xdebug.remote_handler="dbgp"

Con esto ya podremos debugar PHP perfectamente. Yo para esta tarea uso Netbeans 6.8, pero antes lo hacía con Eclipse 3.2 y funcionaba igual, luego supongo que funcionará con todo.

PEAR::Cache_Lite

Otro que tal baila. Este tiene el problema que usa una función de la librería de PHP que se ha quitado en algun momento de la versión 5.3.
La solución consiste en instalarlo mediante PEAR directamente. ¿Cómo?

Primero quitamos el módulo instalado por ubuntu:

$ sudo apt-get remove php-cache-lite

Necesitamos tener instalado el PEAR y el paquete de desarrollo de PHP:

$ sudo apt-get install php-pear php5-dev

Y ahora hacemos:

sudo pear install Cache_Lite

¡Y a disfrutarlo!

DBA

Y este funciona bien, excepto cuando se quiere usar el driver “db4″, que, por cierto, es de los que trae preconfigurado, el único con un poco de utilidad.

El error al abrir un fichero “db4″ es este:

dba_open(): Driver initialization failed for handler: db4: Invalid argument

Muy interesante. Googleando por ahí he descubierto que algún manazas (con todo el cariño, porque todos cometemos errores) ha compilado el módulo de DBA contra los ficheros de configuración de una versión de libdb4 diferente a la que luego enlaza.

Este error, que yo sepa, sólo se soluciona de una manera: recompilando PHP o esperando a que alguien graciosamente nos coloque una actualización.

Este no lo he solucionado, en vez de eso estoy usando Cache_Lite en vez de fichero DBA… no hay mal que por bien no venga.

Espero que todo esto le sirva a alguien.

Saludos.

PHP 5: Añadir caché a la capa de acceso a datos

Hace algún tiempo escribí un artículo en Nettuts que enlazo desde aquí.

http://net.tutsplus.com/tutorials/php/oo-php5-adding-caching-to-data-access-layer/

En este artículo expongo cómo añadir caché a nivel del SQL de nuestra aplicación. En el ejemplo se utilizan ficheros para guardar la caché, pero el modelo permite ser extendido con diferentes almacenamientos.

La idea es utilizar el string del SQL como clave para una caché de tipo clave-valor, utilizando, además, expiración por tiempo para la actualización de la caché.

Por si le sirve a alguien…

PHP: Cómo hacer backups de MySQL sin mysqldump

Me encuentro en una situación en la que muchos desarrolladores web nos encontramos: tenemos que hacer backups de bases de datos que están en servidores en donde no tenemos acceso directo al servidor MySQL.

Muchas veces disponemos del excelente phpMyAdmin para ese acceso. Con esta aplicación podemos hacer backups, pero manualmente.

¡Pero yo necesito automatizar estos backups!

Objetivo

Mi objetivo es tener una URL a la que hacer una petición GET y esta haga un volcado SQL de la base de datos en un fichero. Luego ese fichero me lo bajaré con rsync.

mysqldump

Si tenemos acceso a este programa en nuestro servidor podemos usar la instrucción system() de PHP para crear ese fichero. Pero esta es otra de las restricciones de mi caso: ¡tampoco tengo mysqldump!

SELECT INTO OUTFILE

MySQL tiene una extensión en la sentencia SELECT que podría salvarme la vida: INTO OUTFILE ‘fichero’. La cosa viene a ser así:

SELECT * FROM tabla INTO OUTFILE 'fichero'

Esto es genial salvo que ese fichero se guarda necesariamente en el servidor que está corriendo el MySQL. Cuando tienes un servidor web distinto del de MySQL entonces ya no lo puedes usar.

Uffff, esto se complica.

¡Hazlo en PHP!

Necesito un script en PHP que sea capaz de escribir en un fichero el volcado de la mi base de datos.

Milagrosamente mucha otra gente parece que se ha encontrado con el mismo problema. He ido encontrado scripts que más o menos hacían lo que yo necesitaba, pero por una cosa o por otra no los he podido usar directamente. Listo aquí algunos:

BackupMonster

Backup Monster. Está orientado a un servicio manual. Muestra una pantalla de presentación en un idioma que no reconozco. En cualquier caso no es lo que quiero. Para empezar no permite comprimir el fichero resultante.

En sourceforge parece ser que está repetido: mySQLDbBackup.

phpMyBackupPro

phpMyBackupPro. Este es el más interesante. De hecho es una aplicación completa que permite programar backups en el tiempo y muchas más cosas.

Este permite hacer un script al cual llamar para hacer el backup. Para mi caso creo que es un poco sofisticado, aunque admito que me habría servido, no quiero añadir más aplicaciones al servidor.

MySQLDumper

MySQLDumper. Otra aplicación de backup al estilo de la anterior. Con muchas opciones, muy molona, y no me gusta por lo mismo.

Pero muy recomendable si os gustan las herramientas tipo PhpMyAdmin. Una imágen vale más que mil palabras:

NOTA: Realmente soy un poco especialito, porque esta aplicación es la bomba. Pero yo lo que quiero es un módulo en PHP que pueda integrar en mi código fácilmente.

Wasabackup

Wasabackup es muy parecido a lo que estoy buscando. Permite comprimir la salida en formato gzip y guardar el fichero resultante en el servidor, devolverlo como download en la misma petición GET o enviarlo por email.

Sin embargo revisando el código no me ha gustado nada. Tiene una clase central que primero acumula toda la salida en memoria y luego la vuelca al fichero. Me parece que para bases de datos grandes debe dar problemas de memoria.

Ya estoy harto, me lo hago yo

Después de toda una mañana buscando y probando creo que he gastado más tiempo del necesario para hacermelo yo mismo.

Al final me he escrito mi propia librería en PHP (compatible con PHP 4, otro requisito del servidor con el que trabajo). He colocado este código como un proyecto en Sourceforge, puedes bajarte los ficheros allí:

http://mimysqldump.sourceforge.net

El funcionamiento es muy sencillo.

Hay una clase central llamada MysqlDump que recibe los datos de conexión en el constructor, además de una instancia de un objeto de tipo Output.

La clase Output sirve para generar diferentes tipos de salida. Debe extenderse por herencia e implementar sus métodos open(), close() y write(). De momento tengo los siguientes (cuyos nombres explican lo que hacen):

• OutputStdout
• OutputFile
• OutputFileGzip
• OutputFileBzip2

Para hacer un dump a un fichero comprimido con bzip2 hay que hacer un script en php con el siguiente código:

<?
require_once "lib/MysqlDump.php";
require_once "lib/Output.php";
require_once "lib/OutputFileBzip2.php";
 
$e = new MysqlDump($db_host, $db_user, $db_pass, $db_db,
   new OutputFileBzip2($file) );
$e->dumpAllTables();
$e->close();
?>

Más fácil imposible. ¿No?

Ahora el montaje completo de los backups:

Montaje completo

En el servidor web

Copiar en el servidor web que tiene acceso a la base de datos los ficheros de mimysqldumper, por ejemplo dentro de un directorio backup/:

backup/backup.php
backup/lib/OutputFileGzip.php
backup/lib/OutputStdout.php
backup/lib/MysqlDump.php
backup/lib/OutputFileBzip2.php
backup/lib/OutputFile.php
backup/lib/Output.php
backup/lib/SQLExport.php

Editar el fichero backup.php (ahora tiene un ejemplo) y colocar el código anterior, poniendo los datos de la conexión y el nombre del fichero donde volcar el dump.

Por ejemplo:

<?
require_once "lib/MysqlDump.php";
require_once "lib/Output.php";
require_once "lib/OutputFileGzip.php";
 
$file = "backup/backups/database-" . date("Y-m-d") . ".sql.gz";
 
$e = new MysqlDump("misql.host.es", "user", "pasgüor", "mi_drupal",
   new OutputFileGzip($file) );
$e->dumpAllTables();
$e->close();
?>

Este código deja los backups en el directorio backup/backups que tendremos que crear y dar permisos de escritura. Cada ejecución crea un nuevo backup con la fecha del día, de este estilo:

backup/backups/database-2010-03-27.sql.gz

Sobre cómo proteger los backups hay muchas opciones con un .htaccess y está fuera del alcance de este post. En backup/backups yo he puesto esto:

<Files ~ "\.sql\.gz$">
 order deny,allow
 deny from all
</Files>

Lo que impide bajarse los backups con un browser, porque yo me los bajo por rsync. Se debería restringir el acceso al script backup.php para que sólo se pudiera ejecutar desde el cliente (por ejemplo restringiendo a una cierta IP).

En mi casa (cliente)

En el ordenador de mi casa (un Ubuntu muy mono) he colocado un cron que llama cada día al siguiente script bash:

#!/bin/bash
 
#
# paso 1: lanzar las copias en los servidores
#
webs="www.primeraweb.es www.segunda-web.com www.tercera.com/~user3/"
for web in $webs; do
    echo "- Backup de la BD de: $web"
    wget -q -O /dev/null --user-agent="Mozilla/5.0" "http://$web/backup/backup.php"
    # nota: el user agent lo tengo que cambiar para tener acceso a mi servidor
done
 
#
# paso 2: rsync de las cuentas (incluye los backups)
#
cuentas="user1@www.primeraweb.es user2@www.segunda-web.com user3@www.tercera.com"
dest_dir="rsyncs"
if [ ! -d "$dest_dir" ]; then
    mkdir "$dest_dir"
fi
 
for cuenta in $cuentas; do
    dir=`echo $cuenta| cut -d "@" -f1`
    echo "dir=$dir"
    if [ ! -d "$dest_dir/$dir" ]; then
        mkdir "$dest_dir/$dir"
    fi
    echo "- Copiando la cuenta: $cuenta"
    rsync -avz -e ssh "${cuenta}:" "$dest_dir/$dir"
done

El script es muy sencillo. Primero hace un GET en todas las webs del programa backup.php (esto crea el fichero de backup en el servidor web) y luego se baja con rsync los ficheros del servidor web, lo que incluye el directorio backup y el nuevo backup.

El rsync se conecta por SSH a los servidores. Para que no nos pida password tenemos que intercambiar claves entre el servidor y nuestro cliente.

El rsync es genial porque, salvo la primera vez, sólo se descarga los ficheros que han cambiado (en nuestro caso el nuevo backup y si hemos cambiado algun fichero de la web).

NOTA: Para 4 webs que estoy salvando cada una de ellas con un Drupal este script tarda unos 30 segundos en total. No está mal.

Me queda por hacer

Mejorar la seguridad para que backup.php sólo se pueda lanzar desde mi máquina (esto con .htaccess es sencillo).

Purgar ficheros de backup antiguos.

¿Encriptar los ficheros?

Los comentarios son bienvenidos

El código que he colocado aquí es libre, podéis hacer con él lo que queráis, aunque un enlace a este post sería un regalo para mi.

Cualquier duda o sugerencia será bienvenida. Espero que sea útil.

¡Hola!

bcncode está formado por un grupo de programadores de Barcelona. En este blog queremos recoger todo aquello que no sabíamos y que vamos descubriendo en nuestro día a día.

Ti también te sirve no dudes en usarlo. Nos gustaría saber de ti: escribenos un comentario.