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».
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».
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:
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.
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.
Te pedirá confirmar:
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».
Al hacer click en «Apply», saldrá esta ventana. Haz click en «Apply», de nuevo.
Para realizar la importación de la copia de seguridad, vamos a ir a la opción «Server > Data Import».
Y la ventana que te salga a continuación, haz click en «Finish».
Ahora, llega el momento de importar los archivos SQL.
Si hiciste la exportación como yo, en múltiples archivos, hazlo así:
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
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.
Ahora, con el comando cd copiamos la ruta anterior sin el «mysqldump.exe».
cd C:\Program Files\MySQL\MySQL Workbench 8.0\
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\
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.
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;
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;
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».
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
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;
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;
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).
Abajo del todo, te dirá cuantos registros se han obtenido.
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:
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.