MariaDB / Usando mysqldump

Bienvenidos sean a este post, hoy veremos como crear un archivo de volcado mediante la herramienta mysqldump.

En este post hablamos sobre los tipos de backup que tiene mariadb dependiendo de nuestras necesidades y capacidades, para poder realizar un backup logico tenemos una herramienta llamada mysqldump, esta es la mas popular porque viene incluida en todas las versiones tanto de mariadb como de mysql, veamos los pasos que hace para crear el archivo de volcado:

• Se conecta al servidor
• Lee los datos a respaldar
• Se crea el archivo con todos los comandos SQL para generar los mismos datos

Esta es la forma mas basica de creacion del archivo de volcado pero esta herramienta tambien opciones para controlar que informacion vamos a bajar al archivo, aunque tambien podemos modificar al archivo final de forma manual pero esto no es recomendable, dado que el archivo posee comandos genericos que vimos al comienzo del curso nos brinda la posibilidad de no solamente ser utilizado en versiones viejas de mariadb, sino tambien en mysql y con algunas pequeñas modificaciones (en caso de ser necesario) en otras bases de datos, por todas estas razones esta herramienta se convierte en el programa favorita para realizar estas tareas, dado que tambien se puede utilizar para otras aplicaciones como copiar una base de datos o tablas de un servidor a otro, o generar un archivo que esta corriendo durante la instalacion de la aplicacion.

La contra mas importante de un archivo de volcado es que ocupa mucho espacio, esto es porque no solamente tenemos la informacion de una forma descomprimida sino que esta entre comandos SQL para poder generarla, aunque esta herramienta posee una opcion para crear backups textuales pero de eso hablaremos mas adelante, la herramienta soporta tres sintaxis para poder realizar el volcado:

$ mysqldump [opciones] --all-databases > nombre_archivo

Este descargara todas las bases de datos con sus contenidos en un solo archivo, veamos la siguiente:

$ mysqldump [opciones] --databases bd1 bd2 ... > nombre_archivo

Con esta le decimos que descargue las bases de datos con todos los datos al archivo que le pasamos, veamos la siguiente:

$ mysqldump [opciones] nombre_base tabla1 tabla2 ... > nombre_archivo

En este caso descargara las tablas pasadas de la base informada en nombre_base, como pueden ver son las opciones mas basicas que nos permiten definir de mejor forma cual sera la informacion que descargaremos al archivo, aunque en la descarga de multiples bases podemos hacer que ignore algunas tablas mediante la siguiente opcion:

--ignore-table=nombrebd.nombretab

Si necesitamos ignorar multiples tablas usamos la misma opcion con cada una de las tablas que necesitamos ignorar, las opciones para conectarnos son las mismas que usamos para el cliente, las cuales vimos en este post o tambien pueden usar el archivo my.cnf que vimos en este post para ingresar automaticamente.

Pasemos a ver algunas caracteristicas del archivo, de manera predeterminada utiliza a CREATE DATABASE pero si nosotros queremos evitarlo se puede usar a –no-create-db, otra opcion disponible es agregar un DROP DATABASE antes del CREATE DATABASE asi que si existe una base de datos dañada se reemplaza con una completa de datos correctos, para ello debemos usar a –add-drop-database, como dijimos al comienzo de manera predeterminada se incluye la definicion de la tabla y los datos pero estos pueden ser excluidos:

• –no-create-info, no incluye la definicion de la tabla
• –no-data, no incluye los datos de la tabla

Si bien las opciones de CREATE TABLE no son estandard, dado que solo trabajan en mariadb y la mayoria en mysql, no son incluidas salvo que utilicemos la opcion –table-options, tambien podemos usar REPLACE en lugar de INSERT para ingresar los datos, esto evitara que se elimine informacion previa y simplemente se reemplace para ello debemos usar la opcion –replace, aunque tambien podemos usar a INSERT IGNORE mediante la opcion –insert-ignore, cual es la principal diferencia entre ambos? es que INSERT IGNORE a pesar de ignorarlo elimina previamente la informacion en cambio REPLACE no, esto debe ajustarse a la necesidad de cada uno.

Los programas almacenados (triggers, rutinas, funciones y eventos) no son incluidos de manera predeterminada, sin embargo deberian ser incluidos en el backup logico, esto es porque no ocupan mucho espacio, para incluirlos podemos usar las opciones:

• –triggers
• –routines
• –events

Podemos hacer que el volcado consiste de una sola transaccion, esto nos garantiza la integridad de los datos a traves de las tablas, para ello usamos la opcion –single-transaction haciendo que la herramienta comience una transaccion en el nivel de aislamiento REPETEABLE READ antes de comenzar a leer los datos y ejecutar el COMMIT despues del proceso de volcado, tambien disponemos de la opcion –no-autocommit la cual rodeara cada insercion de tabla con las opcion de SET autocommit=0 al comienzo y COMMIT al final, hara el volcado mas rapido pero puede hacer que mientras una tabla es procesada otras tablas pueden ser modificadas por otras sesiones.

De manera predeterminada mysqldump lee todas las filas del servidor dentro de un buffer y luego las escribe juntas dentro de un archivo de volcado, si bien es una herramienta de optimizacion cuando descarguemos grandes cantidades de datos podemos requerir grandes cantidades de memoria, para evitar el «buffeado» podemos usar la opcion –quick, cambiando un poco de tema veamos un ejemplo de como inicia un archivo:

-- MySQL dump 10.19  Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       10.3.29-MariaDB-0+deb10u1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0$
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


...

Ahora veamos como es el volcado de una base de datos:

--
-- Current Database: `base2`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `base2` /*!40100 DEFAULT CHARACTER SET$

USE `base2`;

--
-- Table structure for table `fotos`
--

DROP TABLE IF EXISTS `fotos`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fotos` (
  `correo` varchar(20) NOT NULL,
  `archivo` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `fotos`
--

LOCK TABLES `fotos` WRITE;
/*!40000 ALTER TABLE `fotos` DISABLE KEYS */;
INSERT INTO `fotos` VALUES ('tinchicus@gmail.com','tinchicus.png'),('elgary@tin$
/*!40000 ALTER TABLE `fotos` ENABLE KEYS */;
UNLOCK TABLES;

Aqui tenemos como se crea la base de datos y una de las tablas con sus respectivo volcado de datos, asi tendran por cada una de las bases y tablas en el archivo.

Por ultimo hablaremos sobre como restaurar este archivo, si bien disponemos de muchas formas si el archivo es muy pequeño podremos copiar y pegar el mismo en una GUI para ejecutarlo pero sino podemos usar el cliente en una terminal para volcarlo y para ello podemos usar de la siguiente forma:

$ mysql [opciones] < nombre_archivo

Aunque si tenemos una instancia de mariadb abierta podemos usar el comando SOURCE:

SOURCE 'path_archivo' path'path';

En resumen, hoy hemos visto como crear un archivo de volcado mediante mysqldump, como se hace, las caracteristicas principales de esta, algunas opciones que nos puede ser utiles para nosotros y como recuperarlo finalmente, 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.