MariaDB / Backup de texto delimitado

Bienvenidos sean a este post, hoy veremos otra alternativa de backups.

Cuando hablamos de un backup de texto delimitado nos referimos a un archivo que puede ser leido por humanos donde las columnas estaran separadas por un caracter, el archivo mas difundido o utilizado son los de tipo CSV, Valores Separados por Comas por sus siglas en ingles, si bien estos archivos son separados por comas pero en la realidad el separador se basara en el separador de listas del sistema operativo, aunque mariadb soporta las siguientes posibilidades para generar archivos:

• La opcion –tab de la herramienta mysqldump
• El comando SELECT … INTO OUTFILE
• El motor de almacenamiento CSV
• El motor de almacenamiento CONNECT

Por otro lado mariadb posee las siguientes formas para recuperar los datos generados con las opciones anteriores:

• La herramienta mysqlimport
• El comando LOAD DATA INFILE
• El motor de almacenamiento CSV
• El motor de almacenamiento CONNECT

Hablemos sobre la primera opcion de exportacion como es la opcion –tab, si la utilizamos con mysqldump nos generara dos archivos, uno con la extension .sql donde estaran todos los comandos para la creacion de las bases y tablas (en caso de no existir) y otro con extension .txt donde estaran todos los datos en columnas separaddos por tabuladores y el caracter de nueva linea es lo que diferencia cada linea, aunque estos caracteres lo podemos variar con las opciones de mysqldump, con esta opcion tambien podemos especificar donde se generaran los archivos:

--tab=/TMP/TEST

La herramienta mysqlimport al igual que mysqldump se instalan con todas las distribuciones de mariadb o mysql, su sintaxis es la siguiente:

$ mysqlimport [opciones] nombre_base archivo [archivo] ...

Donde podemos usar las mismas opciones para conectarnos que el cliente pero los unicos valores que si son obligatorios son el nombre de la base donde haremos el volcado y por lo menos debemos indicar un archivo con la informacion, despues podemos pasar todos los que sean necesarios, de manera predeterminada esta herramienta como el archivo se pueden ejecutar desde el servidor pero tambien podemos hacerlo desde el cliente aunque para ello debemos usar la opcion –local.

Pasemos a hablar sobre la siguiente forma de crear archivos, INTO OUTFILE nos permite enviar el resultado de un query a un archivo, sino se especifica un destino esto lo creara de manera predeterminada en el directorio donde se encuentran todos los directorios con los datos, dependera de la distribucion que uses, pero no en el directorio de datos de la base pero una mejor opcion es pasarlo, el inconveniente mas importante es que el archivo no es generado por el usuario del cliente sino por el usuario mysql por lo tanto tengan en cuenta donde lo descargaran para que este usuario tenga acceso, la buena practica recomienda hacerlo en el directorio tmp, por ultimo el archivo se creara en el servidor y no hay forma de hacerlo en el servidor, para entender como funciona vamos a hacer un ejemplo:

MariaDB [(none)]> select *
    -> from base1.tabla3
    -> into outfile '/tmp/sarasa.txt';

En este caso le pedi que haga un volcado de todo el contenido en una tabla, observen como lo unico distinto es la opcion INTO OUTFILE donde le especifico un destino, hagamos un listado para ver si se creo el archivo:

tinchicus@dbn001vrt:~$ ls -l /tmp/sar*
-rw-r--r-- 1 mysql mysql 27 sep  9 03:28 /tmp/sarasa.txt
tinchicus@dbn001vrt:~$

Y si mostramos el archivo veremos el contenido descargado:

tinchicus@dbn001vrt:~$ cat /tmp/sarasa.txt 
5	Martin	Miranda	tinchicus@gmail.com	45	Dunwich
6	Enzo	Tortore	elgary@tinchicus.com	33	Dunwich
7	DarkZero	Aleman	darkie@tinchicus.com	32	Miskatonic
8	Ariel	Polizzi	eltio@tinchicus.com	47	Arkham
16	John	Doe	thebiggerdickonthist	23	New York
tinchicus@dbn001vrt:~$

