Migrando MariaDB. "Modo mantenimiento"
Puede suceder que tengamos en uno de nuestros servidores de bases de datos de MariaDB algún esquema con datos que necesitemos migrar a otro servidor, ya sea MySQL o MariaDB.
Si se trata de datos que están siendo usados por otros usuarios o aplicaciones, necesitaremos evitar el acceso a cualquiera de las tablas del esquema mientras realizamos la migración, para conseguir un dump limpio y consistente de los datos y no llevarnos datos con transacciones incompletas.
Lo más normal es que tengamos securizados los accesos a los esquema de datos, mediante roles y permisos a ciertos usuarios, podeis repasar como se realiza el ajuste de los roles en el artículo:
Luis GuLo
En nuestro caso queremos bloquear los accesos, pero permitir la realización del dump.
Cuando estamos migrando un esquema en MariaDB lo ideal es aislar el esquema temporalmente sin afectar el acceso administrativo necesario para hacer la exportación.
Guía para bloquear accesos sin impedir el dump
1. Revocar privilegios de usuarios no deseados
Aunque se hayan desactivado los roles, los usuarios pueden seguir teniendo privilegios directos.
La acción correcta es revocar temporalmente sus accesos:
REVOKE ALL PRIVILEGES ON esquema_a_migrar.* FROM 'usuario_app'@'host';
FLUSH PRIVILEGES;
Esto nos asegura que no puedan leer ni escribir en ese esquema, aunque tengan sesiones activas.
2. Cerrar sesiones activas (opcional pero recomendable)
Podemos localizar y cerrar las conexiones activas que estén usando ese esquema.
Localizar conexiones:
SELECT ID, USER, HOST, DB, COMMAND FROM information_schema.PROCESSLIST WHERE DB = 'esquema_a_migrar';
Matar las conexiones:
KILL <ID>;
Este paso hay que realizarlo con cuidado, ya que si matamos algun procesos crítico en ejecución podemos crear una corrupción de datos o de algún esquema de alguna tabla.
3. Bloquear el esquema con privilegios mínimos
Una solución más radical sería revocar todos los privilegios y dejar solo acceso al usuario que hará el dump del esquema:
REVOKE ALL PRIVILEGES ON esquema_a_migrar.* FROM PUBLIC;
GRANT SELECT, LOCK TABLES ON esquema_a_migrar.* TO 'usuario_dump'@'host';
FLUSH PRIVILEGES;
4. Realizar el dump
Una vez que ya no tenemos conexiones podemos usar el comando mysqldump con el usuario autorizado:
mysqldump -u usuario_dump -p --single-transaction esquema_a_migrar > esquema_a_migrar.sql
La opción--single-transactiones muy importante si estamos usando InnoDB.
Este parametro evita bloqueos y nos garantiza consistencia de los datos a exportar.
5. Restaurar privilegios después del dump
Si queremos dejar el esquema accesible tras realizar el dump, deberemos volver a asignar los privilegios originales a los usuarios y roles.
Exportar e Importar privilegios de un esquema
Si queremos realizar el dump quitando privilegios y posteriormente restaurarlos, tienes que saber que MariaDB no tiene un comando directo para exportar privilegios.
Sin embargo se pueden reconstruir manualmente con una consulta sobre information_schema, exportarlo a un fichero y luego posteriorme usar ese fichero para importar y regenerar esos privilegios. El proceso sería el siguiente:
1. Generar los GRANTs manualmente
Puedes usar la siguiente consulta para obtener los privilegios asignados a cada usuario sobre un esquema específico:
SELECT CONCAT(
'GRANT ', PRIVILEGE_TYPE, ' ON ', TABLE_SCHEMA, '.* TO \'', GRANTEE, '\';'
) AS grant_statement
FROM information_schema.schema_privileges
WHERE TABLE_SCHEMA = 'esquema';
Esto sacará una lista de sentencias GRANT.
Copia el resultado en un fichero con extensión .sql
2. Exportar a fichero
Crea un fichero que se llame extraer_privilegios_esquema.sql y copia el contenido anterior. sustituye esquema por el nombre real del esquema de la base de datos.
- Opción A: Desde la consola en modo CLI, con el cliente de MariaDB, puedes redirigir la salida a un fichero:
mysql -u root -p -e "extraer_privilegios_esquema.sql" > privilegios_esquema.sql
- Opción B: O desde dentro de la shell de mysql, lo puede hacer activando el modo de redirección:
-- Activamos redirección a un fichero externo
tee privilegios_esquema.sql;
-- Aqui ejecutamos la consulta .sql para extraer los privilegios del esquema
SOURCE extraer_privilegios_esquema.sql;
-- Desactivamos la redirección
notee;
Restaurar los privilegios en el nuevo servidor
Una vez finalizada el dump/migración del esquema, simplemente ejecutaremos el fichero .sql y restauraremos todos los privilegios que habíamos revocado o eliminado.
mysql -u root -p < privilegios_esquema.sql
Nota: Esto te permite no solo llevarte los datos sino también los privilegios entre un servidor y otro.
Extra: Exportar también los usuarios
Si los usuarios no existen en el nuevo servidor, los podemos exportar de la siguiente forma:
SELECT CONCAT(
'CREATE USER \'', user, '\'@\'', host, '\' IDENTIFIED BY PASSWORD \'', authentication_string, '\';'
)
FROM mysql.user
WHERE user IN ('usuario1', 'usuario2');
En la lista de 'usuario1', 'usuario2' , hay que poner los usuarios que querramos exportar.
Es importante revisar las políticas de seguridad del servidor destino, antes de restaurar contraseñas que estén en texto plano o hashes para evitar problemas en la importación.
Extra de SoloConLinux: "Modo Mantenimiento"
En MariaDB no tenemos nada similar a un "modo mantenimiento".
Se podría simular mediante bloqueo del acceso mediante reglas del firewall o rechazando la conexiones temporalmente.
Sin embargo, dado que solo queremos bloquear un esquema y que los otros esquemas de las bases de datos sean accesibles, podemos fabricar nuestro propio "modo mantenimiento" para MariaDB.
Para ello podemos crear un Trigger para realizar este bloqueo.
-- SQL de Modo Mantenimiento de una Tabla el Esquema
CREATE TRIGGER bloqueo_insert
BEFORE INSERT ON esquema.tabla1
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Modo mantenimiento activo';
Trigger de bloque "modo mantenimiento"
Te habrás dado cuenta que este bloqueo sólo se realiza sobre una tabla, ya que los Trigger solo afectan a tablas, habrá que realizar un bloqueo para cada una de las tablas del esquema lo cual no parece una tarea sencilla...
Vamoa a ver como podríamos hacerlo mediante con conjunto de instrucciones SQL, lo realizaremos con un script dinámico que nos lo automatice para ejecutarlo en MariaDB.
Con los siguientes script SQL, vamos a crear y eliminar triggers de bloqueo sobre todas las tablas de un esquema específico.
Esto nos permite simular un “modo mantenimiento” que impide que se realicen operaciones de tipo INSERT, UPDATE y DELETE, pero permite SELECT.
Script para generar triggers de bloqueo
(Modo Mantenimiento ON)
Este script crea los comandos CREATE TRIGGER para todas las tablas del esquema que desees.
Puedes ejecutarlo desde el cliente mysql y guardar la salida a un fichero .sql.
-- ATENCION. Cambiar 'TuEsquema' por el nombre de tu esquema
SET @esquema = 'TuEsquema';
SELECT GROUP_CONCAT(trigger_stmt SEPARATOR '\n\n') AS script_creacion
FROM (
SELECT CONCAT(
'CREATE TRIGGER bloqueo_', TABLE_NAME, '_insert BEFORE INSERT ON ', TABLE_SCHEMA, '.', TABLE_NAME,
' FOR EACH ROW SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = \'Modo mantenimiento activo: INSERT bloqueado\';'
) AS trigger_stmt
FROM information_schema.tables
WHERE TABLE_SCHEMA = @esquema AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONCAT(
'CREATE TRIGGER bloqueo_', TABLE_NAME, '_update BEFORE UPDATE ON ', TABLE_SCHEMA, '.', TABLE_NAME,
' FOR EACH ROW SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = \'Modo mantenimiento activo: UPDATE bloqueado\';'
)
FROM information_schema.tables
WHERE TABLE_SCHEMA = @esquema AND TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONCAT(
'CREATE TRIGGER bloqueo_', TABLE_NAME, '_delete BEFORE DELETE ON ', TABLE_SCHEMA, '.', TABLE_NAME,
' FOR EACH ROW SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = \'Modo mantenimiento activo: DELETE bloqueado\';'
)
FROM information_schema.tables
WHERE TABLE_SCHEMA = @esquema AND TABLE_TYPE = 'BASE TABLE'
) AS triggers;
Copiar la salida en un fichero, por ejemplo mantenimiento_on.sql y ejecutalo como script para activar el bloqueo y el modo mantenimiento.
Script para eliminar los triggers
(Modo Mantenimiento OFF)
Este script crea los comandos DROP TRIGGER para poder eliminar todos los triggers de bloqueo de nuestro esquema:
-- ATENCION. Cambiar 'TuEsquema' por el nombre de tu esquema
SET @esquema = 'TuEsquema';
SELECT GROUP_CONCAT(CONCAT('DROP TRIGGER IF EXISTS ', TRIGGER_NAME, ';') SEPARATOR '\n') AS script_eliminacion
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA = @esquema AND TRIGGER_NAME LIKE 'bloqueo_%';
Igual que hiciste antes, simplemente copia la salida a un fichero mantenimiento_off.sql y ejecutalo para desactivar el modo mantenimiento.
