texto:   A-   A+
eliax

Eliax explica: Cómo respaldar/restaurar rápidamente con MySQL Actualizado: Y CSV grandes
eliax id: 10500 josé elías en jun 25, 2013 a las 01:09 PM (13:09 horas)
MySQLeliaxHoy me salgo un poco de los artículos típicos de eliax para explicar algo un poco más técnico, aunque sencillo, concerniente al respaldo/backup de bases de datos MySQL (y su subsiguiente restauración)...

Sucede que con frecuencia visito amigos o clientes que utilizan esta popular base de datos, y noto la forma en que hacen respaldos y restauraciones de bases de datos, y he notado que muchos pasan un gran trabajo haciendo algo que en realidad se pueda hacer de una forma muy sencilla, y sobre todo mucho más rápida.

Noten que existen soluciones pagas para gestionar el respaldo y restauración de MySQL, pero esta forma que les mostraré no solo es gratis, sino que la más rápida de todas.

Por lo general, los respaldos en MySQL se hacen por alguna interfaz gráfica, siendo quizás la más popular de todas el gestionar phpMyAdmin. Sin embargo herramientas como esa solo son prácticas para hacer respaldos de pequeñas bases de datos (en el orden de unos cuentos miles de registros). En el momento que tratas de procesar tablas de cientos de miles o millones de registros, te metes en problemas.

Esos problemas vienen por lo general en dos formas: O toma muchísimo tiempo hacer el respaldo/restauración, o sencillamente el proceso no se termina, y por lo general eso conlleva a que algunos traten de hacer respaldos de forma manual con programas escritos en todo tipo de lenguajes, lo cual como veremos a continuación es absolutamente innecesario..

Así que el truco es bastante sencillo, y hasta documentado, aunque me sorprende la cantidad de personas que lo desconocen. Los dos comandos para hacer backup y respaldo de bases de datos de MySQL son estos:

mysqldump -u root -p eliax > eliax.sql

mysql -u root -p eliax < eliax.sql

Ahora noten unas cuantas importantes observaciones sobre esos dos comandos:

1. Estamos asumiendo una base de datos que se llama "eliax".

2. El primero de estos comandos hace el respaldo, el segundo hace la restauración. Al ejecutarlos, les preguntará la clave del usuario root para proceder.

3. Para que el segundo comando funcione, deben primer crear una base de datos vacía, que en este caso se llame "eliax", de lo contrario el comando fallará diciendo que no existe la base de datos en donde restaurar los datos. Ojo, que para fines de automatización, podrían crear un script que les cree la base de datos primero.

4. Estos comandos son extremadamente rápidos. Una base de datos que por otros medios quizás le tome buena parte de una hora, con este método les tomará posiblemente unos pocos segundos.

5. Esto funciona tanto en Windows como en derivados de UNIX (Linux, Mac OS X, etc), y yo mismo lo he probado en todos estos entornos. Así mismo debería funcionar con forks de MySQL como MariaDB (esto no lo he probado).

6. En algunos sistemas operativos, estos comandos no se reconocerán en la linea de comando debido a que no están en el PATH (ruta de binarios reconocidos) de comandos del sistema. En esos casos tienen dos opciones: O agregan el directorio donde están los comandos "bin" de MySQL al PATH, o sencillamente llaman estos comandos con la ruta completa al comando directamente.

7. Obviamente, el archivo eliax.sql o debe estar en el mismo directorio en donde están ejecutando el comando, o sino deben especificar la ruta completa en donde se encuentra.

8. Noten que debido a la sencillez de estos comandos, que es posible automatizar en forma recurrente los respaldos de MySQL con scripts sencillos, tanto en un .bat en Windows, como en un script de shell en UNIX y derivados, llamando ese script periódicamente con algo como el programador de tareas de Windows o un cron job en UNIX o similar. En tales casos recomiendo que el script sea lo suficientemente inteligente como para escribir eliax.sql en un formato más apropiado para respaldos, como por ejemplo: eliax_2013_06_26.sql (que indicaría un respaldo el día 26 de junio del 2013).

Como ven, esto es extremadamente sencillo, y espero que ayude a algunos que no estaban al tanto de estos comandos. Y si tiene más trucos, observaciones, mejoras o alternativas, no dejen de compartirlas en los comentarios abajo...

Nota: Si quieren aprender y compartir más de este tipo de tips para desarrolladores de software, sugiero se hagan miembros del grupo hermano de eliax en Facebook para tal fin, developers.X




Actualización 14 de Agosto del 2014: Otro caso similar al ejemplo del artículo es querer cargar a MySQL un archivo .csv extremadamente grande (por ejemplo, de cientos de MegaBytes), y una vez más, por la linea de comando esto se puede hacer super fácil y sobre todo, super rápidamente. He aquí los comandos necesarios (explicación de cada uno más abajo después de los comandos):

1. cd foo

2. mysql -u root -p

3. use eliax

4. load data local infile 'eliax_datos.csv' into
table eliax_datos fields terminated by ','
enclosed by '"' lines terminated by '\n'
(NOMBRES, APELLIDOS);

Y esta es la explicación de cada comando (noten que estoy conciente que todos estos comandos se podrían ejecutar todos como un solo comando, pero de esta manera se entiende mejor lo que estamos haciendo - prefiere la simpleza por motivos didácticos):

