Recuperación de MariaDB - Tablas del Sistema Corruptas

Incidente Crítico
Fecha: 17 de octubre de 2025
Sistema: MariaDB 11.8.3-MariaDB-ubu2404
Severidad: ⚠️ Crítica – Servicio no inicia
Nivel técnico: Administrador de Sistemas Avanzado (leer notas finales)
1. Resumen del Problema
El servidor MariaDB no inicia debido a corrupción de tablas del sistema.
Error principal: Tablas de sistema implicadas en el fallo múltiple
2025-10-17  6:27:08 3 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:08 3 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table './sitelight2_ma3/ma_api_token_table' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/column_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/columns_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/event'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/func'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/global_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_category'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_keyword'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_relation'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_topic'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/index_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/proc'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/procs_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/proxies_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/roles_mapping'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/tables_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_leap_second'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_name'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_transition'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_transition_type'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/global_priv'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:24 43 [Note] Found 15228 of 2114 rows when repairing './sitelight2_ma3/ma_api_token_table'
2025-10-17  6:29:29 150 [Note] Found 3138 of 2928 rows when repairing './sitelight2_matest/ma_bug_file_table'
Tablas afectadas:
mysql.plugin
mysql.servers
mysql.db
mysql.proxies_priv
...
2. Estado del Servicio
Active: failed (Result: exit-code)
Main PID: 1101684 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
3. Análisis Preliminar
3.1. Secuencia del log:
Inicio normal de InnoDB: buffer pool y undo tablespaces OK
Resize de redo log: 96MB → 16MB (LSN=1718365178)
Socket creado en 0.0.0.0:3306
Falla: Error 144 al abrir tablas de privilegios
MariaDB aborta
3.2. Características del entorno:
Motor tablas sistema: InnoDB (no MyISAM)
Buffer pool: 64MB
Instrucciones CPU: AVX512
I/O: io_uring
Rollback segments: 128 (en 3 undo tablespaces)
3.3. Causas probables:
Apagado inesperado del sistema
Fallos de disco (errores I/O)
Falta de espacio en disco (Confirmado)
Corrupción de memoria
Bug en proceso de resize de redo log
4. Plan de Corrección
FASE 1: Diagnóstico Inicial
# 1. Chequear integridad del sistema
df -h /var/lib/mysql
dmesg | grep -i "error\|i/o"
smartctl -H /dev/sda
# 2. Revisar logs
tail -200 /var/log/mysql/error.log
# 3. Confirmar tipo de archivos
cd /var/lib/mysql/mysql/
ls -lh *.ibd | head -20
FASE 2: Backup Preventivo
Importante: Realizar backup completo antes de realizar cambios.
Opción A local si hay espacio en disco
Crea archivo de recuperación:
# 1. Verificar espacio disponible
df -h /var/lib/
du -sh /var/lib/mysql/
# 2. Realizar backup
cd /var/lib/
tar -czf mysql-backup-$(date +%Y%m%d-%H%M%S).tar.gz mysql/
# 3. Verificar integridad del backup
tar -tzf mysql-backup-*.tar.gz | head -20
Se entiende que el motor de bases de datos MySQL|MariaDB no esta activo
Opción B: Backup Remoto on-the-fly (Espacio local insuficiente)
**Escenario: **Incidente causado por falta de espacio en disco, ahora solventado pero insuficiente para crear backup local.
Requisito: SSH configurado con autenticación por llave pública.
Comando único (one-liner):
systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"
Variantes
# Con progreso visual (requiere pv)
systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | pv | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"
# Con compresión paralela (requiere pigz, más rápido)
systemctl stop mariadb && cd /var/lib/ && tar -cf - mysql/ | pigz | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"
# Con SSH optimizado para transferencia rápida
systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | ssh -c aes128-gcm@openssh.com -o Compression=no usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"
Verificación del remoto
# Listar archivo creado
ssh usuario@servidor-remoto "ls -lh /backups/mysql-*"
# Verificar integridad
ssh usuario@servidor-remoto "tar -tzf /backups/mysql-*.tar.gz | head -20"
FASE 3: Arranque en Modo Recovery (innodb_force_recovery)
c# Crear archivo de configuración de recuperación
cat > /etc/my.cnf.d/recovery.cnf << 'EOF'
[mysqld]
innodb_force_recovery = 1
EOF
# Intentar inicio
systemctl start mariadb
systemctl status mariadb
Aumenta el valor de 
innodb_force_recovery (1→6) si sigue fallando.
Referencia rápida:
Valor
Función
1
Ignora páginas corruptas
2
Deshabilita hilos en background
3
No ejecuta rollback de transacciones
4
No merge de insert buffer
5
No escaneo undo log
6
No aplica redo log (Modo Solo Lectura Crítico)
FASE 4: Reparación de Tablas
Con servidor en recovery se aconseja el script si hay muchas tablas
Se puede modificar para añadir las tablas que pudieran faltar en el ejemplo
#!/bin/bash
# Script de reparación masiva de tablas MariaDB
# Usar con MariaDB ya iniciado en modo recovery
# reparir_mariadb.sh
set -e
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color
echo -e "${GREEN}=== Script de reparación masiva MariaDB ===${NC}\n"
# Verificar que MySQL esté corriendo
if ! mysqladmin ping &>/dev/null; then
    echo -e "${RED}ERROR: MariaDB no está corriendo${NC}"
    echo "Asegúrate de que esté iniciado en modo recovery"
    exit 1
