Print-friendly Version

Creación de un Script para Copias de Seguridad de MySQL Programado

Edwin Miguel Triana Gómez, June 2008

Introducción

Este es un tutorial paso-a-paso para la configuración de un script que realiza una copia de seguridad a una base de datos en MySQL y que se ejecuta periódicamente.

Para llevar a cabo esto, voy a crear un script que incluye el comando de copias de seguridad de MySQL (mysqldump) y el comando de copiado en un servidor remoto (scp). Después, se usará cron para ejecutar el script periódicamente.

Nota: Se asume que el lector tiene conocimientos básicos sobre administracion de servidores, sistemas UNIX y servidores de bases de datos.

Requerimientos

• Dos servidores en red
• El motor de bases de datos MySQL
• Un usuario de MySQL con permisos en la base de datos

Mi ambiente de trabajo:

• Sistema Operativo Solaris 10 ejecutandose en el servidor donde las copias de seguridad serán guardadas.
• Sistema Operativo Linux openSUSE 10.2 X86_64 ejecutandose en el servidor de bases de datos.
• MySQL 5.0

Creando el Script

1. Crear un archivo de texto utilizando cualquier editor de texto, (por ejemplo, vi), y añadir #!/bin/sh en la primera linea:

>vi backupScript.sh

#!/bin/sh

2. Definir el nombre del archivo que tendrá la copia de seguridad:

#!/bin/sh
filename="databaseName_backup_"`eval date +m%d_%H_%M_%S`".sql"

La cadena databaseName_backup es el prefijo de la copia. Se puede colocar la que desee.

La cadena `eval date +m%d_%H_%M_%S` ejecuta el comando date con el siguiente formato:

• %Y: Año
• %m: Mes
• %d: Dia del mes
• %H: Hora
• %M: Minutos
• %S: Segundos

La cadena .sql es la extensión del archivo, la cual es opcional, pero se recomienda usarla.

3. Añadir el comando de copia de seguridad:

#!/bin/sh
filename="databaseName_backup_"`eval date +m%d_%H_%M_%S`".sql"
mysqldump --lock-tables -u userName -pUserPassword databaseName > \
/path/to/dump/the/database
/$filename

El manual de referencia de MySQL provee toda la información sobre el comando mysqldump.

El parametro --lock-tables (-l también es valido) bloquea todas las tablas ante de iniciar la copia de seguridad. La tablas son bloqueadas con READ LOCAL con el fin de permitir inserciones concurrentes en el caso de tablas MyISAM. Para tablas transaccionales, como InnoDB y BDB, --single-transaction es una mejor opción, ya que no es necesario bloquear las tablas.

El nombre de usuario y contraseña son especificados en los siguientes parámetros:

• -u indica el nombre de usuario de la base de datos (por ejemplo, -u fulanito).
• -p indica la contraseña del usuario; asegúrese que no hay espacios entre -p y la contraseña (por ejemplo, -pMiContrasena).

4. Guarde y cierre el archivo (en vi, use ESC :wq).

5. Configurar secure copy (scp sin contraseña), con el fin de copiar o mover la copia de seguridad al servidor remoto:

El comando scp es usado para copiar archivos a computadores remotos y es fácilmente configurable para realizar copias sin usar contraseñas. Para hacer esto, se va a generar un par de llaves publica/privada en el servidor donde se ejecuta MySQL, se copia la llave publica en el servidor remoto y se usa el comando sc scp sin contraseña, de la siguiente forma:

a. Generación de la llave:

ssh-keygen -t rsa

La siguiente es la respuesta del sistema:

Generating public/private rsa key pair.
Enter file in which to save the key (/home/username/.ssh/id_rsa):
<Press Enter to use the default>
Enter passphrase (empty for no passphrase): <Enter, en mi caso>
Enter same passphrase again: <Enter de nuevo>
Your identification has been saved in /home/username/.ssh/id_rsa.
Your public key has been saved in /home/username/.ssh/id_rsa.pub.
The key fingerprint is:
e4:f1:b5:3d:57:2e:37:87:2f:55:91:a7:2a:f9:f4:1d username@servername