1. Hacer un CD en la linea de comando para llegar al directorio donde se encuentra el archivo CSV que quieres importar. Asumamos que el archivo se llama eliax_datos.csv y que queremos importarlo a una tabla que se llama "eliax_datos" en la base de datos "eliax", la cual contiene 2 columnas llamadas NOMBRES y APELLIDOS.

2. Esto entra a la linea de comando de MySQL.

3. Esto le dice a MySQL que queremos trabajar con la base de datos eliax.

4. Este es el comando que hace toda la magia. Noten que esas 4 lineas son en realidad una sola linea (que corté para hacer más legible).

autor: josé elías

Comentarios

  • *-* excelente :D me has resulto muchos dolores de cabeza XD

  • Estoy intentando escribir como hacer el backup comprimido y el mensaje de corta.

    Es realmente la forma más sencilla y rápido de hacerlo. Ya como extra vale la pena comprimir el resultado del backup, sobre todo si se estará haciendo de forma periódica.

    Es tan fácil como esto:

    mysqldump -u root -p eliax | gzip -9 > eliax.sql.gz

  • Buen articulo, chevere seria articulos de estos mas seguido, en una seccion "tecnica"

    • Hay otros artículos similares en eliax. Ver la lista al final del artículo en la sección de "Previamente en eliax" ;)

  • Eliax, tengo entendido que este método funciona perfectamente con tablas MyISAM, de hecho lo estoy utilizando en varios servidores en producción. Pero hay que tener cuidado con las tablas InnoDB... Ya que seguramente no se puedan ejecutar transacciones sobre una tabla que se esta realizando el backup (se bloquean), sobre todo OJO con las tablas grandes.
    Este punto no lo tengo bien visto, pero vale la aclaración.
    Hay gente que utiliza la opción --lock-tables=false pero desconozco que pasaría si se ejecutan transacciones en el medio de un backup de una tabla grande.

    • Gabriel,

      Muy buena tu observación.

      Con respecto a tablas InnoDB, depende de lo que quieras hace. Según la documentación oficial, en este enlace...

      http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

      ...puedes tener control sobre tablas InnoDB con las flags --single-transaction (y ver otras opciones en la documentación), que te pueden garantizar una copia de los datos justo en el momento del timestamp en donde iniciaste el proceso de respaldo.

  • que paso con facebook home? nada que ver con el tema pero. . .

    • Tal cual como escribí en mi editorial al respecto, ha sido visto como un Troyano por muchos fabricantes, y le está costando ahora a Facebook el poder convencer a fabricantes (particularmente como a Samsung) de que lo integren en sus equipos...

      Leer el editorial aquí: http://www.eliax.com/index.cfm?post_id=10126

      • Opinión de PRISM,que es extraño no haber oido ninguna opinión tuya.

    • Sorry por salir de tema también... pero Facebook Home fue un fracaso, tanto así que AT&T tuvo que recortar el precio del HTC First para limpiar stock, que era el móvil que representaba al Facebook Home.

      • justamente por eso hice el comentario, leí que solo vendieron 15000 unidades, y en cuanto a la descarga de la aplicación hay muchas quejas

  • Como usuario de MySQL Workbench, solo queria añadir que desde ahi se puede invocar el comando de manera visual, muy muy facil, seleccionando esquemas o tablas y la lista de opciones (no el 100%) aparecen como checkboxes.

  • Yo aporto un par de cosas:

    Primero: si queremos hacer una copia de una base de datos en un horario en que se está usando, aparte de que no es muy recomendable, tenemos el problema de que este comando bloquea las tablas mientras son volcadas. Podemos evitarlo (tomando en cuenta los posibles inconvenientes de copiar cosas mientras estan siendo modificadas) agregando --single-transaction:

    mysqldump --single-transaction -u root -p eliax

    Por otro lado, quizás queramos crear un script que haga el proceso automáticamente, para eso se puede indicar la clave en la línea de comandos cambiando el -p por --password=. Si hacen eso tomen el recaudo de quitar el permiso de lectura a los usuarios que no deban saber la clave. Algo así como chmod 700

    Tengo también un script que hace backups periódicos manteniendo automáticamente una determinada cantidad de copias anteriores y borrando las más viejas. Si a alguien le hace falta que lo pida.

    • Fuera interesante si nos pudieras proveer el script completo amigo estaría muy agradecido...

  • fichero.sql

  • Excelente....nos hacen falta mas artículos técnicos por estos lados...gracias

  • Tengo una BD de 6GB y este proceso duro 18 hora, es normal? pienso que fue demasiado tiempo para restaurar todo? son miles de registro?
    Gracias

    • Eso es demasiado tiempo. Eso podría significar una de estas cosas:

      1. Hiciste el respaldo por red. En tal caso, lo que debes hacer es respaldar los datos al mismo disco local en donde está la base de datos.

      2. Tienes un máquina extremadamente vieja con discos súper lentos (lo menos probable).

      3. Tus discos están a punto de fallar, por lo que esa es una muy buena razón para que hagas tu respaldo de datos.

Añadir Comentario

tu nombre
tu email
(opcional)
web personal
(opcional)
en respuesta a...
comentario de caracteres máximo
7 + 9 = requerido (control anti-SPAM)
 

"Al pricipio de la charla daba la sensación de que tenía una explosión de información a punto de estallar intentando contenerla. Sin duda la mejor charla que escuché en el día."

por "Federico" en oct 2, 2012


en camino a la singularidad...

©2005-2014 josé c. elías
todos los derechos reservados
como compartir los artículos de eliax