Copias de seguridad (Backup) en MySQL

    Esta es la entrada 7 de 11 en la serie: Administrar MySQL desde la línea de comandos

    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




    Dejar un comentario

    Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *