Copias de seguridad de bases de datos MySQL con Python

Copias de seguridad de bases de datos MySQL con Python

Copias de seguridad de bases de datos MySQL con Python - 100 días de Python #21

En este tercer capítulo del bloque de bases de datos con MySQL y Python, seguimos avanzando con nuevos temas. En este capítulo, te mostraré como hacer todo esto:

  • Crear backup (copia de seguridad) de bases de datos MySQL desde Python, Workbench y la consola de Windows.
  • Restaurar backup de MySQL desde Python, Workbench y la consola de Windows.

Lo primero que haremos antes de estar manipulando una base de datos, será una copia de seguridad, de forma que si cometemos un error y alteramos de forma indeseada los datos, podamos restaurarla.

Crear backups de bases de datos MySQL desde Workbench

Empecemos con la forma más fácil de generar copias de seguridad de bases de datos MySQL. Workbench facilita mucho el trabajo a la hora de realizar esta acción.

Vayamos a Workbench. En el menú de opciones, ves a "Server > Data Export".

crear copias de seguridad MySQL

Primero, selecciona una o varias bases de datos a exportar. En el visor de la derecha (haz click en una base de datos para que se muestre) te saldrán las tablas de la base de datos. Puedes dejarlas todas o solo alguna.

Si tuviéramos procedimientos almacenados, eventos y/o triggers, hay que marcar las opciones de la sección "Objects to Export".

En la sección "Export Options" tenemos la posibilidad de crear una carpeta para exportar un archivo .sql para cada tabla (opción "Export to Dump Project Folder") o bien, exportar todo en un archivo con "Export to Self-Contained File".

Selecciona una ruta y haz click en el botón "Start Export".

Exportar bases de datos MySQL Workbench

En mi caso, ha generado en la unidad D:\, una carpeta llamada "copias-SQL". Dentro de esta, una carpeta llamada "world" y en esta, los siguientes archivos .sql:

archivos sql

Ahora, abre uno de estos archivos con MySQL Workbench, el bloc de notas, Visual Studio Code o con cualquier otro programa capaz de editar texto plano.

En este archivo, están todos los datos de la tabla "city" de la base de datos "world" y el código SQL necesario para realizar en un futuro, su importación.

archivo sql en Workbench

Importar archivos SQL en el servidor MySQL desde Workbench

Lo siguiente que haremos, será probar la importación de los archivos .sql exportados desde MySQL Workbench. Para esto, debemos eliminar primero la base de datos del servidor. Asegúrate antes de borrarla, que tengas los archivos exportados del paso anterior.

Ten cuidado con esta opción, ya que está muy a mano, y borra entera la base de datos.

Eliminar base de datos desde MySQL Workbench

Te pedirá confirmar:

drop now workbench

Una vez eliminada, ya no te saldrá en la sección "Schemas" de MySQL Workbench.

Lo primero en la restauración, será crear la base de datos. Esto se puede hacer de diversas formas, pero lo vamos a hacer fácil. Crea una base de datos llamada exactamente "world".

Crear base datos desde Workbench
nombre base de datos

Al hacer click en "Apply", saldrá esta ventana. Haz click en "Apply", de nuevo.

copias de seguridad MySQL

Para realizar la importación de la copia de seguridad, vamos a ir a la opción "Server > Data Import".

Importar bases de datos Mysql

Y la ventana que te salga a continuación, haz click en "Finish".

Mysql Bases de datos

Ahora, llega el momento de importar los archivos SQL.

Si hiciste la exportación como yo, en múltiples archivos, hazlo así:

Importaciones mysql workbench

Selecciona "Import from Dump Project Folder" y pon la ruta a la carpeta que contiene los archivos .sql.

Luego, deja seleccionada la base de datos con todas sus tablas.

Finalmente, haz click en "Start Import".

Si exportas de la otra manera, con todo en un mismo archivo, utiliza la opción "Import from Self-Contained File".

Exportar bases de datos MySQL desde cmd (consola)

Si sabes lo suficiente, puedes trabajar completamente con el servidor MySQL sin la necesidad absoluta de MySQL Workbech. Es más, podrías trabajar incluso con un sistema operativo que solo tenga interfaz de comandos.

Veamos otra forma de crear copias de seguridad de tus bases de datos, es mediante el uso de la consola. Teclea "cmd" en el inicio de Windows y abre el "Símbolo del sistema".

En la consola deberás ir a la ruta donde está "mysqldump.exe". Si hiciste conmigo la instalación igual, deberías tenerlo esta ruta:

C:\Program Files\MySQL\MySQL Workbench 8.0\mysqldump.exe
Exportar bases de datos MySQL desde la consola

Debemos desplazar prompt de la consola a esta carpeta. Lo puedes hacer así:

Si estás en una unidad diferente a la "C:\", haz como yo. Pon "C:" y pulsa enter.

consola de windows

Ahora, con el comando cd copiamos la ruta anterior sin el "mysqldump.exe".

cd C:\Program Files\MySQL\MySQL Workbench 8.0\
ruta mysqldump y mysql

Ahora si, vamos a escribir en esta ruta el comando necesario para que "mysqldump.exe" haga la exportación de la base de datos.

Antes de escribirlo, crea (si quieres) una carpeta donde guardarás la copia. Hazla desde la interfaz gráfica de Windows o como yo, desde la consola.

mkdir D:\copias-SQL\consola\
comandos mysql consola windows

Ahora, ejecuta este comando:

mysqldump --user=root --password=programacionfacil --databases world > D:\copias-SQL\consola\world.sql

Con esto, ya deberías tener la copia de seguridad creada en la carpeta especificada.

Con mysqldump indicamos que queremos ejecutar ese programa desde la consola. Después, le pasamos una serie de argumentos, tal y como hacemos con las funciones de Python.

El argumento --user, sirve para especificar el nombre de usuario del servidor MySQL.

Con --password, indicamos la contraseña para ese usuario.

En --databases, estoy indicando que quiero hacer la copia de la base de datos "world".

Finalmente, con el símbolo mayor qué, indico la ruta donde quiero la salida de este comando, ya que si no la especifico, se hace el "dump sql" que significa volcado sql, pero no se guarda en ningún lado. Queda solo en la consola como si fuera un print() en Python.

Eliminar bases de datos MySQL desde la consola

Asegúrate que tienes copia para restaurar y elimina una vez más la base de datos "world".

Crear base de datos MySQL desde la consola

Puesto que la hemos eliminado, tendremos que crear la base de datos de nuevo. Ya que estamos en la consola, hagámoslo desde aquí.

Iniciar sesión en el servidor MySQL desde la consola

Inicia sesión desde la consola a tu servidor MySQL.

Para ello, pon el siguiente comando:

mysql -h localhost -u root -p

Lo que hace esto, es ejecutar el archivo "mysql.exe", y le da los valores de host (-h), usuario (-u) y la contraseña con -p.

Cuando ejecutes esto, te pedirá la contraseña del usuario root. Ponla y pulsa la tecla ENTER.

mysql consola windows

Si te fijas, el prompt de la consola ha cambiado. Ahora, estás dentro del servidor MySQL y puedes ejecutar comandos directamente sobre él desde la consola.

¿Recuerdas como se creaba una base de datos con una instrucción SQL? Esta de aquí:

Sencillamente, escríbelo y pulsa la tecla ENTER.

CREATE DATABASE world;
crear base de datos mysql desde la consola

Te diría que fueras a Workbench, a mirar si ya está la base de datos. Sin embargo, vamos a consultar al servidor, qué bases de datos tiene para ver si está "world". Así que no necesitamos ni siquiera Workbench.

La instrucción SQL, ya te la mostré. Es esta:

SHOW DATABASES;
mostrar tablas mysql en la consola

Cerrar sesión en el servidor MySQL desde la consola

Lo siguiente, es salir de la sesión con el servidor:

exit

La respuesta que te da el servidor es cuanto menos graciosa. Nos dice adiós con un "Bye".

exit mysql servidor

Una vez tenemos el prompt normal de la consola de Windows, vayamos a proceder con el siguiente paso.

Importar bases de datos MySQL desde la consola

Ahora, deberás poner el siguiente comando para realizar una importación desde la consola a tu servidor MySQL.

mysql --user=root --password=programacionfacil < D:\copias-SQL\consola\world.sql

Recibir tablas de bases de datos MySQL desde la consola

Ahora, vamos a entrar de nuevo en el servidor. Así consultaremos el contenido de la base de datos y podremos comprobar que la importación se ha efectuado correctamente.

mysql -h localhost -u root -p

Seleccionar base de datos MySQL desde la consola

Lo que haremos a continuación, será seleccionar la base de datos sobre la que queremos trabajar.

USE world
utilizar base de datos en la consola

El servidor dirá que la base de datos se ha cambiado. Ya estamos trabajando directamente sobre la base de datos "world".

Para obtener todas las tablas que tiene una base de datos en la consola, debes poner este comando SQL:

SHOW TABLES;
mostrar tablas mysql

Listo. Vemos que las tablas se han importado. Pues lo que voy a hacer, es mostrar algunos registros filtrados mediante una consulta en la tabla "city".

El problema es que no sé qué campos tiene la tabla para poder filtrar por uno de ellos.

Obtener los campos de una tabla MySQL

No te preocupes. Todo se puede saber. Ejecuta el siguiente comando para obtener los campos de una tabla:

SHOW COLUMNS FROM city;
mostrar columnas tabla sql