Podemos ver como cada campo es separado por un tabulador, al igual que la opcion –tab podemos cambiar el caracter separador pero eso lo veremos luego, esto mismo que hicimos podemos hacerlo con el cliente sin ingresar al mismo, veamos el mismo anterior:

$ mariadb -u tinchicus -p -e "select * from base1.tabla3" > /tmp/salida.txt

Esto seria exactamente lo mismo que usar el INTO OUTFILE pero con la diferencia de que aqui se creara el archivo en base al usuario que ejecuta el cliente, pasemos a ver como podemos cargar el archivo que generamos anteriormente mediante LOAD DATA INFILE, veamos su sintaxis:

LOAD DATA [LOW_PRIORITY | CONCURRENT]
    [LOCAL] INFILE 'archivo'
    [REPLACE | IGNORE]
    INTO TABLE nombre_tabla [PARTITION (nombre_part, ...)]
    [CHARACTER SET charset]
    [otras opciones]
    [IGNORE n {LINES | ROWS}]
    [(columna, ...)]
    [SET column = expresion, ...]

Como pueden ver tenemos varias opciones para ajustar un poco mejor como queremos recuperar la informacion, las opciones que no estan entre corchetes son las obligatorias el resto son opcionales, hagamos un ejemplo de como recuperarlo de forma basica:

LOAD DATA INFILE '/tmp/salida.txt' INTO TABLE base1.tabla3;

Veamos algunas de las opciones disponibles:

• LOW_PRIORITY, establece que debe tener menos prioridad que las operaciones de lectura
• CONCURRENT, establece que se deben usar los ingresos concurrentes de MyIsam
• REPLACE, reemplaza los valores existentes
• IGNORE, ignora a los valores ya existentes
• LOCAL, especifica que el archivo sera enviado desde el cliente al servidor
• CHARACTER SET, establece el conjunto de caracteres que usaremos para los datos
• IGNORE n, establece cuantas lineas ignoraremos, LINES o ROWS es por un tema de compatibilidad

Otra opcion interesante que disponemos es la posibilidad ver por medio de SHOW CREATE TABLE como fue el comando para generar una tabla, veamos un ejemplo:

MariaDB [(none)]> show create table base1.tabla3 \G
*************************** 1. row ***************************
       Table: tabla3
Create Table: CREATE TABLE `tabla3` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(20) NOT NULL,
  `apellido` varchar(20) NOT NULL,
  `correo` varchar(20) NOT NULL,
  `edad` int(3),
  `ciudad` varchar(20) DEFAULT NULL COMMENT 'Es una tabla de prueba',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=keybcs2
1 row in set (0.006 sec)

MariaDB [(none)]>

Para establecer los caracteres de limite para mysqldump o mysqlimport se utilizan las siguientes opciones:

• –fields-terminated-by=cadena, establece cual sera el caracter o cadena que separa las columnas
• –fields-enclosed-by=cadena, encomilla los valores de cadena usando la secuencia informada
• –fields-optionally-enclosed-by=string, idem al anterior pero con optionally omitimos las comillas cuando no sean necesarias
• –fields-escaped-by=caracter, se utiliza para indicar cual sera el caracter de escape para acciones especiales
• –lines-terminated-by=cadena, establece el caracter de nueva linea para separar las filas

En cambio para INTO OUTFILE y LOAD DATA INFILE dispondremos de las siguientes opciones:

Opcion	Descripcion
FIELDS TERMINATED BY ‘cadena’	equivale a –fields-terminated-by
FIELDS [OPTIONALLY] ENCLOSED BY ‘cadena’	equviale a –fields-enclosed-by y el OPTIONALLY a –fields-optionally-enclosed-by
FIELDS ESCAPED BY ‘caracter’	equivale a –fields-escaped-by
LINES STARTING BY ‘cadena’	Este es solo usado por LOAD DATA INFILE donde las lineas que comiencen con la cadena informada seran ignoradas
LINES TERMINATED BY ‘cadena’	equivale a –lines-terminated-by

En resumen, hoy hemos visto como crear backups de texto delimitado, de la forma mas practica, dado que se utilizan herramientas que estan disponibles en todas las distribuciones, y practicamente pueden ser utilizados en todas las versiones de mariadb o mysql, espero les haya sido de utilidad sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.