fi
# Conectar a MySQL y obtener lista de todas las tablas corruptas
echo -e "${YELLOW}Identificando tablas corruptas...${NC}"
# Crear script SQL temporal
cat > /tmp/repair_all.sql << 'SQLEOF'
-- Seleccionar base de datos mysql
USE mysql;
-- Intentar reparar todas las tablas del sistema
REPAIR TABLE column_stats;
REPAIR TABLE columns_priv;
REPAIR TABLE db;
REPAIR TABLE event;
REPAIR TABLE func;
REPAIR TABLE general_log;
REPAIR TABLE global_priv;
REPAIR TABLE gtid_slave_pos;
REPAIR TABLE help_category;
REPAIR TABLE help_keyword;
REPAIR TABLE help_relation;
REPAIR TABLE help_topic;
REPAIR TABLE host;
REPAIR TABLE index_stats;
REPAIR TABLE innodb_index_stats;
REPAIR TABLE innodb_table_stats;
REPAIR TABLE plugin;
REPAIR TABLE proc;
REPAIR TABLE procs_priv;
REPAIR TABLE proxies_priv;
REPAIR TABLE roles_mapping;
REPAIR TABLE servers;
REPAIR TABLE slow_log;
REPAIR TABLE table_stats;
REPAIR TABLE tables_priv;
REPAIR TABLE time_zone;
REPAIR TABLE time_zone_leap_second;
REPAIR TABLE time_zone_name;
REPAIR TABLE time_zone_transition;
REPAIR TABLE time_zone_transition_type;
REPAIR TABLE transaction_registry;
REPAIR TABLE user;
-- Mostrar estado
SELECT 'Reparación de tablas del sistema completada' AS Status;
SQLEOF
# Ejecutar reparación
echo -e "${YELLOW}Ejecutando reparación de tablas del sistema...${NC}"
mysql -u root < /tmp/repair_all.sql 2>&1 | tee /tmp/repair_output.log
# Reparar tablas de todas las bases de datos
echo -e "\n${YELLOW}Obteniendo lista de todas las bases de datos...${NC}"
DBS=$(mysql -u root -Bse "SHOW DATABASES;" 2>/dev/null | grep -v -E "^(information_schema|performance_schema|sys)$")
for db in $DBS; do
    echo -e "\n${GREEN}=== Procesando base de datos: $db ===${NC}"
    # Obtener tablas de esta BD
    TABLES=$(mysql -u root -D "$db" -Bse "SHOW TABLES;" 2>/dev/null)
    if [ -z "$TABLES" ]; then
        echo -e "${YELLOW}  No hay tablas en $db${NC}"
        continue
    fi
    for table in $TABLES; do
        echo -n "  Reparando $db.$table ... "
        # Intentar reparar
        RESULT=$(mysql -u root -D "$db" -e "REPAIR TABLE \`$table\`;" 2>&1)
        if echo "$RESULT" | grep -q "OK\|Already up to date"; then
            echo -e "${GREEN}OK${NC}"
        else
            echo -e "${RED}FAILED${NC}"
            echo "$RESULT" >> /tmp/repair_failed.log
        fi
    done