Bien. Ahora ya se los campos que tiene la tabla "city". Pues bien, viendo que tiene un campo numérico (int) llamado población, voy a hacer una consulta SQL que me devuelva todos los registros de ciudades cuyo valor en población sea superior a 1.000.000 habitantes. Esto se consigue con la siguiente consulta:

SELECT * FROM city WHERE population > 1000000 ORDER BY population DESC;

Aparecerán todas las coincidencias con la consulta (filtro) escrita.

Le he dicho al servidor que seleccione (SELECT) todos los regitros (*) de la tabla "city" donde (WHERE) el campo "population" sea mayor que 1.000.000 (> 1000000) y que el resultado de esto, lo ordene por (ORDER BY) el campo "population" en orden descendente, de mayor a menor (DESC).

consultas sql desde la consola

Abajo del todo, te dirá cuantos registros se han obtenido.

registros sql

Crear copia de seguridad MySQL desde Python

Por fin, ahora que ya sabes bastante como funcionan los backups de bases de datos MySQL, ha llegado el momento de realizar una exportación de la base de datos desde Python.

Te lo he sinplificado mucho para que sea más sencillo de entender.

Primero, vamos a importar dos módulos, "subprocess" y "getpass" (este segundo, opcional).

La línea 5, la explicaré más adelante con todo detalle. De momento, que sepas que lo que hace es crear un archivo en el sistema en el que poder escribir. Este archivo se llamará world.sql y se guardará en la raíz de "D:\" según la ruta que le he indicado.

Con "subproccess.Popen()" se le pasa el mismo comando que hemos utilizado en la consola de Windows.

En la parte de la contraseña, le he formateado directamente el uso de "getpass()". Recuerda que "getpass()" ocultaba lo que escribíamos en la consola. Lo utilizamos para las contraseñas de un ejercicio anterior.

#importaciones
import subprocess
import getpass

with open('D:/world.sql', 'w') as out:
    subprocess.Popen(f'mysqldump --user=root --password={getpass.getpass()} --databases world', shell=True, stdout=out)

En teoría, ya está, pero el problema, es que para que mysqldump se ejecute, debemos tener el prompt de la consola en esta carpeta, que es donde está:

C:\Program Files\MySQL\MySQL Workbench 8.0\

La imagen muestra el error que produce fuera de la ruta y la solución accediendo a ella:

Consola python mysql

En esta ruta, si ejecutas el programa con el código anterior, te creará el archivo en la ruta especificada. Si se produce un error, por ejemplo, contraseña incorrecta. Se genera un archivo vacío, de modo, que revisa lo que te devuelve el programa. El archivo generado debería ocupar unos 238 KB.

No obstante, puedes querer dejar una ruta fija para no tener que cambiarla en la consola. Lo puedes hacer así:

Fíjate que he tenido que poner unas comillas dobles en "C:/Program Files/MySQL/MySQL Workbench 8.0/". Esto lo he hecho para incrustar la ruta, ya que con los espacios en blanco, da error (ejemplo Program Files/).

with open('D:/world.sql', 'w') as out:
    subprocess.Popen(f'"C:/Program Files/MySQL/MySQL Workbench 8.0/"mysqldump --user=root --password={getpass.getpass()} --databases world', shell=True, stdout=out)

Lo siguiente que voy a realizar, es eliminar de nuevo la base de datos. Esta vez, hazlo desde donde quieras, ya te he enseñado tres formas. Así, después haremos la importación, pero desde Python.

Importar bases de datos MySQL desde Python

La forma de importar bases de datos MySQL desde Python, es muy similar a la exportación. Usaremos también "subprocess.Popen()".

subprocess.Popen(f'"C:/Program Files/MySQL/MySQL Workbench 8.0/mysql" --user=root --password={getpass.getpass()} < D:/world.sql', shell=True)

¿Para qué sirve el módulo subprocess de Python?

El módulo subprocess de Python sirve para ejecutar programas externos a Python y poder ver y utilizar sus resultados en el propio Python. No vamos a profundizar mucho hoy en este módulo, pero a groso modo, sirve para eso, pues eso es lo que haremos con este Popen() ejecutar el comando de manera externa a Python.

La opción "shell" habilitada con un valor de "True" permite ejecutar todo el comando SQL con las opciones, puesto que si lo ponemos en "False" o lo omitimos, ejecutará en el caso de mi código "mysql" y nada más. Al ejecutar "mysql.exe", nos muestra información sobre la aplicación, pero no completa la acción de todo el conjunto de argumentos que le pasemos.

Por lo del argumento "stdout = out", si no lo especificamos, vamos a obtener la salida en la consola, pero no la exportaremos al archivo .sql. Verás que se genera igual debido a "with open", pero como archivo vacío.

No te pierdas nada sobre Python. Tengo mucho contenido en este enlace.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

curso Java Entrada anterior Operadores de comparación de Java
curso Java Entrada siguiente Los bloques de código e indentaciones en Java