Mysqldump – Backup y restore

La finalidad de escribir estos pequeños artículos, no es la de redactar complejos tutoriales porque para eso ya hay miles de sitios. Pero a mi resulta útil tener pequeños documentos online siempre, que puedo consultar a todas a horas y son de indudable valor para mi trabajo, más si la memoria no es una de tus virtudes 😀

El gran problema del Phpmyadmin es, dejando de lado su velocidad o rendimiento según la conexión que tengas, es el tema de la restauración de la base de datos. Cuando tenemos una base de datos mediana(he tenido problemas con bases de datos de 20MB) ya es complicado hacer el restore vía web. Y si, como puede ocurrir fácilmente, tenemos un monstruo de base de datos, no es viable.

Entonces entra en juego el famoso comando mysqldump. Hablamos en este post sobre MySQL en entornos Linux.

Lo primero es hacer el backup de la base de datos que vamos a mover. Desde el bash, utilizamos el comando mysqldump, que viene con la mayoría de instalaciones de MySQL actuales. Después comprimimos el archivo con tar y gz.

mysqldump --user (usuario) -p(password) (nombre bd) > (nombre).sql
tar -cvvf (archivo).sql.tar (nombre archivo).sql
gzip (archivo).tar

El usuario debe tener los permisos necesarios para poder efectuar el backup de la base de datos. Ahora que tenemos el archivo comprimido, vamos a moverlo hacia su nuevo destino. Hay muchas maneras, y algún día supongo utilizaré otra, pero ahora utilizo el scp(Secure Copy).

scp (archivo).tar.gz (usuario)@(ip host destino):/(directorio)

Si usamos algún otro puerto para el servicio de Ssh, debemos configurar el comando scp con la opción -p. Después de ejecutar el comando nos pedirá el password del usuario que hemos configurado. Sólo queda esperar que acaba la transferencia entre los hosts.

Nos conectamos al nuevo host y creamos la nueva base de datos, su user y password. Ahora, toca hacer el restore. ¡Atención! el restore del archivo .tar.gz se hace con el comando mysql, no con el mysqldump.

gunzip (archivo).tar.gz
tar -xf (archivo).tar
mysql -u (usuario) -p --database=(nombre bd) < (archivo).sql

Introducimos el password para el usuario mysql y a esperar. Puede ser que, el usuario que hemos creado, y más si lo hacemos desde aplicaciones web, no tenga los permisos mysql necesarios para hacer el restore de la base de datos. Un error similar sería así:

ERROR 1044 (42000) at line n: Access denied for user 'usuario@localhost' to database 'prueba'

Si tenemos el password del usuario “root” de mysql, podemos efectuar el restore con el. Pero si tenemos el usuario root disponible, es también posible otorgarle a nuestro nuevo usuario, los permisos necesarios. Desde Phpmyadmin es muy fácil hacerlo, es la pestaña “Privilegios”.
Si estamos desde el bash, nos conectamos al mysql desde línea de comandos y efectuamos la siguiente operación:

GRANT ALL PRIVILEGES ON (nombre bd).* TO 'usuario@localhost' IDENTIFIED BY 'password';

* quiere decir todas las tablas de esa base de datos. Si fuese *.* seria a todas las tablas de todas las bases datos, así que cuidado.

Backup de store procedures y proceder incluso con fallos

mysqldump -f -R --user (usuario) -p(password) --all-databases > (nombre).sql
tar -cvvf (archivo).sql.tar (nombre archivo).sql
gzip (archivo).tar

Incluimos los parámetros -f y -R

• -f: fuerza a completar el backup pese a encontrar errores
• -R: hace también backup de las stored procedures almacenadas

Backup de endpoint, compresión y restaurar

# hacer copia de respaldo
mysqldump -u admin -p -h endpoint.of.my.mysql.com --all-databases | gzip -9 > my.mysql.com.sql.gz

# restaurar la copia
gunzip < my.mysql.com.sql.gz | mysql -u admin -p -h endpoint.of.my.mysql.com

Backup en MySQL RDS

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Si hemos intentado restaurar la base de datos en un MySQL RDS quizá hemos visto este error. Para poder solventarlo, hemos de volver a lanzar el mysqldump pero con un flag especial.

El problema es que hay algunas sentencias MYSQL que en RDS requieren un tipo especial de permiso que RDS nunca te va a dar, por ejemplo:

    SET @@GLOBAL.gtid_purged
    SET @@SESSION.SQL_LOG_BIN

mysqldump -u admin -p -h endpoint.of.my.mysql.com --set-gtid-purged=OFF --all-databases | gzip -9 > my.mysql.com.sql.gz

Unknown table ‘column_statistics’ in information_schema (1109)

Para evitar este fallo, hay que deshabilitar el flag por defecto que viene ya activado en mysqldump

mysqldump -u admin -p -h endpoint.of.my.mysql.com --set-gtid-purged=OFF  --all-databases | gzip -9 > my.mysql.com.sql.gz

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'mydatabase' AND TABLE_NAME = 'deleted';': Unknown table 'column_statistics' in information_schema (1109)

mysqldump -u admin -p -h endpoint.of.my.mysql.com --set-gtid-purged=OFF --column-statistics=0 --all-databases | gzip -9 > my.mysql.com.sql.gz

Links

• https://stackoverflow.com/questions/11601692/mysql-amazon-rds-error-you-do-not-have-super-privileges/56251925#56251925