b. Copie la llave publica (la cual termina con .pub) al servidor remoto (usando scp, FTP, o cualquier otro). Por ejemplo, el siguiente comando copia la llave id_rsa.pub al directorio de usuario (home) en la maquina remota:

scp /home/username/.ssh/id_rsa.pub user@remoteServer:.

c. Guarde la llave en el archivo de llave autorizadas (authorized_keys store).

Para hacer esto, usted debe autenticarse en la maquina remota. Si desea puede usar el comando ssh (por ejemplo, ssh user@remoteServer). Se debe conocer la contraseña del usuario.

Se debe incluir la llave publica en el archivo /home/user/.ssh/authorized_keys (o en /root/.ssh/authorized_keys, si es el root), sin embargo hay que ser cuidadoso porque este archivo puede contener las llaves de otros servidores. Puede usar el siguiente comando para añadir la llave publica a authorized_keys o crear un nuevo archivo si este no existe:

>data="`eval cat ./id_rsa.pub`"

(Se asume que la llave publica se encuentra en el home del usuario.)

>echo $data >> ./.ssh/authorized_keys

(Si el directorio .ssh no existe, usted debe crearlo primero.)

d. Cierre sesión en el servidor remoto.

6. Pruebe la configuración anterior copiando un archivo desde el servidor de bases de datos al servidor remoto usando el comando scp:

scp ./somefile user@remoteServer:.

Si la consola no solicita una contraseña, felicitaciones, usted ha configurado la llave correctamente; si la consola le solicita una contraseña, lo siento, usted debe regresar al paso 5.

7. Incluir el comando scp en el script de copia de seguridad:

#!/bin/sh
filename="databaseName_backup_"`eval date +m%d_%H_%M_%S`".sql"
mysqldump --lock-tables -u userName -pUserPassword databaseName > \
/path/to/dump/the/database/$filename
scp /path/to/dump/the/database/$filename \
user@remoteServer:/path/to/backups
rm /path/to/dump/the/database/$filename

La ultima linea es opcional para quien no desee mantener la copia de seguridad en el servidor de bases de datos.

El directorio /path/to/backups/ debe estar creado, y el usuario debe tener permisos de escritura.

8. Programar la copia de seguridad.

Hasta ahora hemos terminado el script que realiza la copia de seguridad y la copia en el servidor remoto, ahora, solo falta programar el sistema para que el script se ejecute periódicamente, utilizando cron:

a. Abra crontab para añadir el script:

>crontab -e

b. Añada una nueva linea con la ruta del script y el tiempo de ejecución:

min hour * * * /path/to/backupScript.sh

Por ejemplo:

00 02 * * * /home/username/backupScript.sh

En el anterior ejemplo, el script se ejecutará diariamente a la 02:00 horas (formato 24 horas).

Si se desea configurar la ejecución del script de otra forma, tenga en cuenta los siguientes parámetros:

• min: 0 - 59
• hour: 0 - 23
• Primer * desde la izquierda: 1 - 31 (Día del mes)
• Segundo * desde la izquierda: 1 - 12 (Mes; 12 es Diciembre)
• Tercero * desde la izquierda: 0 - 7 (Día de la semana; Domingo es 0 o 7, Lunes es 1, y así sucesivamente)

9. Guarde el archivo (en vi, use ESC:wq).

Nota: El archivo backupScript debe ser ejecutable. Para asegurar esto, se puede usar el comando chmod de la siguiente forma:

chmod +x /path/to/backupScript.sh

10. Listo!!!

Se han completado todos los pasos para realizar copias de seguridad de una base de datos periodicamente.

Acerca del Autor

Mi nombre es Edwin Miguel Triana Gómez, soy Ingeniero de Sistema de la Universidad Nacional de Colombia y trabajo en el Centro de Telemedicina de la misma institución.

English version: Creating a Script for Scheduled MySQL Backups

Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License.