Hacer backup y restaurar copia de seguridad con MySQL

Siempre que manejamos una base de datos, uno de los detalles más a tener en cuenta es la recuperación de la información en caso de fallo del sistema o de ataque de un tercero. MySQL es la elegida por muchos proyectos y desarrollos libres, por lo que a través de esta entrada vamos a explicar cómo proceder a realizar una copia de seguridad, para su posterior restauración.

Veremos dos métodos: copiar directamente los datos que maneja el sistema y generar un volcado a un fichero texto (un dump).

Copiar ficheros de la base de datos

MySQL utiliza la misma estructura de almacenamiento de datos en distintas plataformas, por lo que podemos directamente copiar los ficheros binarios que contienen las tablas, disparadores, funciones, procedimientos, etc.

¿Cómo hacerlo? Copiando y pegando directamente los ficheros del directorio /data de MySQL. Para restaurar los datos podemos hacer directamente el proceso contrario. El único problema que podemos encontrarnos es que al copiar y pegar entre distintos sistemas operativos, por ejemplo de Linux a Windows, en el primer caso las tablas y los ficheros sí serán sensibles a mayúsculas y en el segundo no.

La solución, usar siempre tablas en minúscula. Además, gracias al comentario de carlos apunto otro detalle muy a tener en cuenta (que se me había pasado comentar), parar el servicio antes de realizar la copia:

$ sudo services mysqld stop o $ sudo /etc/init.d/mysqld stop, dependiendo de nuestra distribución.

Exportar datos a un fichero texto

La solución más frecuente no es la primera, que a priori, es bastante simple y encima es mucho más rápida y eficiente (nos olvidamos de tener que generar y leer luego un fichero texto como en este caso). Sin embargo, esta segunda solución es más versátil. En servicios web normalmente no tenemos acceso a los datos almacenados en disco y tenemos que hacer todo a través del cliente MySQL.

¿Cómo? Con la ayuda de mysqldump, el programa que gestiona la creación de este tipo de volcados en formato texto. A pesar de que su sintaxis es bastante rica y tiene muchas opciones, os voy a decir cómo hacer uso del mismo de una forma sencilla y con un resultado que probablemente sea el que busquéis:

$ mysqldump –-opt --verbose --user=tuUsuario --password=tuPassword
 --host=direccion--all-databases > “volcado.sql”

El significado de los parámetros es:

  • –opt: Equivalente a indicar los parámetros básicos para crear un volcado correcto:
    • –add-drop-table: Borrados de comprobación.
    • –add-locks: Utiliza cerrojos para evitar problemas de sección crítica.
    • –create-options: Incluye todas las opciones específicas en los CREATE TABLE.
    • –disable-keys: Crea los índices tras insertar las filas, así es más rápida la importación.
    • –extended-insert: Reduce el tamaño del volcado, aprovechando mejor cada sentencia INSERT
    • –lock-tables: Bloquea la tabla mientras se realiza el volcado. –quick: Acelera el proceso de volcado.
    • –set-charset: Añade sentencias SET NAMES para establecer juegos de caracteres.
  • –verbose: Modo verbose, muestra más información.
  • –user: Usuario.
  • –password: Contraseña.
  • –host: Servidor
  • –all-databases: hace el volcado de todas las bases de datos del sistema, sin necesidad de especificarlas una a una.

El proceso contrario, el volcar en el destino los datos presentes en el fichero SQL recién creado es más simple:

$ mysql --user=tuUsuario --password=tuPassword
 --host=direccionServidor "volcado.sql"

De esta forma ya tendremos insertados los datos que acabamos de volcar en caso de pérdida. Otro día hablaremos de cómo incluir este proceso en el cron para asegurarnos de que se ejecuta de forma periódica y despreocuparnos de este tema.

Author Description

F. Javier Carazo Gil

Casi Ingeniero Informático, co-fundador y administrador de Linux Hispano, además de desarrollar software y mantener sistemas en el CSIC, hace poco he fundado junto con Alberto Hornero una start-up: CODECTION donde trabajamos con software libre. Puedes saber más sobre mí, mi vida laboral, mis libros, mis artículos y mi día a día en @fjcarazo y en mi perfil público de LinkedIn.

  • Pingback: Bitacoras.com

  • Pingback: Hacer backup y restaurar copia de seguridad con MySQL

  • carlos

    tu estás un poco loco, no? En el primer caso, antes de hacer nada, ¡¡¡¡para el servicio!!!!

  • http://www.jcarazo.com carazo

    @carlos: Mil gracias por el apunte. Acabo de actualizar la entrada. Se me había pasado completamente.

  • Pingback: Hacer backup y restaurar copia de seguridad con MySQL | ForoProvincias.Com

  • Pingback: Hacer backup y restaurar copia de seguridad con MySQL | PHP SPain

  • javier parra

    muchas gracias por traernos este tipo de conocimientos

    salu2

  • http://www.jcarazo.com carazo

    @javier parra: Me alegro que os haya gustado.

    ¿Qué otro contenido de este tipo os gustaría leer? ¿De PostgreSQL quizás algo similar?

  • Pingback: Programar copias de seguridad automáticas de PostgreSQL | Linux Hispano

  • Pingback: Programar copias de seguridad automáticas de PostgreSQL | Superlinux

  • Pingback: Hacer backup y restaurar copia de seguridad con MySQL | PHP SPain

  • http://www.arte-laser.com Hector Ruiz

    Me esta mostrando el siguiente error cuando se ejecuta el cron job… no tengo derechos para modificar nada del servidor…

    mysqldump: unknown variable ‘max_heap_table_size=256M’

    que puedo hacer?

  • Karlita Jimenez

    Estimado Javier, me parecio muy buena tu publicacion, de hecho t comento q yo ya hice el backup de mi base de datos y es caso similar al que tu muestras en la parte superior, pero ahora me nace una inquietud como puedo hacer para restaurar el backup hecho, por favor si alguna información tienes ayudame con algunas pautas para hacer la restauración …. espero tu respuesta se te agradece mucho….!!!

  • http://www.jcarazo.com F. Javier Carazo Gil

    @Karlita Jiménez: Aquí está el proceso contrario (siendo drupal la base de datos y drupal_backup.sql el fichero SQL):

    mysql -u admin -p drupal < drupal_backup.sql

  • Pingback: ¿Cómo hacer una copia de seguridad (backup) de WordPress? | Linux Hispano

  • Pingback: Reparar y optimizar todas las bases de datos en MySQL | Linux Hispano