¿Por qué mysqldump?

Yo siempre he hecho las copias de mis bases de datos mediante la opción que da PHPMyAdmin para exportar los datos. Pero cuando una base de datos alcanza un tamaño considerable el exportador de PHPMyAdmin se queda corto ya que PHP no puede procesar toda la información.

En ese momento, hay que pasar a hacer las copias de seguridad mediante los comandos que MySQL nos proporciona. Este método es un poco más tedioso ya que no hay interfaz gráfica como en PHPMyAdmin pero realmente hay 4 opciones y son bastante facilitas.

El programa que nos propociona MySQL se llama mysqldump. Éste comando se encarga de acceder a las base de datos que le indiques y sacarte por la salida estándar el código SQL del backup.

Uso de mysqldump

Tiene muchas opciones (para verlas todas escribe man mysqldump en la consola) pero las que más nos interesan son:

• opt: Esta opción es un popurrí de otras opciones. Lo que hace es añadir al código SQL del backup las ordenes de borrar las tablas si existen con anterioridad, bloquea las tablas para que el backup sea más rápido, escribe en los CREATE TABLE las opciones específicas de cada tabla, hace las inserciones en pocas líneas para que el archivo ocupe menos y se haga más rápido el backup y utiliza las opciones de backup rápido. En definitiva esta opción es muy recomendable ya que te ahorra poner muchas otras opciones y consigue que el backup se haga de la manera más rápida posible.
• user: Esta opción indica a mysqldump el usuario con el que accederá a la base de datos para hacer el backup.
• password: Indica el password a mysqldump.
• host: Indica la dirección donde está la base de datos.

Imaginemos que tengo una base de datos en mi servidor (servidorficticio.com) que se llama base_de_datos_de_prueba. En ella tienen privilegios para leerla y modificarla el usuario jorge con la contraseña mi_nombre_es_jorge. Ahora existen dos opciones: que me pueda conectar al servidor por SSH o que no me dejen.

En el caso de poder conectarme por SSH debería de identificarme en servidorficticio.com y escribir en la línea de comandos:

mysqldump --opt --user=jorge --password=mi_nombre_es_jorge base_de_datos_de_prueba >  backup_base_de_datos_de_prueba.sql

En cambio, si no te dan la opción de conectarte por SSH tendrás que utilizar la opción host y ejecutar la orden desde tu ordenador. El comando que tendrías que escribir sería muy parecido:

mysqldump --opt --host=dominioficticio.com --user=jorge --password=mi_nombre_es_jorge base_de_datos_de_prueba >  backup_base_de_datos_de_prueba.sql

En ambos casos se creará un archivo backup_base_de_datos_de_prueba.sql que contendrá el backup completo de la base de datos.

Backup de más de una base de datos

Ahora tenemos las bases de datos: base_de_datos_de_prueba1 y base_de_datos_de_prueba2. Utilizaremos la opción databases:

mysqldump --opt --user=jorge --password=mi_nombre_es_jorge --databases base_de_datos_de_prueba1 base_de_datos_de_prueba2 >  backup_multiple.sql

Backup de todas las bases de datos

Para hacer un backup completo de toda la base de datos utilizaremos la opción all-databases:

mysqldump --opt --user=jorge --password=mi_nombre_es_jorge --all-databases > backup_total.sql

Problemas típicos

Alguna vez me ha pasado cuando hacía un backup que mysqldump me mostraba que una tabla había producido el error cuando se intentaba bloquear. Concrétamente el error decía algo así:

mysqldump: Got error: 1105: File './xxxx/tabla_problematica.MYD' not found (Errcode: 24) when using LOCK TABLES

Este problema se puede arreglar facilmente añadiendo a la opción lock-tables e igualándola false para que no intente bloquear las tablas y puedea terminar el backup. Esta modificación lo único que cambia es la velocidad en la que se hará el backup. De forma que el comando quedaría:

mysqldump --opt --lock-tables=false --user=jorge --password=mi_nombre_es_jorge base_de_datos_de_prueba >  backup_base_de_datos_de_prueba.sql

Por último, cuando el backup es muy grande (alrededor del GB) es posible que mysqldump avise de que no puede hacerlo porque supera el tamaño permitido. Para arreglar ésto solo hay que cambiar un valor de la configuración de MySQL: max_allowed_packet.

Ésta variable se encuentra en el archivo de configuración de MySQL my.cnf que normalmente se encuentra en el directorio /etc/mysql. Editamos ese archivo y cambiamos la variable max_allowed_packet a lo que queramos, por ejemplo 200MB (el valor defecto es 16MB)

Nota: Si no sabes dónde está tu archivo de configuración my.cnf puedes ejecutar este comando

find / -name 'my.cnf' 2> /dev/null

Un saludo, espero que os sea útil!