Hacer un backup periódico de tu base de datos MySQL en Linux



Sencillo y al grano, necesitaba hacer un backup constante en mi nuevo servidor de LinksDV.com pero para ello primero tenía que preparar algunas cosas.

Número uno, aprender cómo se hacía, es que no escribía un script en bash hace MUCHOS años así que tuve que ponerme a leer bastante, luego releer cómo se configuraba cron y de ahí en más buscar algunos ejemplos.

A continuación explico, para nardos programadores que no son sysadmins pero tienen sus propios servidores, cómo hacerlo, es bastante fácil por suerte, para Linux, claro. Un script Bash para exportar la base de datos MySQL, comprimirla y subirla a un FTP.



Lo primero, el concepto

1.- tenés un site con una base de datos, necesitás backup constante
2.- no podés ni debés guardar el backup en el mismo servidor, hace no mucho tiempo a Mariano Amartino le bajaron Uberbin.net y el backup que guardaba en otra partición del disco del servidor, los hijos de puta de los kakers le borraron todo, había mala leche obviamente.
3.- si tenés más de un servidor en distintos proveedores ya tenés un FTP destino
4.- si además sumás algo tipo dropbox, bueno, mucho mejor, pero supongamos que no lo tenés
5.- hay que armar un pequeño script que exporte la base de datos, la comprima y la suba a ese FTP
6.- hay que decirle al servidor que ejecute la orden tantas veces al día como tu paranoia indique

Con esta idea en mente me puse a buscar, por suerte hay toneladas de scripts que hacen más o menos lo mismo pero por alguna razón el ftp normal no me funcionaba, tuve que optar por lftp que funcionó desde el primer intento.

El script en cuestión es para bash y tiene variables configurables así que podrán usarlo en sus propios servidores, es absolutamente perfectible, se aceptan todas las sugerencias del mundo para hacerlo más eficiente.

Una falencia que tiene, por ejemplo, es que hace un dump cochino de la base de datos y éste se puede comer mucho procesador, al igual que el tar cuando lo comprime, normalmente le pide al server el 100% del procesador sin asco porque nadie le indicó lo contrario. Así que se puede regular e indicarle al sistema operativo cuanta prioridad de CPU darle a cada proceso y, por ende, que no se sienta ningún bajón de performance en la tarea.

Luego hay que tener en cuenta que estos backups ocupan espacio, si uno hace un dump de 45-50Mb por día (como en mi caso) es probable que necesite cada cierto tiempo limpiar lo que sobra, el criterio ahí es personal, uno puede dejarse guardado un backup de cada mes porque el diario mucho que no le hará falta, por ejemplo.

#!/bin/bash
DB_NAME="nombre de la base de datos"
DB_FILE="$DB_NAME-`date  %Y%m%d_%H%M%S`"
DB_BK_FOLDER="/backup/"
DB_BACKUP="$DB_BK_FOLDER$DB_FILE"
DB_USER="usuario db"
DB_PASSWD="pass db"
FTP_SERVER="servidor de ftp"
FTP_USER="usuario ftp"
FTP_PASSW="pass ftp"
echo "Creating MySQL Dump"
mysqldump -u $DB_USER --password=$DB_PASSWD --lock-tables=false $DB_NAME > $DB_BACKUP
echo "Compressing database"
tar czvf $DB_BACKUP.tar.gz $DB_BACKUP.sql
echo "Delete dump file"
rm $DB_BACKUP.sql
echo "Upload to FTP"
lftp -u $FTP_USER,$FTP_PASSW -e "cd backup;
mput $DB_BK_FOLDER$DB_FILE.tar.gz; quit" $FTP_SERVER
echo "Done."


esto lo guardan en un lindo backupdb.sh y le dan permisos de ejecución con un chmod x backupdb.sh y ya lo tienen listo, pero así solo no se ejecutará demasiado. Tengan en cuenta que también hay que crear un directorio local donde se acumularán los archivos a enviar.

Si editamos /etc/crontab vamos a encontrar las tareas programadas, en mi caso decidí que el backup se haga en un horario bien de madrugada tipo 6 AM que es cuando las estadísticas indican que no entra ni el loro.

0 6 * * *       root    /bin/bash /root/backupdb.sh > /dev/null


Tenemos aquí minutos, horas, día del mes, mes, día de la semana, y el usuario, luego lo que vamos a ejecutar y al final tirar el resultado ya sea a un log o, como soy un sorete, al /dev/null que es lo mismo que un agujero negro.

