Copias de seguridad (Backup) en MySQL

mysqldump es una herramienta que permite realizar copias de seguridad a una o varias bases de datos, una o varias tablas. El fichero resultante contiene las instrucciones necesarias para crear las tablas e insertar los datos correspondientes.

mysqldump puede volcar o recuperar el contenido de una tabla completa o registro a registro, esta última es una opción recomendable a la hora de realizar copias de seguridad a tablas que contienen muchos resgistros.

Sinopsis

$ mysqldump [opciones] nom-bd [tablas]
$ mysqldump [opciones] --databases nom-bd1 [nom-bd2 nom-bd4 ...]
$ mysqldump [opciones] --all-databases

Hacer copias de seguridad a la base de datos prueba

$ mysqldump -u usuario -p --opt prueba > prueba.sql

Donde –opt es una atajo a:

--add-drop-table: Añade una sentencia DROP TABLE (Eliminar tabla) antes de
CREATE TABLE

--add-locks: Encierra las sentencias de volcado de cada tabla con LOCK, UNLOCK TABLE.
Permite inserciones mas rápidas

--create-options: Incluye opciones específicas del MySQL en cada sentencia:
CREATE TABLE

--disable-keys: Encierra las sentencias INSERT de cada tabla en un
/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; y
/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;. 

Permite cargar el fichero de volcado más rápido debido a que los índices se crean 
despúes de insertados los registros (Efectivo solo para índices MyISAM no únicos)

--extended-insert: Inserción en masa: Incluye varios registros en la sentencia insert
para cada tabla. Permite que el fichero de volcado (.sql) sea más pequeño y el proceso
de inserción sea más rápido

--lock-tables: Bloquea las tablas antes de volcarlas, permitiendo inserciones concurrentes
para tablas MyISAM, para motores de almacenamientos transaccionales como InnoDB,
BDB --single-transaction es una mejor opción

--quick: Inserta registro a registro en lugar de la tabla entera. Util para tablas con
muchos registros

--set-charset: Añade SET NAME charset-por-defecto

A partir de MySQL 5.1 la opción --opt está habilitada por defecto por tanto tendría el mismo efecto ejecutar

$ mysqldump -u usuario -p prueba < prueba.sql

Para revertir el efecto de una opción incluya --skip-opción

Por ejemplo para no incluir sentencias específicas del MySQL en cada INSERT

$ mysqldump -u usuario -p --opt --skip-create-options prueba > prueba.sql

También puede deshabilitar todas las opciones anteriores ejecutando

$ mysqldump -u usuario -p --skip-opt prueba > prueba.sql

Para habilitar una opción agregue la opción despues de –skip-opt

Por ejemplo para incluir sentencias específicas del MySQL en cada INSERT

$ mysqldump -u usuario -p --skip-opt --extended-insert prueba > prueba.sql

Restaurar base de de datos

$ mysqldump -u usuario -p  nom-base-de-datos < prueba.sql

Atención: Use con cuidado la opción --databases

La opción --databases incluye en el fichero de volcado las sentencias USE, CREATE DATABASES por tanto ejecutar el fichero de volcado sobre una base de datos diferente no tendrá efecto.

Por ejemplo

Hacer copia de seguridad a prueba

$ mysqldump -u usuario -p  --databases prueba > prueba.sql

Restaurar sobre prueba_copy

$ mysql -u usuario -p  prueba_copy < prueba.sql

No tiene efecto ya que en prueba.sql se encuentra la sentencia USE `prueba` y por tanto la operación se realizará sobre prueba y no sobre prueba_copy como puede pensarse al principio

Script para hacer copias de seguridad a base de datos MySQL

#!/usr/bin/env bash
# GNU shell script para realizar copias de seguridad a bases de
# datos MySQL
# ---------------------------------------------------------------
# Copyright (c) 2009 flossblog <http://flossblog.wordpress.com/>
# Este script es liberado bajos los téminos de la GNU GPL
# version 2.0 o superior
# --------------------------------------------------------------
# Uso:
# El script recupera todas las base de datos del servidor MySQL
# especificado en HOST y crea una copia de seguridad para cada
# una de ellas almacenándolas en el directorio especificado en
# BACKDIR. Modifique cada variable según sus necesidades. Puede
# ejecutar el script de forma manual o como tarea programada
# -------------------------------------------------------------
# Última actualización: 31 de mayo del 2010
# -------------------------------------------------------------
HOST=''
#
# Usuario MySQL
#
USER=''
#
# Contraseña
#
PASSWD=''
#
# Directorio hacia donde se van a guardar las copias de seguridad
#
BACKDIR=''
#
# Excluye las bases de datos que contengan las palabras:
# backup, bkup, bakup, bckup, bck, salva, copy, prueba
# y las que terminan en old.
#
# Modifique esta opción según sus necesidades
#
EXCLUDE_PATTERN='b(ac)?kup|salva|bck|^old|old$|copy|prueba'
#
# Recupera las bases de datos
#
DBS=$(mysql -u $USER -h $HOST -p$PASSWD -Bse 'show databases' | egrep -v $EXCLUDE_PATTERN)
#
# Crea las copias de seguridad para cada base de datos
#
for db in $DBS; do
    FILE="$BACKDIR/$db.sql.gz"
    mysqldump --opt -u $USER -p$PASSWD --databases "$db" | gzip -9 > "$FILE"
done

Para usar este script copie el código fuente asígnele un nombre, por ejmplo: backupdb

Asígnele permisos de ejecución

chmod a+x backupdb

Ejecute el script

Tambien puede ponerlo como una tarea programada (Cron)

./backupd

Lecturas recomendadas

– man mysqldump