done
# Resumen
echo -e "\n${GREEN}=== Resumen de reparación ===${NC}"
echo -e "Log completo: /tmp/repair_output.log"
if [ -f /tmp/repair_failed.log ]; then
    echo -e "${RED}Tablas que fallaron: /tmp/repair_failed.log${NC}"
    echo -e "\n${YELLOW}Tablas con errores:${NC}"
    cat /tmp/repair_failed.log
else
    echo -e "${GREEN}Todas las tablas reparadas exitosamente${NC}"
fi
# Optimizar tablas después de reparar
echo -e "\n${YELLOW}¿Deseas optimizar todas las tablas? (s/n)${NC}"
read -r optimize
if [ "$optimize" = "s" ]; then
    echo -e "${YELLOW}Optimizando tablas...${NC}"
    for db in $DBS; do
        echo -e "${GREEN}Optimizando base de datos: $db${NC}"
        TABLES=$(mysql -u root -D "$db" -Bse "SHOW TABLES;" 2>/dev/null)
        for table in $TABLES; do
            echo -n "  Optimizando $db.$table ... "
            mysql -u root -D "$db" -e "OPTIMIZE TABLE \`$table\`;" &>/dev/null && echo -e "${GREEN}OK${NC}" || echo -e "${YELLOW}SKIP${NC}"
        done
    done
fi
echo -e "\n${GREEN}=== Proceso completado ===${NC}"
echo -e "${YELLOW}Siguiente paso: Detener modo recovery y reiniciar MariaDB normalmente${NC}"
# Limpiar
rm -f /tmp/repair_all.sql
FASE 5: Salida del Modo Recovery y Upgrade de Sistema
rm -f /etc/my.cnf.d/recovery.cnf
systemctl restart mariadb
mysql_upgrade --force -u root -p # Importante tras desastre con tablas de sistemas
systemctl restart mariadb
systemctl status mariadb
mysql -u root -e "SELECT VERSION(); SHOW DATABASES;"
5. Contingencias
En caso de no finalizar la solución propuesta es hora de tirar de backups.
A) Instancia nueva y recuperación manual
tar -czf mysql-critical-backup-$(date +%Y%m%d-%H%M%S).tar.gz /var/lib/mysql/
mv /var/lib/mysql /var/lib/mysql.corrupted
mysql_install_db --user=mysql --datadir=/var/lib/mysql
systemctl start mariadb
# Restaurar datos desde backup/ibd/dump si es posible
B) Recuperar desde backup externo
systemctl stop mariadb
rm -rf /var/lib/mysql
tar -xzf /ruta/al/backup.tar.gz -C /var/lib/
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb
6. Verificación Post-Recuperación
mysqlcheck --all-databases --check -u root -p
mysql -u root -e "USE mysql; SHOW TABLES;"
mysql -u root -e "SELECT user,host FROM mysql.user;"
tail -100 /var/log/mysql/error.log | grep -i error
Realizar prueba de conexión desde aplicaciones (paneles de control, etc)
7. Notas y Referencias Técnicas
Desde MariaDB 10.4 las tablas del sistema usan InnoDB, myisamchk ya no es válido
innodb_force_recovery es crítico en estos casos [Referencia][1]
Logs principales: 
/var/log/mysql/error.log, 
journalctl -u mariadb.service, 
/var/log/syslog
Herramientas avanzadas:
innochecksum /var/lib/mysql/mysql/db.ibd
innochecksum /var/lib/mysql/ib_logfile0
mysql -u root -e "SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE 'mysql/%';"
Notas finales
Este documento, requiere cierto grado de madurez en la administración de sistemas.
No es apto para un copy & paste
Referencias
MariaDB InnoDB Recovery
innodb_force_recovery Documentation
Repair Table
Documento actualizado: 17 de octubre de 2025
Autoría: Administración de Sistemas – Abdelkarim Mateos
Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos.
También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad.
El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris
Si necesitas soporte profesional puedes contratar con Castris soporte profesional.