Las probabilidades que el cron te funcion de una son bajísimas si no estas canchero :D y depende de tu distro cómo lo maneje, así que suerte con ello y a googlear.

Se aceptan sugerencias, como dije más arriba, tanto para el script como para el cron y cualquier otro tip que me pueda ayudar, más adelante escribiré un post sobre la experiencia de instalarse un VPS y lo bien que rinde un servidor propio.

Otros posts que podrían llegar a gustarte...

Comentarios

  • chimango    

    Che, el loro no entrará, pero a veces el chimango que está terminando la noche de observación si...
    El script en si no me sirve porque no tengo necesidad de hacer un backup de una base de datos, pero me parece que acabo de recordad cómo se definían y usaban las variables en bash, las dudas mas básicas son siempre mejor atacadas con un ejemplo, gracias!
    (ahora me vuelvo un rato a T! que estoy en un post donde hay forobardo acerca de compositores clásicos. Si. Eso.)

    • Responder
    • Citar
    • Comentado:
  • Renfe en cuenta Fabio que en caso de tablas myisam al menos bloques la tabla entera x la duración del dump, acá no es problema notable pq 50mb salen al toque, pero por ejemplo yo que ando cerca de 2gb me clava el sitio entero, además de la cantidad de procesos que quedan "en espera" de que la consulta responda provoca una especie de reacción en cadena muy fea...
    Posta hacerlo más transparente yo tengo una réplica en una máquina virtual y así hago el backup de forma más segura.
    Revisa tmb el uso del lock que está en el scrip y fíjate si opt no te conviene más.

    En mi caso uso un script modificado que hace el dump, pasa la salida por gunzip y la codifica por gpg y borra el gz, dsps la sube via ftp a un hosting de usa, 2 horas después mi pc baja el archivo y pasa por gpg para de-cifrarlo local

    • Responder
    • Citar
    • Comentado:
  • Muy interesante!

    Una sugerencia es que al comando mysqldump sería interesante agregarle el parámetro \"-R\" ó \"--routines\" para que backapee las rutinas (functions o stored procedures si es que existieran).

    Saludos!

    • Responder
    • Citar
    • Comentado:
  • ahh, quitando mi sindrome de texto predictivo en el mensaje anterior, no vi que el lock era false!!
    lo cual no siempre es deseado...
    tene en cuenta esto:
    For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.

    • Responder
    • Citar
    • Comentado:
  • Voy a ver si aprovecho a pulir mi script con la prolijidad que trae el uso de variables! :D
    En mi laburo yo tambien hago un cochino dump de todo, comprimo y subo a otro server diferente. guardandome hasta los ulitmos 7 dias. Este es mi cochino script:

    echo " Montando unidad de red"
    mount -t smbfs -o username=admin,password=admin //fileserver/db /media/netbackup

    echo " Copiando todas las bases a un solo archivo"

    mysqldump --user=USUARIO --password=CLAVE --all-databases > /media/netbackup/Backup-tablas/all-databases.sql

    echo " Empaquetando y comprimiento las bases"
    cd /media/netbackup//Backup-tablas/
    tar -zcf Virtualbackup_$(date +%Y_%m_%d).tgz *.sql
    rm *.sql

    echo " Buscando y borrando los archivos de mas de 7 dias"
    find -name ´Virtual*.tgz´ -type f -mtime +7 -exec rm -f {} \;
    cd

    echo " Desmontando unidad de red"
    umount /media/netbackup/

    echo " Backup realizado exitosamente"

    Lo dejo por si a alguien le es util. o por si alguien lo quiere pulir y pasarmelo :D

    Por cierto, (y perdon por si la pregunta es muy de newbie), a que llamas VPS???? Virtual Private Server?

    • Responder
    • Citar
    • Comentado:
  • Si hacés un dump con las sentencias SQL solamente, me suele resultar más útil usar gzip directamente en vez de tarrear y gzippear a la vez. De esta forma ya se almacena comprimido

    mysqldump -u $DB_USER --password=$DB_PASSWD --lock-tables=false $DB_NAME | gzip > $DB_BACKUP.sql.gz

    y lo podés restaurar directamente desde el mismo archivo

    zcat $DB_BACKUP.sql.gz | mysql -u pitufo -p base_de_datos

    Si tenés hardware suficiente podés comprimir con parallel bzip2 o xz (lzma2) para tener mejor radio de compresión.

    Y de paso, muchas veces para no backupear al pedo en sitios que tienen pocas modificaciones, comparo en el mismo script el backup nuevo con el anterior; si son iguales elimino el nuevo.

    • Responder
    • Citar
    • Comentado:
  • luis manson dijo:

    Renfe en cuenta Fabio que en caso de tablas myisam al menos bloques la tabla entera x la duración del dump, acá no es problema notable pq 50mb salen al toque, pero por ejemplo yo que ando cerca de 2gb me clava el sitio entero, además de la cantidad de procesos que quedan "en espera" de que la consulta responda provoca una especie de reacción en cadena muy fea...
    Posta hacerlo más transparente yo tengo una réplica en una máquina virtual y así hago el backup de forma más segura.
    Revisa tmb el uso del lock que está en el scrip y fíjate si opt no te conviene más.

    En mi caso uso un script modificado que hace el dump, pasa la salida por gunzip y la codifica por gpg y borra el gz, dsps la sube via ftp a un hosting de usa, 2 horas después mi pc baja el archivo y pasa por gpg para de-cifrarlo local


    explicate mejor el primer párrafo que no te termino de entender :P


    choripancho dijo:
    Muy interesante!

    Una sugerencia es que al comando mysqldump sería interesante agregarle el parámetro \"-R\" ó \"--routines\" para que backapee las rutinas (functions o stored procedures si es que existieran).

    Saludos!


    en este caso no existen, es un site muy "a la antigua" Guiño además preferí no usarlos (antes) porque usaba hostings pedorros que tienen cero control de sus mysql, ahora ya es cosa distinta, podría empezar a usarlos.


    luis barrios dijo:

    Por cierto, (y perdon por si la pregunta es muy de newbie), a que llamas VPS???? Virtual Private Server?


    exacto, alquilé un VPS, pero te toca a vos instalar todos los paquetes, armar los scripts, configurar el apache, el mysql, php, seguridad, etc. es más divertido y a la vez más eficiente, comprás paquete por procesador/memoria/espacio/tráfico y no es tan lento como un shared

    • Responder
    • Citar
    • Comentado:
  • Nachox    

    El viaje entre servidores lo podès hacer con scp que usa ssh en lugar de ftp, que ya va encriptado (depende de tu paranoia, obviamente). Con una llave pùblica del usuario de backup en el host destino ni te hace falta loguearte.
    Por otra parte, en algùn momento me habìa hecho un script en bash para ir borrando archivos (logs en mi caso) mas viejos que el tiempo que le determines, para ir haciendo espacio automàticamente. Si te sirve, avisame que lo busco y te lo paso.

    • Responder
    • Citar
    • Comentado:
  • bardiel84    

    Tambien en vez de usar ftp, un muy buena alternativa es usar rsync, te permite configurar la velocidad de transferencia, e incluso solo transferir de forma incremental (ej armas un plan de backup local (por fechas) y con rsync solo te va haciendo las copias periodicas y si borras archivos en el source, te los borra en el destino).
    otra opcion como decia luis es tener una base replicada en un chroot de mysql que replique la base y el dump lo haces ahi... y te evitas el bloquear la base y es una vuelta de tuerca adiciona (el tema es que si te acceden al vps, y te borran el chroot y la base se fue todo al carajo. Pero si haces eso + rsync o ftp ya estas mucho muy mejor parado frente a un posible boludo que se hace el hacker.

    • Responder
    • Citar
    • Comentado:
  • Fabio Baccaglioni dijo:

    luis manson dijo:
    Renfe en cuenta Fabio que en caso de tablas myisam al menos bloques la tabla entera x la duración del dump, acá no es problema notable pq 50mb salen al toque, pero por ejemplo yo que ando cerca de 2gb me clava el sitio entero, además de la cantidad de procesos que quedan "en espera" de que la consulta responda provoca una especie de reacción en cadena muy fea...
    Posta hacerlo más transparente yo tengo una réplica en una máquina virtual y así hago el backup de forma más segura.
    Revisa tmb el uso del lock que está en el scrip y fíjate si opt no te conviene más.

    En mi caso uso un script modificado que hace el dump, pasa la salida por gunzip y la codifica por gpg y borra el gz, dsps la sube via ftp a un hosting de usa, 2 horas después mi pc baja el archivo y pasa por gpg para de-cifrarlo local


    explicate mejor el primer párrafo que no te termino de entender :P


    perdon, el texto predictivo no es tan omnisciente :P

    Lo que te decia va mas o menos relacionado con el siguiente comentario, que cuando haces el dump vas a bloquear las tablas completas si son MyISAM, no te es la gran cosa porque 50Mb lo escribis en un par de segundos, pero en mi caso con >2Gb de datos causa que a medida que llegan pedidos estos se queden en memoria "esperando" que mysql responda con los resultados, ahora considera decenas de pedidos generando procesos PHP que se quedan en el aire unos cuantos minutos, en un toque la carga del servidor se va al cielo

    Para resolver esto yo tengo un MySQL haciendo de esclavo que se mantiene casi el 100% sincronizado con el maestro, cuando se cumple la hora un script detiene el proceso que ejecuta las consultas en la replica (pero no dejo de recibir los logs) bloquea todas las tablas y hace el dump tranquilo, terminado esto, inicia de nuevo la replica que se pone al dia, mientras tanto el script pasa por gunzip el dump, despues lo pasa a gpg que lo encripta y borra el gz, acto seguido lo sube a un FTP, cuando termina me manda un log por mail con toda la info
    dos horas dsps la PC de casa entra al FTP, baja el dump y lo desencripta

    Asi tengo 2 backups offsite y uno en la misma red que el servidor

    PD: hechale un ojo a mariadb y a innobackupex

    • Responder
    • Citar
    • Comentado:
  • nbensa    

    Si todavía nadie lo dijo:

    LOCK TABLES WITH READ LOCK + LVM snapshot (o btrfs subvol snap ...) + UNLOCK TABLES + rsync

    Tu base va a tener un lock por, como mucho, un par de segundos, y que el rsync tarde lo que quiera. Me funciona para bases de varios GB y muy acupadas. Y si todavía querés un dump, podés montar el snapshot, levantar una segunda instancia de mysql en otro sock, y hacer el dump.

    Por ahí te sirva esto: http://www.lullabot.com/blog/articles/mysql-backups-using-lvm-snapshots

    Y si no usás LVM... I feel sorry for you :-)

    • Responder
    • Citar
    • Comentado:
  • Nbensa, de donde te recuerdo, psicofxp?

    • Responder
    • Citar
    • Comentado:
  • Si por casualidad algun script kiddie entra a tu server, con ese script tiene acceso a la BD y al ftp de de backups...
    Como dicen por ahi, deberias usar una key para autenticarte (SFTP) y quizas, algun usuario con permisos de solo lectura en la BD...

    • Responder
    • Citar
    • Comentado:
  • Nachox    

    luis manson dijo:

    Nbensa, de donde te recuerdo, psicofxp?


    El Luis Manson mismo del foro milestone?

    • Responder
    • Citar
    • Comentado:
  • Ezequiel Calderara dijo:

    Si por casualidad algun script kiddie entra a tu server, con ese script tiene acceso a la BD y al ftp de de backups...
    Como dicen por ahi, deberias usar una key para autenticarte (SFTP) y quizas, algun usuario con permisos de solo lectura en la BD...


    si un script kiddie entra a mi cuenta de root ya es medio demasiado tarde para todo, el ftp de backups es irrelevante y no tiene acceso por dominio, es sólo ftp, (no es tan hackeable) pero el script está como root en el server, si el tipo ya llegó hasta el script significa que ya tenía acceso a todo el servidor ¿de que me sirve encriptar lo que sale como backup? onda, ya estaría en el recontra horno

    • Responder
    • Citar
    • Comentado:
  • Como dijeron mas arriba, si te hackean el server, simplemente mirando el crontab aparece el bash y cuando lo ven tienen todos los accesos.

    Este tipo de operaciones creo es mejor realizarlas de forma fantasma, el cron y el mysqldump deberian estar corriendo en otro servidor completamente fantasma que tenga acceso remoto a la db del server principal.
    ahi si los cagas, o no?

    • Responder
    • Citar
    • Comentado:
  • Pibe subte dijo:

    Como dijeron mas arriba, si te hackean el server, simplemente mirando el crontab aparece el bash y cuando lo ven tienen todos los accesos.

    Este tipo de operaciones creo es mejor realizarlas de forma fantasma, el cron y el mysqldump deberian estar corriendo en otro servidor completamente fantasma que tenga acceso remoto a la db del server principal.
    ahi si los cagas, o no?


    si ahí los cagás pero te obliga a tener no uno, dos servers independientes, pay for it

    • Responder
    • Citar
    • Comentado:

Deje su comentario:

Tranquilo, su email nunca será revelado.
La gente de bien tiene URL, no se olvide del http/https
Para evitar bots, si se tardó mucho en leer la nota seguramente no sirva y tenga que intentar dos veces

Negrita Cursiva Imagen Enlace


comentarios ofensivos o que no hagan al enriquecimiento del post serán borrados/editados por el administrador